topic Re: SAS 64bit to Oracle connection in SAS Enterprise Guide

SAS 64bit to Oracle connection

learner_sas — Wed, 10 Dec 2014 20:53:15 GMT

Hello Everybody,

I am trying to use libname statement to connect sas to oracle below is the code I ran;

libname myoralib oracle user=userone password=XXXXXXX path=pathone; /* pathone is name of system dsn name for ODBC driver for oracle. My pathone does not have any characters not allowed in sas naming convention*/

but I got error saying oracle saying

ERROR: The ORACLE engine cannot be found.

I saw all of the comments and updates but still not able to figure out.

I am wondering if anybody has ran to same problem like me.

Re: SAS 64bit to Oracle connection

Haikuo — Wed, 10 Dec 2014 21:17:55 GMT

There may be more problems, but as error message suggested:

You have: "/* pathone is name of system dsn name for ODBC driver for oracle. ", but you attempted to use Oracle engine in your libname statement:

"libname myoralib oracle ". Try to replace 'oracle' with 'ODBC', see if it works.

Haikuo

Re: SAS 64bit to Oracle connection

learner_sas — Wed, 10 Dec 2014 23:47:50 GMT

I did it. Same problem it says "ODBC" driver can not found.

Re: SAS 64bit to Oracle connection

Haikuo — Thu, 11 Dec 2014 00:13:13 GMT

The syntax is a little off, it generally goes like:

libname mydblib odbc datasrc=orasrvr1 user=testuser password=testpass;

While orasrvr1 is your DSN. I have never seen "path=" in ODBC engine, it was seen in Oracle engine though.

That being said, make sure you have the ODBC driver for Oracle (version may vary) installed and corresponding DSN configured in ODBC data source administrator (Windows).

Good Luck,

Haikuo

Re: SAS 64bit to Oracle connection

jakarman — Thu, 11 Dec 2014 06:21:58 GMT

Do a "proc setinit; run; " As it looks that either you are not licensed to the mentione SAS/ACCess software or the installation is done wrongly.

Re: SAS 64bit to Oracle connection

learner_sas — Thu, 11 Dec 2014 09:41:05 GMT

after running proc setint;run;Output as follows. It says SAS/Access for two of the products. Do I need more for Oracle connection.

Original site validation data

Site name: company name

Site number: a random number

Expiration: 29JUN2015.

Grace Period: 45 days (ending 13AUG2015).

Warning Period: 45 days (ending 27SEP2015).

System birthday: 27OCT2014.

Operating System: WX64_WKS.

Product expiration dates:

---Base SAS Software 29JUN2015

---SAS/STAT 29JUN2015

---SAS/GRAPH 29JUN2015

---SAS Enterprise Miner 29JUN2015

---SAS Enterprise Guide 29JUN2015

---SAS/ACCESS Interface to PC Files 29JUN2015

---SAS/ACCESS Interface to OLE DB 29JUN2015

---SAS Enterprise Miner for Desktop 29JUN2015

---SAS Workspace Server for Local Access 29JUN2015

---SAS Add-in for Microsoft Excel

Thank you

Re: SAS 64bit to Oracle connection

jakarman — Thu, 11 Dec 2014 14:17:07 GMT

There is no ODBC and no Oracle access licensed. You cannot use these ones.

You can used OLEDB and it should be possible to go to Oracle wiht that one.

See: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001361171.htm

There is an example how to connect orcle using this.

Re: SAS 64bit to Oracle connection

ChrisHemedinger — Thu, 11 Dec 2014 14:18:46 GMT

Here are the steps that I use for ODBC:

Step 1 must be done on your SAS environment. If your SAS runs on a different Windows machine, the data source must be defined there as a DSN. If you're running everything local (SAS and SAS Enterprise Guide are on your local PC), then you can configure all of this on your machine.

1. Use Windows Data Sources (ODBC) to define a System DSN for your Oracle data source. This requires an Oracle ODBC driver. I have the Oracle in instantclient_12_1. Be sure to test the connection in the Data Sources facility. If using the 64-bit version of SAS, you'll need the 64-bit Oracle client and you'll define this DSN in the 64-bit Data Sources facility.

Step 2 can be done from SAS Enterprise Guide, connected to your SAS server.

2. In your SAS program, use a libname statement like:

libname mylib odbc datasrc="YOURDSN" USER="userID" password="Password" ;

As Jaap points out, you don't have SAS/ACCESS to ODBC so you'll need to pursue OLE DB instead. I recommend similar steps for testing the connection outside of SAS code. You can try it with File->Open->OLEDB in SAS Enterprise Guide, but be aware that direct access to the database from SAS Enterprise Guide is not an efficient method for accessing that data in your SAS program.

If you're concerned about the password in open code, you can try the techniques in this article:

Five strategies to eliminate passwords from your SAS programs

Chris

Re: SAS 64bit to Oracle connection

learner_sas — Thu, 11 Dec 2014 14:24:13 GMT

Thank you I will try all the options and update

Re: SAS 64bit to Oracle connection

learner_sas — Fri, 12 Dec 2014 15:33:08 GMT

Hello Chris,

I was able to use OLE DB and connect SAS in my local machine to the tables in oracle servers. As you suggested my tables are really big and took really long time if I selected more than one table. I will explore my other options.

I always read your blog. You are awesome.

Re: SAS 64bit to Oracle connection

MarkL — Tue, 15 Sep 2015 18:02:07 GMT

Hi Chris, I realize this string is a bit old, but here goes, does the order of installation (SAS and Oracle client) matter? At my site we have a couple hundred users that just got new 64-bit laptops with SAS 9.4 pre-installed. Our IT department put the 32-bit Oracle client on all of these which of course does not work (error we are seeing is below.) IT completed packaging a 64-bit Oracle client and engaged me for UAT. They pushed it to my machine but I still get the same error. I will start a new post/thread for this. Thank you !!

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA found but not
loadable..
Please make sure Oracle environment is set correctly.
Look in the install/Config doc for additional info for your platform.
Other possible reasons - incomplete Oracle client install, 32/64-bit mismatch between Oracle
client & SAS, incorrect Oracle client version(Oracle client must match the version picked
during post-install process), incompatible sasora for your OS or its attribs don't permit
SAS to load it.
ERROR: Error in the LIBNAME statement.