2 Using JPublisher

Publishing User-Defined SQL Types

Using JPublisher to publish SQL objects or collections as Java classes is straightforward. This section provides examples of this for the Order Entry (OE) schema, which is part of Oracle Database sample schema. If you do not have the sample schema installed, but have your own object types that you would like to publish, then replace the user name, password, and object names accordingly.

Assuming that the password for the OE schema is OE, use the following command to publish the CATEGORY_TYP SQL object type, where % is the system prompt:

% jpub -user=OE -sql=CATEGORY_TYP:CategoryTyp
Enter OE password: password

The JPublisher -user option specifies the user name. The -sql option specifies the types to be published. The SQL type and Java class is separated by a colon (:). CATEGORY_TYP is the name of the SQL type, and CategoryTyp is the name of the corresponding Java class that is to be generated.

JPublisher echoes the names of the SQL types that it publishes to the standard output:

OE.CATEGORY_TYP

In addition to the CategoryTyp.java file, JPublisher also generates the CategoryTypeRef.java file. This is a strongly typed wrapper class for SQL object references to OE.CATEGORY_TYP. Both these files can be compiled with the Java compiler, javac.

Another example of publishing SQL object types, in this case the CUSTOMER_TYP type, by using the shorthand -u for "-user=" and -s for "-sql=" is:

% jpub -u OE -s CUSTOMER_TYP:CustomerTyp
Enter OE password: password

The options -u and -s are followed by a space and then the value.

JPublisher reports a list of SQL object types. Whenever it encounters an object type for the first time, whether through an attribute, an object reference, or a collection that has element types as objects or collections, it automatically generates a wrapper class for that type as well. The list of SQL object types for the OE schema are:

OE.CUSTOMER_TYP
OE.CORPORATE_CUSTOMER_TYP
OE.CUST_ADDRESS_TYP
OE.PHONE_LIST_TYP
OE.ORDER_LIST_TYP
OE.ORDER_TYP
OE.ORDER_ITEM_LIST_TYP
OE.ORDER_ITEM_TYP
OE.PRODUCT_INFORMATION_TYP
OE.INVENTORY_LIST_TYP
OE.INVENTORY_TYP
OE.WAREHOUSE_TYP

Two source files are generated for each object type in this example: one for a Java class, such as CustomerTyp, to represent instances of the object type, and one for a reference class, such as CustomerTypeRef, to represent references to the object type.

Notice the naming scheme that JPublisher uses by default. For example, the OE.PRODUCT_INFORMATION_TYP SQL type is converted to a Java class, ProductInformationTyp.

Although JPublisher automatically generates wrapper classes for embedded types, it does not do so for subtypes of given object types. In this case, you have to explicitly enumerate all the subtypes that you want to have published. The CATEGORY_TYP type has three subtypes: LEAF_CATEGORY_TYP, COMPOSITE_CATEGORY_TYP, and CATALOG_TYP. The following is a single, wraparound JPublisher command line to publish the subtypes of the object type:

% jpub  -u OE  -s COMPOSITE_CATEGORY_TYP:CompositeCategoryTyp
        -s LEAF_CATEGORY_TYP:LeafCategoryTyp,CATALOG_TYP:CatalogTyp
Enter OE password: password

JPublisher lists the processed types as output, as follows:

OE.COMPOSITE_CATEGORY_TYP
OE.SUBCATEGORY_REF_LIST_TYP
OE.LEAF_CATEGORY_TYP
OE.CATALOG_TYP
OE.CATEGORY_TYP
OE.PRODUCT_REF_LIST_TYP

Keep in mind the following information:

• If you want to unparse several types, then you can list them all together in the -sql or -s option, each separated by a comma, or you can supply several -sql options on the command line.

• Although JPublisher does not automatically generate wrapper classes for all subtypes, it does generate them for all supertypes.

• For SQL objects with methods, such as CATALOG_TYP, JPublisher uses SQLJ classes to implement the wrapper methods. In Oracle Database 11g, the use of SQLJ classes, as opposed to regular Java classes, is invisible to you unless you use one of the backward-compatibility modes.

If the code that JPublisher generates does not provide the functionality or behavior you want, then you can extend generated wrapper classes to override or complement their functionality. Consider the following example:

% jpub -u OE -s WAREHOUSE_TYP:JPubWarehouse:MyWarehouse
Enter OE password: password

The JPublisher output is:

OE.WAREHOUSE_TYP

With this command, JPublisher generates both JPubWarehouse.java and MyWarehouse.java. The JPubWarehouse.java file is regenerated every time you rerun this command. The MyWarehouse.java generated file can be customized by you and will not be overwritten by future runs of this command. You can add new methods in MyWarehouse.java and override the method implementations from JPubWarehouse.java.

The class that is used to materialize WAREHOUSE_TYP instances in Java is the specialized MyWarehouse class. If you want user-specific subclasses for all types in an object type hierarchy, then you must specify triplets of the form SQL_TYPE:JPubClass:UserClass, for all members of the hierarchy, as shown in the preceding JPublisher command.

Once you have generated and compiled Java wrapper classes with JPublisher, you can use the object wrappers directly.

The following SQLJ class calls a PL/SQL stored procedure. Assume that register_warehouse takes a WAREHOUSE_TYP instance as an IN OUT parameter. Code comments show the corresponding #sql command. By default, JPublisher generates and translates the SQLJ code automatically.

java.math.BigDecimal location = new java.math.BigDecimal(10); 
java.math.BigDecimal warehouseId = new java.math.BigDecimal(10); 
MyWarehouse w = new MyWarehouse(warehouseId,"Industrial Park",location); 
//  ************************************************************
//  #sql { call register_warehouse(:INOUT w) };
//  ************************************************************
//
// declare temps 
oracle.jdbc.OracleCallableStatement __sJT_st = null;
sqlj.runtime.ref.DefaultContext __sJT_cc = 
     sqlj.runtime.ref.DefaultContext.getDefaultContext();
if (__sJT_cc==null)
  sqlj.runtime.error.RuntimeRefErrors.raise_NULL_CONN_CTX(); 
sqlj.runtime.ExecutionContext.OracleContext __sJT_ec = 
     ((__sJT_cc.getExecutionContext()==null) ? 
          sqlj.runtime.ExecutionContext.raiseNullExecCtx() :
               __sJT_cc.getExecutionContext().getOracleContext()); 
try
{ 
  String theSqlTS = "BEGIN register_warehouse( :1 ) \n; END;"; 
  __sJT_st = __sJT_ec.prepareOracleCall(__sJT_cc,"0RegisterWarehouse",theSqlTS); 
  if (__sJT_ec.isNew()) 
  { 
    __sJT_st.registerOutParameter(1,2002,"OE.WAREHOUSE_TYP"); 
  } 
  // set IN parameters 
  if (w==null) 
    __sJT_st.setNull(1,2002,"OE.WAREHOUSE_TYP"); 
  else __sJT_st.setORAData(1,w); 
    // execute statement 
    __sJT_ec.oracleExecuteUpdate(); 
  // retrieve OUT parameters 
  w = (MyWarehouse)__sJT_st.getORAData(1,MyWarehouse.getORADataFactory()); 
}
finally
{
  __sJT_ec.oracleClose();
} 

