DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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?


Accepted Solutions
Solution
‎09-27-2011 01:53 PM
Occasional Contributor
Posts: 18

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

Posted in reply to MitchNussbaum

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

View solution in original post


All Replies
Solution
‎09-27-2011 01:53 PM
Occasional Contributor
Posts: 18

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

Posted in reply to MitchNussbaum

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

Occasional Contributor
Posts: 6

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 3018 views
  • 0 likes
  • 2 in conversation