DB utilities – Oracle JPublisher enables you to easily access PL/SQL from Java

The tool JPublisher is a little bit unknown. But it is a very handy tool for accessing PL/SQL stored procedures, packages, functions as well as object, collection or reference types from Java. There is no need for writing error prone Java code when you need to access those objects: JPublisher just creates the Java code you need. Lately I used JPublisher to create a Java class for accessing the DDL of database objects. The package DBMS_METADATA contains a GET_DDL function which returns a CLOB containing the DDL you have asked for.

Setup

I used the JPublisher version of JDeveloper 10.1.2. Actually the command line tool jpub did not work immediately. I needed to set the following environment variables:

ORACLE_HOME

Must point to the JDeveloper home directory.

CLASSPATH

Must be set to (on Windows) %ORACLE_HOME%\jdbc\lib\classes12.jar;%ORACLE_HOME%\sqlj\lib\runtime12.jar;%ORACLE_HOME%\sqlj\lib\translator.jar

Usage

JPublisher will by default translate all routines of a package and since I needed only the GET_DDL function I needed to specify an input file where all other routines were excluded from translation. I created the file dbms_metadata.jpub as follows:

SQL SYS.DBMS_METADATA
AS DbmsMetadata
TRANSLATE open AS null,
set_filter AS null,
set_count AS null,
set_xmlformat AS null,
get_query AS null,
set_parse_item AS null,
set_transform_param AS null,
set_remap_param AS null,
fetch_xml AS null,
fetch_ddl AS null,
fetch_ddl_text AS null,
fetch_clob AS null,
network_open AS null,
network_callouts AS null,
network_fetch_clob AS null,
network_fetch_errors AS null,
network_fetch_parse AS null,
fetch_xml_clob AS null,
close AS null,
get_xml AS null,
get_dependent_xml AS null,
get_dependent_ddl AS null,
get_granted_xml AS null,
get_granted_ddl AS null,
get_dpstrm_md AS null,
set_debug AS null,
net_set_debug AS null,
free_context_entry AS null,
fetch_objnums AS null,
fetch_sorted_objnums AS null,
get_domidx_metadata AS null,
oktoexp_2ndary_table AS null,
patch_typeid AS null,
get_sysprivs AS null,
get_procobj AS null,
get_procobj_grant AS null,
get_action_instance AS null,
get_action_sys AS null,
get_action_schema AS null,
get_plugts_blk AS null,
get_java_metadata AS null,
get_prepost_table_act AS null,
openw AS null,
convert AS null,
put AS null,
add_transform AS null

The following commands will generate the DbmsMetadata.java file:

jpub -user=scott/tiger -url=jdbc:oracle:thin:@localhost:1521:ORCL -case=mixed -omit_schema_names -input=dbms_metadata.jpub
sqlj -compile=false DbmsMetadata.sqlj

The class DbmsMetadata can be constructed by supplying a database connection.

Conclusion

I think JPublisher is a neat tool and it worked fine for me.