[TD42] Using Oracle Instant Client, how?

[Dave Rabelink]

We need to get rid of the huge Oracle Client install which takes a lot of time to deploy to workstations and introduce version collisions.

I have seen that there is a way to connect to Oracle using the Instant Client which actually only needs a xcopy of a limited set of Oracle dll's/files.
It would be nice to deploy the apps having these Instant Client files along with it.

I'm not an Oracle expert and my first tries did not work. So I hope someone could give the correct procedure to get it working.
(and confirm that a TD42 app can connect to Oracle using the Instant Client)

This is the setup:

- TD42
- Windows XP SP3 32 bit
- Oracle database 10g
- Oracle Instant Client setup 'instantclient-basiclite-win32-10.2.0.5.zip'

(1)
I created a new folder on my workstation named
c:\working\OraInstantTest

(2)
I downloaded the Instant Client mentioned above and placed all files in the zip to the folder c:\working\OraInstantTest.
No subfolders, all files are at one place (as in the zip).

(3)
I created a tnsnames.ora file with these contents:

Code: Select all

# tnsnames.ora Network Configuration File
# Generated by Oracle configuration tools.

GENOCST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = usaXXXX.eur.tld)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = GENOCST)
    )
  )

I placed this in the folder c:\working\OraInstantTest

(4)
I created a Sql.ini with these contents

Code: Select all

[oragtwy]
remotedbname=GENOCST,@GENOCST

substitute=SYSSQL.,
substitute=",
longbuffer=32767
fetchrow=20

[win32client.dll]
comdll=sqlora32

And placed this file also in the folder.

(5)
I added to the global PATH settings at the front:

Code: Select all

path=c:\working\OraInstantTest;.....

and added two system variables:

Code: Select all

ORACLE_HOME   = C:\Working\OraInstantTest
TNS_ADMIN   = C:\Working\OraInstantTest

(6)
I created a TD test application with this code:

Code: Select all

Set SqlDatabase = "GENOCST"
Set SqlUser = "TestUser"
Set SqlPassword = "Test1"
!
If SqlConnect( hSql )
   Set bConnected = TRUE
   Call SqlDisconnect( hSql )

Created an executable and placed it in the mentioned folder.

I get this errormessage running the exe when trying to connect:

SqlErrorMsg.png

When I install the complete Oracle Client and run the same application the connection is ok.
(so database is alive, user credentials are fine)

I hope someone can point me to a solution.

[PeterFr]

Hi Dave,

we work with TD52 and Oracle Instant Client 11gR2.
We never used TD42, so I can't confirm it.

Maybe some stupid questions/remarks:
- maybe check again if PATH is realy set in your environment
(close explorere and start it again)
- If you have already a installed a oracle client and you have done a uninstall
check with regedit under HKLM/Software/ there should be no ORACLE section
- we don't set ORACLE_HOME with IC and it's working

[Harald]

My first idea is to set the NLS_LANG env var:

NLS_LANG=American_America.AR8MSWIN1256

I also set

[oragtwy]
uselob=1

in sql.ini for oracle client version >= 10

Kind regards
Harald

[Dave Rabelink]

Well, after many googles and trial 'n errors I finally found a solution.

There were 2 issues.

1)
The Oracle Instant Client light only supports English and a smaller subset of codepages.
The server codepage forces to use not the light but the basic version.
Unfortunately the extra dll involved is about 80Mb. But that is way better than a full client install.

2)
When in the registry this value is present (from a prior Oracle full Client install)

Code: Select all

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\NLS_LANG = "NA"

the Instant Client seems to take this into account and a new error is presented :

"ORA-12705: Cannot access NLS data files or invalid environment specified"

I tried to set another NLS_LANG environment variable. I was in the assumption the environment variables have priority over registry settings.

Code: Select all

NLS_LANG = DUTCH_THE NETHERLANDS.WE8MSWIN1252

But Instant Client keeps using the registry setting and ignores the environment variable.
I deleted the registry key mentioned above and now the test application connects to Oracle without problems !

It sounds to me this is strange behavior of the Instant Client. I expected it to be a xcopy install and only to set environment variables at user level. This would make the application easy deployable and somewhat disconnected from the current Oracle installs on workstations.

As the user has no authorization to change or delete the HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\NLS_LANG key it seemed I was stuck.
But as a workaround, I created a new key HKEY_CURRENT_USER\Software\Oracle\NLS_LANG and set this to DUTCH_THE NETHERLANDS.WE8MSWIN1252.

This user setting seems to have priority over the local machine setting. This user key can be set by the user.

Another thing I tested is which files from the basic version are actually needed. It seems only these two dll's are used and loaded when connection a TD application to Oracle:

Code: Select all

oci.dll
oraociei10.dll

The total size is 88Mb. The other files in the original basic ZIP file seems not to be needed and I deleted them from the local folder.

So in short, this is my setup to use Oracle (10g) from TD apps (v42):

- Place the two dll's (oci.dll and oraociei10.dll) in the same folder as the TD application executable
- Place a tnsnames.ora file in the same folder
- At startup of the application, write the correct HKEY_CURRENT_USER\Software\Oracle\NLS_LANG value to the registry
- At startup of the application, set the TNS_ADMIN environment variable to the application folder
- At startup of the application, add the application folder to the PATH settings, in front


I assume that my assumptions above are correct.
Remarks are welcome.