Oracle column length increase when metadata is registered to DI 4.2

[mrzd]

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!

[LinusH]

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

[Anika]

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

Thanks in advance.

[JBailey]

Anika,

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

Best wishes,

Jeff

[Patrick]

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.

[Anika]

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

[sdoorneveld]

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

[Patrick]

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.

[Ramprakash]

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.

[SASKiwi]

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

[Ramprakash]

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. 

[Patrick]

---

@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= ;

[Ramprakash]

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.

[SASKiwi]

@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.