1 Introduction to JPublisher

This chapter provides an overview of the JPublisher utility, a summary of JPublisher operations, and a sample translation. It covers the following topics:

Overview of JPublisher

JPublisher is a utility that generates Java classes to represent database entities, such as SQL objects and PL/SQL packages, in a Java client program. It also provides support for publishing from SQL, PL/SQL, and server-side Java to Web services and enabling the invocation of external Web services from inside the database. JPublisher is written in Java.

JPublisher can create classes to represent the following database entities types:

  • User-defined SQL objects

  • Object references

  • User-defined SQL collections

  • PL/SQL packages

  • Server-side Java classes

  • SQL queries and data manipulation language (DML) statements

JPublisher enables you to specify and customize the mapping of these entities to Java classes in a strongly typed paradigm.

Note:

The term, strongly typed, indicates that a particular Java type is associated with a particular user-defined SQL type, such as an object type. For example, a Person class is associated with a corresponding PERSON SQL type. In addition, there is a corresponding Java type for each attribute of the SQL object type.

The utility generates the accessor methods, getXXX() and setXXX(), for each attribute of an object type. If the object type has stored procedures, then JPublisher can generate wrapper methods to invoke the stored procedures. In this scenario, a wrapper method is a Java method that invokes a stored procedure, which runs in Oracle Database.

JPublisher can also generate classes for PL/SQL packages. These classes have wrapper methods to call the stored procedures in a PL/SQL package.

Instead of directly using JPublisher-generated classes, you can:

  • Extend the generated classes. This process is straightforward. JPublisher generates initial versions of the subclasses, to which you can add any desired functionality.

  • Write your own Java classes. This approach is flexible, but time-consuming and error-prone.

  • Use generic, weakly typed classes of the oracle.sql package to represent object, object reference, and collection types. If these classes meet your requirements, then you do not need JPublisher. Use this approach if you need to generically process any SQL object, collection, reference, or OPAQUE type.

In addition, JPublisher simplifies access to PL/SQL types from Java. You can use predefined or user-defined mappings between PL/SQL and SQL types, as well as use PL/SQL conversion functions between such types. With these mappings in place, JPublisher can automatically generate all the required Java and PL/SQL code. It also enables you to publish server-side Java classes to client-side Java classes, allowing your application to make direct calls to Java classes in the database.

Several features enable the exposure of Java classes, which are generated from publishing SQL, PL/SQL, or server-side Java entities, as Web services.

JPublisher Initial Considerations

The following sections provide an overview of JPublisher features and requirements. It also describes and how JPublisher uses SQLJ in its code generation:

New Features in Oracle Database 11g Release 2 JPublisher

There are no new features in this release of JPublisher.

General Requirements for JPublisher

This section describes the basic requirements for JPublisher. It also discusses situations with less stringent requirements.

When you use the JPublisher utility, you must also have classes for the Oracle SQLJ implementation, the Oracle JDBC implementation, and a Sun Microsystems JDK, among other things.

To use all features of JPublisher, you must have the following installed and set in the appropriate environment variables, as applicable:

  • Oracle Database 11g

  • JPublisher invocation script or executable

    The jpub script for UNIX systems or the jpub.exe program for Microsoft Windows must be in your file path. These are typically in ORACLE_HOME/bin, or ORACLE_HOME/sqlj/bin for manual downloads. With proper setup, if you type just jpub on the command line, you will see information about common JPublisher options and input settings.

  • JPublisher and SQLJ translator classes

    These classes are in the translator.jar library, typically in ORACLE_HOME/sqlj/lib.

    Note:

    The translator library is also automatically loaded into the database in translator-jserver.jar.
  • SQLJ run time classes

    The SQLJ run-time library is runtime12.jar for JDK 1.5 and later. It is typically located in ORACLE_HOME/sqlj/lib. It includes JPublisher client-side run-time classes, particularly oracle.jpub.reflect.Client, and JPublisher server-side run-time classes, particularly oracle.jpub.reflect.Server. These classes are used for Java call-ins to the database.

  • Oracle Database 11g or Oracle Database 10g JDBC drivers

    If you are using JPublisher 11g with Oracle 11g JDBC, then the supported JDK versions are only JDK 1.5 and 1.6. However, if you are using JPublisher 11g with Oracle JDBC 10g driver, then you can also use JDK version 1.4.x. Oracle JDBC library is ojdbc14.jar for JDK 1.4, ojdbc5*.jar for JDK 1.5, and ojdbc6*.jar for JDK 1.6. It is typically located in ORACLE_HOME/jdbc/lib. Each JDBC library also includes the JPublisher run-time classes in the oracle.jpub.runtime package.

  • Web services classes

    These classes are included in the dbwsa.jar and dbwsclient.jar libraries, which are typically located in ORACLE_HOME/sqlj/lib.

    Note:

    These .jar files are not included in JPublisher distribution, but are included in the database Web services call-out utility, which can be downloaded from http://www.oracle.com/technetwork/database/database-083829.html.
  • Additional PL/SQL packages and Java Archive (JAR) files in the database, as needed

    There are packages and JAR files that must be in the database if you use JPublisher features for Web services call-ins, Web services call-outs, support for PL/SQL types, or support for invocation of server-side Java classes. Some of these packages and files are preloaded, but some must be loaded manually.

  • aurora.zip

    When publishing a Web services client using -proxywsdl or publishing server-side Java classes using -dbjava, JPublisher may load generated Java wrapper into the database. In this case, the ORACLE_HOME/lib/aurora.zip file is required. On Microsoft Windows, add this file to CLASSPATH. On Unix systems, the jpub script picks up aurora.zip automatically. If the aurora.zip file is not available, then you can turn off the JPublisher loading behavior by specifying -proxyopt=noload on the command line.

  • JDK version 1.4 or later

    For Web services call-outs or to map SYS.XMLType for Web services, you need JDK 1.4 or later.

