Enforcing a blank value as being NULL so that load to Oracle fails.

[KirstenM]

I have created a table in Oracle with a column FIELD1 which is defined as VARCHAR2(20 BYTE)  NOT NULL.

Users are able to append data to this table using SAS Enterprise Guide.

I need to prevent them from appending data where FIELD1 is blank.

However if the users make FIELD1 ='' in SAS (ie. blank or empty) they are able to append to the Oracle table so NOT NULL is not doing the trick. 

Appears that SAS interprets = ''  as a character field with a length of 1  rather than a null.

[LinusH]

You can try the NULLCHAR=YES option:

SAS Help Center: NULLCHAR= Data Set Option

[Tom]

In SAS there is no difference between an all blank character variable and missing value.  Character strings are fixed length.

.

So if you don't want them to insert blanks then tell them not to set the value to blanks.

I question the value of allowing the Oracle table to accept a value that is blank anyway as you seem to want to exclude missing values.  What is the difference between an all blank value and a null that you are trying to detect?  How are you going to tell the difference between a character string that is only blanks and one that is "empty"?

[Tom]

SAS variables are fixed length and padded with spaces.  So assigning ' ' or '' to a character variable will result in all spaces.

You might try using the TRIMN() function.  That will return a completely empty string when the value only contains spaces.

insert into myora.mytable select id,trimn(name),age from have.

[Tom]

The first result from google search of this issue is this thread from 5 years ago.

https://communities.sas.com/t5/SAS-Data-Management/Insert-SAS-Character-Blank-as-Oracle-NULL/td-p/50...