2 Datatype and Java-to-Java Type Mappings

Overview of JPublisher Datatype Mappings

You can specify one of the following settings for datatype mappings when you use the type mapping options (-builtintypes, -lobtypes, -numbertypes, and -usertypes):

• oracle

• jdbc

• objectjdbc (for -numbertypes only)

• bigdecimal (for -numbertypes only)

These mappings, described in "JPublisher Mapping Categories", affect the argument and result types JPublisher uses in the methods it generates.

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

The 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 datatypes as closely as possible so that little or no data conversion is required between the Java and the SQL formats. You do not lose any information and have greater flexibility in how you process and unpack the data. The Oracle mappings for standard SQL types are the most convenient representations if you are manipulating data within the database or moving data (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 2-1 lists the mappings from SQL and PL/SQL datatypes to Java types, using the Oracle and JDBC mappings. You can use all the supported datatypes listed in this table as argument or result types for PL/SQL methods. You can use a subset of the datatypes as object attribute types, as listed in "Allowed Object Attribute Types".

The SQL and PL/SQL Datatype column contains all possible datatypes.

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 supplied by Oracle and are designed to minimize the overhead incurred when converting Oracle datatypes to Java types. Refer to the Oracle Database JDBC Developer's Guide and Reference for more information on the oracle.sql package.

The JDBC Mapping column lists the corresponding Java types JPublisher uses when all the type mapping options are set to jdbc. For standard SQL datatypes, JPublisher uses Java types specified in the JDBC specification. For SQL datatypes 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 datatypes are not directly supported by JPublisher, in particular those types that pertain to PL/SQL only. 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.

The following notes correspond to marked 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 runtime. 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, and functions from the SYS.SQLJUTL package are used for the conversions. See "JPublisher User Type Map and Default Type Map".

3. SQL URI types, also known as "data links", are mapped to java.net.URL in the JPublisher default type map, and functions from the SYS.SQLJUTL package are used for the conversions. See "Data Link Support and Mapping".

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

5. Mapping of the SQL OPAQUE type SYS.XMLTYPE to the Java class oracle.xdb.XMLType 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".

6. 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 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; another to drop the SQL type and the PL/SQL package created by the first script. Also see "Type Mapping Support for PL/SQL RECORD and Indexed-by Table Types".

7. To support a PL/SQL indexed-by table type, JPublisher first maps the indexed-by table type into a SQL collection type, 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 indexed-by table type; the other to drop the collection type and the PL/SQL package created by the first script. Also see "Type Mapping Support for PL/SQL RECORD and Indexed-by Table Types".

8. If you use the JDBC OCI driver to call PL/SQL stored procedures or object methods, then you have direct support for scalar indexed-by tables, also known as PL/SQL TABLE types. In this case, use a type map entry for JPublisher that specifies the PL/SQL scalar indexed-by table type and a corresponding Java array type. JPublisher can then automatically publish PL/SQL or object method signatures that use this scalar indexed-by type. See also "Type Mapping Support for Scalar Indexed-by Tables Using JDBC OCI".

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, and 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 (as appropriate). Where applicable, they also specify conversion functions to convert between PL/SQL types and SQL types. See "Options for Type Maps" for additional information about syntax for the JPublisher type map commands and for documentation of the four type map options.

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 datatypes that unsupported by JDBC. (Also see "Support for PL/SQL Datatypes".)

• Avoid regenerating a Java class to map to a user-defined type. For example, assume you have a user-defined STUDENT SQL object type 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 "Example: Using the Type Map to Avoid Regeneration", which follows shortly.

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 OPAQUE type SYS.XMLTYPE and the Java wrapper class oracle.xdb.XMLType. In addition, it maps the PL/SQL BOOLEAN type to Java boolean and to SQL INTEGER through two conversion functions defined in the SYS.SQLJUTL package. Also, the default type map provides mappings between SQL INTERVAL types and the Java String type, and between SQL URI types and the java.net.URL type.

However, you may (for example) prefer mapping the PL/SQL BOOLEAN type to the Java object type Boolean to capture SQL NULL values in addition to 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.

This example uses the JPublisher type map to avoid having Java map classes regenerated. 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 (with the JPublisher output lines shown):

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

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

If you want to regenerate the classes for TRIP without regenerating the classes for CITY, 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, so the City.java and CityRef.java files are not regenerated. This is because of the addition of the CITY:City relationship to the user type map, which makes JPublisher aware that the (already existing) City class is to be used for mapping.

JPublisher Logical Progression for Datatype 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 specified there. See the preceding section, "JPublisher User Type Map and Default Type Map".

2. Checks the predefined Java mappings for SQL and PL/SQL types. See "SQL and PL/SQL Mappings to Oracle and JDBC Types".

3. Checks whether the datatype to be mapped is a PL/SQL RECORD type or indexed-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 indexed-by table type, JPublisher generates a corresponding SQL collection type that it can then map to Java. See "Type Mapping Support for PL/SQL RECORD and Indexed-by Table Types" for details and examples.

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

Allowed Object Attribute Types

You can use a subset of the SQL datatypes in Table 2-1 as object attribute types. The allowable types 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 TIMESTAMP types TIMESTAMP, TIMESTAMP WITH TIMEZONE, and TIMESTAMP WITH LOCAL TIMEZONE as object attributes; however, the Oracle JDBC implementation does not.

Mapping of REF CURSOR Types and Result Sets

If a PL/SQL stored procedure or function or a SQL query returns a REF CURSOR, then JPublisher by default generates a method to map the REF CURSOR to the following:

• java.sql.ResultSet

In addition, for a SQL query (but not a REF CURSOR returned by a stored procedure or function), JPublisher generates a method to map the REF CURSOR to the following:

• An array of rows, in which each row is represented by a JavaBean instance

Additionally, 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 these types:

• javax.xml.transform.Source

• oracle.jdbc.rowset.OracleWebRowSet

• org.w3c.dom.Document

Take the following steps, as desired, to ensure that JPublisher can find the classes:

1. Ensure that the libraries translator.jar, runtime12.jar, and classes12.jar (or ojdbc14.jar) are in the classpath. These contain JPublisher and SQLJ translator classes, SQLJ runtime classes, and JDBC classes, respectively.

2. For mapping to Source, use JDK 1.4. (This class is not defined in earlier JDK versions.)

3. For mapping to OracleWebRowSet, add ORACLE_HOME/jdbc/lib/ocrs12.jar to the classpath.

4. For mapping to Document, add ORACLE_HOME/lib/xmlparsev2.jar to the classpath.

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.

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 CURSORs. Following 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 you copy this file into myrefcursormaps.properties. There are four MAPPING sections, intended to map REF CURSORs to ResultSet, OracleWebRowSet, Document, and Source (according to the SOURCETYPE and TARGETTYPE entries). For this example, lines are commented out (by "#" characters) 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 as follows to use your custom mappings:

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

Data Link Support and Mapping

JPublisher supports the use of SQL URI types that store universal resource locators (URIs), referred to as data links. These types—SYS.URITYPE and the subtypes SYS.DBURITYPE, SYS.XDBURITYPE, and SYS.HTTPURITYPE—are mapped to the java.net.URL Java type.

As an example, consider the following SQL type that uses a URITYPE attribute:

create or replace type dl_obj as object (myurl sys.uritype);
/

And assume that JPublisher is invoked with the following command line (with JPublisher output also shown):

% jpub -u scott/tiger -s dl_obj
SCOTT.DL_OBJ

This command results in the following methods in the generated Java code:

public java.net.URL getMyurl() throws SQLException;
public void setMyurl(java.net.URL myurl) throws SQLException;

JPublisher adds the following definitions to the jpub.properties file to specify the URI type mappings:

jpub.adddefaulttypemap=
SYS.URITYPE:java.net.URL:VARCHAR2:SYS.URIFACTORY.GETURI:SYS.SQLJUTL.URI2CHAR
jpub.adddefaulttypemap=
SYS.DBURITYPE:java.net.URL:VARCHAR2:SYS.DBURITYPE.CREATEURI:SYS.SQLJUTL.URI2URL
jpub.adddefaulttypemap=
SYS.XDBURITYPE:java.net.URL:VARCHAR2:SYS.XDBURITYPE.CREATEURI:SYS.SQLJUTL.URI2URL
jpub.adddefaulttypemap=
SYS.HTTPURITYPE:java.net.URL:VARCHAR2:SYS.HTTPURITYPE:SYS.SQLJUTL.URI2URL

This includes specification of data conversion functions. Also see "Type Mapping Support Through PL/SQL Conversion Functions" and "Options for Type Maps".

Type Mapping Support for OPAQUE Types

This section describes JPublisher type mapping support for OPAQUE types in general and the OPAQUE type SYS.XMLTYPE in particular.

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

-addtypemap=sql_opaque_type:java_wrapper_class

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

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

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

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

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

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

procedure setXMLMessage(x xmltype, y number);
function getXMLMessage(id number) return xmltype;

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

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

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 Indexed-by Tables Using JDBC OCI

The Oracle JDBC OCI driver directly supports PL/SQL scalar indexed-by tables with numeric or character elements. (If you are not using the JDBC OCI driver, see "Type Mapping Support for PL/SQL RECORD and Indexed-by Table Types".) An indexed-by table with numeric elements can be mapped to the following Java array types:

• int[]

• double[]

• float[]

• java.math.BigDecimal[]

• oracle.sql.NUMBER[]

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

• String[]

• oracle.sql.CHAR[]

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

• Whenever you use the indexed-by table type in an OUT or IN OUT parameter position, you must specify the maximum number of elements. (This is optional otherwise.) This is defined 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 indexed-by tables with character elements, you can optionally specify the maximum size of an individual element (in bytes). This setting is defined using SQL-like size syntax. For example, for an indexed-by table used for IN arguments, you could specify String[](30). Or specify oracle.sql.CHAR[20](255) for an indexed-by table of maximum length 20, each of whose elements will not exceed 255 bytes.

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

As an example, consider the following code fragment from the definition of a PL/SQL package INDEXBY in the schema SCOTT. Assume this is available in a file 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 indexed-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 (...). Or 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]

