ArcSDE 9.3 Direct Connection with Oracle

[KhoaTran1]

Hi everyone,

I'm writing code in Java to get direct connection to an Oracle database but unsuccessfully. I've looked around and looked for a solution for almost a week but unable to find one. Hopefully someone here can help me with this.

Server: Enterprise Geodatabase in Oracle 10g, ArcSDE 9.3.1, ArcGIS 9.3.1

I've installed ArcGIS 9.3.1 and ArcSDE931 SDK on my client computer [Windows 7 32-bit]. I also set the SDEHOME, ORAHOME [and also tried ORACLE_HOME], as well as add %SDEHOME%/bin and %ORAHOME%/bin to my path. I also set the TNS_ADMIN to %ORAHOME%/NETWORK/ADMIN. I downloaded both "10201_client_win32" (i.e. Oracle Client 10g) and "10203_vista_w2k8_x86_production_client" (i.e. Oracle Client 10g for Win 7) from Oracle download site and custom installed all the possible softwares that it has on the list. Then, I tried to test the connection based on this instruction http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Setting_up_clients_for_a_direct_connec...
and stuck with the 2nd step: sqlplus <user name>/<password>@<Oracle Net service name>. The message error I received was "ORA-12560: TNS: protocol adapter error." I think I got this error because Oracle service is not running. But since I only installed Oracle Client, I don't think the Oracle service is supposed to be there and thus doing "lsnrctl" in the command line does not work.

Anyway, I went ahead and wrote some Java code. And I've never got passed this one line [conn = new SeConnection(server, instance, database, user, password);].

SeConnection conn = null;
try {
  System.out.println("Connecting");
  conn = new SeConnection(server, "sde:oracle10g", database, user, password);
  System.out.println("Connected");
} catch (SeException e) {
   _log.error(e.getMessage());
   _log.error(e.getSeError().getErrDesc());
   _log.error(e.getSeError().getSdeErrMsg());
}

The error message that I received was:
Exception in thread "Thread-0" java.lang.UnsatisfiedLinkError: com.esri.sde.sdk.client.JGsrvr.gsrvrGetErrorCode(J)I
at com.esri.sde.sdk.client.JGsrvr.gsrvrGetErrorCode(Native Method)
at com.esri.sde.sdk.client.JGsrvr.run(Unknown Source)

I googled the error and it seems like no one's got it yet. According to my understanding the "java.lang.UnsatisfiedError" occurred when the library is not properly linked. However, I've checked all the ArcSDE jar files and required dll in the %SDEHOME% and they're all there.

Does any of you have the solution for this?

[VinceAngelo]

If TNSPING and SQL*Plus can't connect, there's little chance that ArcGIS will. You need to
configure your client tnsnames.ora file with an HOST that points to the Oracle server and
a SERVICE_NAME that specifies the ORACLE_SID.  The Net Configuration Assistant utility
can help (I generally copy tnsnames.ora from the server). 

To make your life easier, be sure that you only have one Oracle client installed (use the
10.2.0.3 known to be compatible with Windows 7).

- V

[KhoaTran1]

Thank you for your quick response.

I currently have the 10.2.0.3 client installed [only] and a tnsnames.ora. I made some progress after using the Net Configuration Utility. The error is now "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor". I'm guessing my problem now is the listener.ora . I'll try to play around with this a little bit more. I'll take your advise to establish the connection with SQL*Plus first before using direct connection with ArcSDE. Will report tomorrow.

[VinceAngelo]

Make sure you can access the server from the server, then use the server tnsnames.ora
from the client.  You would need to allow traffic on the listener port if a firewall is enabled.

- V

[KhoaTran1]

Vince,

I apologize for the late response. Thanks to your instructions, I've successfully connect to the remote Oracle server through SQL*Plus with the command: sqlplus <user name>/<password>@<Oracle Net service name>. Besides using Net Configuration Assistant utility, I figured out that my computer needs to be IN THE SAME NETWORK of that server (i.e. using vpn). That, however, leads to another problem to test the connection with ArcCatalog since I'm using a server license manager in a different network. Once I used vpn to connect to the Oracle server, I was unable to open ArcCatalog. Do you have any solution for this issue?

Again, my main goal is to use direct connection with Java. I'm not sure if that would help to stay away from the 2 different vpn problems.

