topic Re: PROC SQL Getting a list from an Oracle Database in SAS Data Science

PROC SQL Getting a list from an Oracle Database

kodmfl — Mon, 31 Mar 2025 17:18:24 GMT

I have an Oracle database that contains multiple tables some of which I can readily access because I know the names and other which I cannot access because I do not know the names. I have the proper information regarding passwords and location but I don't know the syntax for creating a list of the table names in the data base. To access specific tables I am using the following code which does work:

proc sql;
connect to odbc (dsn='DSNAME' uid=UIDNAME pwd='PASSWORDXXXXXXX');
create table OUTDAT as select * from connection to odbc (select * from UIDNAME .TABLENAME);
RUN;

DATA OUTDAT; SET OUTDAT;RUN;

Does anyone know if there is a way to obtain a list of all of the tables in the Oracle database using the same ODBC connect syntax or do I need something else?

Re: PROC SQL Getting a list from an Oracle Database

Tom — Mon, 31 Mar 2025 18:42:49 GMT

Normally would be told what schema to use in the Oracle database. You would then create a libref to connect to that specific schema in the Oracle database. The schema might be already set in the ODBC DSN you are using. But you should be able to override with the SCHEMA= option.

libname myora odbc 
  dsn='DSNAME'
  uid=UIDNAME pwd='PASSWORDXXXXXXX'
  schema='oracle_schema_name' 
;

and then the normal SAS tools for finding out what datasets exist in a library will work. Such as PROC CONTENTS (or PROC DATASETS).

proc contents data=mylib._all_ out=contents noprint;
run;

Or the DICTIONARY metadata views.

proc sql;
create table datasets as 
select * from dictionary.tables
where libname='MYLIB'
;
create table variables as 
select * from dictionary.columns
where libname='MYLIB'
;
quit;

Since you are using ODBC you could also use the special ODBC catalog queries. Note that if you already have a connection created with libref you can use that libref in your PROC SQL instead of making a new connection.

proc sql;
connect using mylib;
create table datasets as 
select * from connection to mylib
(ODBC:SQLTables(,,))
;
create table variables as 
select * from connection to mylib
(ODBC:SQLColumns(,,,))
;
quit;

Re: PROC SQL Getting a list from an Oracle Database

SASKiwi — Mon, 31 Mar 2025 20:21:21 GMT

You could make use of the Oracle dictionary tables. If you run this query in SQL Passthru it will list all tables you have access to:

SELECT owner, table_name FROM all_tables