3

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
Superdooperhero
  • 7,584
  • 19
  • 83
  • 138
  • can you try including $JAVA_TOP/oracle/apps/xdo/oa/schema/server in the classpath. Are you using any IDE? – 0o'-Varun-'o0 Oct 05 '17 at 08:34
  • 1
    @Aura This does not use the `CLASSPATH` or any IDE - this is loading the classes into an Oracle database so that they can be referenced internally. The `loadjava` application is a/the correct method to do this so the question boils down to trying to work out why `loadjava` is failing and fixing that. – MT0 Oct 05 '17 at 08:42
  • @ Aura Tried CLASSPATH=$CLASSPATH:/oracle/hcmgrpd1/fs2/EBSapps/comn/java/classes/oracle/apps/xdo/oa/schema/server and then ran again using SQL Plus. Did not work – Superdooperhero Oct 05 '17 at 08:44
  • The `class` file you are referencing will almost certainly have dependencies to other classes in the same package so loading it `class` by `class` is going to be a very painful experience as you trawl through multiple dependencies. Instead, just use `loadjava` to load the `jar` which contains the entire package. – MT0 Oct 05 '17 at 08:46
  • @Superdooperhero Oracle does not use the system `CLASSPATH` it has its own internal `CLASSPATH` (which, I believe, refers to a single directory and cannot be modified). Using the `loadjava` application or [`CREATE JAVA CLASS`](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5012.htm) is the way to add classes into that `CLASSPATH`. – MT0 Oct 05 '17 at 08:50
  • Don't think there is any jar containing all these classes. How would I go about finding such a jar? – Superdooperhero Oct 05 '17 at 08:56

1 Answers1

3

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.

MT0
  • 143,790
  • 11
  • 59
  • 117