- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Everything looks right. try change it to IDWE_OWN.CUSTOMERS instead of customers
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.