PS: I already tried the Java code [SeConnection] but received the same error:
Exception in thread "Thread-0" java.lang.UnsatisfiedLinkError: com.esri.sde.sdk.client.JGsrvr.gsrvrGetErrorCode(J)I
at com.esri.sde.sdk.client.JGsrvr.gsrvrGetErrorCode(Native Method)
at com.esri.sde.sdk.client.JGsrvr.run(Unknown Source)

[VinceAngelo]

Network connectivity is required for any TCP/IP session to function (both ArcSDE and TNS
use TCP/IP).  I've never successfully placed a single box on two VPNs at the same time,
though it should be possible if they're not the *same* network.  I doubt an Esri forum is
the best place to find help in exotic subnet configuration.

Unsatisfied link errors all derive from improper DLL search path resolution.  You'll need to
walk through what files are required and identify where they're coming from.  This is a
trivial exercise on Unix hosts (`which`, `ldd`, and `file` are your friends), but can be a
source of agony on Windows hosts, even with a tool like Dependency Walker available.

- V

[KhoaTran1]

Vince,

I'll try something else tomorrow but I want to make sure with you I have the right configuration for PATH, SDEHOME, ORAHOME, and TNS_ADMIN. Could you please check the followings and confirm if it's right (or I don't need to do anything else with the environment variable, the dlls)

C:\Windows\system32>echo %PATH%
C:\oracle\product\10.2.0\client_4\bin;C:\Windows\system32;C:\Windows;C:\Windows\
System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files\Micro
soft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Bi
nn;C:\arcgis\arcexe9x\bin;C:\arcgis\arcexe9x\lib;C:\Program Files\Java\jdk1.6.0_
25\bin;C:\Python31;C:\Program Files\ArcGIS\arcsde\bin

C:\Windows\system32>echo %SDEHOME%
C:\Program Files\ArcGIS\arcsde

C:\Windows\system32>echo %ORAHOME%
C:\oracle\product\10.2.0\client_4

C:\Windows\system32>echo %TNS_ADMIN%
C:\oracle\product\10.2.0\client_4\network\ADMIN

I think my Oracle Client is working because I can do SQL*Plus. To be able to do direct connection, the above 4 variables are all I need to worry, correct?

[VinceAngelo]

The proper variable for Oracle is ORACLE_HOME (at which point the TNS_NAMES
variable would not be required).

- V

[KhoaTran1]

Great!! Thank you.

[KhoaTran1]

Vince,

Thanks to your help, I finally got the code working to connect to the server.

There are currently several references on how to set up direct connection but I think the followings would save other people some time.

1) I was originally working on Windows 7 but if it's possible, I strongly recommend to use Windows XP. You'd save a lot of time in setting up the environment. In case you use Windows 7, remember to run program as administrator. Otherwise, there'd be several errors when you install, read, and write file.
2) You'll need to install ArcGIS (of course), ArcSDE SDK, and Oracle Client. I got it working with Oracle Client 10 (10201_client_win32) and ArcGIS 10.
3) As Vince had in this thread, Net Configuration Assistant utility is highly recommended to set up the "tnsnames.ora". In order to know if you get the correct "tnsnames.ora", use SQL*Plus from the command line.
4) Follow the instructions on the following page to test your connection: http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Setting_up_clients_for_a_direct_connec...
5) You need to manually set up SDEHOME. The default destination for ArcGIS 10 is C:\Program Files\ArcGIS\arcsde. You also need to add some parameters to PATH, which are C:\oracle\product\10.2.0\client_1\bin;C:\Program Files\ArcGIS\arcsde\bin. You might NOT need to set up ORACLE_HOME (C:\oracle\product\10.2.0\client_1) and TNS_NAMES (C:\oracle\product\10.2.0\client_1\network\admin). I get rid of those two variables and my code is still working.
6) In case you receive "The supplied SDEHOME path is invalid or null" when catching the exception, besides other cause and solutions on other web pages, confirm your input [server, instance, database, user, password]. The value for instance would be based on your oracle client. In my case, I installed Oracle Client 10g so I put "sde:oracle10g". If you install 11g, that would change accordingly. For the password, it should be YOUR_PASSWORD@<Oracle Net Service name>.

Hope that helps!

Again, I really appreciate your help, Vince.