Desktop productivity for business analysts and programmers

SAS 64bit to Oracle connection

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

SAS 64bit to Oracle connection

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.


Accepted Solutions
Solution
‎12-11-2014 09:18 AM
Community Manager
Posts: 2,692

Re: SAS 64bit to Oracle connection

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: SAS 64bit to Oracle connection

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

Frequent Contributor
Posts: 89

Re: SAS 64bit to Oracle connection

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

Respected Advisor
Posts: 3,124

Re: SAS 64bit to Oracle connection

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

Valued Guide
Posts: 3,206

Re: SAS 64bit to Oracle connection

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.

---->-- ja karman --<-----
Frequent Contributor
Posts: 89

Re: SAS 64bit to Oracle connection

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

Solution
‎12-11-2014 09:18 AM
Community Manager
Posts: 2,692

Re: SAS 64bit to Oracle connection

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

Frequent Contributor
Posts: 89

Re: SAS 64bit to Oracle connection

Thank you I will try all the options and update

Frequent Contributor
Posts: 89

Re: SAS 64bit to Oracle connection

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.

New Contributor
Posts: 3

Re: SAS 64bit to Oracle connection

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.

Valued Guide
Posts: 3,206

Re: SAS 64bit to Oracle connection

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.

---->-- ja karman --<-----
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 4204 views
  • 2 likes
  • 5 in conversation