This chapter describes how you can use JPublisher for:
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 the 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/OE -sql=CATEGORY_TYP:CategoryTyp
The JPublisher -user
option specifies the user name and password. 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/OE -s CUSTOMER_TYP:CustomerTyp
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/OE -s COMPOSITE_CATEGORY_TYP:CompositeCategoryTyp -s LEAF_CATEGORY_TYP:LeafCategoryTyp,CATALOG_TYP:CatalogTyp
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 10g, the use of SQLJ classes, as opposed to regular Java classes, is invisible to you unless you use one of the backward-compatibility modes.
Note:
Prior to Oracle Database 10g, the generation of SQLJ classes resulted in the creation of visible.sqlj
source files. In Oracle Database 10g, if you set the JPublisher -compatible
flag to a value of 8i
, both8i
, 9i
, or sqlj
, then visible .sqlj
source files will be generated.
In any of these modes, you can use the JPublisher -sqlj
option as an alternative to using the sqlj
command-line utility to translate .sqlj
files.
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/OE -s WAREHOUSE_TYP:JPubWarehouse:MyWarehouse
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.
Note:
The preceding examples using theOE
schema are for illustrative purposes only and may not be completely up-to-date regarding the composition of the schema.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);
See Also:
"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.
Note:
If your stored procedures use types that are specific to PL/SQL and are not supported by Java, then special steps are required to map these arguments to SQL and then to Java.The following command publishes the SYS.DBMS_LOB
package into Java:
% jpub -u SCOTT/TIGER -s SYS.DBMS_LOB:DbmsLob
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 null
s, 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/TIGER -s SYS.DBMS_LOB:DbmsLob
The JPublisher output is:
SYS.DBMS_LOB
See Also:
"Mappings for Numeric Types"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/TIGER -s TOPLEVEL:SQLTopLevel
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 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:
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/TIGER -sql=AQNAME:javaName
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 need to 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, the jpub
script distributed with Oracle Database 10g release 2 (10.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.
You can publish a queue using the same settings that are used for publishing a SQL type or PL/SQL stored procedure.
Consider a queue, toy_queue
, declared as follows:
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' );
The following command publishes toy_queue
as a Java program:
% jpub -user=SCOTT/TIGER -sql=toy_queue:ToyQueue
Note:
When creating a queue or topic, you can specify a SQL type as the payload type. The payload type is transformed into and from the JMS message types.The command generates ToyQueue.java
, with the following APIs:
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); }
Like for PL/SQL stored procedures, JPublisher generates connection and data source management APIs, such as setConnection()
and setDataSource()
. The addTypeMap()
method enables you to specify type mapping if the payload type is a SQL type hierarchy. The send()
method enqueues a message. The receive()
method dequeues a message from the queue. This method blocks until a message is available to dequeue. The receiveNoWait()
method dequeues a message and returns null
if no message is available. The last receive()
method in the ToyQueue
class dequeues a message satisfying the selector. The selector is a condition specified in the AQ convention. For example, consider the condition:
priority > 3 and Subject IN ('spider','tank')
This selects messages with priority higher than 3
and with spider
and tank
as the Subject
attribute.
QueueMessage
is a subclass of ORAData
and is generated for the queue_message
payload type, which is a SQL type published as the result of publishing the queue.The following sample client code uses the generated ToyQueue
class. The client code sends a message to the queue, dequeues the queue using the block operator receive()
, and continues dequeuing messages using receiveNoWait()
, until all messages in the queue are dequeued.
... 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(); } ...
Consider a topic declared as follows:
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' );
The queue table, topic_queue_table
, has the Multiple_consumers
property set to TRUE
, indicating that the queue table hosts topics instead of queues.
You can publish the topic as follows:
% jpub -user=SCOTT/TIGER -sql=toy_topic:ToyTopic
The command generates ToyTopic.java
with the following APIs:
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); }
The publish
methods enqueue a message addressed to all the subscribers or a list of subscribers. The deleveryMode
parameter takes the value javax.jms.DeliveryMode.PERSISTENT
or javax.jms.DeliveryMode.NON_PERSISTENT
. However, only DeliveryMode.PERSISTENT
is supported in Oracle Database 10g release 2 (10.2). The priority
parameter specifies the priority of the message. The timeToLive
parameter specifies the time in milliseconds after which the message will be timed out. A value of 0
indicates the message is not timed out.The receive
methods dequeue a message addressed to the specified receiver.The following sample client code uses the generated ToyTopic
class. The client sends a message to two receivers, ToyParty
and ToyFactory
, and then dequeues the topic as ToyParty
, ToyLand
, and ToyFactory
respectively.
... 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"); ...
A stream is a special case of AQ. It can have only SYS.ANYDATA
as the payload type. As a limitation, JPublisher-generated code for streams requires the JDBC Oracle Call Interface (OCI) driver. However, the code generated for queue and topic run on both the JDBC Thin and JDBC OCI driver.
Publishing a stream is similar to publishing an AQ. The following command will publish the stream, toy_stream
:
% jpub -user=SCOTT/TIGER -sql=toy_stream:ToyStream
This command generates the ToyStream.java
file.
The difference between publishing a stream and an AQ or a topic is that when a stream is published, the payload type will always be SYS.ANYDATA
, which is mapped to java.lang.Object
.
The ToyStream.java
file contains the following APIs:
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); }
Here is a sample code that uses the generated ToyStream
class:
... 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()); ...
The sample code sends messages of various types, such as String
, Integer
, and java.util.Map
. For the QueueMessage
JDBC custom type, the addTypeMap()
method is called to specify SQL type to Java type mapping.
Oracle Database 10g introduces the native Java interface feature for calls to server-side Java code. 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.
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/tiger -java=oracle.sqlj.checker.JdbcVersion:JdbcVersion Client
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 10g is required, or sqljutl.jar
needs to be installed in SYS
.
In 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/tiger -dbjava=oracle.sqlj.checker.JdbcVersion:JdbcVersionClient
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.
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/tiger -dbjava=oracle.sqlj.checker.JdbcVersion -plsqlfile=jdbcversion.sql -plsqlpackage=jdbcversion -proxyopts=multiple
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;
An extra method, newJdbcVersion()
, is created this time. 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/tiger -r -v -f Add.java Total.java
Run JPublisher using the following command:
% jpub -user=scott/tiger -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 need to load the Java stored procedure together with the published classes.
For example, on UNIX, you can load Add.java
, Total.java
, and AddJPub.java
using the following command:
% loadjava -u scott/tiger -r -v -f Add.java Total.java AddJPub.java
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.
See Also:
Oracle Database Java Developer's Guide for information about PL/SQL wrappersPL/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 release
XXX
()
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 release
XXX
(long)
method is provided for releasing an individual instance according to the specified handle. A releaseAll
XXX
()
method is provided for releasing all existing instances.
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.
See Also:
Oracle Database PL/SQL User's Guide and Reference for information about table functions.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/tiger -dbjava=Add -proxyopts=arrayall,tabfun
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)
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:
The user calls the PL/SQL wrapper, which in turn calls the Java stored procedure wrapper.
The Java stored procedure calls the client proxy.
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 two JAR files, dbwsa.jar
and dbwsclient.jar
. These files are included in Database Web Services Callout Utility 10g release 2, which can be downloaded from:
http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html
Both dbwsa.jar
and dbwsclient.jar
should be copied to ORACLE_HOME
/sqlj/lib
. The dbwsa.jar
file is required in the classpath when JPublisher publishes a WSDL file. On UNIX, the jpub
command-line script includes the ORACLE_HOME
/sqlj/lib/dbwsa.jar
. Therefore, you do not have to include it in the classpath. It is to be loaded into the database. It can be loaded into a user schema or into the SYS
schema to be visible to other schemas. As an example, on UNIX, to load the .jar
file into SYS
, use the following loadjava
command:
% loadjava -u sys/change_on_install -r -v -f -s -grant public ORACLE_HOME/sqlj/lib/dbwsclient.jar
The dbwsclient.jar
file includes client run time for both Oracle SOAP Web services and Oracle JAX- RPC Web services run time. This JAR file can be loaded into Oracle Database 10g. However, it cannot be loaded into Oracle9i Database.
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/change_on_install -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
The commands are in UNIX format. 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 9.2 or later releases of Oracle Database, run the following command:
% loadjava -u sys/change_on_install -r -v -s -f -grant public \ ${J2EE_HOME}/lib/jssl-1_2.jar \ ${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
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.
The -proxyopts=soap
setting specifies that the PL/SQL wrapper will use the 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 HelloServiceEJB
, 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/tiger -sysuser=sys/change_on_install -url=jdbc:oracle:thin:@localhost:1521:orcl -proxywsdl=http://localhost:8888/javacallout/javacallout?WSDL -package=javacallout -dir=genproxy
The command gives the following output:
genproxy/HelloServiceEJBJPub.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="HelloServiceEJB" 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="HelloServiceEJB"> <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.