Skip Headers
Oracle® Database JPublisher User's Guide
11g Release 1 (11.1)

Part Number B31226-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Data Type and Java-to-Java Type Mappings

This chapter discusses the JPublisher support for data type mapping, including a section on JPublisher styles and style files for Java-to-Java type mappings. These style files are primarily used to provide Web services support. The chapter contains the following sections:

JPublisher Data Type Mappings

This section covers the JPublisher functionality for mapping from SQL and PL/SQL to Java in the following topics:

Overview of JPublisher Data Type Mappings

When you use the -builtintypes, -lobtypes, -numbertypes, and -usertypes type mapping options, you can specify one of the following settings for data type mappings:

  • oracle

  • jdbc

  • objectjdbc

  • bigdecimal

Note:

The objectjdbc and bigdecimal settings are for the -numbertypes options only.

These mappings affect the argument and result types that JPublisher uses in the methods it generates.

The class that JPublisher generates for an object type has the getXXX() and setXXX() accessor methods for the object attributes. The class that JPublisher generates for a VARRAY or nested table type has the getXXX() and setXXX() methods, which access the elements of the array or nested table. When generation of wrapper methods is enabled, the class that JPublisher generates for an object type or PL/SQL package has wrapper methods. These wrapper methods invoke server methods, or stored procedures, of the object type or package. The mapping options control the argument and result types that these methods use.

The Java Database Connectivity (JDBC) and Object JDBC mappings use familiar Java types that can be manipulated using standard Java operations. The Oracle mapping is the most efficient mapping. The oracle.sql types match the Oracle internal data types as closely as possible so that little or no data conversion is required between the Java and SQL formats. You do not lose any information and have greater flexibility in how you process and unpack the data. If you are manipulating data or moving data within the database, then the Oracle mappings for standard SQL types are the most convenient representations. For example, performing SELECT and INSERT operations from one existing table to another. When data format conversion is necessary, you can use methods in the oracle.sql.* classes to convert to Java native types.

SQL and PL/SQL Mappings to Oracle and JDBC Types

Table 3-1 lists the mappings from SQL and PL/SQL data types to Java types. You can use all the supported data types listed in this table as argument or result types for PL/SQL methods. You can also use a subset of the data types as object attribute types.

The SQL and PL/SQL Data Type column contains all possible data types.

The Oracle Mapping column lists the corresponding Java types that JPublisher uses when all the type mapping options are set to oracle. These types are found in the oracle.sql package provided by Oracle and are designed to minimize the overhead incurred when converting Oracle data types to Java types.

See Also:

Oracle Database JDBC Developer's Guide and Reference for more information about the oracle.sql package

The JDBC Mapping column lists the corresponding Java types that JPublisher uses when all the type mapping options are set to jdbc. For standard SQL data types, JPublisher uses Java types specified in the JDBC specification. For SQL data types that are Oracle extensions, JPublisher uses the oracle.sql.* types. When you set the -numbertypes option to objectjdbc, the corresponding types are the same as in the JDBC Mapping column, except that primitive Java types, such as int, are replaced with their object counterparts, such as java.lang.Integer.

Note:

Type correspondences explicitly defined in the JPublisher type map, such as PL/SQL BOOLEAN to SQL NUMBER to Java boolean, are not affected by the mapping option settings.

A few data types are not directly supported by JPublisher, in particular those types that pertain only to PL/SQL. You can overcome these limitations by providing equivalent SQL and Java types, as well as PL/SQL conversion functions between PL/SQL and SQL representations. The annotations and subsequent sections explain these conversions further.

Table 3-1 SQL and PL/SQL Data Type to Oracle and JDBC Mapping Classes

SQL and PL/SQL Data Type Oracle Mapping JDBC Mapping

CHAR, CHARACTER, LONG, STRING, VARCHAR, VARCHAR2

oracle.sql.CHAR

java.lang.String

NCHAR, NVARCHAR2

oracle.sql.NCHAR (note 1)

oracle.sql.NString (note 1)

NCLOB

oracle.sql.NCLOB (note 1)

oracle.sql.NCLOB (note 1)

RAW, LONG RAW

oracle.sql.RAW

byte[]

BINARY_INTEGER, NATURAL, NATURALN, PLS_INTEGER, POSITIVE, POSITIVEN, SIGNTYPE, INT, INTEGER

oracle.sql.NUMBER

int

DEC, DECIMAL, NUMBER, NUMERIC

oracle.sql.NUMBER

java.math.BigDecimal

DOUBLE PRECISION, FLOAT

oracle.sql.NUMBER

double

SMALLINT

oracle.sql.NUMBER

int

REAL

oracle.sql.NUMBER

float

DATE

oracle.sql.DATE

java.sql.Timestamp

TIMESTAMP

TIMESTAMP WITH TZ

TIMESTAMP WITH LOCAL TZ

oracle.sql.TIMESTAMP

oracle.sql.TIMESTAMPTZ

oracle.sql.TIMESTAMPLTZ

java.sql.Timestamp

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

String (note 2)

String (note 2)

ROWID, UROWID

oracle.sql.ROWID

oracle.sql.ROWID

BOOLEAN

boolean (note 3)

boolean (note 3)

CLOB

oracle.sql.CLOB

java.sql.Clob

BLOB

oracle.sql.BLOB

java.sql.Blob

BFILE

oracle.sql.BFILE

oracle.sql.BFILE

Object types

Generated class

Generated class

SQLJ object types

Java class defined at type creation

Java class defined at type creation

OPAQUE types

Generated or predefined class (note 4)

Generated or predefined class (note 4)

RECORD types

Through mapping to SQL object type (note 5)

Through mapping to SQL object type (note 5)

Nested table, VARRAY

Generated class implemented using oracle.sql.ARRAY

java.sql.Array

Reference to object type

Generated class implemented using oracle.sql.REF

java.sql.Ref

REF CURSOR

java.sql.ResultSet

java.sql.ResultSet

Index-by tables

Through mapping to SQL collection (note 6)

Through mapping to SQL collection (note 6)

Scalar (numeric or character)

Index-by tables

Through mapping to Java array (note 7)

Through mapping to Java array (note 7)

User-defined subtypes

Same as for base type

Same as for base type


Data Type Mapping Notes The following notes correspond to the entries in the preceding table:

  1. The Java classes oracle.sql.NCHAR, oracle.sql.NCLOB, and oracle.sql.NString are not part of JDBC but are distributed with the JPublisher run time. JPublisher uses these classes to represent the NCHAR form of use of the corresponding classes, oracle.sql.CHAR, oracle.sql.CLOB, and java.lang.String.

  2. Mappings of SQL INTERVAL types to the Java String type are defined in the JPublisher default type map. Functions from the SYS.SQLJUTL package are used for the conversions.

  3. Mapping of PL/SQL BOOLEAN to SQL NUMBER and Java boolean is defined in the default JPublisher type map. This process uses conversion functions from the SYS.SQLJUTL package.

  4. Mapping of the SYS.XMLTYPE SQL OPAQUE type to the oracle.xdb.XMLType Java class is defined in the default JPublisher type map. For other OPAQUE types, the vendor typically provides a corresponding Java class. In this case, you must specify a JPublisher type map entry that defines the correspondence between the SQL OPAQUE type and the corresponding Java wrapper class. If JPublisher encounters an OPAQUE type that does not have a type map entry, then it generates a Java wrapper class for that OPAQUE type.

  5. To support a PL/SQL RECORD type, JPublisher maps the RECORD type to a SQL object type and then to a Java type corresponding to the SQL object type. JPublisher generates two SQL scripts. One script is to create the SQL object type and to create a PL/SQL package containing the conversion functions between the SQL type and the RECORD type. The other script is used to drop the SQL type and the PL/SQL package created by the first script.

  6. To support a PL/SQL index-by table type, JPublisher first maps the index-by table type into a SQL collection type and then maps it into a Java class corresponding to that SQL collection type. JPublisher generates two SQL scripts. One to create the SQL collection type and to create a PL/SQL package containing conversion functions between the SQL collection type and the index-by table type. The other to drop the collection type and the PL/SQL package created by the first script.

  7. If you use the JDBC driver to call PL/SQL stored procedures or object methods, then you have direct support for scalar index-by tables, also known as PL/SQL TABLE types. In this case, you need to use a type map entry for JPublisher that specifies the PL/SQL scalar index-by table type and a corresponding Java array type. JPublisher can then automatically publish PL/SQL or object method signatures that use this scalar index-by type.