Installing JPublisher

In order to install JPublisher, you must install SQLJ in your system. Perform the following steps to install SQLJ:

  1. Download and unzip the Oracle Database 11g Client CD.

  2. Execute Oracle Universal Installer by running the setup.exe file.

    The Welcome window appears as shown in Figure 1-8.

    Figure 1-1 Oracle Universal Installer Welcome Screen

    Description of Figure 1-1 follows
    Description of "Figure 1-1 Oracle Universal Installer Welcome Screen"

  3. Click Next.

    The Select Installation Type window appears as shown in Figure 1-2.

    Figure 1-2 Oracle Universal Installer Select Installation Type Screen

    Description of Figure 1-2 follows
    Description of "Figure 1-2 Oracle Universal Installer Select Installation Type Screen"

  4. Select Custom as the installation type and click Next.

    The Install Location window appears as shown in Figure 1-3.

    Figure 1-3 Oracle Universal Installer Install Location Screen

    Description of Figure 1-3 follows
    Description of "Figure 1-3 Oracle Universal Installer Install Location Screen"

  5. Verify whether your Oracle Database Home is correct or not and click Next.

    The Product-Specific Prerequisite Checks window appears as shown in Figure 1-4.

    Figure 1-4 Oracle Universal Installer Product-Specific Prerequisite Check Screen

    Description of Figure 1-4 follows
    Description of "Figure 1-4 Oracle Universal Installer Product-Specific Prerequisite Check Screen"

  6. The installer verifies that your system meets the pre-requisites. Click Next after the verification is complete.

    The Available Product Components window appears as shown in Figure 1-5.

    Figure 1-5 Oracle Universal Installer Available Product Components Screen

    Description of Figure 1-5 follows
    Description of "Figure 1-5 Oracle Universal Installer Available Product Components Screen"

  7. Select Oracle SQLJ from the Components column in the Oracle Client section and click Next.

    The Summary window appears.

    Figure 1-6 Oracle Universal Installer Summary Screen

    Description of Figure 1-6 follows
    Description of "Figure 1-6 Oracle Universal Installer Summary Screen"

  8. Review your selections, and then click Install.

    The Install window appears displaying the progress.

    Figure 1-7 Oracle Universal Installer Install Screen

    Description of Figure 1-7 follows
    Description of "Figure 1-7 Oracle Universal Installer Install Screen"

  9. Click Exit and then click Yes.

Verifying JPublisher Installation

Depending on the JPublisher features that you need to use, some or all of the following PL/SQL packages and JAR files must be present in the database:

  • The SQLJUTL package, to support PL/SQL types

  • The SQLJUTL2 package, to support invocation of server-side Java classes

  • The UTL_DBWS package, to support Web services call-outs

  • The dbwsclient.jar file, to support the Java API for XML-based Remote Procedure Call (JAX-RPC) or Simple Object Access Protocol (SOAP) client proxy classes for Web services call-outs from Oracle Database 11g or Oracle Database 10g.

  • JAR files to support SOAP client proxy classes for Web services call-outs from Oracle9i or Oracle8i Database

    For Web services call-outs from Oracle9i Database or Oracle8i Database, there is no JAR file similar to dbwsclient.jar. You must load several JAR files instead. Also note that JPublisher does not yet support JAX-RPC client proxy classes in Oracle9i or Oracle8i.

Note:

  • Starting from Oracle Database 10g, the sqljutl.jar is preloaded in the database and you get built-in support for Web services call-ins. The sqljutl.jar file that was required to load manually in Oracle9i Database and Oracle8i Database, is not shipped anymore.

  • The UTL_DBWS package and the dbwsclient.jar file are associated with each other, and both support the same set of features.

This section covers the following topics:

Verifying or Installing the SQLJUTL and SQLJUTL2 Packages

In Oracle Database 11g, the PL/SQL packages SQLJUTL and SQLJUTL2 are automatically installed in the database SYS schema. To verify the installation, try to describe the packages as follows:

SQL> describe sys.sqljutl
SQL> describe sys.sqljutl2

If JPublisher displays a message similar to the following, then the packages are missing:

Warning: Cannot determine what kind of type is <schema>.<type.> You likely need
to install SYS.SQLJUTL. The database returns: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.SQLJUTL' must be declared

To install the SQLJUTL and SQLJUTL2 packages, you must install one of the following files in the SYS schema:

  • ORACLE_HOME/sqlj/lib/sqljutl.sql for Oracle9i, Oracle Database 10g, or Oracle Database 11g

  • ORACLE_HOME/sqlj/lib/sqljutl8.sql for Oracle8i

Verifying or Installing the UTL_DBWS Package

In Oracle Database 11g release 2 (11.2), the UTL_DBWS PL/SQL package is not automatically installed in the database SYS schema due to security reasons. You must install this package by running the utl_dbws_decl.sql and utl_dbws_body.sql scripts included as callout utilities available on the following OTN page:

http://www.oracle.com/technetwork/database/database-083829.html

To verify the installation, try to describe the package as follows:

SQL> describe sys.utl_dbws

Verifying or Loading the dbwsclient.jar File

In Oracle Database 11g, the following file must be loaded into the database for Web services call-outs:

ORACLE_HOME/sqlj/lib/dbwsclient.jar

It is not preloaded, but you can verify whether it is already loaded by running the following query in the SYS schema:

SQL>  select status, object_type from all_objects where
      dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy$1';

The following result indicates that the file is already loaded:

