SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Oracle column length increase when metadata is registered to DI 4.2

Reply
Occasional Contributor
Posts: 7

Oracle column length increase when metadata is registered to DI 4.2

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!

Super User
Posts: 5,426

Oracle column length increase when metadata is registered to DI 4.2

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.

/Linus

Data never sleeps
Occasional Contributor
Posts: 5

Re: Oracle column length increase when metadata is registered to DI 4.2

We just had the same problem. Did you solve it?

Thanks in advance.

SAS Employee
Posts: 215

Re: Oracle column length increase when metadata is registered to DI 4.2

Anika,

Can you provide an example of what you are seeing? Which specific data types is this happening to?

Best wishes,

Jeff

Respected Advisor
Posts: 4,173

Re: Oracle column length increase when metadata is registered to DI 4.2

Just thinking:

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.

Occasional Contributor
Posts: 5

Re: Oracle column length increase when metadata is registered to DI 4.2

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:

proc sql;

connect to oracle(user=scott orapw=tiger path="orcl");

select * from connection to oracle

(select * from v$nls_parameters);

disconnect from oracle;

quit;

Read more:

http://support.sas.com/resources/papers/92unicodesrvr.pdf

and in the documentation:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113591.htm

Occasional Contributor
Posts: 8

Re: Oracle column length increase when metadata is registered to DI 4.2

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

Respected Advisor
Posts: 4,173

Re: Oracle column length increase when metadata is registered to DI 4.2

Posted in reply to sdoorneveld

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.

Ask a Question
Discussion stats
  • 7 replies
  • 2299 views
  • 0 likes
  • 6 in conversation