JPublisher User Type Map and Default Type Map

JPublisher has a user type map, which is controlled by the -typemap and -addtypemap options and starts out empty. It also has a default type map, which is controlled by the -defaulttypemap and -adddefaulttypemap options and starts with entries such as the following:

jpub.defaulttypemap=SYS.XMLTYPE:oracle.xdb.XMLType
jpub.adddefaulttypemap=BOOLEAN:boolean:INTEGER:
SYS.SQLJUTL.INT2BOOL:SYS.SQLJUTL.BOOL2INT
jpub.adddefaulttypemap=INTERVAL DAY TO SECOND:String:CHAR:
SYS.SQLJUTL.CHAR2IDS:SYS.SQLJUTL.IDS2CHAR
jpub.adddefaulttypemap=INTERVAL YEAR TO MONTH:String:CHAR:
SYS.SQLJUTL.CHAR2IYM:SYS.SQLJUTL.IYM2CHAR

These commands, which include some wraparound lines, indicate mappings between PL/SQL types, Java types, and SQL types. Where applicable, they also specify conversion functions to convert between PL/SQL types and SQL types.

JPublisher checks the default type map first. If you attempt in the user type map to redefine a mapping that is in the default type map, JPublisher generates a warning message and ignores the redefinition. Similarly, attempts to add mappings through -adddefaulttypemap or -addtypemap settings that conflict with previous mappings are ignored and generate warnings.

There are typically two scenarios for using the type maps:

  • Specify type mappings for PL/SQL data types that are unsupported by JDBC.

  • Avoid regenerating a Java class to map to a user-defined type. For example, assume you have a user-defined SQL object type, STUDENT, and have already generated a Student class to map to it. If you specify the STUDENT:Student mapping in the user type map, then JPublisher finds the Student class and uses it for mapping without regenerating it.

To use custom mappings, it is recommended that you clear the default type map, as follows:

-defaulttypemap=

Then use the -addtypemap option to put any required mappings into the user type map.

The predefined default type map defines a correspondence between the SYS.XMLTYPE SQL OPAQUE type and the oracle.xdb.XMLType Java wrapper class. In addition, it maps the PL/SQL BOOLEAN type to the Java boolean type and the SQL INTEGER type through two conversion functions defined in the SYS.SQLJUTL package. Also, the default type map provides mappings between the SQL INTERVAL type and the Java String type.

However, you may prefer mapping the PL/SQL BOOLEAN type to the Java object type Boolean to capture the SQL NULL values in addition to the true and false values. You can accomplish this by resetting the default type map, as shown by the following:

-defaulttypemap=BOOLEAN:Boolean:INTEGER:SYS.SQLJUTL.INT2BOOL:SYS.SQLJUTL.BOOL2INT

This changes the designated Java type from boolean to Boolean, as well as eliminating any other existing default type map entries. The rest of the conversion remains valid.

Example: Using the Type Map to Avoid Regeneration The following example uses the JPublisher type map to avoid the mapping of regenerated Java classes. Assume the following type declarations, noting that the CITY type is an attribute of the TRIP type:

SQL> CREATE TYPE city AS OBJECT (name VARCHAR2(20), state VARCHAR2(10));
/
SQL> CREATE OR REPLACE TYPE trip AS OBJECT (leave DATE, place city);
/

Now assume that you invoke JPublisher as follows:

% jpub -u scott/tiger -s TRIP:Trip

The JPublisher output is:

SCOTT.TRIP
SCOTT.CITY

Only TRIP is specified for processing. However, the command produces the source files City.java, CityRef.java, Trip.java, and TripRef.java, because CITY is an attribute.

If you want to regenerate the classes for TRIP without regenerating the classes for CITY, then you can rerun JPublisher as follows:

% jpub -u scott/tiger -addtypemap=CITY:City -s TRIP:Trip
SCOTT.TRIP

As you can see from the output line, the CITY type is not reprocessed and, therefore, the City.java and CityRef.java files are not regenerated. This is because of the addition of the CITY:City relationship to the type map, which informs JPublisher that the existing City class is to be used for mapping.

JPublisher Logical Progression for Data Type Mappings

To map a given SQL or PL/SQL type to Java, JPublisher uses the following logical progression:

  1. Checks the type maps to see if the mapping is already specified.

  2. Checks the predefined Java mappings for SQL and PL/SQL types.

  3. Checks whether the data type to be mapped is a PL/SQL RECORD type or an index-by table type. If it is a PL/SQL RECORD type, JPublisher generates a corresponding SQL object type that it can then map to Java. If it is an index-by table type, JPublisher generates a corresponding SQL collection type that it can then map to Java.

  4. If none of steps 1 through 3 apply, then the data type must be a user-defined type. JPublisher generates an ORAData or SQLData class to map it according to the JPublisher option settings.

Object Attribute Types

You can use a subset of the SQL data types in Table 3-1 as object attribute types. The types that can be used are listed here:

  • CHAR, VARCHAR, VARCHAR2, CHARACTER

  • NCHAR, NVARCHAR2

  • DATE

  • DECIMAL, DEC, NUMBER, NUMERIC

  • DOUBLE PRECISION, FLOAT

  • INTEGER, SMALLINT, INT

  • REAL

  • RAW, LONG RAW

  • CLOB

  • BLOB

  • BFILE

  • NCLOB

  • Object type, OPAQUE type, SQLJ object type

  • Nested table, VARRAY type

  • Object reference type

JPublisher supports the following TIMESTAMP types as object attributes:

  • TIMESTAMP

  • TIMESTAMP WITH TIMEZONE

  • TIMESTAMP WITH LOCAL TIMEZONE

Note:

The Oracle JDBC implementation does not support the TIMESTAMP types.

REF CURSOR Types and Result Sets Mapping

If a PL/SQL stored procedure or function or a SQL query returns a REF CURSOR, then JPublisher generates a method, by default, to map the REF CURSOR to java.sql.ResultSet.

In addition, for a SQL query, but not for a REF CURSOR returned by a stored procedure or function, JPublisher generates a method to map the REF CURSOR to an array of rows. In this array, each row is represented by a JavaBean instance.

In addition, with a setting of -style=webservices-common, if the following classes are available in the classpath, then JPublisher generates methods to map the REF CURSOR to the following types:

  • javax.xml.transform.Source

  • oracle.jdbc.rowset.OracleWebRowSet

  • org.w3c.dom.Document

Note:

  • The dependency of having the class in the classpath in order to generate the mapping is specified by a CONDITION statement in the style file. The CONDITION statement lists required classes.

  • The webservices9 and webservices10 style files include webservices-common, but override these mappings. Therefore, JPublisher will not produce these mappings with a setting of -style=webservices9 or -style=webservices10.

