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

[PhilipH]

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

[Kalind_Patel]

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.

[RW9]

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.

[boemskats]

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