SAS Database Library - Accessing Oracle views and tables in multiple schemas

[eslna]

Hello,

I am not sure which location to do this under so I will ask the question here.

We have a SAS environment that uses Oracle as its data source. Most of our connections connect directly to a Schema that has all the views and/or tables that we need for that specific project.

We have no issues setting this up.

We do have one team that wants to come onboard and they have multiple schemas with multiple tables in the same Oracle database and they want us to surface this via SAS.

Our administrator created a database connection and was successful, however, because this database account has read access to so many schemas and tables, they did not specify a schema when the connection was created. This makes it impossible to register any tables(because the database account doesnt have any tables associated with it directly)..

What I'd like to know is, is it possible to surface all the tables I need from all the schemas that my oracle account has access to in one library so I can register them via SMC? 

[LinusH]

I think that schema is mandatory for making a libref - that's the way we set it up at least. Guess that this is problem "only" during registration...?

[eslna]

Thanks for your response. The issue that we have is that a lot of our data sets will be sourced from multiple schemas and tables in the same database. For example, this is what we would like to do.

User = ABC (has access to Schema_A and Schema_B but doesnt possess any objects of its own)

Select * 

FROM SCHEMA_A.TABLE_A A

JOIN SCHEMA_B.TABLE_B B ON (A.ID=B.ID)

where A.STATUS= 'Active'

We'd ideally want the Library connection to use ABC as the username  but want to have the flexibility to access any tables and schemas that have been granted access to ABC at the database level.

We could write the SQL at the oracle level under the ABC user and surface the tables that way but we wanted to know if we could perform that function on the SAS side.

[SASKiwi]

If you define one LIBREF or library source per Oracle schema then your SAS SQL will be virtually identical to what you wrote. Any username you use to access Oracle would have to be set up for read access to all of the required schemas.  

[AnandVyas]

Hi @eslna

For registering any library in SMC, you would need a schema for the library. So if you have multiple schema's then you can define multiple libraries in SMC. Just to be sure the user going to access both the libraries has access to both the schema's in oracle.

if you dont wish to do it this way you can use libname statements for the same.

Many examples shared already in this communitypost:

https://communities.sas.com/t5/SAS-Procedures/How-to-access-Oracle-data-residing-in-separate-schemas...

Thanks,
AV

[eslna]

Thank you!

[eslna · Accepted solution]

It was suggested that I try using concatenated libraries as detailed in this document:http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000933322.htm