If required, you need to perform the following actions to ensure that JPublisher can find the classes:

  1. Ensure that the libraries translator.jar, runtime12.jar, and ojdbc5.jar are in the classpath. These files contain JPublisher and SQLJ translator classes, SQLJ run time classes, and JDBC classes, respectively.

  2. Use Java Development Kit (JDK) 1.4, for mapping to Source. This class is not defined in earlier JDK versions.

  3. Add ORACLE_HOME/jdbc/lib/rowset-jsr114.jar to the classpath, for mapping to OracleWebRowSet.

  4. Add ORACLE_HOME/lib/xmlparsev2.jar to the classpath, for mapping to Document.

Consider the following PL/SQL stored procedure:

TYPE curtype1 IS REF CURSOR RETURN emp%rowtype;
FUNCTION get1 RETURN curtype1;

If the OracleWebRowSet class is found in the classpath during publishing, but Document and Source are not, then JPublisher generates the following methods for the get1 function:

public oracle.jdbc.rowset.OracleWebRowSet get1WebRowSet()
                                 throws java.sql.SQLException;
public java.sql.ResultSet get1() throws java.sql.SQLException;

The names of methods returning Document and Source would be get1XMLDocument() and get1XMLSource(), respectively.

Disabling Mapping to Source, OracleWebRowSet, or Document

There is currently no JPublisher option to explicitly enable or disable mapping to Source, OracleWebRowSet, or Document. The only condition in the webservices-common style file is whether the classes exist in the classpath. However, you can copy and edit your own style file if you want more control over how JPublisher maps REF CURSOR. The following code is an excerpt from the webservices-common file that has been copied and edited as an example. Descriptions of the edits follow the code.

BEGIN_TRANSFORMATION
MAPPING
SOURCETYPE java.sql.ResultSet
TARGETTYPE java.sql.ResultSet
RETURN
%2 = %1;
END_RETURN;
END_MAPPING

MAPPING
#CONDITION oracle.jdbc.rowset.OracleWebRowSet
SOURCETYPE java.sql.ResultSet
TARGETTYPE oracle.jdbc.rowset.OracleWebRowSet
TARGETSUFFIX WebRowSet
RETURN
%2 = null;
if (%1!=null)
{
  %2 = new oracle.jdbc.rowset.OracleWebRowSet();
  %2.populate(%1);
}
END_RETURN
END_MAPPING
 
#MAPPING
#CONDITION org.w3c.dom.Document oracle.xml.sql.query.OracleXMLQuery
#SOURCETYPE java.sql.ResultSet
#TARGETTYPE org.w3c.dom.Document
#TARGETSUFFIX XMLDocument
#RETURN
#%2 = null;
#if (%1!=null)
#  %2= (new oracle.xml.sql.query.OracleXMLQuery
#                                 (_getConnection(), %1)).getXMLDOM();
#END_RETURN
#END_MAPPING

MAPPING
CONDITION org.w3c.dom.Document oracle.xml.sql.query.OracleXMLQuery
          javax.xml.transform.Source javax.xml.transform.dom.DOMSource
SOURCETYPE java.sql.ResultSet
TARGETTYPE javax.xml.transform.Source
TARGETSUFFIX XMLSource
RETURN
%2 = null;
if (%1!=null)
  %2= new javax.xml.transform.dom.DOMSource
      ((new oracle.xml.sql.query.OracleXMLQuery
       (new oracle.xml.sql.dataset.OracleXMLDataSetExtJdbc(_getConnection(),
       (oracle.jdbc.OracleResultSet) %1))).getXMLDOM());
END_RETURN
END_MAPPING
END_TRANSFORMATION

Assume that you copy this file into myrefcursormaps.properties. There are four MAPPING sections intended for mapping REF CURSOR to ResultSet, OracleWebRowSet, Document, and Source according to the SOURCETYPE and TARGETTYPE entries. For this example, lines are commented out using the "#" character to accomplish the following:

  • The CONDITION statement is commented out for the OracleWebRowSet mapping. Because of this, JPublisher will generate a method for this mapping regardless of whether OracleWebRowSet is in the classpath.

  • The entire MAPPING section is commented out for the Document mapping. JPublisher will not generate a method for this mapping.

Run JPublisher with the following options to use your custom mappings:

% jpub -u scott/tiger -style=myrefcursormaps -s MYTYPE:MyType

Connection in JDBC Mapping

With the -usertypes=jdbc setting, JPublisher generates SQLData for a SQL object type. The underlying JDBC connection for a SQLData instance is not automatically set by the JDBC driver. Therefore, before accessing attributes in a SQLData instance, you must set a JDBC connection using the setConnectionContext() method.

Consider Address is a SQLData class generated by JPublisher with -usertypes=jdbc. The following code segment accesses the attribute of an Address instance. Note that the setConnectionContext call explicitly initializes the underlying JDBC connection.

...
ResultSet rset = stmt.executeQuery();
Address address = (Address) rset.getObject(1);
address.setConnectionContext(new sqlj.runtime.ref.DefaultContext(connection));
String addr = address.getAddress();
...

Note:

Other -usertypes settings do not require setting the connection, as described in the preceding code example.

On the other hand, for ORAData types that JPublisher generates with the -usertypes=oracle setting or by default, connection initialization is not required. The underlying JDBC connection for ORAData is already assigned at the time it is read from ResultSet.

Support for PL/SQL Data Types

There are three scenarios if JPublisher encounters a PL/SQL stored procedure or function, including method of a SQL object type, which uses a PL/SQL type that is unsupported by JDBC:

The following sections discuss further details of JPublisher type mapping features for PL/SQL types unsupported by JDBC:

Type Mapping Support for OPAQUE Types

This section describes JPublisher type mapping support for SQL OPAQUE types in general and the SYS.XMLTYPE SQL OPAQUE type in particular. It covers the following topics:

Note:

If you want JPublisher to generate wrapper classes for SQL OPAQUE types, then you must use an Oracle9i Database release 2 (9.2) or later installation and JDBC driver.

Support for OPAQUE Types

The Oracle JDBC and SQLJ implementations support SQL OPAQUE types published as Java classes implementing the oracle.sql.ORAData interface. Such classes must contain the following public, static fields and methods:

public static String _SQL_NAME = "SQL_name_of_OPAQUE_type";
public static int _SQL_TYPECODE = OracleTypes.OPAQUE;
public static ORADataFactory getORADataFactory() { ... }

If you have a Java wrapper class to map to a SQL OPAQUE type, and the class meets this requirement, then you can specify the mapping through the JPublisher user type map. Use the -addtypemap option with the following syntax to append the mapping to the user type map:

-addtypemap=sql_opaque_type:java_wrapper_class

In Oracle Database 11g, the SYS.XMLTYPE SQL OPAQUE type is mapped to the oracle.xdb.XMLType Java class through the JPublisher default type map. You could accomplish the same thing explicitly through the user type map, as follows:

-addtypemap=SYS.XMLTYPE:oracle.xdb.XMLType

Whenever JPublisher encounters a SQL OPAQUE type for which no type correspondence has been provided, it publishes a Java wrapper class. Consider the following SQL type defined in the SCOTT schema:

CREATE TYPE X_TYP AS OBJECT (xml SYS.XMLTYPE);

The following command publishes X_TYP as a Java class XTyp:

% jpub -u scott/tiger -s X_TYP:XTyp

By default, the xml attribute is published using oracle.xdb.XMLType, which is the predefined type mapping for SYS.XMLTYPE. If you clear the JPublisher default type map, then a wrapper class, Xmltype, will automatically be generated for the SYS.XMLTYPE attribute. You can verify this by invoking JPublisher as follows:

