Oracle Connection using OS X

Databases
1

Trying to connect to Oracle and continue to receive the following message:
“Error connecting to Oracle: Can not load
Oracle library from /Library/Oracle/instantclient/libclntsh.dylib”

Running on OS X (10.10.3)
Xojo version 2015 Release 1

Went through some of the forum posts and verified the following:
Using Oracle 32 bit instant client
ENV is set to use the 32 bit instant client
tnsnames.ora is correct and works

I entered the following in the App’s Open Event Handler
System.EnvironmentVariable(“ORACLE_HOME”) = “/Library/Oracle/instantclient”
System.EnvironmentVariable(“DYLD_LIBRARY_PATH”) = “/Library/Oracle/instantclient”

Still the same error. Help…

Any assistance is greatly appreciated.

2

I know I struggled with this for a week and just gave up as I was doing it for fun. I’ll be curious what kind of responses you get. Good luck.

3

I found the following in my notes:

[quote]Download instant client 10_2 for OSX from oracle.
Rename libclntsh.dylib.10.1 to libclntsh.dylib
Use shell path to point to the folder where this dylib resides.[/quote]
https://www.google.ch/search?q=oracle+instant+client+download+osx&gws_rd=ssl

And an old test I once used:

[code]Sub Open()
me.AutoQuit= True

dim db as OracleDatabase
if TargetMacOS then
'System.EnvironmentVariable(“DYLD_LIBRARY_PATH”)="/usr/local/oracle/instantclient_10_2/"

Dim f As FolderItem
f=SelectFolder

Dim pfad As String = f.ShellPath

System.EnvironmentVariable("DYLD_LIBRARY_PATH")= pfad

end if

db = new OracleDatabase
db.databaseName = “(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.138.130)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE)))”
db.username = “oliver”
db.password = “ro*******”
db.debug = 1 'look in the console
if not db.connect() then
MsgBox db.errormessage
return
Else
beep
MsgBox “Connected!”
end if

dim rs As RecordSet
rs = db.SQLSelect(“SELECT * FROM DEPT”)
while rs <> Nil AND NOT rs.EOF
MsgBox rs.Field(“LOC”).StringValue
rs.MoveNext
wend
End Sub[/code]

It used to work, but I have no Oracle at hand now, to quickly test it again.

4

Well, I know it works fine under Yosemite (MacOS 10.10.4) and I see no reason why it would not work under 10.10.3. Also, I only have XoJo 2015 Release 2.1 here, so there is a possibility you might need to upgrade your Xojo version, but I do not think so.

So, I assume you have downloaded and unzipped the 32bit client. I had a bit of a time with a mixup between the 32 and 64 bit clients, and that is exactly the error message such a mixup will produce.

Anyways, here is how I would proceed to debug this. Hope it helps a bit. :slight_smile:

1. Open a terminal window and change to the library where you unzipped the Oracle instant client packages.
Assuming you installed all the available packages, it should look something like thie listing below. You do not have to have the SDK, SQLPLUS, and JDBC libraries and support files loaded, but it doesn’t hurt if you do. I aways do, especially sqlplus.

Pauls-iMac:OracleCLient Paul$ ls -CFa ./ glogin.sql ojdbc6.jar ../ libclntsh.dylib@ orai18n-mapping.jar .DS_Store libclntsh.dylib.11.1* orai18n.jar BASIC_README libheteroxa11.dylib* precomp/ JDBC_README libnnz11.dylib* sdk/ PRECOMP_README libocci.dylib.11.1* sqlplus* SQLPLUS_README libociei.dylib* uidrvci* TOOLS_README libocijdbc11.dylib* wrc* adrci* libsqlplus.dylib* xstreams.jar cobsqlintf.o libsqlplusic.dylib* genezi* ojdbc5.jar

2. Be very sure you have the 32Bit client in this folder.

