Oracle how to import missing java classes when calling java from plsql

Question

I am trying to gradually write a java function that can take the Oracle XML (BI) Publisher Report (not BI Publisher as used in Business Intelligence but rather XML Publisher used by Oracle Applications) functionality and provide the output of the report as a clob. So basically I would like to take a report definition and template that outputs a tax file and instead return the tax file into a clob that I can then manipulate further using PLSQL. If anyone knows of an existing function that can do this please let me know.

Not knowing much about Java I took this Stack Overflow Calling Java from PL/SQL question and tried to start and expand on it.

However I cannot import some classes into the Java program.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.Serializable;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import oracle.apps.xdo.oa.schema.server.TemplateHelper;
public class Hello
{
   public static String world()
   {
      return "Hello world";
   }
};
/

If I try import oracle.apps.xdo.oa.schema.server.TemplateHelper; the Java compile fails with

Errors for JAVA SOURCE Hello:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      Hello:13: cannot find symbol
0/0      symbol  : class TemplateHelper
0/0      1 error
0/0      import oracle.apps.xdo.oa.schema.server.TemplateHelper;
0/0      ^
0/0      location: package oracle.apps.xdo.oa.schema.server

The class TemplateHelper is under $JAVA_TOP/oracle/apps/xdo/oa/schema/server/TemplateHelper.class on the server where $JAVA_TOP is included in the CLASSPATH.

I also tried

loadjava -user apps ./oracle/apps/xdo/oa/schema/server/TemplateHelper.class

but for some reason this returns

SQL Error while connecting with oci8 driver to default database: Closed Connection
exiting  : could not open connection

even though all other programs work fine with the connection.

Does anyone know how I can import the class?

Answer 1

You can try CREATE JAVA CLASS:

CREATE OR REPLACE DIRECTORY xml_template_dir
  AS '/path/to/oracle/apps/xdo/oa/schema/server/';
/

CREATE JAVA CLASS USING BFILE (xml_template_dir, 'TemplateHelper.class' )
/

However, while this might load the class it will almost certainly have other dependencies and will fail when you try to use that class and you will then need to load those dependencies and then the dependencies dependencies and so on...

You would be better to find a JAR containing the entire package (or create the package yourself from your existing directory structure) and use loadjava:

loadjava -user APPS/password@sid -resolve XML_Publisher.jar

(If you need to overwrite existing classes that failed to load then you may need the -force option as well.)

You can then test to see if anything has failed to load using:

SELECT object_name
FROM   user_objects
WHERE  object_type = 'JAVA CLASS'
AND    status != 'VALID';

Also note, just because the class loaded successfully does not mean that it will not generate runtime exceptions when you invoke the class.