STATUS  OBJECT_TYPE
------- -------------------
VALID   JAVA CLASS
VALID   SYNONYM

If it not loaded, then you can use the loadjava utility to load it as shown in the following example:

% loadjava -oci8 -u sys -r -v -f -s 
           -grant public dbwsclient.jar
Password: password

Note:

Before loading this file, verify that java_pool_size is set to at least 80 MB and shared_pool_size is set to at least 96 MB.

Loading JAR Files For Web Services Call-Outs in Oracle9i or Oracle8i

For Web services call-outs from an Oracle9i or Oracle8i database, use SOAP client proxy classes. For this, you must load a number of JAR files into the database. This can be accomplished with the following command:

% loadjava -u sys -r -v -s -f -grant public 
           ORACLE_HOME/soap/lib/soap.jar 
           ORACLE_HOME/dms/lib/dms.jar 
           J2EE_HOME/lib/servlet.jar 
           J2EE_HOME/lib/ejb.jar 
           J2EE_HOME/lib/mail.jar
Password: password

You can obtain these files from an Oracle Application Server installation. You would presumably run Web services in conjunction with Oracle Application Server Containers for J2EE (OC4J).

Note:

  • The JAX-RPC client proxy classes are not yet supported in Oracle9i or Oracle8i.

  • Before loading this file, verify that java_pool_size is set to at least 80 MB and shared_pool_size is set to at least 96 MB.

Setting Up Password File for Remote SYS Login

By default, if the -user and -sysuser options are set while publishing Web services client using -proxywsdl or publishing server-side Java classes using -dbjava, then JPublisher will load the generated Java and PL/SQL wrappers into the database. When the -url setting specifies a JDBC Thin driver, the loading process requires the database password file to be set up properly. You can set up the password file by performing the following steps:

  1. On the command line, enter the following command:

    orapwd file=$ORACLE_HOME/dbs/orapw entries=5
    Enter password: password
    

    In the preceding command, yourpass is the password of your choice.

  2. From SQL*Plus, connect to the database as SYSDBA, as follows:

    CONNECT / AS SYSDBA
    

    Change the password of SYS to the password set in the previous step, as follows:

    PASSWORD SYS
           Changing password for SYS
           New password: password
           Retype new password: password
    
  3. Edit the init.ora file and add the following line to it:

    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    

    This enables remote login as SYSDBA.

Alternatively, with the password file set up, you can manually load JPublisher generated PL/SQL wrapper and Java wrapper into the database. To turn off automatic loading by JPublihser, specify -proxyopt=noload on the command line.

JPublisher Usage of the SQLJ Implementation

This section covers the following topics:

Overview of SQLJ Usage

Oracle SQLJ translator and run-time libraries are supplied with the JPublisher product. The JPublisher utility uses the Oracle SQLJ implementation by generating SQLJ code as an intermediate step in most circumstances, such as the creation of wrapper methods. The wrapper methods are created either for classes representing PL/SQL packages or for classes representing SQL object types that define PL/SQL stored procedures. In these cases, JPublisher uses Oracle SQLJ translator during compilation and Oracle SQLJ run time during program execution.

In Oracle Database 11g, the usage of SQLJ by JPublisher is transparent by default. SQLJ source files that JPublisher generates are automatically translated and deleted, unless you specify otherwise in the JPublisher settings. This automatic translation saves you the effort of explicitly translating the files. The resulting .java files, which use the SQLJ functionality, and the associated .class files produced by compilation, define the SQLJ classes. These classes use the Oracle SQLJ run-time application programming interfaces (APIs) while running. Generated classes that do not use the SQLJ run time are referred to as non-SQLJ classes. Non-SQLJ classes are generated when JPublisher creates classes for SQL types that do not have stored procedures or when JPublisher is specifically set to not generate wrapper methods.

In Oracle Database 11g, it is possible to pass options to the SQLJ translator through the JPublisher -sqlj option.

JPublisher is included in translator.jar, which also contains the SQLJ translator library. JPublisher generated classes may rely on runtime12.jar, which is the SQLJ run-time library for Java Development Kit (JDK) 1.5 and later.

Overview of SQLJ Concepts

A SQLJ program is a Java program containing embedded SQL statements that comply with the International Standardization Organization (ISO) standard SQLJ Language Reference syntax. SQLJ source code contains a mixture of standard Java source, SQLJ class declarations, and SQLJ executable statements with embedded SQL operations.

SQLJ was chosen because it uses simplified code for database access as compared to JDBC code. In SQLJ, a SQL statement is embedded in a single #sql statement, but several JDBC statements may be required for the same operation.

This section briefly defines the following key concepts of SQLJ:

  • Connection contexts

    A SQLJ connection context object is a strongly typed database connection object. You can use each connection context class for a particular set of interrelated SQL entities. This means that all the connections you define using a particular connection context class will use tables, views, and stored procedures that have names and data types in common. In theory, the advantage in tailoring connection context classes to sets of SQL entities is in the degree of online semantics-checking that is permitted during SQLJ translation. JPublisher does not use online semantics-checking when it invokes the SQLJ translator, but you can use this feature if you choose to work with .sqlj files directly.

    The sqlj.runtime.ref.DefaultContext connection context class is used by default. The SQLJ default context is a default connection object and an instance of this class. The DefaultContext class or any custom connection context class implements the standard sqlj.runtime.ConnectionContext interface. You can use the JPublisher -context option to specify the connection context class that JPublisher instantiates for database connections.

  • Iterators

    A SQLJ iterator is a strongly typed version of a JDBC result set and is associated with the underlying database cursor. SQLJ iterators are used for taking query results from a SELECT statement. The strong typing is based on the data type of each query column.

  • Execution contexts

    A SQLJ execution context is an instance of the standard sqlj.runtime.ExecutionContext class and provides a context in which SQL operations are run. An execution context instance is associated either implicitly or explicitly with each SQL operation that is run through SQLJ code.