% jpub -u scott/tiger -s X_TYP:XTyp -defaulttypemap=

The -defaulttypemap option is for setting the JPublisher default type map. Giving it no value, as in the preceding example, clears it.

Support for XMLTYPE

In Oracle Database 11g, the SYS.XMLTYPE SQL OPAQUE type is supported with the oracle.xdb.XMLType Java class located in ORACLE_HOME/lib/xsu12.jar. This class is the default mapping, but it requires the Oracle Database 11g JDBC Oracle Call Interface (OCI) driver. It is currently not supported by the JDBC Thin driver.

The SQLJ run time provides the Java class oracle.sql.SimpleXMLType as an alternative mapping for SYS.XMLTYPE. This works on both the OCI driver and the Thin driver. With the following setting, JPublisher maps SYS.XMLTYPE to oracle.sql.SimpleXMLType:

-adddefaulttypemap=SYS.XMLTYPE:oracle.sql.SimpleXMLType

SimpleXMLType, defined in runtime12.jar, can read an XMLTYPE instance as a java.lang.String instance, or create an XMLTYPE instance from a String instance.

For Java-to-Java type transformations, which is often necessary for Web services, the webservices-common.properties style file specifies the preceding mapping as well as the Java-to-Java mapping of SimpleXMLType to java.lang.String. With a setting of -style=webservices-common, JPublisher maps SYS.XMLTYPE to SimpleXMLType in the generated base Java class and to String in the user subclass.

The webservices9.properties and webservices10.properties style files include the webservices-common.properties file. However, these files override the Java-to-Java mapping from SimpleXMLType to String. The webservices9.properties file maps SimpleXMLType to org.w3c.dom.DocumentFragment for the user subclass and the webservices10.properties file maps it to javax.xml.transform.Source.

Consider a setting of -style=webservices9 as an example. The user subclass converts from SimpleXMLType to DocumentFragment or from DocumentFragment to SimpleXMLType, so that a SQL or PL/SQL method using SYS.XMLTYPE can be exposed as a Java method by using org.w3c.dom.DocumentFragment. The following example contains the JPublisher command line and portions of the PL/SQL procedure, the Java interface, the base Java class, and the user subclass.

The JPublisher command on the command line is:

% jpub -u scott/tiger -sql=xtest:XTestBase:XTestUser#XTest -style=webservices9

The SQL definitions are:

PROCEDUR setXMLMessage(x XMLTYPE, y NUMBER);
FUNCTION getXMLMessage(id NUMBER) RETURN XMLTYPE;

The definitions in the XTest interface are:

public org.w3c.dom.DocumentFragment getxmlmessage(java.math.BigDecimal id)
public void setxmlmessage(org.w3c.dom.DocumentFragment x,
                          java.math.BigDecimal y)

The definitions in XTestBase.java are:

public oracle.sql.SimpleXMLType _getxmlmessage (java.math.BigDecimal id)
public void _setxmlmessage (oracle.sql.SimpleXMLType x, java.math.BigDecimal y)

The definitions in XTestUser.java are:

public org.w3c.dom.DocumentFragment getxmlmessage(java.math.BigDecimal id) 
public void setxmlmessage(org.w3c.dom.DocumentFragment x,
                          java.math.BigDecimal y)

Type Mapping Support for Scalar Index-by Tables

The term scalar PL/SQL index-by table refers to a PL/SQL index-by table with elements of VARCHAR and numerical types. Starting 10g Release 2 (10.2), JPublisher can map a simple PL/SQL index-by table into a Java array, as an alternative to mapping PL/SQL index-by tables into custom JDBC types. The option plsqlindextable specifies how a simple PL/SQL index-by table is mapped.

-plsqlindextable=custom|array|int

If -plsqlindextable=custom is set, all indexby tables are mapped to custom JDBC types, such as SQLData, CustomDatum, or ORAData. If -plsqlindextable=array or -plsqlindextable=int is set, a simple index-by table will be mapped to a Java array. With -plsqlindextable=int, the int value specifies the array capacity, which is 32768 by default. The default setting for this option is custom.

Consider the following PL/SQL package:

CREATE OR REPLACE PACKAGE indexbytable_package AS
  TYPE index_tbl1 IS TABLE OF VARCHAR2(111) INDEX BY binary_integer;
  TYPE index_tbl2 IS TABLE OF NUMBER INDEX BY binary_integer;
  TYPE varray_tbl3 IS VARRAY(100) OF VARCHAR2(20);
  TYPE nested_tbl4 IS TABLE OF VARCHAR2(20);
  FUNCTION echo_index_tbl1(a index_tbl1) RETURN index_tbl1;
  FUNCTION echo_index_tbl2(a index_tbl2) RETURN index_tbl2;
  FUNCTION echo_varray_tbl3(a varray_tbl3) RETURN varray_tbl3;
  FUNCTION echo_nested_tbl4(a nested_tbl4) RETURN nested_tbl4;
END;
/

Run the following command:

% jpub -u scott/tiger -sql=indexbytable_package:IndexbyTablePackage#IndexbyTableIntf -plsqlindextable=32

The -plsqlindextable=32 setting specifies that simple index-by tables are mapped to Java arrays, with a capacity of 32. The following interface is generated in IndexbyTableIntf.java:

public interface IndexbyTableIntf
{
 public String[] echoIndexTbl1(String[] a);
 public java.math.BigDecimal[] echoIndexTbl2(java.math.BigDecimal[] a);
 public IndexbytableintfVarrayTbl3 echoVarrayTbl4(IndexbytableintfVarrayTbl3 a);
 public IndexbytableintfNestedTbl4 echoVarrayTbl4(IndexbytableintfNestedTbl4 a);
}

In the generated code, the simple index-by table types, index_tbl1 and index_tb2, are mapped to String[] and BigDecimal[] respectively. The nested table and varray table, however, are still mapped to custom JDBC types, because they are not index-by tables and their mappings are not affected by the -plsqlindextable setting.

The limitation of mapping PL/SQL index-by table to an array is that the table must be indexed by integer. If a PL/SQL package contains both tables indexed by integer and by VARCHAR, you cannot use the setting -plsqlindexbytable=array or -plsqlindexbytable=int. Otherwise the mapping for the table indexed by VARCHAR will encounter run-time errors. Instead, one must use -plsqlindexbytable=custom.

Mapping of the index-by table elements follows the JDBC type mappings. For example, with JDBC mapping, SMALLINT is mapped to the Java int type. Therefore, an index-by table of SMALLINT is mapped to int[]. The -plsqlindexbytable=array or -plsqlindexbytable=int setting will be ignored if Oracle mappings are turned on for numbers, that is, -numbertypes=oracle. The reason is that the Java array mapped to the index-by table must have string or numerical Java types as elements, while Oracle mappings map SQL numbers into oracle.sql types.

The Oracle JDBC drivers directly support PL/SQL scalar index-by tables with numeric or character elements. An index-by table with numeric elements can be mapped to the following Java array types:

  • int[]

  • double[]

  • float[]

  • java.math.BigDecimal[]

  • oracle.sql.NUMBER[]

An index-by table with character elements can be mapped to the following Java array types:

  • String[]

  • oracle.sql.CHAR[]

In the following circumstances, you must convey certain information for an index-by table type, as described:

  • Whenever you use the index-by table type in an OUT or IN OUT parameter, you must specify the maximum number of elements, which is otherwise optional. You can specify the maximum number of elements using the customary syntax for Java array allocation. For example, you could specify int[100] to denote a type that can accommodate up to 100 elements or oracle.sql.CHAR[20] for up to 20 elements.

  • For index-by tables with character elements, you can optionally specify the maximum size of an individual element, in bytes. This setting is defined using the SQL-like size syntax. For example, for an index-by table used for IN arguments, you could specify String[](30). You could also specify oracle.sql.CHAR[20](255) for an index-by table of maximum length 20, the elements of which will not exceed 255 bytes each.

