CODEWITHSUNDEEP
sqloracle-databaseoracle11g
touchchandra
touchchandra

Reputation: 1556

In Oracle, How to handle NULL values when inserted to a Table

I have one table, which has a filed. When I insert in that table with the Empty value like '' or null, it should get converted to 'DUMMY-VALUE'.

--Have one table;
CREATE TABLE TEST  ( FIELD1 VARCHAR2(50) );

--When I insert ''
INSERT INTO TEST(FIELD1) VALUES('');

SELECT * FROM TEST
--Expected Result 'DUMMY-VALUE' but not NULL

I can apply NVL('','DUMMY-VALUE') in INSERT statement but I am allowed to change CREATE statement only. For Now, I am handing this with TRIGGER but , wondering if there is any alternative in 11g however I know about DEFAULT ON NULL for oracle 12c.

Upvotes: 0

Views: 1787

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try this:

CREATE TABLE TEST (FIELD1 VARCHAR2(50) DEFAULT 'DUMMY-VALUE');

then use the DEFAULT keyword in the INSERT statement:

INSERT INTO TEST (FIELD1) VALUES (DEFAULT);

SQLFiddle here

Share and enjoy.

Upvotes: 2

CompEng
CompEng

Reputation: 7376

You can do like this:

create table TEST  (FIELD1 VARCHAR2(50) default 'DUMMY-VALUE'  );

and when you want to insert you should insert without that field if the values is NULL or empty

Upvotes: 2

Related Questions