Backward-Compatibility Modes Affecting SQLJ Source Files

In Oracle8i Database and Oracle9i Database, JPublisher produces .sqlj source files as visible output, which you can translate by using the SQLJ command-line interface.

Note:

On UNIX systems, you can access the SQLJ command-line interface by running the sqlj script. In Microsoft Windows, you use sqlj.exe.

In Oracle Database 11g, JPublisher supports several backward-compatibility settings through the -compatible option. This option enables you to work with generated .sqlj files in a similar fashion. Some of the -compatible option settings are as follows:

  • -compatible=sqlj

    This forces JPublisher skip the step of translating .sqlj files. You must translate the .sqlj files explicitly. To translate the files, you can either run JPublisher using only the -sqlj option or you can run the SQLJ translator directly through its own command-line interface.

  • -compatible=9i

    This sets JPublisher to Oracle9i compatibility mode. In this mode, JPublisher generates .sqlj files with the same code as in Oracle9i versions. This enables you to work directly with .sqlj files.

  • -compatible=8i or -compatible=both8i

    This sets JPublisher to Oracle8i compatibility mode. JPublisher then generates .sqlj files with the same code as in Oracle8i versions. As with Oracle9i compatibility mode, this mode enables you to work directly with .sqlj files.

Oracle8i and Oracle9i compatibility modes, particularly the former, result in significant differences in the code that JPublisher generates. If your only goal is to work directly with the .sqlj files, then use the sqlj setting.

Situations for Reduced Requirements

If you do not use certain features of JPublisher, then your requirements may be less stringent. Some of the situations for reduced requirements are as follows:

  • If you never generate classes that implement the Oracle-specific oracle.sql.ORAData interface or the deprecated oracle.sql.CustomDatum interface, then you can use a non-Oracle JDBC driver and connect to a non-Oracle database. However, JPublisher must be able to connect to Oracle Database.

    Note:

    Oracle does not test or support configurations that use non-Oracle components.
  • If you instruct JPublisher to not generate wrapper methods by setting -methods=false, or if your object types do not define any methods, then JPublisher will not generate wrapper methods or produce any SQLJ classes. In these circumstances, there is no SQLJ translation step and the SQLJ translator is not required.

  • If you do not use JPublisher functionality to enable Web services call-outs, then you do not need dbwsa.jar or dbwsclient.jar to be loaded in the database.

JPublisher Limitations

You must be aware of the following limitations when you use JPublisher:

  • JPublisher support for PL/SQL RECORD and indexed-by table types is limited. An intermediate wrapper layer is used to map a RECORD or an indexed-by-table argument to a SQL type that JDBC supports. In addition, JPublisher cannot fully support the semantics of indexed-by tables. An indexed-by table is similar in structure to a Java hashtable, but information is lost when JPublisher maps this to a SQL TABLE type.

  • If you use an INPUT file to specify type mappings, then note that some potentially disruptive error conditions do not result in error or warning messages from JPublisher. Additionally, there are reserved terms that you are not permitted to use as SQL or Java identifiers.

  • The -omit_schema_names JPublisher option has a boolean logic, but does not use the same syntax as other boolean options. You can use this option to instruct JPublisher to not use schema names to qualify SQL names that are referenced in wrapper classes. By default, JPublisher uses schema names to qualify SQL names. To disable the use of schema names, enter the -omit_schema_names option on the command line, but do not attempt to set -omit_schema_names=true or -omit_schema_names=false.

Note:

This chapter refers to the input file specified by the -input option as the INPUT file to distinguish from any other kinds of input files.

What JPublisher Can Publish

You can use JPublisher to publish:

  • SQL user-defined types

  • PL/SQL packages

  • Server-side Java classes

  • SQL queries or DML statements

  • Proxy classes and wrappers for Web services call-outs

  • Oracle Streams AQ

JPublisher Mappings and Mapping Categories

The following sections provide a basic overview of JPublisher mappings and mapping categories:

JPublisher Mappings for User-Defined Types and PL/SQL Types

JPublisher provides mappings from the following to Java classes:

  • User-defined SQL types

  • PL/SQL types

Representing User-Defined SQL Types Through JPublisher

You can use an Oracle-specific implementation, a standard implementation, or a generic implementation in representing user-defined SQL types, such as objects, collections, object references, and OPAQUE types, in your Java program.

Following is a summary of these three approaches:

  • Use classes that implement the Oracle-specific ORAData interface.

    JPublisher generates classes that implement the oracle.sql.ORAData interface. The ORAData interface supports SQL objects, object references, collections, and OPAQUE types in a strongly typed way. That is, for each specific object, object reference, collection, or OPAQUE type in the database, there is a corresponding Java type.

  • Use classes that implement the standard SQLData interface, as described in the JDBC specification.

    JPublisher generates classes for SQL object types that implement the java.sql.SQLData interface. When you use the SQLData interface, all object reference types are represented generically as java.sql.Ref and all collection types are represented generically as java.sql.Array. In addition, when using SQLData, there is no mechanism for representing OPAQUE types.

  • Use oracle.sql.* classes.

    You can use the oracle.sql.* classes to represent user-defined types generically. The oracle.sql.STRUCT class represents all object types, the oracle.sql.ARRAY class represents all the variable array (VARRAY) and nested table types, the oracle.sql.REF class represents all the object reference types, and the oracle.sql.OPAQUE class represents all OPAQUE types. These classes are immutable in the same way that java.lang.String is.

    Choose this option for code that processes objects, collections, references, or OPAQUE types in a generic way. Unlike classes implementing ORAData or SQLData, oracle.sql.* classes are not strongly typed.

