proc append into ORACLE (NOT NULL & DEFAULT constraint)

[puntigama]

I'm using proc append to load data into an Oracle table (using SAS/ACCESS). One column of the oracle table is defined as NOT NULL with DEFAULT 'S'.

But this column doesn't exist in the SAS Table which shall be loaded into Oracle and here the problem starts.

I thought that for this column the default value shall be assigned but SAS taught me otherwise. A blank character is inserted. I've already looked for possible sas options (NULLCHAR, NULLCHARVAL, DBNULL etc.) but it doesn't work..

Below a simple example:

ORACLE Table:

CREATE TABLE TEST

(

  NUM NUMBER(4)        NOT NULL,

  COD VARCHAR2(1 CHAR) DEFAULT 'S' NOT NULL

)

SAS Table:

data TMP;

  NUM = 198;

run;

SAS-Code:

libname orahome oracle path=&ORAPATH schema=&ORASCHEMA user=&ORAUSER password="&ORAPASS";

options sastrace=',,,dsa' sastraceloc=saslog;

proc append base = orahome.TEST 

            data = TMP;

quit;

Does anyone have a glue how to tell SAS not to automatically add the missing column in Oracle to the INSERT statement and use instead the DBMS mechanism?

The Log shows that there is a INSERT statemend generated with all columns existing in ORACLE:

INSERT  INTO TMP_COAL_COLLATERAL_PUA

("NUM","COD") VALUES

(:"NUM",:"COD")

It seems that SAS is adding the missing column with a blank value.

Any hints for solving this issue would be appreciated.

Thx Arnold

[Peter_C]

Instead of using PROC APPEND use the generated syntax, modified to exclude the missing column that you want to take as default.

[puntigama]

Dear Peter,

thanks for your reply. Of course this would be an option. We have lot's of such transfers and sometimes the database model of the Oracle DB is modified weeks or months before we implement these changes. That's the reason why we differ in table structure. I thaught that there would be a simple option to do this (there are tons of options and maybe I've overseen one). But it seems I have to code it via checking the differences based on the dictionary information and generate a INSERT statement by myself.

Thx anyhow!

[LinusH]

We had som difficulties with default constraints in SQL Server.

I think we solved by using IGNORE_READ_ONLY_COLUMNS=YES libname option.

[Florent]

You should maybe have a look at the COALESCE function that allows you to replace any empty value of a field with a default value. To exclude any row having missing values for a column, I would use the same method as suggested by Peter.C.

Kr,

Florent

[puntigama]

Thanks for your suggestions.

Finally I did it by adding the default column which only exists in the Oracle Table but at the moment not in the SAS table to the drop dataset option for the oracle table within the proc append procedure.

This prevents SAS to add this column in the INSERT statement.

options sastrace=',,,dsa' sastraceloc=saslog;

proc append base = orahome.TEST (DROP = COD)

            data = TMP;

quit;

The INSERT statement then looks like the following:

INSERT  INTO TMP_COAL_COLLATERAL_PUA

("NUM") VALUES

(:"NUM")

Cheers Arnold

[Patrick]

SAS doesn't have a concept of a NULL value and a missing character value is a Blank.

I believe you found a clever and simple way to get around this without using pass-through SQL.

The one thing to consider: If you're having this column also in your source data (and you didn't just add it initially for loading) then the column could in a later phase be populated. Using a coalesce() function like Florent suggests and replace missings with the Oracle default of 'S' could save you from changing code in a later stage.

On a related note:

I've tried lately to implement an Oracle table with a primary key which gets automatically incremented when adding data to the table (using a trigger). This worked beautifully when using SQL Insert but I couldn't make it work when using bulk loading (with my limited Oracle skills). Are you bulk loading as well and is this Not Null constraint with default value then still working?

[abdelbaki22]

nice trick