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

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

PhilipH — Wed, 25 Jan 2017 10:41:48 GMT

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

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

Kalind_Patel — Wed, 25 Jan 2017 10:48:24 GMT

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.

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

RW9 — Wed, 25 Jan 2017 12:35:13 GMT

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.

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

boemskats — Mon, 30 Jan 2017 21:47:07 GMT

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