Empty library while connecting SAS to Oracle database via OBC

Question

I'm trying to connect SAS to a new database on Oracle via ODBC connection. I cannot connect to the engine directly as I don't have this license to do so.

I have set up the database correctly and I can access the views on Power BI with the Oracle connection.

My next step is to add this to the ODBC Data Source Administrator. When I add this to System DSN and test the connection here, it works fine.

The next step is connecting SAS to this ODBC and I have tried to do this via a libname statement.

libname mylib odbc datasrc="DATASRC" USER="userid" password="password" schema=XXDATAXX;

This statement returns a correct assignment of the library and makes the connection. I have tested it is actually working by putting in a wrong user id/ password and it does indeed not assign.

My issue is that when it does assign correctly, the SAS library is empty and I see nothing. Has anyone had this error occur before or have some ideas?

Things I have also tried is:

Checking my read/write acess (I have read only)
Adding options such as preserve_col_names=yes incase the table names are too long
Cross checking the tsanames.ora file and the database name is correct

Richard Richard · Accepted Answer · 2020-01-29T20:40:43

You may need to specify driver specific name value pairs in the ACCESS engine NOPROMPT= option. Oracle drivers should recognize parameter name Initial Catalog=. The valid parameter names and values depend on the ODBC driver you are utilizing and the data base system it is connecting you to.

Untested:

libname mylib odbc 
  datasrc="DATASRC" 
  USER="userid" 
  password="password"  
  schema=XXDATAXX
  NOPROMPT='Provider=OraOLEDB.Oracle;Initial Catalog=myDataBase;'
;

See SAS Doc "LIBNAME Statement for the ODBC Engine"

NOPROMPT=<'>ODBC-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you complete the connection string.

https://www.connectionstrings.com/oracle/ is also a good reference for different noprompt connection strings to try.

Empty library while connecting SAS to Oracle database via OBC

1 Answers