Note:

You can create your own classes, but this is not recommended. If you create your own classes or generate classes for an inheritance hierarchy of object types, then your classes must be registered using a type map.

In addition to strong typing, JPublisher-generated classes that implement ORAData or SQLData have the following advantages:

  • The classes are customized, rather than generic. You access attributes of an object using getXXX() and setXXX() methods named after the particular attributes of the object. Note that you must explicitly update the object in the database if there are any changes to its data.

  • The classes are mutable. You can modify attributes of an object or elements of a collection. An exception is that ORAData classes representing object reference types are not mutable, because an object reference does not have any subcomponents that can be modified. You can, however, use the setValue() method of a reference object to change the database value that the reference points to.

  • You can generate Java wrapper classes that are serializable or that have the toString() method to print out the object along with its attribute values.

Compared to classes that implement SQLData, classes that implement ORAData are fundamentally more efficient, because ORAData classes avoid unnecessary conversions to native Java types.

Using Strongly Typed Object References for ORAData Implementations

For Oracle ORAData implementations, JPublisher always generates strongly typed object reference classes, in contrast to using the weakly typed oracle.sql.REF class. This is to provide greater type safety and to mirror the behavior in SQL, in which object references are strongly typed. The strongly typed classes, for example, the PersonRef class for references to the PERSON object, are wrappers for the oracle.sql.REF class.

In these strongly typed REF wrappers, a getValue() method produces an instance of the SQL object that is referenced as of an instance of the corresponding Java class. In the case of inheritance, the method produces an instance of a subclass of the corresponding Java class.

For example, if there is a PERSON object type in the database with a corresponding Person Java class, then there will also be a PersonRef Java class. The getValue() method of the PersonRef class would return a Person instance containing the data for a PERSON object in the database. In addition, JPublisher also generates a static cast() method on the PersonRef class. This permits you to convert other typed references to a PersonRef instance.

Whenever a SQL object type has an attribute that is an object reference, the Java class corresponding to the object type would have an attribute that is an instance of a Java class corresponding to the appropriate reference type. For example, if there is a PERSON object with a MANAGER REF attribute, then the corresponding Person Java class will have a ManagerRef attribute.

Using PL/SQL Types Through JPublisher

JDBC does not support PL/SQL-specific types, such as the BOOLEAN type and PL/SQL RECORD types that are used in stored procedures or functions. JPublisher provides the following workarounds for PL/SQL types:

  • JPublisher has a type map that you can use to specify the mapping for a PL/SQL type unsupported by JDBC.

  • For PL/SQL RECORD types or indexed-by tables types, you have the choice of JPublisher automatically creating a SQL object type or SQL collection type, respectively, as an intermediate step in the mapping.

With either workaround, JPublisher creates PL/SQL conversion functions or uses predefined conversion functions that are typically found in the SYS.SQLJUTL package to convert between a PL/SQL type and a corresponding SQL type. The conversion functions can be used in generated Java code that calls a stored procedure directly, or JPublisher can create a wrapper function around the PL/SQL stored procedure, where the generated Java code calls the wrapper function, which calls the conversion functions. Either way, only SQL types are exposed to JDBC.

JPublisher Mapping Categories

JPublisher offers different categories of data type mappings from SQL to Java. Each type mapping option has at least two possible values: jdbc or oracle. The -numbertypes option has two additional alternatives: objectjdbc and bigdecimal. The following sections describe these mappings categories.

JDBC Mapping

In JDBC mapping:

  • Most numeric data types are mapped to Java primitive types, such as int and float.

  • The DECIMAL and NUMBER type are mapped to the java.math.BigDecimal.

  • Large object (LOB) type and other non-numeric built-in types are mapped to the standard JDBC types, such as java.sql.Blob and java.sql.Timestamp.

For object types, JPublisher generates SQLData classes. Because predefined data types that are Oracle extensions, such as BFILE and ROWID, do not have JDBC mappings, only the oracle.sql.* mapping is supported for these types.

The Java primitive types used in the JDBC mapping do not support NULL values and do not guard against integer overflow or floating-point loss of precision. If you are using the JDBC mapping and you attempt to call an accessor method to get an attribute of a primitive type whose value is NULL, then an exception is thrown. If the primitive type is short or int, then an exception is thrown if the value is too large to fit in a short or int variable.

Object JDBC Mapping

In Object JDBC mapping, most numeric data types are mapped to Java wrapper classes, such as java.lang.Integer and java.lang.Float, and DECIMAL and NUMBER are mapped to java.math.BigDecimal. This differs from the JDBC mapping, which does not use primitive types.

Object JDBC is the default mapping for numeric types. When you use the Object JDBC mapping, all your returned values are objects. If you attempt to get an attribute whose value is NULL, then a NULL object is returned. The Java wrapper classes used in the Object JDBC mapping do not guard against integer overflow or floating-point loss of precision. If you call an accessor method to get an attribute that maps to java.lang.Integer, then an exception is thrown if the value is too large to fit.

BigDecimal Mapping

In BigDecimal mapping, all numeric data types are mapped to java.math.BigDecimal. This supports NULL values and large values.

Oracle Mapping

In Oracle mapping, the numeric, LOB, or other built-in types are mapped to classes in the oracle.sql package. For example, the DATE type is mapped to oracle.sql.DATE and all numeric types are mapped to oracle.sql.NUMBER. For object, collection, and object reference types, JPublisher generates ORAData classes.