Use the JPublisher -addtypemap option to add instructions to the user type map to specify correspondences between PL/SQL types, which are scalar index-by tables, and the corresponding Java array types. The size hints that are given using the syntax outlined earlier are embedded into the generated SQLJ class and thus conveyed to JDBC at run time.

As an example, consider the following code fragment from the definition of the INDEXBY PL/SQL package in the SCOTT schema. Assume this is available in a file called indexby.sql.

CREATE OR REPLACE PACKAGE indexby AS

--  jpub.addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
--  jpub.addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
--  jpub.addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

 TYPE varchar_ary IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
 TYPE integer_ary IS TABLE OF INTEGER        INDEX BY BINARY_INTEGER;
 TYPE float_ary   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;

 FUNCTION get_float_ary RETURN float_ary;
 PROCEDURE pow_integer_ary(x integer_ary, y OUT integer_ary);
 PROCEDURE xform_varchar_ary(x IN OUT varchar_ary);

END indexby;
/
CREATE OR REPLACE PACKAGE BODY indexby IS ...
/

The following are the required -addtypemap directives for mapping the three index-by table types:

-addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
-addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
-addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

Note that depending on the operating system shell you are using, you may have to quote options that contain square brackets [...] or parentheses (...). You can avoid this by placing such options into a JPublisher properties file, as follows:

jpub.addtypemap=SCOTT.INDEXBY.VARCHAR_ARY:String[1000](4000)
jpub.addtypemap=SCOTT.INDEXBY.INTEGER_ARY:int[1000]
jpub.addtypemap=SCOTT.INDEXBY.FLOAT_ARY:double[1000]

Additionally, as a feature of convenience, JPublisher directives in a properties file are recognized when placed behind a "--" prefix (two dashes), whereas any entry that does not start with "jpub." or with "-- jpub." is ignored. So, you can place JPublisher directives into SQL scripts and reuse the same SQL scripts as JPublisher properties files. Thus, after invoking the indexby.sql script to define the INDEXBY package, you can now run JPublisher to publish this package as a Java class, IndexBy, as follows:

% jpub -u scott/tiger -s INDEXBY:IndexBy -props=indexby.sql

As mentioned previously, you can use this mapping of scalar index-by tables only with the Oracle JDBC drivers. If you are using another driver or if you want to create driver-independent code, then you must define SQL types that correspond to the index-by table types, as well as defining conversion functions that map between the two.

Type Mapping Support Through PL/SQL Conversion Functions

This section discusses the mechanism that JPublisher uses for supporting PL/SQL types in Java code, through PL/SQL conversion functions that convert between each PL/SQL type and a corresponding SQL type to allow access by JDBC.

In general, Java programs do not support the binding of PL/SQL-specific types. The only way you can use such types from Java is to use PL/SQL code to map them to SQL types, and then access these SQL types from Java. However, one exception is the scalar index-by table type.

JPublisher makes this task more convenient through the use of its type maps. For a particular PL/SQL type, specify the following information in a JPublisher type map entry:

  • Name of the PL/SQL type, typically of the form:

    SCHEMA.PACKAGE.TYPE
    
    
  • Name of the corresponding Java wrapper class

  • Name of the SQL type that corresponds to the PL/SQL type

    You must be able to directly map this type to the Java wrapper type. For example, if the SQL type is NUMBER, then the corresponding Java type could be int, double, Integer, Double, java.math.BigDecimal, or oracle.sql.NUMBER. If the SQL type is an object type, then the corresponding Java type would be an object wrapper class that implements the oracle.sql.ORAData or java.sql.SQLData interface. The object wrapper class is typically generated by JPublisher.

  • Name of a PL/SQL conversion function that maps the SQL type to the PL/SQL type

  • Name of a PL/SQL conversion function that maps the PL/SQL type to the SQL type

The -addtypemap specification for this has the following form:

-addtypemap=plsql_type:java_type:sql_type:sql_to_plsql_fun:plsql_to_sql_fun

As an example, consider a type map entry for supporting the PL/SQL BOOLEAN type. It consists of the following specifications:

  • Name of the PL/SQL type: BOOLEAN

  • Specification to map it to Java boolean

  • Corresponding SQL type: INTEGER

    JDBC considers boolean values as special numeric values.

  • Name of the PL/SQL function that maps from SQL to PL/SQL: INT2BOOL

    The code for the function is:

    FUNCTION int2bool(i INTEGER) RETURN BOOLEAN IS
    BEGIN IF i IS NULL THEN RETURN NULL;
          ELSE RETURN i<>0;
          END IF;
    END int2bool;
    
    
  • Name of the PL/SQL function that maps from PL/SQL to SQL: BOOL2INT

    The code for the function is:

    FUNCTION bool2int(b BOOLEAN) RETURN INTEGER IS
    BEGIN IF b IS NULL THEN RETURN NULL;
          ELSIF b THEN RETURN 1;
          ELSE RETURN 0; 
          END IF;
    END bool2int;
    
    

You can put all this together in the following type map entry:

-addtypemap=BOOLEAN:boolean:INTEGER:INT2BOOL:BOOL2INT

Such a type map entry assumes that the SQL type, the Java type, and both conversion functions have been defined in SQL, Java, and PL/SQL, respectively. Note that there is already an entry for PL/SQL BOOLEAN in the JPublisher default type map. If you want to try the preceding type map entry, you will have to override the default type map. You can use the JPublisher -defaulttypemap option to accomplish this, as follows:

% jpub -u scott/tiger -s SYS.SQLJUTL:SQLJUtl
       -defaulttypemap=BOOLEAN:boolean:INTEGER:INT2BOOL:BOOL2INT

Note:

  • In some cases, such as with INT2BOOL and BOOL2INT in the preceding example, JPublisher has conversion functions that are predefined, typically in the SYS.SQLJUTL package. In other cases, such as for RECORD types and index-by table types, JPublisher generates conversion functions during execution.

  • Although this manual describes conversions as mapping between SQL and PL/SQL types, there is no intrinsic restriction to PL/SQL in this approach. You could also map between different SQL types. In fact, this is done in the JPublisher default type map to support SQL INTERVAL types, which are mapped to VARCHAR2 values and back.