In Java Database Connectivity (JDBC), you typically register the relationship between the SQL type name and the corresponding Java class in the type map for your connection instance. This is required once for each connection. This type mapping can be done as shown in the following example:

java.util.Map typeMap = conn.getTypeMap();
typeMap.put("OE.WAREHOUSE_TYP", MyWarehouse.class);
conn.setTypeMap(typeMap);

The following JDBC code is equivalent to the JPublisher output, that is, the translated SQLJ code, shown previously:

CallableStatement cs = conn.prepareCall("{call register_warehouse(?)}");
((OracleCallableStatement)cs).registerOutParameter
                 (1,oracle.jdbc.OracleTypes.STRUCT,"OE.WAREHOUSE_TYP");
cs.setObject(w);
cs.executeUpdate();
w = cs.getObject(1);

Publishing PL/SQL Packages

In addition to mapping SQL objects, you may want to encapsulate entire PL/SQL packages as Java classes. JPublisher offers functionality to create Java wrapper methods for the stored procedures of a PL/SQL package.

However, the concept of representing PL/SQL stored procedures as Java methods presents a problem. Arguments to the PL/SQL functions and procedures may use the PL/SQL OUT or IN OUT mode, but there are no equivalent modes for passing arguments in Java. A method that takes an int argument, for example, is not able to modify this argument in such a way that its callers can receive a new value for it. As a workaround, JPublisher can generate single-element arrays for OUT and IN OUT arguments. For example, consider an integer array int[] abc. The input value is provided in abc[0], and the modified output value is also returned in abc[0]. JPublisher also uses a similar pattern when generating code for SQL object type methods.

The following command publishes the SYS.DBMS_LOB package into Java:

% jpub -u scott -s SYS.DBMS_LOB:DbmsLob
Enter scott password: password

The JPublisher output is:

SYS.DBMS_LOB

Because DBMS_LOB is publicly visible, you can access it from a different schema, such as SCOTT. Note that this JPublisher invocation creates a SQLJ class in DbmsLob.java that contains the calls to the PL/SQL package. The generated Java methods are actually the instance methods. The idea is that you create an instance of the package using a JDBC connection or a SQLJ connection context and then call the methods on that instance.

Use of Object Types Instead of Java Primitive Numbers

When you examine the generated code, notice that JPublisher has generated java.lang.Integer as arguments to various methods. Using Java object types, such as Integer, instead of Java primitive types, such as int, permits you to represent SQL NULL values directly as Java nulls, and JPublisher generates these by default. However, for the DBMS_LOB package, int is preferable over the Integer object type. The following modified JPublisher invocation accomplishes this through the -numbertypes option:

% jpub -numbertypes=jdbc  -u scott  -s SYS.DBMS_LOB:DbmsLob
Enter scott password: password

The JPublisher output is:

SYS.DBMS_LOB

Wrapper Class for Procedures at the SQL Top Level

JPublisher also enables you to generate a wrapper class for the functions and procedures at the SQL top level. Use the special package name TOPLEVEL, as in the following example:

% jpub  -u scott  -s TOPLEVEL:SQLTopLevel
Enter scott password: password

The JPublisher output is:

SCOTT.top-level_scope

A warning appears if there are no stored functions or procedures in the SQL top-level scope.

Publishing Oracle Streams AQ

Publishing Oracle Streams Advanced Queue (AQ) as Java classes is similar to publishing PL/SQL stored procedures. JPublisher exposes a queue as a Java program using AQ Java Message Service (JMS) application programming interfaces (APIs). This Java program can be further published into Web services by the Web services assembler. You can perform the following:

• Publishing a Queue as a Java Class

• Publishing a Topic as a Java Class

• Publishing a Stream as a Java Class

Oracle Streams AQ can be categorized into queue, topic, and stream. A queue is a one-to-one message channel with a declared payload type. A topic is a one to many message channel with a declared payload type. A stream is a queue or topic with SYS.ANYDATA as the payload type.

You can publish a queue, topic, or stream using the -sql option as follows:

%jpub -user=scott -sql=AQNAME:javaName
Enter scott password: password

AQNAME is the name of a queue table, queue, topic, or stream. javaName is the name of the corresponding Java class.

In Microsoft Windows, you must add the following Java Archive (JAR) files to CLASSPATH for JPublisher to publish a queue. These two files are required for the running of the JPublisher-generated code for Oracle Streams AQ.

ORACLE_HOME/rdbms/jlib/jmscommon.jar
ORACLE_HOME/rdbms/jlib/aqapi.jar

On UNIX systems, the jpub script distributed with Oracle Database 11g Release 2 (11.2) includes these JAR files.

For Oracle Streams AQ, the usage of the -sql option is the same as SQL types and PL/SQL stored procedures. You can specify subclasses and interfaces. Other options available to SQL types and PL/SQL packages, such as -genpattern, -style, -builtintypes, and -compatible, are also available with Oracle Streams AQ.

CREATE TYPE scott.queue_message AS OBJECT (
  Subject VARCHAR2(30),
  Text VARCHAR2(80)
);
dbms_aqadm.create_queue_table (
  Queue_table => 'scott.queue_queue_table',
  Queue_payload_type => 'scott.queue_message'
);
dbms_aqadm.create_queue (
  queue_name  => 'scott.toy_queue',
  queue_table => 'scott.queue_queue_table'
);
dbms_aqadm.start_queue (
  queue_name => 'scott.toy_queue'
);

% jpub  -user=scott  -sql=toy_queue:ToyQueue
Enter scott password: password

public class ToyQueue
{
   public ToyQueue();
   public ToyQueue(java.sql.Connection conn);
   public ToyQueue(javax.sql.DataSource dataSource);
   public void setConnection(java.sql.Connection conn);
   public void setDataSource(javax.sql.DataSource ds);
   public void addTypeMap(String sqlName, String javaName);
   public void send(QueueMessage payload);
   public QueueMessage receive();
   public QueueMessage receiveNoWait();
   public QueueMessage receive(java.lang.String selector, boolean noWait);
}

priority > 3 and Subject IN ('spider','tank') 

...
ToyQueue q = new ToyQueue(getConnection());
QueueMessage m = new QueueMessage("scooby doo", "lights out");
q.send(m);
System.out.println("Message sent: " + m.getSubject() + " " + m.getText());
m = new QueueMessage("dalmatian", "solve the puzzle");
q.send(m);
System.out.println("Message sent: " + m.getSubject() + " " + m.getText());
m = q.receive();
while (m!=null)
{
  System.out.println("Message received: " + m.getSubject() + " " + m.getText());
  m = q.receiveNoWait();
}
...

CREATE TYPE scott.topic_message AS OBJECT (
  Subject VARCHAR2(30),
  Text VARCHAR2(80)
);
dbms_aqadm.create_queue_table (
  Queue_table => 'scott.topic_queue_table',
  Multiple_consumers => TRUE,
  Queue_payload_type => 'scott.topic_message'
);
dbms_aqadm.create_queue (
  queue_name  => 'scott.toy_topic',
  queue_table => 'scott.topic_queue_table'
);
dbms_aqadm.start_queue (
  queue_name => 'scott.toy_topic'
);

% jpub -user=scott -sql=toy_topic:ToyTopic
Enter scott password: password

