SQL Job Error Message: java.lang.UnsatisfiedLinkError: ocijdbc11 (Not found in java.library.path)

[Legacy User]

You're running an SQL Job which unfortunately fails. When checking the logfile you see the following :

20161019/132830.244 - U02012035 Start Agent query with RunID '******'.

20161019/132830.246 - U02012041 Query a table without waiting for a database. RunID: '******'

20161019/132830.248 - U02012046 RunID '******': Failed to create a database connection: 'java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path'

20161019/132830.250 - U02012037 Query with RunID '******' ended abnormally.

This means that the path in the Enviroment Variable (LD_LIBRARY_PATH) to the Oracle Client is missing or not set. 

Please add the following in .profile:

LD_LIBRARY_PATH=/ora_app/product/11.2.0.2/client_1/lib; export LD_LIBRARY_PATH

[Michael A. Lowry]

I’m getting a similar error from the SQL agent when testing a connection object:

com.uc4.ex.sql.ConnectionException: java.lang.UnsatisfiedLinkError: ocijdbc12 (Not found in java.library.path)


LD_LIBRARY_PATH includes the path to the Oracle client lib directory, and in there is the file libocijdbc11.so. (Note the difference between 11 and 12.)

On another similar system, it works fine. Any ideas what I’m overlooking?

[Legacy User]

Hi Michael_Lowry

Many thanks for your request.
I will look into this and as soon as i have an answer i will let you know. 

Best Regards
Daniel Hausdorf

[Legacy User]

Update:

1. New SQL database agent
2. No Oracle ojdbc7.jar in /opt/automic/v11/sql_oracle/bin/jdbc
3. No entry in PATH and LD_LIBRARY_PATH

Result:
https://us.v-cdn.net/5019921/uploads/editor/el/g65ehrrixu5g.png" width="963">
       4. Restart the agent with the ojdbc7.jar
       5. No LD_LIBRARY_PATH set

Result:
https://us.v-cdn.net/5019921/uploads/editor/ig/6x2bavlryeja.png" width="993">

       6. Add $ORACLE_HOME/lib to the LD_LIBRARY_PATH

Result:
https://us.v-cdn.net/5019921/uploads/editor/g9/vzbr7jm1zg8o.png" width="1033">

       7. Restart agent.

[Michael A. Lowry]

I’m seeing this error again when trying to run an Oracle SQL job using the new v12.2 SQL agent.

com.uc4.ex.sql.ConnectionException: java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path

I followed the instructions provided by Daniel above, but the problem remains. Did something change in v12.2?

[Michael A. Lowry]

I fixed the problem. The solution and explanation follow.

1. The SQL Agent requires that the JDBC driver JAR file be installed in the jdbc subdirectory of the directory containing the SQL Agent JAR file ucxjsqlx.jar. It is not sufficient to place the JDBC JAR somewhere else and include that location in the CLASSPATH. If the required JDBC JAR is not found in the jdbc subdirectory, the SQL agent will throw the following exception:
   

   com.uc4.ex.sql.ConnectionException: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

2. The SQL Agent loads the highest-numbered JDBC JAR file it finds in the jdbc subdirectory. E.g., if the directory contains both ojdbc6.jar and ojdbc7.jar, the agent will load ojdbc7.jar.
   

   The SQL Agent INI file, ucxjsqlx.ini, appears to provide no way to specify the path of a specific JDBC file to use.

3. Different versions of the JDBC driver work with different versions Oracle (and Java).
   

   Oracle Database version	JDBC JAR files specific to the release
   12.2 or 12cR2	ojdbc8.jar with JDK 8
   12.1 or 12cR1	ojdbc7.jar with JDK 7 and JDK 8 ojdbc6.jar with JDK 6
   11.2 or 11gR2	ojdbc6.jar with JDK 6, JDK 7, and JDK 8 (Note: JDK7 and JDK8 are supported in 11.2.0.3 and 11.2.0.4 only) ojdbc5.jar with JDK 5

   Source: compatibility table of Oracle and JDBC/JDK versions.
   E.g., ojdbc6.jar supports Oracle 11, but ojdbc7.jar requires Oracle 12. So if the ojdbc7.jar file is loaded, but only Oracle 11 client libraries are found in LD_LIBRARY_PATH, the following exception will be thrown:
   

   com.uc4.ex.sql.ConnectionException: java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path

 

That last example describes the situation that was causing the problem on this agent. The jdbc subdirectory contained ojdbc6.jar, ojdbc7.jar, and ojdbc8.jar, but the system had only the Oracle 11 client installed. When I removed the ojdbc7.jar and ojdbc8.jar files and restarted the agent, the problem went away.

 

I will install the Oracle 12 client soon; then I will be able to put the newer JDBC JARs back in place.

 

#oracle #jdbc #ojdbc #caautomicworkloadautomation #sqlagent #ucxjsqlx

[Michael A. Lowry]

The SQL Agent installation instructions include some cryptic information about the JDBC driver. In light of my findings above, it is interesting to revisit these sections of the documentation:

Copy the file ojdbc5.jar or ojdbc6.jar to the folder's JDBC directory after you have installed the driver.

...

The agent searches the relevant jar-file classes in the JDBC folder. The file name is irrelevant.

This suggests that the name of the JDBC JAR file is actually unimportant, and that the agent looks at the names of the classes (and versions) of the files contained in each JAR file under the jdbc subdirectory.

It is important to use the most current jar file. Store only one jar file in the JDBC folder.

Well, it’s actually more important to use the JAR file that works with the version of Java and Oracle being used. However I actually think this is the author’s rather unusual way of saying that the agent loads the JDBC driver with the highest version.

 

Having more than one JDBC JAR file in the directory is not a problem in itself.