Oracle Library Issues

[Jim_Ogilvie]

Hi all,

I am trying to complete set up of an ODBC library to an Oracle database.

My system admin has set up a System DSN (64-bit Oracle driver), which has been tested and returns the expected connection success message.

I have set up a libname statement that uses the System DSN, and the SAS log reports that it has been successfully assigned, but I can't see any tables/views...

libname ORADEV odbc dsn=Ora_Dev_DB password=XXXXXXXXXXX schema=SAS_SERVICE preserve_tab_names=yes;

"SAS_SERVICE" is the username set up for the connection by our Oracle DBA (I'm not 100% if schema should = this or something else).

Note: My two SAS Connect licences have been used on ODBC and PCFILES, I don't have a spare.

[Tom]

USERNAME and SCHEMA are two different things.  It might be they have the same value if the Oracle database was setup to use the same value for both the username and the name of the schema. 

Currently you are not telling SAS what username to use when connecting to ODBC.  I am not sure if that is (or even can be) hard coded into the definition of the DSN when using ODBC connection.  It is possible it connected using your username and you don't access to see the objects (tables/views) in the SCHEMA you asked to use as the default.

[Jim_Ogilvie]

Thanks Tom.

Yes these were my initial thoughts...

The Oracle DBA tells me that the schema has been set to the same value as the username.

I also understand that our system admin set up the System DSN, using the Oracle Client, at which point he needed to provide the credentials.

Thanks

Jim

[Tom]

It shouldn't hurt to tell SAS what user to use.

libname ORADEV odbc dsn=Ora_Dev_DB 
  username=SAS_SERVICE
  password=XXXXXXXXXXX 
  schema=SAS_SERVICE
  preserve_tab_names=yes
;

Is it connecting?  Are you sure the data isn't there.  Try querying one table using pass thru and see.

For example this code will count how many observations are in the in table/view named SAS_SERVICE.TABLE1.

proc sql;
connect using ORADEV ;
select * from connection to ORADEV
(select count(*) as NOBS from SAS_SERVICE.TABLE1)
;
quit;

[Jim_Ogilvie · Accepted solution]

The libname including "username" threw an error.

However.....

Genius!

That proc sql worked, but only when I substituted "SAS_Service" for the name of the "Table Owner" (this was also provided by the Oracle DBA earlier).

I have never seen a library work in this way before, how would you us it in a SAS data step?

Also, do you have any idea how do set the libname up so I can browse the tables in the library tree?

[Tom]

Not sure what you mean by your first comment.  That is how the LIBNAME statement always works.  You have to point to the level that includes the individual tables/views.  So make sure the libref is pointing to the schema that holds the data you want to see.  So if the LIBNAME statement says to use schema XXX then in pass thru SQL code you also need to use XXX as the prefix on the table/view name to see those tables/views.  So if you made a libref named MYORA that pointed to a schema named MYSCHEMA.  In SAS code you can reference the table MYTABLE as MYORA.MYTABLE, but in Oracle code you would need to use MYSCHEMA.MYTABLE.

As to whether you can browse the table names using SAS it depends a little on how well it works.  Not all of SAS GUI tools can query remote databases as easily as they can real SAS libraries. You could try running PROC CONTENTS instead to see what the libref sees.

Adding the PRESERVE_TAB_NAMES might make these easier or harder.  This option has to do with how SAS/Access tries to handle table names that are not valid SAS member names.  For example a table name with a space or period or dollar sign in it.

[Jim_Ogilvie]

ERROR: Libref IFS_DEV2 is not assigned.
 ERROR: Error in the LIBNAME statement.
 ERROR 22-7: Invalid option name USERNAME.

[Tom]

Different engines (ODBC vs ORACLE) use different options on the LIBNAME statement.  I think for ODBC it might be USER instead of USERNAME,  Or perhaps USERID or UID.

Looks like this knowledge base post is related to your question.

https://support.sas.com/kb/38/112.html

[Jim_Ogilvie]

Nice one Tom - "user=" worked fine.

I actually got it working and saw some tables listed but then it has caused SAS Studio to hang.

Using the code in the article, I think I can see the reason for the hung (or extreme lag). The table owner I am referencing in "schema=" has access to 14,977 tables/views!

Any idea on handling that? Something in the SAS config file perhaps?

[rabindrakumar1]

Hi, 

You can also check if this works for you:

LIBNAME IFS_DEV2 Oracle CONNECTION=SHAREDREAD
readbuff=5000 dbsliceparm=(all,2) DEFER=YES DSN=IFS_DEV2
SCHEMA=<Schema_name> AUTHDOMAIN="Authentication_Domain_Name" ;

From SAS Management Console you can also fetch the details for Schema and Authdomain by Right clicking on Library>Properties>Data Server tab

[Ksharp]

Using the following code to get SCHEMA and Table Name.

proc sql;
   connect to odbc(SERVER=sjfx01 user=xxxxx  password=xxxxx        
                   dsn=ksharp );
   create table work.odbcSchema as
          select * from connection to odbc 
          (ODBC::SQLTables "","","","");
quit;