01-25-2017 05:41 AM
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.
01-25-2017 05:48 AM
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.
01-25-2017 07:35 AM
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.
01-30-2017 04:47 PM
This explicit passthrough is a good idea. You'll need to query from all_tables - Oracle's idea of dictionary.tables.