DATA Step, Macro, Functions and more

Connect to Oracle Database

Reply
Contributor
Posts: 25

Connect to Oracle Database

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

PROC Star
Posts: 326

Re: Connect to Oracle Database

Posted in reply to Amber_Nicole94

Everything looks right. try change it to IDWE_OWN.CUSTOMERS instead of customers

Contributor
Posts: 25

Re: Connect to Oracle Database

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.

 

PROC Star
Posts: 326

Re: Connect to Oracle Database

Posted in reply to Amber_Nicole94

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.

Contributor
Posts: 25

Re: Connect to Oracle Database

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.

Frequent Contributor
Posts: 83

Re: Connect to Oracle Database

[ Edited ]
Posted in reply to Amber_Nicole94

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;
Super User
Super User
Posts: 7,977

Re: Connect to Oracle Database

Posted in reply to Amber_Nicole94

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.

Super User
Super User
Posts: 7,070

Re: Connect to Oracle Database

Posted in reply to Amber_Nicole94

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.

https://communities.sas.com/t5/SAS-Communities-Library/A-quick-guide-to-connecting-to-Oracle-from-SA...

 

Ask a Question
Discussion stats
  • 7 replies
  • 334 views
  • 0 likes
  • 5 in conversation