3 Data Type and Java-to-Java Type Mappings

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

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 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 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.

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.

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.

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.

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.

   See Also:

   "JPublisher User Type Map and Default Type Map"

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.

   See Also:

   "Type Mapping Support for OPAQUE Types"

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.

   See Also:

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

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.

   See Also:

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

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 must 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.

   See Also:

   "Type Mapping Support for Scalar Index-by Tables"

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.

  See Also:

  "Example: Using the Type Map to Avoid Regeneration"

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 -s TRIP:Trip
Enter scott password: password

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 -addtypemap=CITY:City -s TRIP:Trip SCOTT.TRIP
Enter scott password: password

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

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

If required, you must 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 -style=myrefcursormaps -s MYTYPE:MyType
Enter scott password: password

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();
...

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.

Type Mapping Support for OPAQUE Types

This section describes JPublisher type mapping support for SQL OPAQUE types in general.

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 -s X_TYP:XTyp
Enter scott password: password

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 -s X_TYP:XTyp -defaulttypemap=
Enter scott password: password

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

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 -sql=indexbytable_package:IndexbyTablePackage#IndexbyTableIntf -plsqlindextable=32
Enter scott password: password

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.

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 -s INDEXBY:IndexBy -props=indexby.sql
Enter scott password: password

As mentioned previously, you can use this mapping of scalar index-by tables only with 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 -s SYS.SQLJUTL:SQLJUtl -defaulttypemap=BOOLEAN:boolean:INTEGER:INT2BOOL:BOOL2INT
Enter scott password: password

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.

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

• Support for RECORD Types

• Support for 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;

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

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

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;
/

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

% jpub -u scott -p done.properties -s COMPANY -plsqlpackage=WRAPPER2
       -plsqlfile=wrapper2.sql,dropper2.sql
Enter scott password: password

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

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;

-- 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;

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 -s COMPANY:Company -plsqlpackage=WRAPPER1
       -plsqlfile=wrapper1.sql,dropper1.sql -plsqlmap=false
Enter scott password: password

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

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.

/oracle/jpub/mesg/webservices-common.properties
/oracle/jpub/mesg/webservices10.properties
/oracle/jpub/mesg/webservices9.properties

-style=webservices10

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:

• Style File TRANSFORMATION Section

• Style File OPTIONS 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

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.

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.xdb.XMLType
      END_OPTIONS
      TRANSFORM
      MAPPING
      SOURCETYPE oracle.xdb.XMLType
      TARGETTYPE java.lang.String
      # XMLType => String
      OUT
      %2 = null;
      if (%1!=null) %2=%1.getStringVal();
      END_OUT
      # String => XMLType
      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.xdb.XMLType);

And JPublisher maps it as follows in the user subclass:

void foo (arg String);