See "Properties File Structure and Syntax" for information about properties files. For general information about the -addtypemap option, see "Additional Entry to the User Type Map (-addtypemap)".

Additionally, as a convenience feature, 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 indexed-by tables only with the JDBC OCI driver. 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 indexed-by table types, as well as defining conversion functions that map between the two. Refer to "Type Mapping Support for PL/SQL RECORD and Indexed-by Table Types".

Type Mapping Support Through PL/SQL Conversion Functions

This section discusses the general mechanism 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. Sections that follow this section are concerned with mapping issues specific to PL/SQL RECORD types and PL/SQL indexed-by table types, respectively.

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. (Although one exception is scalar indexed-by tables. See the preceding section, "Type Mapping Support for Scalar Indexed-by Tables Using JDBC OCI".)

JPublisher makes this task more convenient through use of its type maps. (Also see "JPublisher User Type Map and Default Type Map".) 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 following 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, typically generated by JPublisher, that implements the oracle.sql.ORAData or java.sql.SQLData interface.

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

• Name of a 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

Also see "Options for Type Maps".

As an example, consider a type map entry for supporting the PL/SQL type BOOLEAN. 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, INT2BOOL, that maps from SQL to PL/SQL (from NUMBER to BOOLEAN)

  Here is the code for that function:

  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, BOOL2INT, that maps from PL/SQL to SQL (from BOOLEAN to NUMBER)

  Here is the code for that function:

  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;
  
  

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. See "JPublisher User Type Map and 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 (where this is a single wraparound command line):

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

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, calling this original stored procedure and processing 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. Also see "Direct Use of PL/SQL Conversion Functions Versus Use of Wrapper Functions" and "PL/SQL Code Generation Options".