public class ToyTopic
{
  public ToyTopic(javax.sql.DataSource dataSource);
  public void setConnection(java.sql.Connection conn);
  public void setDataSource(javax.sql.DataSource ds);
  public void addTypeMap(String sqlName,String javaName);
  public void publish(TopicMessage payload);
  public void publish(TopicMessage payload, java.lang.String[] recipients);
  public void publish(TopicMessage payload, int deliveryMode, int priority,
        long timeToLive);
  public void subscribe(java.lang.String subscriber);
  public void unsubscribe(java.lang.String subscriber);
  public TopicMessage receiveNoWait(java.lang.String receiver);
  public TopicMessage receive(java.lang.String receiver);
  public TopicMessage receive(java.lang.String receiver,
        java.lang.String selector);
}

...
ToyTopic topic = new ToyTopic(getConnection());
TopicMessage m = new TopicMessage("scooby doo", "lights out");

topic.publish(m, new String[]{"ToyParty", "ToyFactory"});
System.out.println("Message broadcasted: " + m.getSubject() + " " + m.getText());
m = new TopicMessage("dalmatian", "solve the puzzle");
topic.publish(m, new String[]{"ToyParty", "ToyLand"});
System.out.println("Message broadcasted: " + m.getSubject() + " " + m.getText());

m = topic.receive("ToyParty");
System.out.println("ToyParty receive " + m.getSubject() + " " + m.getText());
m = topic.receive("ToyParty");
System.out.println("ToyParty receive " + m.getSubject() + " " + m.getText());

m = topic.receiveNoWait("ToyLand");
System.out.println("ToyFactory receive " + m.getSubject() + " " + m.getText());
m = topic.receiveNoWait("ToyFactory");
System.out.println("ToyFactory receive " + m.getSubject() + " " + m.getText());
m = topic.receiveNoWait("ToyFactory");
...

% jpub  -user=scott  -sql=toy_stream:ToyStream
Enter scott password: password

public class ToyStream
{
  public ToyStream();
  public ToyStream(java.sql.Connection conn);
  public ToyStream(javax.sql.DataSource dataSource);
  public void setConnection(java.sql.Connection conn);
  public void setDataSource(javax.sql.DataSource ds);
  public void addTypeMap(String sqlName, String javaName);
  public void publish(Object payload);
  public void publish(Object payload, java.lang.String[] recipients);
  public void publish(Object payload, int deliveryMode, 
         int priority, long timeToLive);
  public void subscribe(java.lang.String subscriber);
  public void unsubscribe(java.lang.String subscriber);
  public Object receiveNoWait(java.lang.String receiver);
  public Object receive(java.lang.String receiver);
  public Object receive(java.lang.String receiver, java.lang.String selector);
  public Object receive(java.lang.String receiver, java.lang.String selector, 
         long timeout);
}

...
System.out.println("*** testStream with an OCI connection");
Object response = null;
ToyStream stream = new ToyStream(getOCIConnection());

