Your SAS programs, embedded in web apps and elsewhere

Checking if Oracle DB Table is avilable before pulling from Oracle to SAS

Reply
Contributor
Posts: 71

Checking if Oracle DB Table is avilable before pulling from Oracle to SAS

Hello,

we got a bit of an unstable Oracle DB environment and I wonder if there is a way to check if table A & B is available

before I run my proc sql to select from them.

Bye

Frequent Contributor
Posts: 96

Re: Checking if Oracle DB Table is avilable before pulling from Oracle to SAS

Why not you create a temporary odbc library for your oracle DB in SAS EG or Base SAS, and check that two tables are presence or not in that database.

Super User
Super User
Posts: 7,942

Re: Checking if Oracle DB Table is avilable before pulling from Oracle to SAS

You could select from dictionaries.tables as pass through, this would return what tables are available, something like:

proc sql;
  connect to oracle (path=...);
  create table O_DATA as
  select * from connection to oracle (
    select * from DICTIONARY.TABLES where NAME="<yourtable>");
  disconnect from oracle;
quit;

data _null_;
  set o_data;
  call execute('proc sql; ... quit;');
run;

So pull the metadata from oracle where the name is your table (note you will need to update specifics), then in a datastep generate the proc sql to extract data only if a row for your dataset is returned, i.e. the above will return no obs if your table doesn't exist, hence will not generate the call execute.

Frequent Contributor
Posts: 106

Re: Checking if Oracle DB Table is avilable before pulling from Oracle to SAS

This explicit passthrough is a good idea. You'll need to query from all_tables - Oracle's idea of dictionary.tables.

 

Ask a Question
Discussion stats
  • 3 replies
  • 124 views
  • 0 likes
  • 4 in conversation