Because the Oracle mapping uses no primitive types, it can represent a NULL value as a Java null in all cases. Also, it can represent the largest numeric values that can be stored in the database, because it uses the oracle.sql.NUMBER class for all numeric types.

JPublisher Input and Output

To publish database entities, JPublisher connects to the database and retrieves descriptions of SQL types, PL/SQL packages, or server-side Java classes that you specify on the command line or in an INPUT file. By default, JPublisher connects to the database by using the JDBC Oracle Call Interface (OCI) driver, which requires an Oracle client installation, including Oracle Net Services and required support files. If you do not have an Oracle client installation, then JPublisher can use Oracle JDBC Thin driver.

JPublisher generates a Java class for each SQL type or PL/SQL package that it translates and each server-side Java class that it processes. Generated classes include code required to read and write objects in the database. When you deploy the generated JPublisher classes, your JDBC driver installation includes all the necessary run-time files. If JPublisher generates wrapper methods for stored procedures, then the classes that it produces use the SQLJ run time during execution. In this case, you must also have the SQLJ run-time library runtime12.jar.

When you call a wrapper method on an instance of a class that was generated for a SQL object, the SQL value for the corresponding object is sent to the server along with any IN or IN OUT arguments. Then the method is invoked, and the new object value is returned to the client along with any OUT or IN OUT arguments. Note that this results in a database round trip. If the method call only performs a simple state change on the object, then there will be better performance if you write and use equivalent Java that affects the state change locally.

The number of classes that JPublisher produces depends on whether you request ORAData classes or SQLData classes.

To publish external Web services for access from inside a database, JPublisher accesses a specified Web Service Description Language (WSDL) document and directs the generation of appropriate client proxy classes. It then generates wrapper classes, as necessary, and PL/SQL wrappers to allow Web services call-outs from PL/SQL.

The following subsections provide more detail:

Input to JPublisher

You can specify input options on the command line and in a JPublisher properties file. In addition to producing Java classes for the translated entities, JPublisher writes the names of the translated objects and packages to the standard output.

You can use a file known as the JPublisher INPUT file to specify the SQL types, PL/SQL packages, or server-side Java classes that JPublisher should publish. It also controls the naming of the generated packages and classes.

To use a properties file to specify option settings, specify the name of the properties file on the command line by using the -props option. JPublisher processes a properties file as if its contents were inserted in sequence on the command line at the point of the -props option. For additional flexibility, properties files can also be SQL script files in which the JPublisher directives are embedded in SQL comments.

Output from JPublisher

This section describes JPublisher output for user-defined object types, user-defined collection types, OPAQUE types, PL/SQL packages, server-side Java classes, SQL queries or DML statements, and AQs and streams.

Note:

Be aware that when JPublisher publishes a database entity, such as a SQL type or PL/SQL package, it also generates classes for any types that are referenced by the entity. For example, if a stored procedure in a PL/SQL package that is being published uses a SQL object type as an argument, then a class will be generated to map to that SQL object type.

Java Output for User-Defined Object Types

For a user-defined object type, when you run JPublisher and request ORAData classes, JPublisher creates the following:

  • An object class that represents instances of Oracle object type in your Java program

    For each object type, JPublisher generates a type.java file for the class code. For example, JPublisher generates Employee.java for Oracle object type EMPLOYEE.

  • A stub subclass (optional)

    It is named as specified in your JPublisher settings. You can modify the generated stub subclass for custom functionality.

  • An interface for the generated class or subclass to implement (optional)

  • A related reference class for object references

    JPublisher generates a typeRef.java file for the REF class associated with the object type. For example, JPublisher generates the EmployeeRef.java file for references of Oracle object type EMPLOYEE.

  • Java classes for any object or collection or OPAQUE attributes nested directly or indirectly within the top-level object

    This is necessary so that attributes can be materialized in Java whenever an instance of the top-level class is materialized. If an attribute type, such as a SQL OPAQUE type or a PL/SQL type, has been premapped, then JPublisher uses the target Java type from the map.

Note:

For ORAData implementations, a strongly typed reference class is always generated, regardless of whether the SQL object type uses references.

If you request SQLData classes, then JPublisher does not generate the object reference class and classes for nested collection attributes or OPAQUE attributes.

Java Output for User-Defined Collection Types

When you run JPublisher for a user-defined collection type, you must request ORAData classes. JPublisher creates the following:

  • A collection class to act as a type definition that corresponds to Oracle collection type

    For each collection type JPublisher translates, it generates a type.java file. For nested tables, the generated class has methods to get and set the nested table as an entire array and to get and set individual elements of the table. JPublisher translates collection types when generating ORAData classes, but not when generating SQLData classes.

  • If the elements of the collection are objects, then a Java class for the element type and Java classes for any object or collection attributes nested directly or indirectly within the element type

    This is necessary so that object elements can be materialized in Java whenever an instance of the collection is materialized.

  • An interface that is implemented by the generated type (optional)

Note:

Unlike for object types, you do not have the option of generating user subclasses for collection types.

Java Output for OPAQUE Types

When you run JPublisher for an OPAQUE type, you must request ORAData classes. JPublisher creates a Java class that acts as a wrapper for the OPAQUE type, providing Java versions of the OPAQUE type methods as well as protected APIs to access the representation of the OPAQUE type in a subclass.

However, in most cases, Java wrapper classes for the SQL OPAQUE types are furnished by the provider of the OPAQUE types. For example, the oracle.xdb.XMLType class for the SYS.XMLTYPE SQL OPAQUE type. In such cases, ensure that the correspondence between the SQL type and the Java type is predefined to JPublisher through the type map.

Java Output for PL/SQL Packages

