01-17-2012 10:51 PM
We have Oracletables created using proc sql Oracle pass-through scripts with defined lengthattributes. However, when we register their metadata, the length attributes of the columns increase 4 times. Have you encountered this as well? Is there an option that we can use to prevent this increase and be able to keep the original column specs in Oracle?
Thanks in advance for the help!
01-23-2012 09:07 AM
Can't say that I have.
What data type did you use in Oracle?
What does the libname statement used for import looks like?
The log from the import can also be of interest.
01-15-2014 10:38 AM
Can you provide an example of what you are seeing? Which specific data types is this happening to?
01-15-2014 07:32 PM
Are your data types by any chance NCHAR and NVARCHAR2 and the encoding in Oracle is UTF-8?
If so then from what I've read here Supporting Multilingual Databases with Unicode you would specify the length of these types in Oracle by number of characters regardless of the Bytes required to store such characters.
As much as I understand in SAS the length of a character variable specifies the Bytes available for storage (and not the number of characters).
UTF-8 uses up to 4 Bites per character so multiplying the length by 4 for SAS would make sense.
If you are dealing with UTF-8 then you would also have to care about the encoding on the SAS side SAS(R) 9.4 National Language Support (NLS): Reference Guide, Second Edition.
03-18-2014 10:12 AM
Thank you for your answer and sorry for not respondig a long time now...
Also the SAS Support told me that this ist only possible, when we use UTF-8 somewhere.
Check it in SAS:
proc options group=languagecontrol;run;
And in Oracle:
connect to oracle(user=scott orapw=tiger path="orcl");
select * from connection to oracle
(select * from v$nls_parameters);
disconnect from oracle;
and in the documentation:
03-19-2014 05:36 AM
When you define your libname, you can add serveral options. Then most important ones are DB_LENGTH_SEMANTICS_BYTE (defines if it's varchar2 of byte for character fields) and DB_CLIENT_MAX_BYTES (defines how much byte a single character must be).
here's an example:
LIBNAME ORA ORACLE INSERTBUFF=10000 READBUFF=50000 PATH=OSAS SCHEMA=SAS USER=SAS PASSWORD="blabla" DB_LENGTH_SEMANTICS_BYTE=NO DBCLIENT_MAX_BYTES=1;
DBCLIENT_MAX_BYTES=1 will prevent oracle for blowing up your column lengths by 4
03-23-2014 03:37 PM
Thanks @sdoorneveld I wasn't aware of these options and I'm sure that's going to be very useful for me sooner or later.