topic Not able to insert sas date value into oracle table in SAS Programming

Not able to insert sas date value into oracle table

Banu — Tue, 28 Mar 2023 19:23:36 GMT

Hi Team,
I tried a lot to insert sas date value into oracle date variable, but always giving ERROR: Oracle execute error: ORA-0140: Cannot insert NULL into oracle table DATE field.

Below is my code:
Proc sql;
Create table A as Select "28MAR2023" As src_dt from tmp;

Proc sql;
Create table B as Select src_dt format=date9. from tmp;
quit;

Proc append data=B
Base=db1.tbl(sasdatefmt=(src_dt=date9.)) ;
Run;
Getting an error and code trying insert NULL value. Please suggest me how can I insert date value to oracle table date column.

Re: Not able to insert sas date value into oracle table

Reeza — Tue, 28 Mar 2023 19:30:24 GMT

Most DBs have datetime values, not just dates. Try creating a datetime variable instead of a date variable and inserting that.

Create table A as Select dhms("28MAR2023"d, 0, 0, 0) As src_dt format=datetime20. from tmp;

Re: Not able to insert sas date value into oracle table

Tom — Tue, 28 Mar 2023 19:47:57 GMT

Your first CREATE statement is make a CHARACTER variable.

Your second CREATE statement is assuming that the variable SRC_DT in the TMP dataset is NUMERIC and has value are the number of days since 01JAN1960. Otherwise you could not attach the numeric format DATE9. to the variable and have it print anything reasonable.

Errors about NULL values sounds like perhaps the table in Oracle is defined with a NOT NULL constraint. Do you have any missing values in the dataset B? For which variables? Does dataset B have ALL of the variable that exist in the Oracle table TB1? Otherwise SAS will set those values to NULL, which might be the cause of the NULL value message.

Re: Not able to insert sas date value into oracle table

Banu — Tue, 28 Mar 2023 19:49:58 GMT

I even tried below steps.
Proc sql;
Create table A as Select "28MAR2023" As src_dt from tmp;

Proc sql;
Create table B as Select dhms(src_dt,0,0,0) as src_dt format=datetime20. from tmp;
quit;

Proc append data=B
Base=db1.tbl(sasdatefmt=(src_dt=datetime20.)) ;
Run;

In this case also same issue, I am not able to resolve.

Re: Not able to insert sas date value into oracle table

Reeza — Tue, 28 Mar 2023 19:53:07 GMT

Note the d after the date in my code that isn't in yours. That tells SAS that the value is a date, not just text.

Re: Not able to insert sas date value into oracle table

Banu — Tue, 28 Mar 2023 19:53:34 GMT

I think you are right, need to verify the sas table columns and oracle table columns. And Yes src_dt is not null in my case. I will revisit my code and will get back you.