Pauls-iMac:OracleCLient Paul$ file *.dylib
libclntsh.dylib:     Mach-O dynamically linked shared library i386
libheteroxa11.dylib: Mach-O bundle i386
libnnz11.dylib:      Mach-O dynamically linked shared library i386
libociei.dylib:      Mach-O bundle i386
libocijdbc11.dylib:  Mach-O bundle i386
libsqlplus.dylib:    Mach-O dynamically linked shared library i386
libsqlplusic.dylib:  Mach-O bundle i386

If any of these files show up (the 64bit libraries) that is probably where your trouble is. The 64bit libraries look like this:

Pauls-iMac:OracleClient64 Paul$ file *.dylib
libclntsh.dylib:     Mach-O 64-bit dynamically linked shared library x86_64
libheteroxa11.dylib: Mach-O 64-bit bundle x86_64
libnnz11.dylib:      Mach-O 64-bit dynamically linked shared library x86_64
libociei.dylib:      Mach-O 64-bit bundle x86_64
libocijdbc11.dylib:  Mach-O 64-bit bundle x86_64
libsqlplus.dylib:    Mach-O 64-bit dynamically linked shared library x86_64
libsqlplusic.dylib:  Mach-O 64-bit bundle x86_64

3. Now use sqlplus to test your connection to the database.
You might need to setup your terminal environment first. You can use the code below as a sample, adjusting the library locations to wherever you want them to be.

A. Add the following lines, suitably adjusted to your .bash_profile script. Alternately, you can put them into any script and execute them each time you want to use Oracle, but the .bash_profile step is much easier.

export ORACLE_HOME=/Users/Paul/OracleClient
export DYLD_LIBRARY_PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:$DYLD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:$PATH

B. Now you can connect to your database with a suitable quick connect string.
For example, this is a connection to an Oracle XE database running on a PC in my upstairs office:

Pauls-iMac:~ Paul$ sqlplus rsen/sxxxxs@paul/XE

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 3 15:47:57 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> desc DT01;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CODE					   NOT NULL CHAR(4)
 DESCR					   NOT NULL VARCHAR2(80)
 LAST_UPDATE				   NOT NULL TIMESTAMP(6)

SQL> select CODE from DT01;

CODE
----
COMF
COMP
FEAF
FEAP
PRLS
REVF
REVP

7 rows selected.

SQL> quit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Pauls-iMac:~ Paul$

** If this does not work for you, then something is wrong in your Oracle install. Stop and revisit previous steps. **

4. Now attempt to connect from Xojo.
I recommend taking the OracleExample from the documentation and modifying it slightly to this, adjusting for your own connection parameters of course. (Mmm- that didn’t work out too well, can not upload screenshots.)
Okay, then:

A. In App -> Event Handlers, and an Open Event. Then include the following line, again appropriately adjusted for the location you installed Oracle at.

System.EnvironmentVariable("DYLD_LIBRARY_PATH")="/Users/Paul/OracleClient"

B. Under MainWindow -> ConnectButton -> Action, the code should already look similar to this, but just in case…

  mDb = New OracleDatabase
  
  mDb.DatabaseName = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rsdb.rsenpubs.com)(PORT=1521))(CONNECT_DATA=(SID=XE) (SERVER=POOLED)))"
  mDb.UserName = "rsen"
  mDb.Password = "sxxxxs"
  mDB.Debug = 1
  
  If mDb.Connect Then
    mIsConnected = True
    ConnectStatusLabel.Text = "Connected to Oracle!"
  Else
    mIsConnected = False
    ConnectStatusLabel.Text = "Error connecting to Oracle: " + mDb.ErrorMessage
  End If

C. Run the app! Click on the 'Connect to Oracle" button, and you should get back a message that says 'Connected to Oracle!".

Hope that helps a bit. Post details if something is still not working.

Yours,
-Paul

5

Bob - I read through your ordeal working with ARGen and Oracle some time back. Until now, your post and the replies (conversation) seamed to be the most in the forum regarding connecting to Oracle. Doesn’t appear to be an issue with Windows, just OS X. Thanks for the reply.

