How to determine/set the Java classpath for an Oracle DB

javaoracleoracle-12c

l am trying to make use of some proprietary Oracle Java classes/methods using an Oracle provided virtual machine (E-Business Suite Vision, Oracle 12.2.28)

On my test server I define a function:

[oracle@apps java_test]$ sqlplus USER/PASS@SID

SQL*Plus: Release 10.1.0.5.0 - Production on Tue May 21 10:21:24 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create or replace function methodname(foo in varchar2, bar in varchar2)
return varchar2 as
language java
name 'path.to.classname.methodname(java.lang.String,java.lang.String) return java.lang.String';
/  2    3    4    5  

Function created.

Which seems to compile. However when I attempt to call it, I get the following error.

SQL> select methodname('foo', 'bar') FROM DUAL;
select methodname('foo', 'bar') FROM DUAL
                                                                                                                                     *
ERROR at line 1:
ORA-29540: class path/to/classname does not exist

l know for a fact that this class exists at /u01/install/APPS/fs1/EBSapps/comn/java/classes/path/to/classname.class. The question is have is, how do I set the classpath for the database (either permanently or for the session) so that it can access this method? Alternatively, is there a query to determine what the classpath is so that I can copy the classes to the proper location in the operating system?

Best Answer

The JVM embedded in your Oracle database only allows you to access java classes loaded inside that database: accessing external classes (from OS files) is not allowed.

First you need to find out what java classes are already loaded in your database by reading dictionary view ALL_JAVA_CLASSES. You will find that it has already 10's of thousands of classes loaded. 

SQL> select owner, count(*) from all_java_classes group by owner order by owner;

OWNER        COUNT(*)
---------- ----------
MDSYS            1134
ORDSYS           1944
SYS             34358

3 rows selected.

But it is unlikely that the EBS-specific classes you want to invoke are there. So you then need to load those classes in your database.

You can do that using the loadjava command line tool, which lets you load entire jar files into your database. Or you can also use the DBMS_JAVA package, specifically the LOADJAVA() method.

Of course those EBS classes may have dependencies on other EBS or third-party classes. You will then also need to load those.

Read about it in the Java Developer's Guide (https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdev)

Related Question