Be aware that under some circumstances, PL/SQL wrapper functions are also created by JPublisher. Each wrapper function wraps a stored procedure that uses PL/SQL types. It calls this original stored procedure and processes its PL/SQL input or output through the appropriate conversion functions so that only the corresponding SQL types are exposed to Java. The following JPublisher options control how JPublisher creates code for invocation of PL/SQL stored procedures that use PL/SQL types, including the use of conversion functions and possibly the use of wrapper functions:

  • -plsqlpackage=plsql_package

    This option determines the name of the PL/SQL package into which JPublisher generates the PL/SQL conversion functions: a function to convert each unsupported PL/SQL type to the corresponding SQL type and a function to convert from each corresponding SQL type back to the PL/SQL type. Optionally, depending on how you set the -plsqlmap option, the package also contains wrapper functions for the original stored procedures, with each wrapper function invoking the appropriate conversion function.

    If you do not specify a package name, then JPublisher uses JPUB_PLSQL_WRAPPER.

  • -plsqlfile=plsql_wrapper_script,plsql_dropper_script

    This option determines the name of the wrapper script and dropper script that JPublisher creates. The wrapper script creates necessary SQL types that map to unsupported PL/SQL types and also creates the PL/SQL package. The dropper script drops these SQL types and the PL/SQL package.

    If the files already exist, then they will be overwritten. If you do not specify any file names, then JPublisher will write to the files named plsql_wrapper.sql and plsql_dropper.sql.

  • -plsqlmap=flag

    This option specifies whether JPublisher generates wrapper functions for stored procedures that use PL/SQL types. Each wrapper function calls the corresponding stored procedure and the appropriate PL/SQL conversion functions for PL/SQL input or output of the stored procedure. Only the corresponding SQL types are exposed to Java. The flag setting can be any of the following:

    • true

      This is the default setting. JPublisher generates PL/SQL wrapper functions only as needed. For any given stored procedure, if the Java code to call it and convert its PL/SQL types directly is simple enough, and if PL/SQL types are used only as IN parameters or for the function return, then the generated code calls the stored procedure directly instead. The code then processes the PL/SQL input or output through the appropriate conversion functions.

      If a PL/SQL type is used as an OUT or IN OUT parameter, then wrapper functions are required, because conversions between PL/SQL and SQL representations may be necessary either before or after calling the original stored procedure.

    • false

      JPublisher does not generate PL/SQL wrapper functions. If it encounters a PL/SQL type in a signature that cannot be supported by a direct call and conversion, then it skips the generation of Java code for the particular stored procedure.

    • always

      JPublisher generates a PL/SQL wrapper function for every stored procedure that uses a PL/SQL type. This setting is useful for generating a proxy PL/SQL package that complements an original PL/SQL package, providing JDBC-accessible signatures for those functions or procedures that were not accessible through JDBC in the original package.

Type Mapping Support for PL/SQL RECORD and Index-By Table Types

JPublisher automatically publishes a PL/SQL RECORD type whenever it publishes a PL/SQL stored procedure or function that uses that type as an argument or return type. The same is true for PL/SQL index-by table types. This is the only way that a RECORD type or index-by table type can be published. There is no way to explicitly request any such types to be published through JPublisher option settings.

Note:

The following are limitations to the JPublisher support for PL/SQL RECORD and index-by table types:
  • An intermediate wrapper layer is required to map a RECORD or index-by-table argument to a SQL type that JDBC can support. In addition, JPublisher cannot fully support the semantics of index-by tables. An index-by table is similar in structure to a Java hashtable, but information is lost when JPublisher maps this to a SQL TABLE type.

  • If you use the JDBC OCI driver and require only the publishing of scalar index-by tables, then you can use the direct mapping between Java and these types.

The following sections demonstrate JPublisher support for PL/SQL RECORD types and index-by table types:

Sample Package for RECORD Type and Index-By Table Type Support

The following PL/SQL package is used to illustrate JPublisher support for PL/SQL RECORD and index-by table types:

CREATE OR REPLACE PACKAGE company IS
  TYPE emp_rec IS RECORD (empno NUMBER, ename VARCHAR2(10));
  TYPE emp_tbl IS TABLE OF emp_rec INDEX BY binary_integer;
  PROCEDURE set_emp_rec(er emp_rec);
  FUNCTION get_emp_rec(empno number) RETURN emp_rec;
  FUNCTION get_emp_tbl RETURN emp_tbl;
END;

This package defines a PL/SQL RECORD type, EMP_REC, and a PL/SQL index-by table type, EMP_TBL. Use the following command to publish the COMPANY package:

% jpub -u scott/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
  -plsqlfile=wrapper1.sql,dropper1.sql

The JPublisher output is:

SCOTT.COMPANY
SCOTT."COMPANY.EMP_REC"
SCOTT."COMPANY.EMP_TBL"
J2T-138, NOTE: Wrote PL/SQL package WRAPPER1 to file wrapper1.sql.
Wrote the dropping script to file dropper1.sql

In this example, JPublisher generates Company.java for the Java wrapper class for the COMPANY package, as well as the following SQL and Java entities:

  • The wrapper1.sql script that creates the SQL types corresponding to the PL/SQL RECORD and index-by table types, and also creates the conversion functions between the SQL types and the PL/SQL types

  • The dropper1.sql script that removes the SQL types and conversion functions created by wrapper1.sql

  • The CompanyEmpRec.java source file for the Java wrapper class for the SQL object type that is generated for the PL/SQL RECORD type

  • The CompanyEmpTbl.java source file for the Java wrapper class for the SQL collection type that is generated for the PL/SQL index-by table type

Support for RECORD Types

This section continues the example from Sample Package for RECORD Type and Index-By Table Type Support. For the PL/SQL RECORD type, EMP_REC, JPublisher generates the corresponding COMPANY_EMP_REC SQL object type. JPublisher also generates the conversion functions between the two. In this example, the following is generated in wrapper1.sql for EMP_REC:

CREATE OR REPLACE TYPE COMPANY_EMP_REC AS OBJECT (
                       EMPNO NUMBER(22),
                       ENAME VARCHAR2(10)
);
/
-- Declare package containing conversion functions between SQL and PL/SQL types
CREATE OR REPLACE PACKAGE WRAPPER1 AS
   -- Declare the conversion functions the PL/SQL type COMPANY.EMP_REC
        FUNCTION PL2COMPANY_EMP_REC(aPlsqlItem COMPANY.EMP_REC)
        RETURN COMPANY_EMP_REC;
        FUNCTION COMPANY_EMP_REC2PL(aSqlItem COMPANY_EMP_REC)
        RETURN COMPANY.EMP_REC;
END WRAPPER1;
/

In addition, JPublisher publishes the COMPANY_EMP_REC SQL object type into the CompanyEmpRec.java Java source file.

Once the PL/SQL RECORD type is published, you can add the mapping to the type map. The following is an entry in a sample JPublisher properties file, done.properties:

jpub.addtypemap=SCOTT.COMPANY.EMP_REC:CompanyEmpRec:COMPANY_EMP_REC:
WRAPPER1.COMPANY_EMP_REC2PL:WRAPPER1.PL2COMPANY_EMP_REC

Use this type map entry whenever you publish a package or type that refers to the RECORD type, EMP_REC. For example, the following JPublisher invocation uses done.properties with this type map entry:

% jpub -u scott/tiger -p done.properties -s COMPANY -plsqlpackage=WRAPPER2
       -plsqlfile=wrapper2.sql,dropper2.sql

The JPublisher output is:

SCOTT.COMPANY
SCOTT."COMPANY.EMP_TBL"
J2T-138, NOTE: Wrote PL/SQL package WRAPPER2 to file wrapper2.sql. 
Wrote the dropping script to file dropper2.sql

Support for Index-By Table Types

This section continues the example from Sample Package for RECORD Type and Index-By Table Type Support.

To support an index-by table type, a SQL collection type must be defined that permits conversion to and from the PL/SQL index-by table type. JPublisher also supports PL/SQL nested tables and VARRAYs in the same fashion. Therefore, JPublisher generates the same code for the following three definitions of EMP_TBL:

TYPE emp_tbl IS TABLE OF emp_rec INDEX BY binary_integer;
TYPE emp_tbl IS TABLE OF emp_rec;
TYPE emp_tbl IS VARRAY OF emp_rec;

For the PL/SQL index-by table type EMP_TBL, JPublisher generates a SQL collection type, and conversion functions between the index-by table type and the SQL collection type.

In addition to what was shown for the RECORD type earlier, JPublisher generates the following:

