Oracle SQL Updating a NOT NULL column with an empty string

Question

I am having an issue writing an SQL Update statement, where I need to update a not null field with an empty string.

UPDATE channel_mgmt.channels
    SET registered_address_id=p_address_id
        ,vendor_id=p_spc_code
     WHERE id=v_channel_id;

In this case, p_spc_code, can be set to '', and I am running into a SQL error when I run this:

Error report:
ORA-01407: cannot update ("CHANNEL_MGMT"."CHANNELS"."VENDOR_ID") to NULL
ORA-06512: at line 8973
 01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

Any ideas how I can get around this? I need to use the empty string some cases, but I am unsure why oracle is complaining about a null value.

desc channel_mgmt.channels
Name                  Null     Type               
--------------------- -------- ------------------ 
ID                    NOT NULL NUMBER(16)         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR)  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16)         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR)

Answer 1

If there is a NOT NULL constraint defined on a column you cannot update the table by specifying null or zero length string('') as a new value for a column. Oracle treats zero length string('' when assigned to a varchar2 column or variable) as NULL, so from the oracle's point of view, these two statements are the same set col_name = null and set col_name = ''. If you want to allow for a column to accept NULLs you need to remove not null constraint from that column if it won do any harm:

alter table <<table_name>> modify (<<column_name>> [<<data type>>] null)

Answer 2

Oracle can't distinguish between and empty string and a null value. It uses the same marker (one byte of zeroes) for both of them.

Answer 3

Have you tried using ' ' (string with one space in it)? If varchar, than I believe comparisons (for most cases, i.e. you're not specifying to compare white chars) work as expected.

What I mean that ' ' = ' ' (single and double whitespace) should evaluate to true for varchar fields.

(I don't have an instance of Oracle so I can't be sure...)