Oliver - I also came across your posts regarding this back when Bob K was “fighting with it.”
You suggested renaming the dylib file. I was using links rather than renaming files. Curious if this could be the culprit. I am also using the 11g client not the 10g client. I will remove the 11g and try the 10g 32 bit client.

Paul - I had the 64bit 11g installed then installed the 32bit 11g, as noted to Oliver above, I am removing all then will reinstall the 10g 32 bit client, verify sqlplus, tnsping, etc. then see how it goes. Thanks for the detailed reply. I was getting nowhere with the support ticket I created with Xojo. Their reply was, “Ok. I’m going to see what I can come up with on this.”

If I could figure out how to post an image to this forum, I would (vs a url to an image). I provided support screen shots displaying the directories, links and the env output along with the event code.

Thanks guys! We’ll see what happens.
Bob

6

If you had the 64 bit client installed, it is possible the symlink is pointing to the 64 bit library. Says the guy who got bit for an entire day by exactly that… (grin)

7

Eureka! I finally figured out the culprit after blowing a couple of hours while everyone else in the house was still asleep from last night’s fireworks (4th of July).

Take Paul’s instructions above while ensuring you use the 10g 32 bit Oracle instantclient as mentioned by Oliver.

So much for keeping up with the Jones’ by having the 11g client installed. I removed all the Oracle clients (both 32 and 64bit). Installed the 11g 32 bit client and still no luck. I then removed the 11g client and installed the 10g 32 bit client. Set the environment in the App’s open event handler (just in case), fired it up. I am now talking to my 11g database instances. Now I get to go through and tinker with all my other DB Dev clients to see which ones if any are broken now that I downgraded my client.

Bob K. - you may want to try it again with the 10g client if you haven’t already.

Thanks Guys!

8

I tried to acknowledge that the combination of Oliver’s and Paul’s answered/resolved my question/issue. esoTalk only allows one answer.

9

Well, whatever works! I have the 11.2.0.4.0 32bit Instant Client installed here with no troubles, and I use the 64bit client for C and COBOL, but I am a version of MacOS up level from you, as well as a Xojo version up level.

Glad you got it working in any case. It’s pretty awesome the first time a query actually comes back after tangling with the confusion there.

Oh, if you are using a regular client, there are some extra steps necessary, but Instant client is so darn easy to use that I tend to not use the regular client for much of anything anymore.

Instant client works connecting to a 12c database form a Mac as well. :wink:
-Paul

10

I’m getting this error when I try to connect to an Oracle Database within Xojo.

Error while trying to retrieve text for error ORA-12537?

Any help?

P.S. I downloaded 10_2 and followed Oliver Osswald instructions.

https://www.google.ch/search?q=oracle+instant+client+download+osx&gws_rd=ssl

11

Alternatively you could try MBS SQL Plugin…

12

Thanks Christian… I figured it out.

One thing I can’t understand is, I put all this in my .bash_profile (I’m on a MAC)

export ORACLE_HOME=/Users/bob/instantclient_11_2 export DYLD_LIBRARY_PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:$DYLD_LIBRARY_PATH export PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:$PATH

But I still need to put these instructions in my code for the Oracle Database to connect:

System.EnvironmentVariable("ORACLE_HOME") = "/Users/bob/instantclient_11_2" System.EnvironmentVariable("DYLD_LIBRARY_PATH") = "/Users/bob/instantclient_11_2"

When I ship my application I hope I don’t have to tell my users that they need to install the oracle instant client and set all these environment variables for me. I’m hoping I can have the instantclient_11_2 directory in my resource folder of my application and have my environment variables point to them.

13

Its been a while… but I’m guessing its a matter of scope…
Your export statements don’t live long enough… and are not available to the scope in which your app is executing…
and I bet PATH survives because it existed BEFORE you issued those command, so it just used the values in EXPORT statements, and then the other two went out of scope

14

As far as I know your bash rc isn’t executed when you log in as a regular user
Only when you start a terminal session