1、Accessing ORACLE data through SAS,Susanne Young Bank One,Overview,You need to use SAS to access data stored in Oracle tables or views,Options,LIBNAME statement pass-thru SQL query,LIBNAME statement,LIBNAME MYORALIB ORACLE USER=MYUID PW=MYPASSWORD PATH=OraInfo SCHEMA=MYSCHEMA;,LIBNAME statement,14 LI
2、BNAME MYORALIB ORACLE USER=MYUID PW=XXXXXXX PATH=OraInfo SCHEMA=MYUIDNOTE: Libref MYORALIB was successfully assigned as follows:Engine: ORACLEPhysical Name: OraInfo,LIBNAME statement,LIBNAME statement,PROC PRINT DATA=MYORALIB.NBR_DAYS_TO_DATE_LKUP; RUN;,LIBNAME statement,Helpful Info Automatic macro
3、 variables-1017,Sql pass thru query,PROC SQL; CONNECT TO ORACLE(USER=MYUID ORAPW=MYPASSWORD PATH=OraInfo);SELECT * FROM CONNECTION TO ORACLE (SELECT * FROM MYSCHEMA.NBR_DAYS_TO_DATE_LKUP); DISCONNECT FROM ORACLE; QUIT;,Sql pass thru query,Sql pass thru query,Execute statementexecute (grant select on
4、 MYSCHEMA.MY_TABLE to OTHERUID) by oracle; execute (execute credit(234,5000) by oracle; execute (create unique index my_pk on MYUID.MY_TABLE (MYFIELD) ) by oracle; Execute (drop table MYUID.MY_TABLE) by oracle;,Sql pass thru query,Automatic macro variables-12560,What you need to know,Path= option,Wh
5、at you need to know,What you need to know,Schema = option In Microsoft Access, when connecting to Oracle tables, the Schema name is identified as the text to the left of the period.The best way to find out is to go to the source, your Oracle DBA,Error Messages,The service youre referencing is not co
6、nfigured in your sqlnet.ora file. ERROR: ORACLE connection error: ORA-12154: TNS:could not resolve service name. ERROR: Error in the LIBNAME statement. You do not have SAS/Access for ORACLE or its not configured properly ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. The SASORA code app
7、endage could not be loaded. ERROR: Error in the LIBNAME statement.,Error Messages,Youve entered your User ID or password incorrectly or its expired. ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon denied. ERROR: Error in the LIBNAME statement.,Options,The following option
8、 statement causes information about the query that is being passed to Oracle to be printed in the SAS log, so you can see the logic being passed.Options SASTRACE= ,d SASTRACELOC=SASLOG;,Alternatives,So what if I dont have SAS/Access for Oracle?,Using ODBC drivers,Syntax for Libname statement LIBNAME
9、 MYORALIB ODBC DSN=MyOracle UID=MYUID PW=MYPASSWORD SCHEMA=MYSCHEMA; Syntax for pass-thru SQL query PROC SQL; CONNECT TO ODBC(DSN=MyOracle UID=MYUID PW=MYPASSWORD);SELECT * FROM CONNECTION TO ODBC (SELECT * FROM FIN1MGR.NBR_DAYS_TO_DATE_LKUP); DISCONNECT FROM ODBC; QUIT;,Conclusion,SAS offers a vari
10、ety of ways to access data that is stored in Oracle. Work with your Oracle DBA to assistance with connection questions. Utilize the numerous Users Group papers on the subject of methods and efficiencies accessing Oracle data.,References,SUGI 27 Data Warehousing and Enterprise Solutions Cant Relate?
11、A Primer on using SAS with your relational database Garth Helf, IBM Corporation SUGI 26 Advanced Tutorials Database Access Using the SAS System Frederic Pratter, Computer Science Department, University of Montana Missoula SUGI 28 Advanced Tutorials SAS/ACCESS to External Databases: Wisdom for the Warehouse User Judy Loren, Health Dialog Data Service, Inc SUGI 27 Advanced Tutorials Reading From Alternate Sources: What To Do When The Input Is Not a Flat File Michael Davis, Bassett Consulting Services,