When you run JPublisher for a PL/SQL package, it creates a Java class with wrapper methods that invoke the stored procedures of the package on the server. IN arguments for the methods are transmitted from the client to the server, and OUT arguments and results are returned from the server to the client.

Java Output for Server-Side Java Classes and Web Services Call-Outs

When you run JPublisher for a server-side Java class used for general purposes, it creates the source code, type.java, for a client-side stub class that mirrors the server class. When you call the client-side methods, the corresponding server-side methods are called transparently.

For Web services call-outs, JPublisher typically generates wrapper classes for the server-side client proxy classes. These wrapper classes act as bridges to the corresponding PL/SQL wrappers. This is necessary to publish any proxy class instance method as a static method, because PL/SQL does not support instance methods.

Java Output for SQL Queries or DML Statements

When you run JPublisher for a SQL query or DML statement, it creates the following:

  • A Java class that implements the method that runs the SQL statement

  • A Java stub subclass, named as specified in your JPublisher settings (optional)

    You can modify this stub subclass for custom functionality.

  • A Java interface for the generated class or subclass to implement (optional)

Java Output for AQs and Streams

When you run JPublisher for an AQ or a topic, it creates the following:

  • A Java class for the queue or topic

  • A Java class for the payload type of the queue or topic

In the case of a stream, JPublisher generates a Java class for the stream. The payload is always SYS.ANYDATA, which is mapped to java.lang.Object.

PL/SQL Output

Depending on your usage, JPublisher may generate a PL/SQL package and associated PL/SQL scripts.

PL/SQL Package

JPublisher typically generates a PL/SQL package with PL/SQL code for any of the following:

  • PL/SQL call specifications for generated Java methods

  • PL/SQL conversion functions and wrapper functions to support PL/SQL types

  • PL/SQL table functions

Conversion functions, and optionally wrapper functions, are employed to map PL/SQL types used in the calling sequences of any stored procedures that JPublisher translates. The functions convert between PL/SQL types and corresponding SQL types, given that JDBC does not generally support PL/SQL types.

PL/SQL Scripts

JPublisher generates the following PL/SQL scripts:

  • A wrapper script to create the PL/SQL package and any necessary SQL types

  • A script to grant permission to run the wrapper script

  • A script to revoke permission to run the wrapper script

  • A script to drop the package and types created by the wrapper script

JPublisher Operation

This section discusses the following topics:

Overview of the Publishing Process: Generation and Use of Output

Figure 1-8 illustrates the basic steps for publishing specified SQL types, PL/SQL packages, or server-side Java classes. The steps are as follows:

  1. Run JPublisher with input from the command line, properties file, and INPUT file, as desired.

  2. JPublisher accesses the database to which it is attached to obtain definitions of SQL or PL/SQL entities that you specify for publishing.

  3. JPublisher generates .java or .sqlj source files, as appropriate, depending on whether wrapper methods are created for stored procedures.

  4. By default, JPublisher invokes the SQLJ translator, which is provided as part of JPublisher, to translate .sqlj files into .java files.

  5. For SQLJ classes, by default, the SQLJ translator invokes the Java compiler to compile.java files into.class files. For non-SQLJ classes, JPublisher invokes the Java compiler.

  6. JPublisher generates PL/SQL wrappers and scripts, as appropriate, in addition to the .class files. There is a script to create the PL/SQL wrapper package and any necessary SQL types, such as types to map to PL/SQL types, a script to drop these entities, and scripts to grant or revoke required privileges.

  7. In the case of proxy class generation through the -proxywsdl or -proxyclasses option, JPublisher can load generated PL/SQL wrappers and scripts into the database to which it is connected for execution in the database PL/SQL engine.

  8. By default, JPublisher loads generated Java classes for Web services call-outs into the database to which it is connected, for execution in Oracle JVM. JPublisher-generated classes other than those for Web services call-outs typically execute in a client or middle-tier JVM. You may also have your own classes, such as subclasses of JPublisher-generated classes, that would typically execute in a client or middle-tier JVM.

Figure 1-8 Translating and Using JPublisher-Generated Code

JPublisher operational flow. Link for description follows.
Description of "Figure 1-8 Translating and Using JPublisher-Generated Code"

JPublisher Command-Line Syntax

On most operating systems, you can start JPublisher from the command line by typing jpub followed by a series of option settings, as follows:

% jpub -option1=value1 -option2=value2 ...

JPublisher responds by connecting to the database and obtaining the declarations of the types or packages you specify. It then generates one or more custom Java classes and writes the names of the translated object types or PL/SQL packages to the standard output.

Here is an example of a single wraparound command that invokes JPublisher:

% jpub -user=scott -input=demoin -numbertypes=oracle -usertypes=oracle -dir=demo -d=demo -package=corp
Enter scott password: password

Enter the command on the command line, allowing it to wrap as necessary. This command directs JPublisher to connect to the database with the user name scott and password TIGER and to translate data types to Java classes, based on instructions in the INPUT file demoin. The -numbertypes=oracle option directs JPublisher to map object attribute types to Java classes supplied by Oracle, and the -usertypes=oracle option directs JPublisher to generate Oracle-specific ORAData classes. JPublisher places the classes that it generates in the corp package under the demo directory.

Note:

This chapter refers to the input file specified by the -input option as the INPUT file to distinguish from any other kinds of input files.

JPublisher also supports specification of .java files, or .sqlj files, if you are using SQLJ source files directly, on the JPublisher command line. In addition to any JPublisher-generated files, the specified files are translated and compiled. For example:

% jpub ...options... Myclass.java

Note:

  • No spaces are permitted around the equal sign (=) on the JPublisher command line.

  • If you run JPublisher without any command-line input, then it displays an option list and then terminates.