• -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 from each unsupported PL/SQL type to the corresponding SQL type, and a function to convert from each corresponding SQL type back 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 specify no package name, 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 creates the PL/SQL package. The dropper script drops these SQL types and the PL/SQL package.

  If the files already exist, they will be overwritten. If no file names are specified, JPublisher will write to 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 invokes 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 (default): 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 generated code instead calls the stored procedure directly, processing its PL/SQL input or output through the appropriate conversion functions.

    If a PL/SQL type is used as an OUT or IN OUT parameter, 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 direct call and conversion, then it skips 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 Indexed-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 indexed-by table types (also known as PL/SQL TABLE types). This is the only way that a RECORD type or indexed-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 indexed-by table types, respectively:

• Sample Package for RECORD Type and Indexed-by Table Type Support

• Support for RECORD Types

• Support for Indexed-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/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
  -plsqlfile=wrapper1.sql,dropper1.sql
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/tiger -p done.properties -s COMPANY -plsqlpackage=WRAPPER2
       -plsqlfile=wrapper2.sql,dropper2.sql
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

The preceding sections, beginning with "Type Mapping Support Through PL/SQL Conversion Functions", discuss how JPublisher uses PL/SQL conversion functions to convert between PL/SQL types, which are generally not supported by JDBC, and corresponding SQL types that have been defined. In generating Java code to invoke a stored procedure that uses a PL/SQL type, JPublisher can employ either of the following modes of operation:

