JT’s Blog

Using Oracle’s JPublisher

Oracle has a handy little utility called JPublisher. This tool can be used to generate Java source code for PL/SQL packages and types. I’ve been using it for the last year or so to generate class files over PL/SQL packages. The tool works nicely by generating a Java class with methods mapped to each procedure or function in the targeted package. The method’s types correspond to the signature of the procedure or function. The Java developer can construct the class with a JDBC connection, then invoke the methods as needed.

JPublisher has been integrated into JDeveloper. Within JDeveloper, in the database browser, you can navigate to packages, then right click and select ‘Generate Java’ to bring up a wizard which runs JPublisher to generate Java class files. I got this to run fine in the 10G version of JDeveloper. The only limitation that I found was the database browser in 10G was limited to the signed in schema. The 11g version allows you to select different schemas, but I have never been able to get it to work. The 11g JDeveloper wizard just does nothing when executed. I left a post on the Oracle forums for JDeveloper. Their development team was not able to recreate the problem. Go figure. I’ve been able to replicate this issue on 4 different machines now.

The Oracle documentation for JPublisher is good, but if you just want to build a class over a package, it can be difficult to find the information you need to get started.

To run JPublisher you will need source in 3 jar files. All of these are included in the Oracle client install.

set CLASSPATH=c:\oracle\ora92\sqlj\lib\translator.jar;%CLASSPATH%
set CLASSPATH=c:\oracle\ora92\jdbc\lib\classes12.jar;%CLASSPATH%
set CLASSPATH=c:\oracle\ora92\sqlj\lib\runtime.jar;%CLASSPATH%

On my windoz machine I just added these to the environment.

The command line interface can be rather long. For ease, I created a little batch file to run it. Then I could edit the command in Notepad++. Here is an example:

java oracle.jpub.Doit -user=xxx -url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=xxxx) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxx)))"
 -numbertypes=oracle -usertypes=oracle -package=com.jtsblog.db.packages  -case=mixed -input=gen.txt

The Oracle documentation examples show running JPublisher as

jpub ...

from the command line. I was never able to get that to work. I’m not sure what I missed, but running the Java class directly works fine for me (oracle.jpub.Doit). The user parameter is the user to connect to the database with. The url must be a valid JDBC url for the database.

The type mapping between Java and PL/SQL can be a little tricky. You have a number of options on setting up how JPublisher behaves for this. See this link for more info.

The last parameter is for an input file. I called mine ‘gen.txt’ and stored in the same directory as the batch file. In this file, you will store the objects you want JPublisher to generate source for.

Example:

SQL jt.my_pkg AS MyPkg

This tells JPublisher to map the package jt.my_pkg and generate a Java class called MyPkg. Since my batch file command has the parameter

-package=com.jtsblog.db.packages

, the class will be generated in the package ‘com.jtsbolg.db.packages’. You can map multiple packages and types simply by adding additional lines to the input file.

My little example here only touches on a small part of what JPublisher can do. You can learn more about JPublisher from Oracle’s documentation here.

This entry was posted on Sunday, June 7th, 2009 at 9:30 am and is filed under Uncategorized. The entry is tagged under Oracle JPublisher. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.