Need Help running running oracle query usng SAS Pass thru or Libname facility

[Santt0sh]

Hi Experts!

I am trying to retrieve some information from oracle tables like DBA_TABLES, all_cons_columns

i tried to run

For E.x.

Select table_name,Owner

   from DBA_TABLES

and this returns the value on Oracle sql developer, but when i try to run the same query within SAS using SAS Pass thru or libname i get an error stating the table DBA_Tables does not exists.

DB = Dev01;

Schema = Salesr;

Please enlighten me if we can use Oracle tables like DBA_Tables in SAS to retrieve the information.

I need to gather information about the tables, schemas, PK indexes and partitions along with other details to verify tables.

Thanks in Advance.

[ballardw]

Any time you get an error that you want help with you should include the SAS Log including the entire code of the procedure or data step that generates the error and all the notes associated as well as the error. Copy the text from the log. Then on the forum open a text box using the </> icon that appears above the message window and then paste the text.

In the case of code using an external database such as Oracle it is also a good idea to include from the log the code that actually connects to the data base XXXXing over any sensitive information such as user names and passwords if included, and any messages from the connection attempt.

Pasting into the text box is helpful in the case of diagnostic characters sometimes provided by SAS and the box also visually separates code/error text from discussion.

The results of connection attempts can be the key bit. If the connection was not successful then there is no way for SAS to see or use the external database tables and would generate errors such as you describe.

[Santt0sh]

Hi All,

Apologies!

Please find the Logs for Pass Thru and LIBNAME.

Please note that we use a macro to connect to Oracle or any database.

LOG Pass Thru:

SUSER@LINUXPATH/dir1/path1/xxxx/abc/yymm2405/input/_result/oracle_load
%LET (variable name is PREVMLOGIC)
MLOGIC (GET_ORACLE_LOGIN2):
MPRINT (GET_ORACLE_LOGIN2): options nomautolocdisplay nomprint nonotes
******** Macro GET_ORACLE_LOGIN2: unencrypts Oracle password and calls either Proc SQL or Libname ********
>> Optional: "ORACLE_CONNECTION_METHOD" defaults to PASSTHRU - otherwise must be either LIBNAME or (for Proc SQL) passthru >> Required: "DATABASE" can be 6 char or 10 (eg uatdb1) but macro converts 6-char DB names to 10-char DB service names
and must be one of these: sales1_dev devdb1 devdb2 devdb3 devdb4 uatdb1 uatdb2 uatdb3 uatdb4 prddb1 prddb2 prddb3 prddb4 >> Required: "SCHEMA" requried only ==IF== LIBNAME is specified for oracle_connection_method then must be <= 8 char
>> Optional: "USER" defaults to USER if blank otherwise must be user or user1
>> Optional: "PWDIR" defaults to /$HOSTNAME/usr/cmfprod but can be changed
>> Optional: "OPTIONAL_CONNECT_PARM" defaults to readbuff-1000 for USER ID.
env dev
If you are using SAS/Oracle to update oracle, then add dbcommit=10000
**************************************************
***************************
*******************
>> env = dev
LIBNAME SALESR ORACLE PATH=d01bri_dev SCHEMA-SALESR
MPRINT (GET_DETAIL.GET_ORACLE_LOGIN2):
USER=user1
;
SOURCE MPRINTNEST SYMBOLGEN MLOGIC;
MLOGIC (GET ORACLE_LOGIN2): Ending execution.
MPRINT (GET DETAIL):
MPRINT (GET_DETAIL):
proc sql;
SYMBOLGEN: Macro variable ORA_DATABASE resolves to sales1_dev
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. ERROR: Librefs are restricted to eight characters. sales1_dev has been truncated.
MPRINT (GET_DETAIL): select table_name, owner from sales1_dev.dba_tables WHERE OWNER in ('OWNER1','SALESR');
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT (GET_DETAIL): disconnect from oracle ;
NOTE: Statement not executed due to NOEXEC option.
MPRINT (GET_DETAIL): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time
cpu time
MLOGIC (GET_DETAIL):
0.00 seconds
0.00 seconds
PUT "SCHEMA ====== &ora_schema.
SYMBOLGEN: Macro variable ORA_SCHEMA resolves to SALESR
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. "SCHEMA =➖➖➖➖➖ SALESR
MLOGIC (GET_DETAIL):
"
Ending execution.
60
61
62
63
64