Sample JPublisher Translation

This section provides a sample JPublisher translation of a user-defined object type. At this point, do not worry about the details of the code JPublisher generates. You can find more information about JPublisher input and output files, options, data type mappings, and translation later in this manual.

Note:

For more examples, go to ORACLE_HOME/sqlj/demo/jpub in your Oracle Database installation.

Create the object type EMPLOYEE:

CREATE TYPE employee AS OBJECT
(
    name       VARCHAR2(30),
    empno      INTEGER,
    deptno     NUMBER,
    hiredate   DATE,
    salary     REAL
);

The INTEGER, NUMBER, and REAL types are all stored in the database as NUMBER types, but after translation they have different representations in the Java program, based on your setting of the -numbertypes option.

Assume JPublisher translates the types according to the following command entered on the command line:

% jpub -user=scott -dir=demo -numbertypes=objectjdbc -builtintypes=jdbc -package=corp -case=mixed -sql=Employee
Enter scott password: password

Note that JPublisher generates a non-SQLJ class, because the EMPLOYEE object type does not define any methods.

Because -dir=demo and -package=corp are specified on the JPublisher command line, the translated class Employee is written to Employee.java at the following location:

./demo/corp/Employee.java

Note:

This location is specific for a UNIX system.

The Employee.java class file would contain the code shown in the following example.

Note:

The details of the code JPublisher generates are subject to change. In particular, non-public methods, non-public fields, and all method bodies may be generated differently.
package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Employee implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.EMPLOYEE";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  private static int[] _sqlType =  { 12,4,2,91,7 };
  private static ORADataFactory[] _factory = new ORADataFactory[5];
  protected static final Employee _EmployeeFactory = new Employee(false);

  public static ORADataFactory getORADataFactory()
  { return _EmployeeFactory; }

  /* constructor */
  protected Employee(boolean init)
  { if(init) _struct = new MutableStruct(new Object[5], _sqlType, _factory); }
  public Employee()
  { this(true); }
  public Employee(String name, Integer empno, java.math.BigDecimal deptno,
                  java.sql.Timestamp hiredate, Float salary) 
   throws SQLException
  { this(true);
    setName(name);
    setEmpno(empno);
    setDeptno(deptno);
    setHiredate(hiredate);
    setSalary(salary);
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(Employee o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null; 
    if (o == null) o = new Employee(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }
  /* accessor methods */
  public String getName() throws SQLException
  { return (String) _struct.getAttribute(0); }

  public void setName(String name) throws SQLException
  { _struct.setAttribute(0, name); }

  public Integer getEmpno() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setEmpno(Integer empno) throws SQLException
  { _struct.setAttribute(1, empno); }

  public java.math.BigDecimal getDeptno() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(2); }

  public void setDeptno(java.math.BigDecimal deptno) throws SQLException
  { _struct.setAttribute(2, deptno); }

  public java.sql.Timestamp getHiredate() throws SQLException
  { return (java.sql.Timestamp) _struct.getAttribute(3); }

  public void setHiredate(java.sql.Timestamp hiredate) throws SQLException
  { _struct.setAttribute(3, hiredate); }

  public Float getSalary() throws SQLException
  { return (Float) _struct.getAttribute(4); }

  public void setSalary(Float salary) throws SQLException
  { _struct.setAttribute(4, salary); }

}

Code Generation Notes 

  • JPublisher also generates object constructors based on the object attributes.

  • Additional private or public methods may be generated with other option settings. For example, the -serializable=true setting results in the object wrapper class implementing the interface java.io.Serializable and in the generation of private writeObject() and readObject() methods. In addition, the -tostring=true setting results in the generation of a public toString() method.

  • There is a protected _struct field in JPublisher-generated code for SQL object types. This is an instance of the oracle.jpub.runtime.MutableStruct internal class. It contains the data in original SQL format. In general, you should never reference this field directly. Instead, use the -methods=always or -methods=named setting, as necessary, to ensure that JPublisher produces setFrom() and setValueFrom() methods, and then use these methods when extending a class.

  • JPublisher generates SQLJ classes instead of non-SQLJ classes in the following circumstances:

    • The SQL object being published has methods, and the -methods=false setting is not specified.

    • A PL/SQL package, stored procedure, query, or DML statement is published, and the -methods=false setting is not specified.

    In addition:

    • If a SQLJ class is created for a type definition, then a SQLJ class is also created for the corresponding REF definition.

    • If a SQLJ class is created for a base class, then SQLJ classes are also created for any subclasses.

    This means that, in a backward-compatibility mode, JPublisher generates .sqlj files instead of .java files.

JPublisher also generates an EmployeeRef.java class. The source code is as follows:

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class EmployeeRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "SCOTT.EMPLOYEE";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

private static final EmployeeRef _EmployeeRefFactory = new EmployeeRef();

  public static ORADataFactory getORADataFactory()
  { return _EmployeeRefFactory; }
  /* constructor */
  public EmployeeRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    EmployeeRef r = new EmployeeRef();
    r._ref = (REF) d;
    return r;
  }

  public static EmployeeRef cast(ORAData o) throws SQLException
  {
     if (o == null) return null;
     try { return (EmployeeRef) getORADataFactory().create(o.toDatum(null),
           OracleTypes.REF); }
     catch (Exception exn)
     { throw new SQLException("Unable to convert "+o.getClass().getName()+" to
             EmployeeRef: "+exn.toString()); }
  }

  public Employee getValue() throws SQLException
  {
     return (Employee) Employee.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(Employee c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

Note:

JPublisher also generates a public static cast() method to cast from other strongly typed references into a strongly typed reference instance.