• Invoke the stored procedure directly, processing its 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, 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. Also see "Generation of PL/SQL Wrapper Functions (-plsqlmap)".

As an example, consider the PL/SQL stored procedure SCOTT.COMPANY.GET_EMP_TBL that returns the PL/SQL indexed-by table type EMP_TBL. Assume that the COMPANY package, introduced in "Sample Package for RECORD Type and Indexed-by Table Type Support", is processed by JPublisher through the following command (with JPublisher output also shown):

% jpub -u scott/tiger -s COMPANY:Company -plsqlpackage=WRAPPER1
       -plsqlfile=wrapper1.sql,dropper1.sql -plsqlmap=false
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 indexed-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 indexed-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, WRAPPER1.GET_EMP_TBL in this example, looks like the following, calling the original stored procedure and processing its output through the conversion function:

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 looks like this:

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, for any stored procedure for which the Java code for direct invocation and conversion is too complex, or for 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 Datatypes Unsupported by JDBC

The preceding sections describe the mechanisms that JPublisher employs to access PL/SQL types unsupported in 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 Specification and Locations

Use the JPublisher -style option, also discussed in "Style File for Java-to-Java Type Mappings (-style)", 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, using the first file that it finds:

1. First, it looks for the following resource in the classpath:

   /oracle/jpub/mesg/stylename.properties
   
   

2. Next, it takes stylename as a (possibly qualified) resource name and looks for the following in the classpath:

   /stylename-dir/stylename-base.properties
   
   

3. Finally, it takes stylename as a (possibly qualified) name 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 scenario #1, 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 setting -style=webservices uses /oracle/jpub/mesg/webservices.properties from translator.jar (even if there is also a webservices.properties file in the current directory).

However, if you specify -style=mystyle, and no mystyle.properties resource is 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 Formats

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

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

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 mappings of REF CURSORs. These mappings are summarized in Table 2-2.

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

See "Mapping of REF CURSOR Types and Result Sets" for more information about how to use some or all of the mappings for result sets.

Use of Multiple Style Files

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

• The OPTIONS sections are concatenated.

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

This functionality is useful if you want to overwrite earlier defined type mappings or add new type mappings. For example, if you want to map SYS.XMLTYPE into java.lang.String, you can append the setting -style=xml2string to the JPublisher command line, assuming for this example that this will access the style file ./xml2string.properties, which 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);