Connect to Oracle Database

[Amber_Nicole94]

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

[kiranv_]

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

[Amber_Nicole94]

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.

[kiranv_]

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.

[Amber_Nicole94]

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.

[Sven111]

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;

[RW9]

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.

[Tom]

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