1 / 22

Accessing ORACLE data through SAS

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.

vasilis
Download Presentation

Accessing ORACLE data through SAS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Accessing ORACLE data through SAS Susanne Young Bank One

  2. Overview • You need to use SAS to access data stored in Oracle tables or views

  3. Options • LIBNAME statement • pass-thru SQL query

  4. LIBNAME statement LIBNAME MYORALIB ORACLE USER=MYUID PW=MYPASSWORD PATH='@OraInfo' SCHEMA=MYSCHEMA;

  5. LIBNAME statement 14 LIBNAME MYORALIB ORACLE USER=MYUIDPW=XXXXXXX PATH='@OraInfo' SCHEMA=MYUID NOTE: Libref MYORALIB was successfully assigned as follows: Engine: ORACLE Physical Name: @OraInfo

  6. LIBNAME statement

  7. LIBNAME statement PROCPRINTDATA=MYORALIB.NBR_DAYS_TO_DATE_LKUP; RUN;

  8. LIBNAME statement • Helpful Info • Automatic macro variables • &sysdbmsg displays the DBMS error messages 58 %put &sysdbmsg; ORACLE: ORA-01017: invalid username/password; logon denied • &sysdbrc displays the DBMS error codes 59 %put &sysdbrc; -1017

  9. Sql pass thru query PROCSQL; CONNECTTO ORACLE(USER=MYUID ORAPW=MYPASSWORD PATH='@OraInfo'); SELECT * FROM CONNECTION TO ORACLE (SELECT * FROM MYSCHEMA.NBR_DAYS_TO_DATE_LKUP); DISCONNECTFROM ORACLE; QUIT;

  10. Sql pass thru query

  11. Sql pass thru query • Execute statement • execute (grant select on 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;

  12. Sql pass thru query • Automatic macro variables • &sqlxmsg displays the DBMS error messages 64 %put &SQLXMSG; ORA-12560: TNS:protocol adapter error • &sqlxrc displays the DBMS error codes 65 %put &SQLXRC; -12560

  13. What you need to know • Path= option

  14. What you need to know

  15. 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

  16. Error Messages • The service you’re referencing is not configured 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 it’s not configured properly ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. The SASORA code appendage could not be loaded. ERROR: Error in the LIBNAME statement.

  17. Error Messages • You’ve entered your User ID or password incorrectly or it’s expired. ERROR: ORACLE connection error: ORA-01017: invalid username/password; logon denied. ERROR: Error in the LIBNAME statement.

  18. Options • The following option 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;

  19. Alternatives • So what if I don’t have SAS/Access for Oracle?

  20. Using ODBC drivers • Syntax for Libname statement • LIBNAME MYORALIB ODBC DSN=MyOracle UID=MYUID PW=MYPASSWORD SCHEMA=MYSCHEMA; • Syntax for pass-thru SQL query • PROCSQL; • CONNECTTO ODBC(DSN=MyOracle UID=MYUID PW=MYPASSWORD); • SELECT * FROM CONNECTION TO ODBC • (SELECT * FROM FIN1MGR.NBR_DAYS_TO_DATE_LKUP); • DISCONNECTFROM ODBC; • QUIT;

  21. Conclusion • SAS offers a variety 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.

  22. References • SUGI 27 Data Warehousing and Enterprise Solutions • Can’t Relate? 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

More Related