stream.publish("Seaside news", new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println("Received: " + response);

stream.publish(new Integer(333), new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println("Received: " + response);

stream.publish(new Float(3.33), new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println("Received: " + response);

stream.publish("Science Monitor".getBytes(), new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println("Received: " + new String((byte[])response));

stream.publish(new String[]{"gamma", "beta"}, new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println("Received: " + ((String[]) response)[0]);

HashMap map = new HashMap();
map.put("US", "dollar");
map.put("Japan", "yen");
map.put("Austrilia", "dollar");
map.put("Britian", "pound");
stream.publish(map, new String[]{"ToyParty"});
response = stream.receive("ToyParty");
map = (HashMap) response;
System.out.println("Message received: " + map.get("Britian") + ", " + map.get("US") + ", " + map.get("Austrilia"));

stream.addTypeMap("SCOTT.QUEUE_MESSAGE", "queue.wrapper.simple.QueueMessage");
stream.addTypeMap("QUEUE_MESSAGE", "queue.wrapper.simple.QueueMessage");
QueueMessage m = new QueueMessage("Knowing", "world currency");
stream.publish(m, new String[]{"ToyParty"});
response = stream.receive("ToyParty");
System.out.println(response);
m = (QueueMessage) response;
System.out.println("Message received: " + m.getSubject() + " " + m.getText());
...

Publishing Server-Side Java Classes Through Native Java Interface

Prior to Oracle Database 10g, calling Java stored procedures and functions from a database client required JDBC calls to the associated PL/SQL wrappers. Each PL/SQL wrapper had to be manually published with a SQL signature and a Java implementation. This process had the following disadvantages:

• The signatures permitted only Java types that had direct SQL equivalents.

• Exceptions issued in Java were not properly returned.

Starting from Oracle Database 10g, you can use the native Java interface feature for calls to server-side Java code. The JPublisher -java option provides functionality to overcome these disadvantages.

To remedy the deficiencies of JDBC calls to associated PL/SQL wrappers, the -java option makes use of an API for direct invocation of static Java methods. This functionality is also useful for Web services.

The functionality of the -java option mirrors that of the -sql option, creating a client-side Java stub class to access a server-side Java class. This is in contrast to creating a client-side Java class to access a server-side SQL object or PL/SQL package. The client-side stub class uses SQL code that mirrors the server-side class and includes the following features:

• Methods corresponding to the public, static methods of the server class

• Two constructors, one that takes a JDBC connection and one that takes the SQLJ default connection context instance

At run time, the stub class is instantiated with a JDBC connection. Calls to its methods result in calls to the corresponding methods of the server-side class. Any Java types used in these published methods must be primitive or serializable.

You can use the -java option to publish a server-side Java class, as follows:

-java=className

Consider the oracle.sqlj.checker.JdbcVersion server-side Java class, with the following APIs:

public class oracle.sqlj.checker.JdbcVersion
{
  public oracle.sqlj.checker.JdbcVersion();
  public static int getDriverMajorVersion();
  public static int getDriverMinorVersion();
  public static java.lang.String getDriverName();
  public static java.lang.String getDriverVersion();
  public static java.lang.String getJdbcLibraryName();
  public static java.lang.String getRecommendedRuntimeZip();
  public static java.lang.String getRuntimeVersion();
  public static java.lang.String getSqljLibraryName();
  public static boolean hasNewStatementCache();
  public static boolean hasOracleContextIsNew();
  public static boolean hasOracleSavepoint();
  public static void main(java.lang.String[]);
  public java.lang.String toString();
  public static java.lang.String to_string();
}

As an example, assume that you want to call the following method on the server:

public String oracle.sqlj.checker.JdbcVersion.to_string();

Use the following command to publish JdbcVersion for client-side invocation:

% jpub -sql=scott -java=oracle.sqlj.checker.JdbcVersion:JdbcVersion Client
Enter scott password: password

This command generates the client-side Java class, JdbcVersionClient, which contains the following APIs:

public class JdbcVersionClient
{
  public long newInstance();
  public JdbcVersionClient();
  public JdbcVersionClient(java.sql.Connection conn);
  public JdbcVersionClient(sqlj.runtime.ref.DefaultContext ctx);
  public java.lang.String toString(long _handle);
  public int getDriverMajorVersion();
  public int getDriverMinorVersion();
  public java.lang.String getDriverName();
  public java.lang.String getDriverVersion();
  public java.lang.String getJdbcLibraryName();
  public java.lang.String getRecommendedRuntimeZip();
  public java.lang.String getRuntimeVersion();
  public java.lang.String getSqljLibraryName();
  public boolean hasNewStatementCache();
  public boolean hasOracleContextIsNew();
  public boolean hasOracleSavepoint();
  public void main(java.lang.String[] p0);
  public java.lang.String to_string();
}

Compare oracle.sqlj.checker.JdbcVersion with JdbcVersionClient. All static methods are mapped to instance methods in the client-side code. A instance method in the server-side class, toString() for example, is mapped to a method with an extra handle. A handle represents an instance of oracle.sqlj.checker.JdbcVersion in the server. The handle is used to call the instance method on the server-side. The extra method in JdbcVersionClient is newInstance(), which creates a new instance of oracle.sqlj.checker.JdbcVersion in the server and returns its handle.

Publishing the server-side Java class has the following constraints:

• Instance methods can be published only if the class to be published has a public empty constructor.

• Only serializable parameter and return types are supported. Methods with nonserializable types will not be published.

• Oracle Database 11g or Oracle Database 10g is required.

Publishing Server-Side Java Classes Through PL/SQL Wrappers

Since Oracle Database 10g release 2 (10.2), JPublisher provides a new approach to publish server-side Java classes. It generates the following to call server-side Java:

• Java stored procedure wrapper for the server-side class

• PL/SQL wrapper for the Java stored procedure wrapper

• Client-side Java code to call the PL/SQL wrapper

The Java stored procedure wraps the server-side Java code, which accomplishes the following:

• Wraps an instance method into a static method. Each method in the server-side Java code is wrapped by a static method. An instance method can be mapped in a single or multiple-instance fashion.

• Converts Java types into types that can be exposed to the PL/SQL call specification. For example, the Java type byte[] is converted into oracle.sql.BLOB.

The PL/SQL wrapper calls the Java stored procedure. The client-side Java code calls the PL/SQL wrapper through JDBC calls. The -java option requires that the class to be exposed is already loaded into the database.

The supported Java types are:

• JDBC supported types

• Java beans

• Arrays of supported types

• Serializable types

To publish a server-side class, use the -dbjava option, as follows:

-dbjava=server-sideClassName:client-sideClassName

The client-sideClassName setting must be specified. Otherwise, JPublisher will not generate client-side Java class. To publish oracle.sqlj.checker.JdbcVersion, use the following command:

% jpub -user=scott -dbjava=oracle.sqlj.checker.JdbcVersion:JdbcVersionClient
Enter scott password: password

The command generates the following output:

oracle/sqlj/checker/JdbcVersionJPub.java
plsql_wrapper.sql
plsql_dropper.sql
SCOTT.JPUBTBL_VARCHAR2
SCOTT.JPUB_PLSQL_WRAPPER
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Loading JdbcVersionJPub.java

The command generates the JdbcVersionJPub Java stored procedure, the PL/SQL wrapper, and the client-side JdbcVersionClient class. JdbcVersionJPub.java and plsql_wrapper.sql are automatically loaded into the database. JdbcVersionClient has the following APIs:

public class JdbcVersionClient
{
  public JdbcVersionClient();
  public JdbcVersionClient(java.sql.Connection conn);
  public void setConnection(java.sql.Connection conn);
  public void setDataSource(javax.sql.DataSource ds);
  public String toString0();
  public java.math.BigDecimal getDriverMajorVersion();
  public java.math.BigDecimal getDriverMinorVersion();
  public String getDriverName();
  public String getDriverVersion();
  public String getJdbcLibraryName();
  public String getRecommendedRuntimeZip();
  public String getRuntimeVersion();
  public String getSqljLibraryName();
  public java.math.BigDecimal hasNewStatementCache();
  public java.math.BigDecimal hasOracleContextIsNew();
  public java.math.BigDecimal hasOracleSavepoint();
  public void main0(JpubtblVarchar2 arg0);
  public String to_string();
}

Compare JdbcVersion and JdbcVersionClient. It shows a limitation of JPublisher-generated code. The generated client-side APIs are not exactly the same as the original server-side APIs. To illustrate this limitation, the following is a list of several inconsistencies between JdbcVersion and JdbcVersionClient:

• The static methods are all mapped to instance methods, because a client-side method requires a JDBC connection to run.

• A client-side method always throws java.sql.SQLException, while exceptions thrown from the server-side class will be passed to the client wrapped with SQLException.

• The toString() method is renamed to toString0(). This is a limitation imposed by the stored procedure wrapper, where any method overwriting java.lang.Object methods has to be renamed to avoid conflicts.

• The parameter and return types may be different. Numeric types in the server-side are mapped to java.math.BigDecimal. Array types, such as String[], are mapped to JDBC custom types. For example, the parameter of main() is mapped to JpubtblVarchar2, a subclass of ORAData, which the JPublisher command generates to represent an array of strings.

• The main() method in the server-side Java class will be renamed to main0(), due to the Java stored procedure limitation.

Compared to -java, the advantage of -dbjava is the support for more types and working with pre-10g database versions. However, the disadvantages are extra PL/SQL and Java stored procedure layers at run time and the increased possibility of change in the method signature in the client-side Java class.

Publishing Server-Side Java Classes to PL/SQL

JPublisher can generate PL/SQL wrappers for server-side Java classes. A Java class is mapped to a PL/SQL package. Each PL/SQL method corresponds to a Java method. This feature relieves the customer from writing the PL/SQL call specification and creating SQL types used in the call specification.

You can use the -dbjava option to generate the PL/SQL wrapper for a server-side Java class as follows:

-dbjava=server-sideJavaClass

Do not specify a name after server-sideJavaClass. Otherwise, JPublisher will map the server-side Java class to a client-side Java class.

As an example, generate the PL/SQL wrapper for oracle.sqlj.checker.JdbcVersion using the following command:

% java -dbjava=oracle.sqlj.checker.JdbcVersion

The command generates the following output:

oracle/sqlj/checker/JdbcVersionJPub.java
plsql_wrapper.sql
plsql_dropper.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Loading JdbcVersionJPub.java

The command generates and loads the Java stored procedure wrapper, JdbcVersionJPub.java, and also its PL/SQL wrapper, plsql_wrapper.sql, which declares the package JPUB_PLSQL_WRAPPER. The JPUB_PLSQL_WRAPPER package can be used to call the methods of oracle.sqlj.checker.JdbcVersion.

It often makes sense to specify -plsqlfile and -plsqlpackage with -dbjava. Consider the following command:

% java -dbjava=oracle.sqlj.checker.JdbcVersion -plsqlfile=jdbcversion.sql -plsqlpackage=jdbcversion

The command generates the following output:

oracle/sqlj/checker/JdbcVersionJPub.java
jdbcversion.sql
jdbcversion_dropper.sql
Executing jdbcversion_dropper.sql
Executing jdbcversion.sql
Loading JdbcVersionJPub.java

The command generates jdbcversion.sql, which declares the jdbcversion PL/SQL package as the wrapper for oracle.sqlj.checker.JdbcVersion. The package is declared as follows:

CREATE OR REPLACE PACKAGE jdbcversion AS
  FUNCTION toString0 RETURN VARCHAR2;
  FUNCTION getDriverMajorVersion RETURN NUMBER;
  FUNCTION getDriverMinorVersion RETURN NUMBER;
  FUNCTION getDriverName RETURN VARCHAR2;
  FUNCTION getDriverVersion RETURN VARCHAR2;
  FUNCTION getJdbcLibraryName RETURN VARCHAR2;
  FUNCTION getRecommendedRuntimeZip RETURN VARCHAR2;
  FUNCTION getRuntimeVersion RETURN VARCHAR2;
  FUNCTION getSqljLibraryName RETURN VARCHAR2;
  FUNCTION hasNewStatementCache RETURN NUMBER;
  FUNCTION hasOracleContextIsNew RETURN NUMBER;
  FUNCTION hasOracleSavepoint RETURN NUMBER;
  PROCEDURE main0(arg0 JPUBTBL_VARCHAR2);
  FUNCTION to_string RETURN VARCHAR2;
END jdbcversion;

Note that the methods toString() and main() are renamed to toString0() and main0(), because of the Java stored procedure limitation.

You can run the PL/SQL stored procedures in the jdbcversion package as follows:

SQL> SELECT jdbcversion.toString0 FROM DUAL;
 
TOSTRING0
--------------------------------------------------------------------------------
Oracle JDBC driver version 10.2 (10.2.0.0.0)
SQLJ runtime: Oracle 9.2.0 for JDBC SERVER/JDK 1.2.x - Built on Oct 10, 2004

The -dbjava command publishes both static and instance methods. To publish the static method only, use the following setting:

-proxyopts=static

If the server-side class has a public empty constructor, then its instance methods can be published. Instance methods can be called in two ways, through a default single instance inside the server, or through individual instances. The following option determines the approach used to call instance methods inside the server:

-proxyopts=single|multiple

The default setting is:

-proxyopts=single

The preceding SQL statement calls the toString0() method using the single instance.

You can publish oracle.sqlj.checker.JdbcVersion using -proxyopts=multiple, as follows:

% jpub -user=scott -dbjava=oracle.sqlj.checker.JdbcVersion -plsqlfile=jdbcversion.sql -plsqlpackage=jdbcversion 
-proxyopts=multiple
Enter scott password: password

This command generates the jdbcversion PL/SQL package, with the following methods different from the previous example:

CREATE OR REPLACE PACKAGE jdbcversion AS
  FUNCTION toString0(handleJdbcVersion NUMBER) RETURN VARCHAR2;
  ...
  FUNCTION newJdbcVersion RETURN NUMBER;
END jdbcversion;

Starting from Oracle database 10.2, an extra method, newJdbcVersion(), is created. You can create an instance using this method and use the instance to call the toString0() method. Run the following script in SQL*Plus:

set serveroutput on
DECLARE
  text varchar2(1000);
  inst number;
BEGIN
  inst := jdbcversion.newJdbcVersion;
  text := jdbcversion.toString0(inst);
  dbms_output.put_line(text);
END;
/

This script returns:

Oracle JDBC driver version 10.2 (10.2.0.0.0)
SQLJ runtime: Oracle 9.2.0 for JDBC
SERVER/JDK 1.2.x - Built on Oct 10, 2004

PL/SQL procedure successfully completed.

The following parameter and return types are supported:

• JDBC supported types

• Java beans

• Arrays of supported types

Java beans are mapped to the generic JDBC struct class, oracle.sql.STRUCT at the Java stored procedure layer, and SQL object types and SQL table types at the PL/SQL layer. The following option determines how array parameters are handled:

-proxyopts=arrayin|arrayout|arrayinout|arrayall

The default setting is:

-proxyopts=arrayin

With -proxyopts=arrayall, a method containing array parameters is mapped to three PL/SQL methods. For example, consider the foo(int[]) method. This method is mapped to the following methods:

PROCEDURE foo(n NUMBERTBL);
PRECEDURE foo_o(n IN NUMBER);
PROCEDURE foo_io(n IN OUT NUMBER);

The first method treats the array argument as an input, the second treats the array as a holder for an output value, and the third treats the array as a holder for both input and output values. With -proxyopts=arrayin, which is the default setting, the foo(int[]) method is mapped to the first method. With -proxyopts=arrayout, the foo(int[]) method is mapped to the second method. With -proxyopts=arrayinout, the foo(int[]) method is mapped to the third method.

Consider a more complex example that uses two classes. The Add class uses Total and arrays in the methods. Total is a Java Bean and is therefore supported by server-side classes publishing. The two classes are defined as follows:

public class Add
{
  public static int[] add(int[] i, int[] j)
  {
    for (int k=0; k<i.length; k++)
      i[k] = i[k] + j[k];
    return i;
  }
  public int add(Total arg)
  {
    total = total + arg.getTotal();
    return total;
  }
  private int total;
}

public class Total
{
  public void setTotal(int total)
  {
    this.total = total;
  }
  public int getTotal()
  {
    return total;
  }
  private int total;
}

Load the two classes into the database, as follows:

% loadjava -u scott -r -v -f Add.java Total.java
Password: password

Run JPublisher using the following command:

% jpub -user=scott -dbjava=Add  -proxyopts=arrayall

The command generates the following output:

AddJPub.java
plsql_wrapper.sql
plsql_dropper.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Loading AddJPub.java

The generated PL/SQL wrapper, plsql_wrapper.sql, will have the following declaration:

CREATE OR REPLACE TYPE JPUBOBJ_Total AS OBJECT (total_ NUMBER);
CREATE OR REPLACE TYPE JPUBTBL_NUMBER AS TABLE OF NUMBER;
CREATE OR REPLACE PACKAGE JPUB_PLSQL_WRAPPER AS
  FUNCTION add(arg0  JPUBOBJ_Total) RETURN NUMBER;
  FUNCTION add_io(arg0  JPUBOBJ_Total) RETURN NUMBER;
  FUNCTION add(arg0  JPUBTBL_NUMBER,arg1  JPUBTBL_NUMBER) RETURN JPUBTBL_NUMBER;
  FUNCTION add_o(arg0 OUT  NUMBER,arg1 OUT  NUMBER) RETURN JPUBTBL_NUMBER;
  FUNCTION add_io(arg0 IN OUT  NUMBER,arg1 IN OUT NUMBER) RETURN JPUBTBL_NUMBER;
END JPUB_PLSQL_WRAPPER;

The following SQL script, when run in SQL*Plus, uses the generated PL/SQL wrapper:

SQL> set serveroutput on
SQL> 
DECLARE
  totalx JPUBOBJ_Total;
  n NUMBER;
  n1 NUMBER;
  n2 NUMBER;
  add1 JPUBTBL_NUMBER;
  add2 JPUBTBL_NUMBER;
  add3 JPUBTBL_NUMBER;
BEGIN
  totalx := JPUBOBJ_Total(2004);
  n := JPUB_PLSQL_WRAPPER.add(totalx);
  n := JPUB_PLSQL_WRAPPER.add(totalx);
  DBMS_OUTPUT.PUT('total ');
  DBMS_OUTPUT.PUT_LINE(n);
 
  add1 := JPUBTBL_NUMBER(10, 20);
  add2 := JPUBTBL_NUMBER(100, 200);
  add3 := JPUB_PLSQL_WRAPPER.add(add1, add2);
  DBMS_OUTPUT.PUT('add ');
  DBMS_OUTPUT.PUT(add3(1));
  DBMS_OUTPUT.PUT(' ');
  DBMS_OUTPUT.PUT_LINE(add3(2));
 
  n1 := 99;
  n2 := 199;
  add3 := JPUB_PLSQL_WRAPPER.add_io(n1, n2);
  DBMS_OUTPUT.PUT('add_io ');
  DBMS_OUTPUT.PUT_LINE(n1);
END; 
/

The script generates the following output:

total 4008
add 110 220
add_io 298
PL/SQL procedure successfully completed.

The -dbjava option requires the classes being published to be present in the database. You can use -proxyclasses instead, which requires the classes being published to be specified in the classpath. Compile Add.java and Total.java, and include Add and Total in the classpath. You can use the following command to publish Add, instead of the -dbjava option:

% jpub -proxyclasses=Add

The command generates the following output:

AddJPub.java
plsql_wrapper.sql
plsql_dropper.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql

The -proxyclasses option loads the generated PL/SQL wrapper. However, it does not load the generated Java stored procedure, AddJPub.java, because this procedure requires the published classes to exist on the server. You must load the Java stored procedure together with the published classes.

For example, on UNIX systems, you can load Add.java, Total.java, and AddJPub.java using the following command:

% loadjava -u scott -r -v -f Add.java Total.java AddJPub.java
Password: password

Once Add.java, Total.java, and AddJPub.java are loaded, the PL/SQL wrapper is ready for use.

Mechanisms Used in Exposing Java to PL/SQL

JPublisher supports easy access to server-side Java classes by generating PL/SQL wrappers, otherwise known as PL/SQL call specifications. A PL/SQL wrapper is a PL/SQL package that can invoke methods of one or more given Java classes.

PL/SQL supports only static methods. Java classes with only static methods or classes for which you want to expose only static methods can be wrapped in a straightforward manner. However, for Java classes that have instance methods that you want to expose, an intermediate wrapper class is necessary to expose the instance methods as static methods for use by PL/SQL.

A wrapper class is also required if the Java class to be wrapped uses anything other than Java primitive types in its method calling sequences.

For instance methods in a class that is to be wrapped, JPublisher can use either or both of the following mechanisms in the wrapper class:

• Each wrapped class can be treated as a singleton, meaning that a single default instance is used. This instance is created the first time a method is called and is reused for each subsequent method call. Handles are not necessary and are not used. This mechanism is referred to as the singleton mechanism and is the default behavior when JPublisher provides wrapper classes for Web services client proxy classes.

  A releaseXXX() method is provided to remove the reference to the default instance and permit it to be garbage-collected.

• Instances of the wrapped class can be identified through handles, also known as ID numbers. JPublisher uses long numbers as handles and creates static methods in the wrapper class. The method signatures of these methods are modified to include the handle of the instance on which to invoke a method. This allows the PL/SQL wrapper to use the handles in accessing instances of the wrapped class. In this scenario, you must create an instance of each wrapped class to obtain a handle. Then you provide a handle for each subsequent instance method invocation. This mechanism is referred to as the handle mechanism.

  A releaseXXX(long) method is provided for releasing an individual instance according to the specified handle. A releaseAllXXX() method is provided for releasing all existing instances.

Publishing Server-Side Java Classes to Table Functions

The -dbjava option can generate table functions from the generated PL/SQL wrapper. Table functions are used if you want to expose data through database tables, rather than through stored function returns or stored procedure output values. A table function returns a database table.

For a table function to be generated for a given method, the following must be true:

• For wrapping instance methods, the singleton mechanism must be enabled. This is the default setting for -dbjava and -proxyclasses.

• The wrapped Web service method must correspond to a stored procedure with OUT arguments or to a stored function.

When used with the -dbjava or -proxyclasses option, the JPublisher -proxyopts=tabfun setting requests a table function created for each PL/SQL function in the generated PL/SQL wrapper. Consider the Add class example discussed earlier. Run the following command:

% jpub -user=scott -dbjava=Add  -proxyopts=arrayall,tabfun
Enter scott password: password

The command generates the following output:

AddJPub.java
plsql_wrapper.sql
plsql_dropper.sql
Executing plsql_dropper.sql
Executing plsql_wrapper.sql
Loading AddJPub.java

This command generates the following extra table functions, in addition to the PL/SQL methods generated in the earlier example:

CREATE OR REPLACE PACKAGE JPUB_PLSQL_WRAPPER AS
   FUNCTION add(arg0 JPUBOBJ_Total) RETURN NUMBER;
   FUNCTION TO_TABLE_add(cur SYS_REFCURSOR) RETURN GRAPH_TAB_add_JPUBOBJ_Total PIPELINED;
   FUNCTION add(arg0 JPUBTBL_NUMBER,arg1  JPUBTBL_NUMBER) RETURN JPUBTBL_NUMBER;
   FUNCTION TO_TABLE_add0(cur SYS_REFCURSOR) RETURN GRAPH_TAB_add_JPUBTBL_NUMBER PIPELINED;
   FUNCTION add_o(arg0 OUT NUMBER, arg1 OUT NUMBER) RETURN JPUBTBL_NUMBER;
   FUNCTION TO_TABLE_add_o(cur SYS_REFCURSOR) RETURN GRAPH_TAB_add_o_JPUBTBL_NUMBER PIPELINED;
   FUNCTION add_io(arg0 IN OUT NUMBER, arg1 IN OUT NUMBER) RETURN JPUBTBL_NUMBER;
   FUNCTION TO_TABLE_add_io(cur SYS_REFCURSOR) RETURN GRAPH_TAB_add_io_JPUBTBL_NUMB PIPELINED;
END JPUB_PLSQL_WRAPPER;
/

The term, graph, is used with table functions. In this usage, a graph is a SQL object that defines the schema of the database table returned by a table function. There are three levels of functionality: a graph object, a table of graph objects, and a table function that returns the table of graph objects. The table of graph objects contains the input to a function and the output from that function.

As an example, consider the following declarations in plsql_wrapper.sql, which define the GRAPH_add_io_JPUBTBL_NUMBER_J graph object and the GRAPH_TAB_add_io_JPUBTBL_NUMB table of graph objects. These two types are generated for the TO_TABLE_add_io table function.

CREATE OR REPLACE TYPE GRAPH_add_io_JPUBTBL_NUMBER_J AS OBJECT(arg0 NUMBER, 
arg1 NUMBER, arg0_out NUMBER, arg1_out NUMBER, res JPUBTBL_NUMBER);
/

CREATE OR REPLACE TYPE GRAPH_TAB_add_io_JPUBTBL_NUMB AS TABLE OF GRAPH_add_io_JPUBTBL_NUMBER_J;
/

Also note that a table function always takes a REF CURSOR as input. For the TO_TABLE_add_io table function, the REF CURSOR expects two arguments, arg0 and arg1. The table function returns an instance of GRAPH_TAB_add_io_JPUBTBL_NUMB.

Run the following SQL script:

SQL> CREATE TABLE tabfun_input(arg0 NUMBER, arg1 NUMBER);
SQL> BEGIN
  INSERT INTO tabfun_input VALUES(97, 106);
  INSERT INTO tabfun_input VALUES(67, 3);
  INSERT INTO tabfun_input VALUES(19, 23);
  INSERT INTO tabfun_input VALUES(98, 271);
  INSERT INTO tabfun_input VALUES(83, 281);
END;
/

SQL>  SELECT * FROM TABLE(JPUB_PLSQL_WRAPPER.TO_TABLE_add_io(CURSOR(SELECT * FROM tabfun_input)));

The query calls TO_TABLE_add_io, which shows the input and output of that table function.

ARG0     ARG1 ARG0_OUT ARG1_OUT RES
-------- -------- -------- -------- -------------------------
      97      106      203      106 JPUBTBL_NUMBER(203)
      67        3       70        3 JPUBTBL_NUMBER(70)
      19       23       42       23 JPUBTBL_NUMBER(42)
      98      271      369      271 JPUBTBL_NUMBER(369)
      83      281      364      281 JPUBTBL_NUMBER(364)

Publishing Web Services Client into PL/SQL

JPublisher can publish a Web Service Description Language (WSDL) file into a PL/SQL package, to allow a database user to call a Web service from PL/SQL. This feature is called as Web services call-out. Given a WSDL file, JPublisher generates a Java-based Web services client proxy, and further generates PL/SQL wrapper for the client proxy. The client proxy is generated by the Oracle Database Web services assembler tool, which is started by JPublisher. Before starting the tool, the following have to be present in the database:

• The client proxy generated by JPublisher

• The PL/SQL wrapper generated by JPublisher

• The Java stored procedure wrapper generated by JPublisher

• The Java API for XML-based Remote Procedure Call (JAX-RPC) Web services client run time or Oracle Simple Object Access Protocol (SOAP) Web services client run time.

These components can be loaded automatically by JPublisher or manually by the user. At run time, a Web services call-out works as follows:

1. The user calls the PL/SQL wrapper, which in turn calls the Java stored procedure wrapper.

2. The Java stored procedure calls the client proxy.

3. The client proxy uses the Web services client run time to call the Web services.

The Java stored procedure wrapper is a required intermediate layer to publish instance methods of the client proxy class as static methods, because PL/SQL supports only static methods.

Web services call-out requires the following JAR files, which are included in Database Web Services Callout Utility 10g release 2:

• dbwsa.jar

• dbwsclientws.jar

• dbwsclientdb101.jar

• dbwsclientdb102.jar

These files can be downloaded from:

http://www.oracle.com/technology/sample_code/tech/java/jsp/callout_users_guide.htm

All the JAR files should be copied to the ORACLE_HOME/sqlj/lib directory. The dbwsa.jar file is required in the classpath when JPublisher publishes a WSDL file. On UNIX systems, the jpub command-line script includes the ORACLE_HOME/sqlj/lib/dbwsa.jar. Therefore, you do not have to include it in the classpath.

The dbwsclientws.jar file contains webservice client classes, irrespective of the version of Oracle database. The dbwsclientdb101.jar file contains webservice client classes that are specific to Oracle database 10.1 release. The dbwsclientdb102.jar file contains webservice client classes that are specific to Oracle database 10.2 or Oracle database 11g.

For Oracle9i Database, only Oracle SOAP Web services client is supported. To load Oracle SOAP Web services client run time into a pre-9.2 Oracle Database, run the following command:

% loadjava -u sys -r -v -s -f -grant public \
                  ${J2EE_HOME}/lib/activation.jar \
                  ${J2EE_HOME}/lib/http_client.jar \
                  ${ORACLE_HOME}/lib/xmlparserv2.jar \
                  ${ORACLE_HOME}/soap/lib/soap.jar \
                  ${J2EE_HOME}/lib/mail.jar
Password: password

The commands are in the format of UNIX systems. However, it gives an idea to Microsoft Windows users about the JAR files that are required for Oracle SOAP Web services client. The JAR files involved are distributed with Oracle9i Application Server releases.

To load Oracle SOAP Web services client into Oracle Database 9.2, run the following command:

% loadjava -u scott -r -v -f -genmissing
 ${ORACLE_HOME}/j2ee/home/lib/jssl-1_2.jar
 ${ORACLE_HOME}/soap/lib/soap.jar
 ${ORACLE_HOME}/dms/lib/dms.jar
 ${ORACLE_HOME}/j2ee/home/lib/servlet.jar 
${ORACLE_HOME}/j2ee/home/lib/ejb.jar 
${ORACLE_HOME}/j2ee/home/lib/mail.jar
Password: password

To load Oracle SOAP Web services client into Oracle Database 10g, run the following command:

% loadjava -u scott -r -f -v -genmissing 
${ORACLE_HOME}/soap/lib/soap.jar 
${ORACLE_HOME}/lib/dms.jar 
${ORACLE_HOME}/jlib/javax-ssl-1_1.jar 
${ORACLE_HOME}/j2ee/home/lib/servlet.jar 
${ORACLE_HOME}/j2ee/home/lib/mail.jar 
${ORACLE_HOME}/j2ee/home/lib/activation.jar
${ORACLE_HOME}/j2ee/home/lib/http_client.jar 
${ORACLE_HOME}/j2ee/home/lib/ejb.jar
Password: password

To load Oracle JAX-RPC client into Oracle 10g Release 1 Database, use any one of the following two options:

• Load the webservice client into the SYS schema using the following command:

  % loadjava -u sys -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb101.jar
  Password: password
  

• Load the webservice client into a user schema using the following command:

  % loadjava -u scott -r -v -f -genmissing dbwsclientws.jar dbwsclientdb101.jar
  Password: password
  

To load Oracle JAX-RPC client into Oracle 10g Release 2 Database or Oracle Database 11g, use any one of the following two options:

• Load the webservice client into the SYS schema using the following command:

  % loadjava -u sys -r -v -f -genmissing -s -grant public dbwsclientws.jar dbwsclientdb102.jar
  Password: password
  

• Load the webservice client into a user schema using the command:

  % loadjava -u scott -r -v -f -genmissing dbwsclientws.jar dbwsclientdb102.jar
  Password: password
  

Web services call-outs require that JPublisher runs on JDK 1.4 or later. The following JPublisher options are related to Web services call-outs:

-proxywsdl=url
-httpproxy=host:port
-endpoint=url
-proxyopts=soap|jaxrpc|noload|tabfun. Default: -proxyopts=jaxrpc|tabfun.
-sysuser=user/password

where,

• The -proxywsdl option specifies the URL or path of a WSDL file, which describes the Web services being published.

• The -httpproxy option specifies the HTTP proxy that is used to access the WSDL file, if the file is outside a firewall.

• The -endpoint option redirects the client to the specified endpoint, rather than the endpoint specified in the WSDL file.

  See Also:

  "WSDL Document for Java and PL/SQL Wrapper Generation" and "Web Services Endpoint"

• The -proxyopts=soap setting specifies that the PL/SQL wrapper will use Oracle SOAP Web services client run time to call the Web services.

  The -proxyopts=jaxrpc setting specifies that the PL/SQL wrapper will use Oracle JAX-RPC Web services client run time to call the Web services.

  The -proxyopts=tabfun setting specifies that table functions be generated for applicable Web services operations.

• The -sysuser setting is recommended for -proxywsdl. It specifies a database user with SYS privileges. The -sysuser setting allows JPublisher to assign appropriate access privileges to run the generated PL/SQL wrappers. The -sysuser setting also allows JPublisher to load Web services client run time, if the run time is not present in the database.

For example, assume that a JAX-RPC Web service, called HelloService, is deployed to the following endpoint:

http://localhost:8888/javacallout/javacallout

The WSDL document for this Web service is at the following location:

http://localhost:8888/javacallout/javacallout?WSDL

The Web service provides an operation called getProperty that takes a Java string specifying the name of a system property, and returns the value of that property. For example, getProperty("os.name") may return SunOS.

Based on the WSDL description of the Web service, JPublisher can direct the generation of a Web service client proxy, and generate Java and PL/SQL wrappers for the client proxy. Use the following command to perform these functions:

% jpub -user=scott -sysuser=sys/sys_password
       -url=jdbc:oracle:thin:@localhost:1521:orcl
       -proxywsdl=http://localhost:8888/javacallout/javacallout?WSDL
       -package=javacallout -dir=genproxy
Enter scott password: password

The command gives the following output:

genproxy/HelloServiceJPub.java
genproxy/plsql_wrapper.sql
genproxy/plsql_dropper.sql
genproxy/plsql_grant.sql
genproxy/plsql_revoke.sql
Executing genproxy/plsql_wrapper.sql
Executing genproxy/plsql_grant.sql
Loading genproxy/plsql_proxy.jar

The -proxyopts setting directs the generation of the JAX-RPC client proxy and wrappers, and the use of a table function to wrap the Web service operation. The -url setting indicates the database, and the -user setting indicates the schema, where JPublisher loads the generated Java and PL/SQL wrappers. The -sysuser setting specifies the SYS account that has the privileges to grant permissions to run the wrapper script.

The plsql_grant.sql and plsql_revoke.sql scripts are generated by JPublisher. These scripts are used to create the PL/SQL wrapper in the database schema, grant permission to run it, revoke that permission, and drop the PL/SQL wrapper from the database schema.

The contents of the WSDL file is as follows:

<?xml version="1.0" encoding="UTF-8"?>
 
<definitions name="HelloService"
             targetNamespace="http://oracle.j2ee.ws/javacallout/Hello"
             xmlns:tns="http://oracle.j2ee.ws/javacallout/Hello"
             xmlns="http://schemas.xmlsoap.org/wsdl/"
             xmlns:xsd="http://www.w3.org/2001/XMLSchema"
             xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
  <types/>
  <message name="HelloServiceInf_getProperty">
    <part name="String_1" type="xsd:string"/>
  </message>
  <message name="HelloServiceInf_getPropertyResponse">
    <part name="result" type="xsd:string"/>
  </message>
  <portType name="HelloServiceInf">
    <operation name="getProperty" parameterOrder="String_1">
      <input message="tns:HelloServiceInf_getProperty"/>
      <output message="tns:HelloServiceInf_getPropertyResponse"/>
    </operation>
  </portType>
  <binding name="HelloServiceInfBinding" type="tns:HelloServiceInf">
    <operation name="getProperty">
      <input>
        <soap:body encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
                   use="encoded"
                   namespace="http://oracle.j2ee.ws/javacallout/Hello"/>
      </input>
      <output>
        <soap:body encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
                   use="encoded"
                   namespace="http://oracle.j2ee.ws/javacallout/Hello"/>
      </output>
      <soap:operation soapAction=""/>
    </operation>
    <soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="rpc"/>
  </binding>
  <service name="HelloService">
    <port name="HelloServiceInfPort" binding="tns:HelloServiceInfBinding">
      <soap:address location="/javacallout"/>
    </port>
  </service>
</definitions>

HelloServiceInf in the <message> element is the name of the service bean and determines the name of the interface that is generated and implemented by the generated JAX-RPC client proxy stub class. The HelloServiceInf interface has the following signature:

public interface HelloServiceInf extends java.rmi.Remote
{
  public String getProperty(String prop) throws java.rmi.RemoteException;
}

The method getProperty() corresponds to the getProperty operation specified in the WSDL document. It returns the value of a specified system property, prop. For example, specify the property os.version to return the operating system version.

The plsql_wrapper.sql file defines the JPUB_PLSQL_WRAPPER PL/SQL wrapper package. This package is created for calling the Web service from PL/SQL. It includes the definition of a table function from the Web service operation getProperty. The script in the plsql_wrapper.sql file is as follows:

CREATE OR REPLACE TYPE GRAPH_getProperty AS OBJECT(
  p0 VARCHAR2(32767),
  res VARCHAR2(32767)
);
/
CREATE OR REPLACE TYPE GRAPH_TAB_getProperty AS TABLE OF GRAPH_getProperty;
/
-- PL/SQL procedures that invoke webserviecs
CREATE OR REPLACE PACKAGE JPUB_PLSQL_WRAPPER AS
  FUNCTION getProperty(p0 VARCHAR2) RETURN VARCHAR2;
  FUNCTION TO_TABLE_getProperty(cur SYS_REFCURSOR) RETURN GRAPH_TAB_getProperty PIPELINED;
END JPUB_PLSQL_WRAPPER;
/

Because the -user and -sysuser settings are specified in the JPublisher command line to publish this Web service, JPublisher will load the generated Java code and PL/SQL wrapper into the database. Once everything is loaded, you can use the PL/SQL wrapper to invoke the Web service.

The PL/SQL wrapper consists of two functions: getProperty and TO_TABLE_getProperty. The getProperty function directly wraps the getProperty() method in the generated client proxy class. For example, the following SQL*Plus command uses getProperty to determine the operating system where the Web service is running:

SQL> SELECT JPUB_PLSQL_WRAPPER.getProperty('os.name') FROM DUAL;
JPUB_PLSQL_WRAPPER.GETPROPERTY('OS.NAME')
-----------------------------------------
SunOS

TO_TABLE_getProperty is a table function based on the getProperty function. It takes a REF CURSOR as input and returns a table. The schema of the table returned is defined by GRAPH_getProperty. In this example, TO_TABLE_getProperty is called with a REF CURSOR obtained from a one-column table of VARCHAR2 data, where each data item is the name of a system property, such as os.version. TO_TABLE_getProperty returns a table in which each row contains an item from the input REF CURSOR, and the result of a getProperty call taking that item as input. The following code is a sample usage of TO_TABLE_getProperty:

SQL> -- Test Table Function
SQL> CREATE TABLE props (name VARCHAR2(50));
SQL> BEGIN
INSERT INTO props VALUES('os.version');
INSERT INTO props VALUES('java.version');
INSERT INTO props VALUES('file.separator');
INSERT INTO props VALUES('file.encoding.pkg');
INSERT INTO props VALUES('java.vm.info');
END;
/
SQL> SELECT * FROM
TABLE(JPUB_PLSQL_WRAPPER.TO_TABLE_getProperty(CURSOR(SELECT * FROM props)));
P0 RES
------------------------------
os.version 5.8
java.version 1.4.1_03
file.separator /
file.encoding.pkg sun.io
java.vm.info mixed mode

This example creates a one-column table of VARCHAR2, populates it with system property names, and uses TO_TABLE_getProperty to find out the values of those system properties. In this example, you can see that the operating system is Sun Microsystems Solaris 5.8.