topic Re: Connect to Oracle Database in SAS Programming

Connect to Oracle Database

Amber_Nicole94 — Mon, 12 Jun 2017 13:18:16 GMT

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

Re: Connect to Oracle Database

kiranv_ — Mon, 12 Jun 2017 13:23:13 GMT

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

Re: Connect to Oracle Database

Amber_Nicole94 — Mon, 12 Jun 2017 13:25:28 GMT

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.

Re: Connect to Oracle Database

kiranv_ — Mon, 12 Jun 2017 13:32:14 GMT

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.

Re: Connect to Oracle Database

Amber_Nicole94 — Mon, 12 Jun 2017 13:35:52 GMT

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.

Re: Connect to Oracle Database

RW9 — Mon, 12 Jun 2017 13:44:59 GMT

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.

Re: Connect to Oracle Database

Tom — Mon, 12 Jun 2017 14:05:04 GMT

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-SAS/ta-p/331849

Re: Connect to Oracle Database

Sven111 — Mon, 12 Jun 2017 17:18:01 GMT

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;