topic Connecting to Oracle via libname odbc -- Schema spec doesn't work? in SAS Programming

Connecting to Oracle via libname odbc -- Schema spec doesn't work?

MitchNussbaum — Tue, 27 Sep 2011 17:01:10 GMT

We need to use SAS/Access-ODBC to connect to our Oracle databases.

When I use the libname statement:

libname test odbc dsn=ora8_prdb1 user=read password=XXXX schema=public;

SAS connects to the database, but the Explorer window for the "test" library is empty, and I am unable to access any of the tables in the schema.

If I connect to Oracle with other clients, using the same credentials, I see lots of tables in the "public" schema, so I don't think this is a validation problem. Am I doing something wrong?

Connecting to Oracle via libname odbc -- Schema spec doesn't work?

mpajoh — Tue, 27 Sep 2011 17:53:08 GMT

Your statement:

libname test odbc dsn=ora8_prdb1 user=read password=XXXX schema=public;

is correct as long as all upper/lower cases are correct.

Here is one I use to connect to an Oracle database:

libname test odbc user=read password=xxxx dsn='TEST' schema=SCHEMA1 preserve_tab_names=yes;

Good luck

Connecting to Oracle via libname odbc -- Schema spec doesn't work?

MitchNussbaum — Thu, 29 Sep 2011 22:57:48 GMT

I didn't think schema-names were case-sensitive on our Oracle database, but when I changed "schema=public" to schema=PUBLIC, I suddenly started seeing the tables in the schema.

So the problem is solved.

Thanks.