06-12-2017 09:18 AM
Can someone help write the code to connect SAS EG to Oracle database?
This is what i have so far:
PROC SQL; CONNECT TO ORACLE AS DBCON (USER=XXXX PASSWORD=XXXX PATH='EDWP'); select * from connection to oracle (select*from CUSTOMERS); disconnect from oracle; quit;
However, I recieve an error once I get to "customers". I do not know if cuctomers is even what I should be putting there. From my IT department, I recieved a list of oracle tables and IDWE_OWN.CUSTOMERS was one of the tables listed.
Using SAS EG 5.1
06-12-2017 09:25 AM
I have tried what you suggested and am still recieving the following error:
ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.
06-12-2017 09:32 AM
I think what you might used and I suggested is libname assigned for a particular schema in oracle. Please ask the IT people, what is the name of schema.
06-12-2017 09:35 AM
How would i create a libname? Sorry, I am fairly new to SAS.
The name of the schema is IDWE, the environment is PROD and the database name is EDWP.
06-12-2017 01:11 PM - edited 06-12-2017 01:18 PM
I generally connect with a LIBNAME statement and then use that in PROC SQL. This way I can use one connection for both implicit and explicit pass-through SQL. For you it would probably look something like this:
LIBNAME OracSAS ORACLE USER=&UserNm PASS=&PassWd PATH='EDWP' SCHEMA=IDWE CONNECTION=GLOBAL; PROC SQL; CONNECT USING OracSAS AS OracDB; /* CODE */ DISCONNECT FROM OracDB; QUIT;
06-12-2017 09:44 AM
From memory (quite a while back) TNSNames.txt is a file on your computer with connection information. It sounds like this file is missing, or has incorrect information in it. Contact your IT and get them to update it.
06-12-2017 10:05 AM
You should get the IT department to configure the TNS names file properly for you.
If that is not possible you can tell SAS the information that it would normally lookup using the tnsname directly.
See this guide.