Insert SAS Character Blank as Oracle NULL

[Patrick]

Hi guys,

I've got a SAS source table and an Oracle target table. I would like to use Proc Append to load into Oracle (staging table).

SAS source -> Ora staging -> Ora target

I need to insert SAS character values with Blank only as NULL values in Oracle (consistently so always SAS blank -> Ora Null).

I haven't found any SAS option which would allow me to get such a behavior. The only ways on how to achieve this I can currently think of:

Option A) Post processing (updates) of values inserted into Ora staging (Blank to Null)

Option B) Before insert triggers on the staging table converting blanks to Nulls

Both options don't really excite me so I'm hoping someone has got a better idea.

I can create the staging table as I wish, I can't change the Oracle target table.

The Oracle target table has NOT NULL constraints turned on.

Thanks,

Patrick

[LinusH]

Sorry @Patrick, perhaps a typo, but how to insert NULL when NOT NULL constraint is on...?!

If you are using bulk load, you can play with NULLCHAR and NULLCHARVAL options.

[Patrick]

@LinusH

" but how to insert NULL when NOT NULL constraint is on...?!"

The intent is to have the database reject records where columns have a NULL value and that's why I need to ensure that I'm loading a missing as NULL and not as blank.

I'm currently not using bulk load as the volumes per single load are very low - but if the options you mention solve my problem then I could switch over to bulk-load.

[Tom]

---

@Patrick wrote:

@LinusH

" but how to insert NULL when NOT NULL constraint is on...?!"

The intent is to have the database reject records where columns have a NULL value and that's why I need to ensure that I'm loading a missing as NULL and not as blank.

I'm currently not using bulk load as the volumes per single load are very low - but if the options you mention solve my problem then I could switch over to bulk-load.

---

Why not just go ahead and apply the validity tests first in SAS instead of bothering the database with invalid data.

[LinusH · Accepted solution]

Ok reading a bit more, NULLCHAR is not limited to bulk load.
Also, given your use case, I would try the YES setting.

[Patrick]

@LinusH

I've done some testing. Data set option nullchar=yes does the trick. Below the Append loading into an already created Oracle table.

proc append base=oralib.test(nullchar=yes) data=work.test;
run;quit;

Also interesting is that the default - nullchar=sas - behaves in a way that:

- SAS char variable missing and Ora target variable with NOT NULL constraint: inserts a blank

- SAS char variable missing and Ora target variable without NOT NULL constraint: inserts NULL

- SAS numeric variable missing and Ora target variable with NOT NULL constraint: Oracle throws an error

- SAS numeric variable missing and Ora target variable without NOT NULL constraint: inserts NULL

With nullchar=yes the behaviour differs as below: 

- SAS char variable missing and Ora target variable with NOT NULL constraint: Oracle throws an error

@Tom Principally agree with what you write. In my actual use case: There is an upstream DataFlux job which should already do all the NOT NULL checking and I should only be getting data which is clean. Having the database check again is only a fail-safe plus I shouldn't be loading a blank instead of NULL.

[Tom]

The defaults look reasonable to me.  And now you know how to select a different behavior.

Normally SAS thinks of blank strings as "missing",  But if you are trying to insert a blank value into a field that does not take null values then inserting it as blank makes sense.

It sounds like you might need an additional constraint on your Oracle field to not allow blanks. That would also prevent users not using SAS from also inserting the blank values. Plus then the SAS NULLCHAR setting wouldn't matter.

[Patrick]

@Tom

"It sounds like you might need an additional constraint on your Oracle field to not allow blanks."

Nope, that's not necessary because with nullchar=yes SAS will insert NULL in case of a blank in the source column.