BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eslna
Obsidian | Level 7

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? 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
6 REPLIES 6
LinusH
Tourmaline | Level 20
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...?
Data never sleeps
eslna
Obsidian | Level 7

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
PROC Star

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.  

Anand_V
Ammonite | Level 13
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
Obsidian | Level 7

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

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 3577 views
  • 0 likes
  • 4 in conversation