After you successfully assign a libref, Oracle tables might not be listed in the library. To make sure the tables are listed, you need to add the schema (owner) by including the SCHEMA= option in the LIBNAME statement.

If you are not sure what schema to use, you can submit the following code to find the appropriate schema. Note that you need to substitute your own Oracle connection information for the user-supplied values oracle-user, oracle-password, and oracle-path.

proc sql;
   connect to oracle(user=oracle-user 
                     password=oracle-password 
                     path=oracle-path);
   create table list_tab as 
          select * from connection to oracle
          (select OWNER, TABLE_NAME from ALL_TABLES);
quit;

The SAS data set LIST_TAB will have a list of tables and their associated schema names.

Once you know the schema name, you can add it to the LIBNAME statement.

libname ora oracle user=oracle-user                      
                   password=oracle-password 
                   path=oracle-path
                   schema=schema-name; 

Operating System and Release Information

proc sql;connect to oracle(user=orauser  password=orapass path=orapath);
create table list_tab as 
select * from connection to oracle
(select OWNER, TABLE_NAME from ALL_TABLES);
quit;