BookmarkSubscribeRSS Feed
mrzd
Calcite | Level 5

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!

13 REPLIES 13
LinusH
Tourmaline | Level 20

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
Anika
Calcite | Level 5

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

Thanks in advance.

JBailey
Barite | Level 11

Anika,

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

Best wishes,

Jeff

Patrick
Opal | Level 21

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
Calcite | Level 5

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
Fluorite | Level 6

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
Opal | Level 21

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
Calcite | Level 5
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
PROC Star

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

Ramprakash
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
PROC Star

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 9916 views
  • 2 likes
  • 8 in conversation