How to insert NULL in to date field Oracle SQL Developer

Question

CREATE TABLE pledge
(
    pledge_ID            NUMBER NOT NULL ,
    pledge_endDate       DATE NULL ,
    pledge_startDate     DATE NULL  ,
    pledge_amount        DECIMAL(9,2) NULL  CONSTRAINT  Currency_1322638346 CHECK (pledge_amount >= 0),
    artist_userID        NUMBER NOT NULL,
    follower_userID      NUMBER NOT NULL, 
CONSTRAINT  XPKPledge PRIMARY KEY (pledge_ID),
CONSTRAINT gets FOREIGN KEY (artist_userID) REFERENCES ArtistMember (user_ID),
CONSTRAINT makes FOREIGN KEY (follower_userID) REFERENCES FollowerMember (user_ID)
);

When I try to insert a null value I get the error below.

INSERT INTO pledge VALUES(559, 'null','1-FEB-2016', 3850, 85275, 88128);

Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error starting at line : 209 in command -
INSERT INTO pledge VALUES(559, 'NULL','1-FEB-2016', 3850, 85275, 88128)
Error at Command Line : 209 Column : 13
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Look at the errors again. The insert is getting ORA-00942. The ORA-00904 is coming from the create table. Fix that first. When you do want to insert null, it should be just null, not 'null' - that is a string literal, not null. And don't rely on implicit date conversions, as you are with the start date. — Alex Poole
– Alex Poole, Commented Mar 18, 2016 at 22:33

MT0 · Accepted Answer · 2016-03-18 23:17:27Z

The SQL Error: ORA-00904: : invalid identifier is probably being caused because your FOREIGN KEYs are referencing a column that does not exist - check that the column names are spelt correctly and that should solve it (and then your CREATE TABLE statement will work).

CREATE TABLE ArtistMember (
  user_ID INT PRIMARY KEY
);

CREATE TABLE FollowerMember (
  user_ID INT PRIMARY KEY
);

CREATE TABLE pledge (
    pledge_ID            INT CONSTRAINT  XPKPledge PRIMARY KEY,
    pledge_endDate       DATE NULL,
    pledge_startDate     DATE NULL,
    pledge_amount        DECIMAL(9,2) NULL  CONSTRAINT  Currency_1322638346 CHECK (pledge_amount >= 0),
    artist_userID        INT NOT NULL CONSTRAINT gets REFERENCES ArtistMember (user_ID),
    follower_userID      INT NOT NULL CONSTRAINT makes REFERENCES FollowerMember (user_ID)
);

INSERT INTO ArtistMember VALUES ( 85275 );
INSERT INTO FollowerMember VALUES( 88128 );
INSERT INTO pledge VALUES(
  559,
  NULL,              -- Use NULL and not 'NULL'
  DATE '2016-02-01', -- Use a Date literal and not a string literal
  3850,
  85275,
  88128
);

If you just use the string in '1-FEB-2016' then Oracle will implicitly try to convert the string literal using the TO_DATE() function with the NLS_DATE_FORMAT session parameter as the format mask. If they match then it will work but this is a client variable so can be changed and then the query will break without the code having changed (and be a pain to debug). The simple answer is to ensure that you compare date value by either using TO_DATE() and specifying the format mask (as per the query above) or to use an ANSI date literal DATE '2016-02-01' (which is independent of the NLS settings).

Vy Do · Accepted Answer · 2018-04-29 03:32:23Z

0

Insert an empty string '' to a NUMBER column, Oracle will insert NULL.

For example, col_2 has data type is date field or number(12,0) (any datatype what not date field), SQL statement set NULL value for col_2 like this:

insert into foo (col_1, col_2) values ('abc', '');

or

insert into foo (col_1, col_2) values ('abc', NULL);

(it is NULL, not 'NULL' or "NULL")

answered Apr 29, 2018 at 3:32

Vy Do

1

Collectives™ on Stack Overflow

How to insert NULL in to date field Oracle SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related