-- Declare the SQL type for the PL/SQL type COMPANY.EMP_TBL
CREATE OR REPLACE TYPE COMPANY_EMP_TBL AS TABLE OF COMPANY_EMP_REC; 
/
-- Declare package containing conversion functions between SQL and PL/SQL types
CREATE OR REPLACE PACKAGE WRAPPER1 AS
   -- Declare the conversion functions for the PL/SQL type COMPANY.EMP_TBL
        FUNCTION PL2COMPANY_EMP_TBL(aPlsqlItem COMPANY.EMP_TBL)
        RETURN COMPANY_EMP_TBL;
        FUNCTION COMPANY_EMP_TBL2PL(aSqlItem COMPANY_EMP_TBL)
        RETURN COMPANY.EMP_TBL;
...
END WRAPPER1;

JPublisher further publishes the SQL collection type into CompanyEmpTbl.java.

As with a PL/SQL RECORD type, once a PL/SQL index-by table type is published, the published result, including the Java wrapper classes, the SQL collection type, and the conversion functions, can be used in the future for publishing PL/SQL packages involving that PL/SQL index-by table type. For example, if you add the following entry into a properties file that you use in invoking JPublisher, say done.properties, then JPublisher will use the provided type map and avoid republishing that index-by table type:

jpub.addtypemap=SCOTT.COMPANY.EMP_TBL:CompanyEmpTbl:COMPANY_EMP_TBL:
WRAPPER1.COMPANY_EMP_TBL2PL:WRAPPER1.PL2COMPANY_EMP_TBL

Direct Use of PL/SQL Conversion Functions Versus Use of Wrapper Functions

In generating Java code to invoke a stored procedure that uses a PL/SQL type, JPublisher can use either of the following modes of operation:

  • Invoke the stored procedure directly, which processes the PL/SQL input or output through the appropriate conversion functions.

  • Invoke a PL/SQL wrapper function, which in turn calls the stored procedure and processes its PL/SQL input or output through the appropriate conversion functions. The wrapper function that is generated by JPublisher uses the corresponding SQL types for input or output.

The -plsqlmap option determines whether JPublisher uses the first mode, the second mode, or possibly either mode, depending on circumstances.

As an example, consider the SCOTT.COMPANY.GET_EMP_TBL PL/SQL stored procedure that returns the EMP_TBL PL/SQL index-by table type. Assume that the COMPANY package, introduced in "Sample Package for RECORD Type and Index-By Table Type Support", is processed by JPublisher through the following command:

% jpub -u scott/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
       -plsqlfile=wrapper1.sql,dropper1.sql -plsqlmap=false

The JPublisher output is:

SCOTT.COMPANY
SCOTT."COMPANY.EMP_REC"
SCOTT."COMPANY.EMP_TBL"
J2T-138, NOTE: Wrote PL/SQL package WRAPPER1 to file wrapper1.sql.
Wrote the dropping script to file dropper1.sql

With this command, JPublisher creates the following:

  • SQL object type COMPANY_EMP_REC to map to the PL/SQL RECORD type EMP_REC

  • SQL collection type COMPANY_EMP_TBL to map to the PL/SQL index-by table type EMP_TBL

  • Java classes to map to COMPANY, COMPANY_EMP_REC, and COMPANY_EMP_TBL

  • PL/SQL package WRAPPER1, which includes the PL/SQL conversion functions to convert between the PL/SQL index-by table type and the SQL collection type

In this example, assume that the conversion function PL2COMPANY_EMP_TBL converts from the PL/SQL EMP_TBL type to the SQL COMPANY_EMP_TBL type. Because of the setting -plsqlmap=false, no wrapper functions are created. The stored procedure is called with the following JDBC statement in generated Java code:

conn.prepareOracleCall = 
("BEGIN :1 := WRAPPER1.PL2COMPANY_EMP_TBL(SCOTT.COMPANY.GET_EMP_TBL()) \n; END;");

SCOTT.COMPANY.GET_EMP_TBL is called directly, with its EMP_TBL output being processed through the PL2COMPANY_EMP_TBL conversion function to return the desired COMPANY_EMP_TBL SQL type.

By contrast, if you run JPublisher with the setting -plsqlmap=always, then WRAPPER1 also includes a PL/SQL wrapper function for every PL/SQL stored procedure that uses a PL/SQL type. In this case, for any given stored procedure, the generated Java code calls the wrapper function instead of the stored procedure. The wrapper function, in this example WRAPPER1.GET_EMP_TBL, calling the original stored procedure and processing its output through the conversion function is as follows:

FUNCTION  GET_EMP_TBL()
   BEGIN 
      RETURN WRAPPER1.PL2COMPANY_EMP_TBL(SCOTT.COMPANY.GET_EMP_TBL()) 
   END;

In the generated Java code, the JDBC statement calling the wrapper function is:

conn.prepareOracleCall("BEGIN :1=SCOTT.WRAPPER1.GET_EMP_TBL() \n; END;");

If -plsqlmap=true, then JPublisher uses direct calls to the original stored procedure wherever possible. However, in the case of any stored procedure for which the Java code for direct invocation and conversion is too complex or any stored procedure that uses PL/SQL types as OUT or IN OUT parameters, JPublisher generates a wrapper function and calls that function in the generated code.

Other Alternatives for Data Types Unsupported by JDBC

The preceding sections describe the mechanisms that JPublisher employs to access PL/SQL types unsupported by JDBC. As an alternative to using JPublisher in this way, you can try one of the following:

  • Rewrite the PL/SQL method to avoid using the type

  • Write an anonymous block that does the following:

    • Converts input types that JDBC supports into the input types used by the PL/SQL stored procedure

    • Converts output types used by the PL/SQL stored procedure into output types that JDBC supports

JPublisher Styles and Style Files

JPublisher style files allow you to specify Java-to-Java type mappings. This is to ensure that generated classes can be used in Web services. As an example, CLOB types, such as java.sql.Clob and oracle.sql.CLOB, cannot be used in Web services, but the data can be used if it is converted to a type that is supported by Web services, such as java.lang.String. JPublisher must generate user subclasses to implement its use of style files and Java-to-Java type transformations.

Typically, style files are provided by Oracle, but there may be situations in which you may want to edit or create your own.

The following sections discuss features and usage of styles and style files:

Style File Specifications and Locations

Use the JPublisher -style option to specify the base name of a style file:

-style=stylename

Based on the stylename you specify, JPublisher looks for a style file as follows, and uses the first file that it finds:

  1. It looks for the following resource in the classpath:

    /oracle/jpub/mesg/stylename.properties
    
    
  2. It takes stylename as a resource name, possibly qualified, and looks for the following in the classpath:

    /stylename-dir/stylename-base.properties
    
    
  3. It takes stylename as a name, possibly qualified, and looks for the following file in the current directory:

    stylename.properties
    
    

    In this case, stylename can optionally include a directory path. If you use the setting -style=mydir/foo, for example, then JPublisher looks for mydir/foo.properties relative to the current directory.

If no matching file is found, JPublisher generates an exception.

As an example of the first scenario, if the resource /oracle/jpub/mesg/webservices.properties exists in ORACLE_HOME/sqlj/lib/translator.jar and translator.jar is found in the classpath, then the -style=webservices setting uses /oracle/jpub/mesg/webservices.properties from translator.jar, even if there is a webservices.properties file in the current directory.

However, if you specify -style=mystyle and a mystyle.properties resource is not found in /oracle/jpub/mesg, but there is a mystyle.properties file in the current directory, then that is used.

Note:

Oracle currently provides three style files:
/oracle/jpub/mesg/webservices-common.properties
/oracle/jpub/mesg/webservices10.properties
/oracle/jpub/mesg/webservices9.properties

