1 Introduction to JPublisher

Verifying or Installing the SQLJUTL and SQLJUTL2 Packages

In Oracle Database 12c, 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 12c

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

Verifying or Installing the UTL_DBWS Package

In Oracle Database 12c release, 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 into the user schema included as callout utilities available on the following OTN page:

https://download.oracle.com/technology/sample_code/tech/java/jsp/dbws-callout-utility-10131.zip

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

SQL> describe hr.utl_dbws

Verifying or Loading the dbwsclient.jar File

In Oracle Database 12c, 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 user 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

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

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 JPublisher, specify -proxyopt=noload on the command line.

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 12c, 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 12c, 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.

  See Also:

  "SQLJ Connection Context Classes"

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

In Oracle Database 12c, 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.

  See Also:

  "Option to Access SQLJ Functionality"

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

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.

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.

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.

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.

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)

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