topic Re: Oracle column length increase when metadata is registered to DI 4.2 in SAS Data Management

Oracle column length increase when metadata is registered to DI 4.2

mrzd — Wed, 18 Jan 2012 03:51:36 GMT

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!

Oracle column length increase when metadata is registered to DI 4.2

LinusH — Mon, 23 Jan 2012 14:07:11 GMT

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

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

Anika — Wed, 15 Jan 2014 10:56:20 GMT

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

Thanks in advance.

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

JBailey — Wed, 15 Jan 2014 15:38:36 GMT

Anika,

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

Best wishes,

Jeff

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

Patrick — Thu, 16 Jan 2014 00:32:25 GMT

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.

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

Anika — Tue, 18 Mar 2014 14:12:38 GMT

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;

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

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

sdoorneveld — Wed, 19 Mar 2014 09:36:39 GMT

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

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

Patrick — Sun, 23 Mar 2014 19:37:44 GMT

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.

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

Ramprakash — Tue, 02 Jun 2020 14:52:31 GMT

DBCLIENT_MAX_BYTES=1 wondering how can we use this option in pass-thorugh or set up as a system option if in case dont want to change existing scripts.

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

SASKiwi — Tue, 02 Jun 2020 19:59:09 GMT

According to the documentation this option is valid in both LIBNAME and SQL passthru CONNECT statements: Doc Link

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

Ramprakash — Thu, 18 Jun 2020 08:35:28 GMT

Hi,

Thanks for the information.

How do i set this option up a system option as I dont find the right information on where to set this as system option in sas viya.

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

Patrick — Thu, 18 Jun 2020 08:56:27 GMT

@Ramprakash wrote:

Hi,

Thanks for the information.

How do i set this option up a system option as I dont find the right information on where to set this as system option in sas viya.

This is a libname option.

What I'm doing more and more for explicit pass-through: connect using <libref>; If you start using this syntax then you basically need to define your libname only once and then use the same definition in all your pass-through code. If you go for this approach then also investigate libname option connection= ;

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

Ramprakash — Thu, 18 Jun 2020 10:10:02 GMT

Thanks Patrick

I am using in SQL pass-through that works as expected and when new users come in we dont need to educate them with this option if this is set globally on the server as system option that was my intention.

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

SASKiwi — Thu, 18 Jun 2020 20:07:50 GMT

@Ramprakash - You can define your Oracle connections as libraries in SAS metadata so they are available to all users. This would have the same effect as setting a global system option.