These are in the translator.jar file, which must be included in your classpath. Each file maps Oracle JDBC types to Java types supported by Web services. Note that the webservices-common.properties file is for general use and is included by both webservices10.properties and webservices9.properties.

To use Web services in Oracle Database 11g, specify the following style file:

-style=webservices10

To use Web services in Oracle9i, specify -style=webservices9.

Style File Format

The key portion of a style file is the TRANSFORMATION section. This section comprises everything between the TRANSFORMATION tag and END_TRANSFORMATION tag. It describes the type transformations, or Java-to-Java mappings, to be applied to types used for object attributes or in method signatures.

For convenience, there is an OPTIONS section in which you can specify any other JPublisher option settings. Because of this section, a style file can replace the functionality of any other JPublisher properties file, in addition to specifying mappings.

This section covers the following topics:

Note:

The following details about style files are provided for general information aboutly and are subject to change.

Style File TRANSFORMATION Section

This section provides a template for a style file TRANSFORMATION section, with comments. Within the TRANSFORMATION section, there is a MAPPING section for each mapping that you specify. The MAPPING section starts at a MAPPING tag and ends with an END_MAPPING tag. Each MAPPING section includes a number of subtags with additional information. In the MAPPING section, the SOURCETYPE and TARGETTYPE tags are the required subtags. Within each TARGETTYPE section, you should generally provide information for at least the RETURN, IN, and OUT cases, using the corresponding tags. The following code illustrates the structure of a typical TRANSFORMATION section:

TRANSFORMATION

 IMPORT
 # Packages to be imported by the generated classes
 END_IMPORT

 # THE FOLLOWING OPTION ONLY APPLIES TO PL/SQL PACKAGES
 # This interface should be implemented/extended by
 # the methods in the user subclasses and interfaces
 # This option takes no effect when subclass is not generated.
 SUBCLASS_INTERFACE java_interface

 # THE FOLLOWING OPTION ONLY APPLIES TO PL/SQL PACKAGES
 # Each method in the interface and the user subclass should
 # throw this exception (the default SQLException will be caught
 # and re-thrown as an exception specified here)
 # This option takes no effect when subclass is not generated.
 SUBCLASS_EXCEPTION Java_exception_type

 STATIC
 # Any code provided here is inserted at the
 # top level of the generated subclass regardless
 # of the actual types used.
 END_STATIC

 # Enumerate as many MAPPING sections as needed.

 MAPPING
 SOURCETYPE Java_source_type
 # Can be mapped to several target types.
 TARGETTYPE Java_target_type

 # With CONDITION specified, the source-to-target
 # mapping is carried out only when the listed Java
 # classes are present during publishing. 
 # The CONDITION section is optional.
 CONDITION list_of_java_classes

 IN
 # Java code for performing the transformation
 # from source type argument %1 to the target
 # type, assigning it to %2.
 END_IN
 IN_AFTER_CALL
 # Java code for processing IN parameters
 # after procedure call.
 END_IN_AFTER_CALL
 OUT
 # Java code for performaing the transformation
 # from a target type instance %2 to the source
 # type, assigning it to %1.
 END_OUT
 RETURN
 # Java code for performing the transformation
 # from source type argument %1 to the target
 # type and returning the target type.
 END_RETURN

 # Include the code given by a DEFINE...END_DEFINE block
 # at the end of this template file.
 USE defined_name

 # Holder for OUT/INOUT of the type defined by SOURCETYPE.
 HOLDER Java_holder_type
 END_TARGETTYPE

 # More TARGETTYPE sections, as needed

 END_MAPPING

 DEFAULT_HOLDER
 # JPublisher will generate holders for types that do
 # not have HOLDER entries defined in this template.
 # This section includes a template for class definitions
 # from which JPublisher will generate .java files for
 # holder classes.
 END_DEFAULT_HOLDER

 # More MAPPING sections, as needed

 DEFINE defined_name
 # Any code provided here is inserted at the
 # top level of the generated class if the
 # source type is used.
 END_DEFINE
 # More DEFINE sections, as needed

END_TRANSFORMATION

Note:

  • Style files use ISO8859_1 encoding. Any characters that cannot be represented directly in this encoding must be represented in Unicode escape sequences.

  • It is permissible to have multiple MAPPING sections with the same SOURCETYPE specification. For argument type, JPublisher uses the last of these MAPPING sections that it encounters.

See Also:

"Passing Output Parameters in JAX-RPC Holders" for a discussion of holders

Style File OPTIONS Section

For convenience, you can specify any desired JPublisher option settings in the OPTIONS section of a style file, in the standard format for JPublisher properties files. The syntax for the same is as follows:

OPTIONS
 # Comments
 jpub.option1=value1
 jpub.option2=value2
 ...
END_OPTIONS

Summary of Key Java-to-Java Type Mappings in Oracle Style Files

The Oracle style files webservices-common.properties, webservices9.properties, and webservices10.properties, through their SOURCETYPE and TARGETTYPE specifications, have a number of important Java-to-Java type mappings to support Web services and REF CURSOR mappings. These mappings are summarized in Table 3-2.

Table 3-2 Summary of Java-to-Java Type Mappings in Oracle Style Files

Source Type Target Type

oracle.sql.NString

java.lang.String

oracle.sql.CLOB

java.lang.String

oracle.sql.BLOB

byte[]

oracle.sql.BFILE

byte[]

java.sql.Timestamp

java.util.Date

java.sql.ResultSet

oracle.jdbc.rowset.OracleWebRowSet

org.w3c.dom.Document

javax.xml.transform.Source

oracle.sql.SimpleXMLType

java.lang.String (webservices-common)

org.w3c.dom.DocumentFragment (webservices9)

javax.xml.transform.Source (webservices10)


The webservices9 and webservices10 files include webservices-common before specifying mappings specific to these files. For SimpleXMLType, note that DocumentFragment overrides String if you set -style=webservices9 and Source overrides String, if you set -style=webservices10.

Use of Multiple Style Files

JPublisher allows multiple -style options on the command line, with the following behavior:

  • The OPTIONS sections are concatenated.

  • The TRANSFORMATION sections are concatenated, except that the entries in the MAPPING sections are overridden, as applicable. A MAPPING entry from a style file specified later on the command line overrides a MAPPING entry with the same SOURCETYPE specification from a style file specified earlier on the command line.

This functionality is useful if you want to overwrite type mappings defined earlier or add new type mappings. For example, if you want to map SYS.XMLTYPE to java.lang.String, then you can append the setting -style=xml2string to the JPublisher command line. This example assumes that the ./xml2string.properties style file will be accessed. This style file is defined as follows:

OPTIONS
       jpub.defaulttypemap=SYS.XMLTYPE:oracle.sql.SimpleXMLType
      END_OPTIONS
      TRANSFORM
      MAPPING
      SOURCETYPE oracle.sql.SimpleXMLType
      TARGETTYPE java.lang.String
      # SimpleXMLType => String
      OUT
      %2 = null;
      if (%1!=null) %2=%1.getstringval();
      END_OUT
      # String => SimpleXMLType
      IN
      %1 = null;
      if (%2!=null)
      {
        %1 = new %p.%c(_getConnection());
        %1 = %1.createxml(%2);
      }
      END_IN
      END_TARGETTYPE
      END_MAPPING
      END_TRANSFORM

Continuing this example, assume the following PL/SQL stored procedure definition:

PROCEDURE foo (arg XMLTYPE);

JPublisher maps this as follows in the base class:

void foo (arg oracle.sql.SimpleXMLType);

And JPublisher maps it as follows in the user subclass:

void foo (arg String);

Note:

By default, JPublisher maps SYS.XMLTYPE to oracle.xdb.XMLType.