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)

Nick Krasnov · Accepted Answer · 2013-10-06 17:32:08Z

8

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)

edited Oct 6, 2013 at 17:32

answered Oct 6, 2013 at 17:11

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John McDonnell Over a year ago

Unfortunatly I cant update the table.. Is it possible to put a check into the insert statement and if null update the column to ' '? as small cheat

Walter Mitty Over a year ago

No, because Oracle can't tell the difference between NULL and an empty string. The same constraint that forbids NULL is going to forbid an empty string. The closest you are going to get is to set it to a string containing a single blank.

veljkoz · Accepted Answer · 2013-10-06 19:06:27Z

5

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...)

answered Oct 6, 2013 at 19:06

veljkoz

8,5129 gold badges58 silver badges92 bronze badges

1 Comment

John McDonnell Over a year ago

That works, not sure why I didn't think of it sooner, was just trying to over complicate matters :(

Walter Mitty · Accepted Answer · 2013-10-06 17:10:30Z

5

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.

answered Oct 6, 2013 at 17:10

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

1 Comment

John McDonnell Over a year ago

Ok, but how does oracle allow an insert statement to work in this case? If I do an Insert, I can set the value in the column to be '', and I want to replicate this is an update statment

Dom84 · Accepted Answer · 2013-10-07 07:53:08Z

0

If you have a NOT NULL column and your going to update it NULL, maybe you need to think about your logic in your application. Otherwise, you could define a nvl(column, some_pattern) for example '???' (depending on the column's length, of course) for unknown empty Data, when you know your not allowed to insert / update NULL.

Than you have something to check for to see if something is behaving not correctly or to filter that three questionmarks in a view or something and display it as NULL again.

edited Oct 7, 2013 at 7:53

answered Oct 7, 2013 at 7:09

Dom84

8669 silver badges22 bronze badges

Comments

Shreyas · Accepted Answer · 2018-10-10 20:21:26Z

your id is not null and you want to make it null you can insert all your table rows into another table as backup and then drop your table and create it again with null able id row and insert your rows again from backup just care if you use your id as your primary key it will be not null again

another way is use ' ' as in your update and it returns just space but it is not null

CREATE TABLE BACKUP(
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));

INSERT INTO BACKUP (SELECT * FROM YourTable);

ALTER TABLE YourTable(
ID                             NUMBER(16),         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR),  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16),         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR));

INSERT INTO YourTable (SELECT * FROM BACKUP);

and now use your query

Collectives™ on Stack Overflow

Oracle SQL Updating a NOT NULL column with an empty string

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related