Logs - LIBNAME:

SUSER@LINUXPATH/dir1/path1/xxxx/abc/yymm2405/input/_result/oracle_load
%LET (variable name is PREVMLOGIC)
MLOGIC (GET_ORACLE_LOGIN2):
MPRINT (GET_ORACLE_LOGIN2): options nomautolocdisplay nomprint nonotes
******** Macro GET_ORACLE_LOGIN2: unencrypts Oracle password and calls either Proc SQL or Libname ********
>> Optional: "ORACLE_CONNECTION_METHOD" defaults to PASSTHRU - otherwise must be either LIBNAME or (for Proc SQL) passthru >> Required: "DATABASE" can be 6 char or 10 (eg uatdb1) but macro converts 6-char DB names to 10-char DB service names
and must be one of these: sales1_dev devdb1 devdb2 devdb3 devdb4 uatdb1 uatdb2 uatdb3 uatdb4 prddb1 prddb2 prddb3 prddb4 >> Required: "SCHEMA" requried only ==IF== LIBNAME is specified for oracle_connection_method then must be <= 8 char
>> Optional: "USER" defaults to USER if blank otherwise must be user or user1
>> Optional: "PWDIR" defaults to /$HOSTNAME/usr/cmfprod but can be changed
>> Optional: "OPTIONAL_CONNECT_PARM" defaults to readbuff-1000 for USER ID.
env dev
If you are using SAS/Oracle to update oracle, then add dbcommit=10000
**************************************************
LIBNAME rcoe ORACLE PATH=sales1_dev SCHEMA=salesr
USER=user1 password = xxxxxxxxxxxxxxxxx
;
SOURCE MPRINTNEST SYMBOLGEN MLOGIC;
Ending execution.
MPRINT (GET DETAIL. GET ORACLE_LOGIN2):
MLOGIC (GET ORACLE_LOGIN2):
MPRINT (GET DETAIL):
;
MPRINT (GET_DETAIL): libname temp ORACLE PATH=sales1_dev SCHEMA=salesr USER= user1 PASSWORD= xxxxxxxxxxxxxxxxxx; NOTE: Libref TEMP was successfully assigned as follows:
Engine:
Physical Name:
ORACLE sales1_dev
proc sql;
MPRINT (GET_DETAIL):
MPRINT (GET DETAIL): select table_name, owner from temp.dba_tables WHERE OWNER in ('OWNER1', 'SALESR'); ERROR: File TEMP.DBA_TABLES. DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT (GET_DETAIL): disconnect from oracle ;
NOTE: Statement not executed due to NOEXEC option.
MPRINT (GET DETAIL): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time
cpu time
0.00 seconds
0.01 seconds
MLOGIC (GET_DETAIL):
%PUT "SCHEMA ====== &ora_schema.
SYMBOLGEN: Macro variable ORA SCHEMA resolves to SALESR
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. "SCHEMA ====== SALESR
MLOGIC (GET_DETAIL):
11
Ending execution.
62
63
64

[Tom · Accepted solution]

What code exactly did you run?

How did you connect to Oracle?  Did you define a libref?

libname mylib oracle ..... ;

How did you try to push the query into Oracle?

proc sql;
connect using mylib;
create table dba_tables as select * from connection to mylib
(select table_name,owner from dba_tables)
;
quit;

[Santt0sh]

Thank  you Tom!

It worked.

I was missing the smallest detail.

[Ksharp]

If you want get META Data from database.
using the following to get schema and table name.

proc sql;
connect to odbc(dsn=xx user=xx passwrod=xx) ;
select *
from connection to odbc(ODBC:SQLTables);
quit;


Or if you connect to ORACLE directly by libname.
proc sql;
connect to oracle(dsn=xx user=xx passwrod=xx) ;
select *
from connection to oracle(ORACLE:SQLTables);
quit;

[Ksharp]

Check ODBC::SQLTables
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1f29m86u65hken1deqcybowtgma.htm