Desktop productivity for business analysts and programmers

One signon and access to multiple schemas in Oracle

Reply
N/A
Posts: 0

One signon and access to multiple schemas in Oracle

I am trying to setup a library, or libraries, that will allow me to access multiple schemas within the same library. Even if each library has to be its own schema, then I would still like to be asked for a logon just once. I would also like to share the same connection, so I do not exceed the limit on simultaneous connections. Are any of these goals possible?

I do not want to keep any usernames or passwords in the program, so I am expecting to use the dbprompt= option. I would like it to use the same username and password (and path) for all the subsequent libraries that may be created. Right now it still asks for a username and password each time the library is accessed the first time. These libraries are all the same, except a different schema is referenced.

I have tried the connection=global option to share the connection, but it still requires I log on 5 times (for 5 different schemas) and then exceeds the limit of 4 simultaneous user sessions. This limit is imposed by Oracle, not SAS, and is a requirement from our DBA.

I just want to signon 1 time and have access to the tables in 5 different schemas, just like I would with any SQL tool using Oracle.

Any ideas?

Mike
N/A
Posts: 0

Re: One signon and access to multiple schemas in Oracle

Yes I had to do this. My solution follows. Just throw in as many libname declarations as you need to concatenate. I worked it out from the online SAS V9 doco. I think the details were buried in the Oracle-specific information.

%let dbpath = db_name;
%let dbuser = roddy1;
%let dbpass = roddy1;

libname schema1 oracle
user = &dbuser
pw = &dbpass
path = &dbpath
dbindex = yes
preserve_col_names = yes
schema = AAA;

libname schema2 oracle
user = &dbuser
pw = &dbpass
path = &dbpath
dbindex = yes
preserve_col_names = yes
schema = BBB;

libname schema3 oracle
user = &dbuser
pw = &dbpass
path = &dbpath
dbindex = yes
preserve_col_names = yes
schema = CCC;

libname ora_db (schema1 schema2 schema3);


Then in your PROC SQL or data step, refer to the tables or views as:

data _null_;
set ora_db.ORACLE_TABLE_NAME;
run;

proc sql;
select *
from ora_db.ORACLE_TABLE_NAME;
quit;


But if you're still having problems (table or view does not exist) try flipping the SHOW_SYNONYMS=YES option on the Oracle libname statement. I also suspected case sensitivity but can't be sure. Once it was all working I just left it alone. In theory, these declarations should not be case sensitive.

This approach works in base SAS and Enterprise Guide. In EG though you must hard code this information. I couldn't really get it working by just putting in a LIBNAME statement to concatenate DB libraries defined in EG Administrator, even with the schema details specified. From memory EG would sometimes not show the table names in the schema when I browsed them, which means your program will not find them either.

Good luck.
N/A
Posts: 0

Re: One signon and access to multiple schemas in Oracle

I thought I had this licked, but found out it is not working as expected. This did not work for me on a couple different fronts. The first is that when I reached the simultaneous connection limit, the libname statements did not work. When I added the CONNECTION=GLOBAL option to each LIBNAME, I was able to create the librefs. But when I created the concatenated libref, I do not know how to access anything in it. When I tried a PROC DATASETS, I got the error of exceeding simulatneous connections still. And the big problem here is trying to get SAS Explorer to recognize all the libraries, so we can explore the datasets. Thanks for the help, but I am still hunting.
Trusted Advisor
Posts: 2,114

Re: One signon and access to multiple schemas in Oracle

When I have to access multiple schemas in one oracle database, I just use the SAS SQL passthrough to get to them (I use the CONNECT statement to get to one of them and then specifying the rest using the schema_name.table_name.column_name in the passthrough query.). Perhaps not elegant, but it works.

Doc Muhlbaier
Ask a Question
Discussion stats
  • 3 replies
  • 1029 views
  • 0 likes
  • 2 in conversation