DBMS_JAVA Package

This chapter provides a description of the DBMS_JAVA package. Use these entry points to provide methods for accessing RDBMS functionality from Java.

FUNCTION longname (shortname VARCHAR2) RETURN VARCHAR2

Return the full name from a Java schema object. Because Java classes and methods can have names exceeding the maximum SQL identifier length, OracleJVM uses abbreviated names internally for SQL access. This function simply returns the original Java name for any (potentially) truncated name. An example of this function is to print the fully qualified name of classes that are invalid:

select dbms_java.longname (object_name) from user_objects 
   where object_type = 'JAVA CLASS' and status = 'INVALID';

FUNCTION shortname (longname VARCHAR2) RETURN VARCHAR2

You can specify a full name to the database by using the shortname() routine of the DBMS_JAVA package, which takes a full name as input and returns the corresponding short name. This is useful when verifying that your classes loaded by querying the USER_OBJECTS view.

FUNCTION get_compiler_option(what VARCHAR2, optionName VARCHAR2)
PROCEDURE set_compiler_option(what VARCHAR2, optionName VARCHAR2,
      value VARCHAR2)
PROCEDURE reset_compiler_option(what VARCHAR2, optionName VARCHAR2)
These three entry points control the options of the Oracle Database Java compiler 
that Oracle Database delivers.

FUNCTION resolver (name VARCHAR2, owner VARCHAR2, type VARCHAR2) RETURN VARCHAR2 

This functions returns the resolver specification for a given object name in schema owner where object is of type type. The caller must have EXECUTE privilege and have access to the given object to use this call.

The name parameter is the shortened name for the object. Refer to dbms_java.shortname() for details.

The value of type is one of SOURCE or CLASS.

If there is an error then a null is returned. If the underlying object has changed then a ObjectTypeChangedException may be signaled.

To execute this function:

select dbms_java.resolver('tst', 'SCOTT', 'CLASS') from dual;

which would return:

DBMS_JAVA.RESOLVER('TST','SCOTT','CLASS')
-----------------------------------------
((* SCOTT)(* PUBLIC))

FUNCTION derivedFrom (name VARCHAR2, owner VARCHAR2, type VARCHAR2) RETURN 
VARCHAR2 

This function returns the source name for object name in schema owner where object is of type type. The caller must have EXECUTE privilege and have access to the given object to use this call.

The name parameter (as well as the returned source) is the shortened name for the object. Refer to dbms_java.shortname() for details.

The value of type is of SOURCE or CLASS.

If there is an error then a null is returned. If the underlying object has changed then a ObjectTypeChangedException may be signaled.

The returned value will be null if the object was not compiled in the ojvm.

To execute this function:

select dbms_java.derivedFrom('tst', 'SCOTT', 'CLASS') from dual;

which would return:

DBMS_JAVA.DERIVEDFROM('TST','SCOTT','CLASS')
-----------------------------------------
tst

FUNCTION fixed_in_instance (name VARCHAR2, owner VARCHAR2, type VARCHAR2) RETURN 
NUMBER 

This function returns the permanently kept status for object name in schema owner where the object is of type type. The caller must have EXECUTE privilege and have access to the given object to use this call.

The name parameter is the shortened name for the object. Refer to dbms_java.shortname() for details.

The value of type is of RESOURCE, SOURCE, CLASS, or SHARED_DATA.

The return number is either 0 (not kept) or 1 (kept).

To execute this function:

select dbms_java.fixed_in_instance('tst', 'SCOTT', 'CLASS') from dual;

which would return:

DBMS_JAVA.FIXED_IN_INSTANCE('TST','SCOTT','CLASS')
-----------------------------------------
0

or

select dbms_java.fixed_in_instance('java/lang/String', 'SYS', 'CLASS') from 
dual;

which would return:

DBMS_JAVA.FIXED_IN_INSTANCE('JAVA/LANG/STRING','SYS','CLASS')
-------------------------------------------------------------
1

PROCEDURE set_output (buffersize NUMBER)

This procedure redirects the output of Java stored procedures and triggers to the DBMS_OUTPUT package.

PROCEDURE start_debugging(host varchar2, port number, timeout number)
PROCEDURE stop_debugging
PROCEDURE restart_debugging(timeout number)

These entry points start and stop the debug agent when debugging.

procedure export_source(name varchar2, schema varchar2, blob BLOB) 
procedure export_source(name varchar2, blob BLOB) 
procedure export_source(name varchar2, CLOB clob) 

procedure export_class(name varchar2, schema varchar2, blob BLOB) 
procedure export_class(name varchar2, blob BLOB) 
procedure export_resource(name varchar2, schema varchar2, blob BLOB) 
procedure export_resource(name varchar2, blob BLOB) 
procedure export_resource(name varchar2, schema varchar2, clob CLOB) 
procedure export_resource(name varchar2, clob CLOB) 

These entry points export a Java source, class, or resource schema object into an Oracle large object (LOB).

In all cases, name is the name of the Java schema object to be exported, schema is the name of the schema owning the object (if not supplied, then the current schema is used), and blob|clob is the large object that receives the specified Java schema object.

You cannot export a class into a CLOB, only into a BLOB. In addition, the internal representation of the source uses the UTF8 format, so that format is used to store the source in the BLOB as well.

PROCEDURE loadjava(options varchar2)
PROCEDURE loadjava(options varchar2, resolver varchar2)
PROCEDURE dropjava(options varchar2)

These procedures allow you to load and drop classes within the database using a call, rather than through the loadjava or dropjava command-line tools. To execute within your Java application, do the following:

call dbms_java.loadjava('... options...');
call dbms_java.dropjava('... options...');

The options are identical to those specified for the loadjava and dropjava command-line tools. Each option should be separated by a blank. Do not separate the options with a comma. The only exception to this is the loadjava -resolver option, which contains blanks. For -resolver, specify all other options first, separate these options by a comma, and then specify the -resolver options, as follows:

call dbms_java.loadjava('... options...', 'resolver_options');

Do not specify the following options, because they relate to the database connection for the loadjava command-line tool: -thin, -oci, -user, -password. The output is directed to System.err. The output typically goes to a trace file, but can be redirected.

For more information on the available options.

PROCEDURE grant_permission( grantee varchar2,
         permission_type varchar2,
        permission_name varchar2,
         permission_action varchar2 )
PROCEDURE restrict_permission( grantee varchar2,
         permission_type varchar2,
        permission_name varchar2,
         permission_action varchar2)
PROCEDURE grant_policy_permission( grantee varchar2,
         permission_schema varchar2,
         permission_type varchar2,
        permission_name varchar2)
PROCEDURE revoke_permission(permission_schema varchar2,
         permission_type varchar2,
        permission_name varchar2,
        permission_action varchar2)
PROCEDURE disable_permission(key number)
PROCEDURE enable_permission(key number)
PROCEDURE delete_permission(key number)

These entry points control the JVM permissions.

procedure set_preference(user varchar2,type varchar2, abspath varchar2, key 
varchar2, value varchar2) 

This procedure inserts or updates a row in the SYS:java$prefs$ table as follows:

call dbms_java.set_preference('SCOTT','U','/my/package/method/three',
'windowsize','22:32');

The following table identifies the valid values for each parameter in this procedure.

Parameter	Description
user	The schema name to which to attach the preference. If the login schema is not SYS, then user must be the current login schema, or the insert will fail.
type	Select the type of preference: U = user preference S = System preference
abspath	The absolute path for the preference.
key	The key value to be used for the lookup or the value name
value	The value of the preference key.

Mapping of SQL Types

Although Oracle Application Server does not currently support LOB types, XMLTYPE, REF CURSORS, and OUT/IN OUT arguments (they will be addressed in future releases), you can use an alternative approach to expose PL/SQL methods and SQL types as Web services.

You can change JPublisher's default behavior to generate code that uses a user-provided subclass. For example, if you have a PL/SQL method that returns a REF CURSOR, JPublisher automatically maps the return type to java.sql.ResultSet. However, this ResultSet type cannot be published as a Web service. To solve this, simply create a new method that can return the result set in a Web service-supported format, such as:

public String [] readRefCursorArray(String arg1, Integer arg2)
  {java.sql.ResultSet rs = getRefCursor(arg1,arg2);
    ... create a String[] from rs and return it... }

Then create an interface that contains the exact methods to publish. You can use JPublisher to easily accomplish this mapping by using the following:

jpub -sql=MYAPP:MyAppBase:MyApp#MyAppInterf...

where:

• MyApp contains the method to return the result set.
• MyAppInterf is the interface that contains the method to publish.

After translating the code for your application, archive all the class files into a single JAR file and use the Web Services Assembler to create a deployable Web service EAR file

Using the Native Java Interface

Oracle Database 10g introduces the native Java interface--new features for calls to server-side Java code. It is a simplified application integration: client-side (and middle-tier) Java applications can directly invoke Java in database without the need for defining a PL/SQL wrapper. Uses server-side Java class reflection capability.

In previous releases, calling Java stored procedures and functions from a database client required JDBC calls to associated PL/SQL wrappers. Each wrapper had to be manually published with a SQL signature and a Java implementation. This 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 avoid these disadvantages. To remedy the deficiencies of JDBC calls to associated PL/SQL wrappers, the -java option makes convenient 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, as opposed to creating a client-side Java class to access a server-side SQL object or PL/SQL package. The client-side stub class uses JPublisher 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 JPublisher default connection context instance

At runtime, 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.

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

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

Use the following -java setting:

-java=oracle.sqlj.checker.JdbcVersion

Features of the Database as a Web Service Consumer

Using the database as a Web Service consumer offers the following features:

• Consuming Web Services form Java-in-the-database - Provides an easy-to-use interface for calling-out web services, thereby insulating developers from low-level SOAP programming. Java classes running in the database can simply and directly invoke external web services by using the previously loaded Java proxy or dynamic invocation.
• Consuming Web Services from SQL and PL/SQL - Allows any SQL-enabled tool or application to transparently and easily consume dynamic data from external web services. After Exposing Web Services methods as Java Stored PRocedures, A PL/SQL wrapper on top of a Java stored procedures hides all Java and SOAP programming details from the SQL client.
• Web Services Data Source - Enables Application and Data integration by turning external web service into an SQL data source, making external Web services appear as regular SQL tables. This table function represents the output of calling external web services and can be used in an SQL query.

Web Service Data Sources (Virtual Table Support)

To access data (returned from single or multiple Web service invocations) through a database table, create a virtual table through a Web service data source. This table function allows you to query a set of returned rows as though it were a table.

The client invokes a Web service and the results are stored in a virtual table in the database. You can pass result sets from function to function, allowing you to set up a sequence of transformation without a table holding intermediate results. To reduce memory usage, you can return the result set rows a few at a time within a function.

By using Web services with the table function, you can manipulate a range of input values (from single or multiple Web services) as a real table. In the following example, the inner SELECT creates rows whose columns are used as arguments for invoking the CALL_WS Web service call-out. The table expression could be used in other SQL queries, for constructing views, and so on.

SELECT <some-columns> 
FROM
  TABLE(WS_TABFUN(CURSOR(SELECT s FROM <some_table>))),
WHERE...

Database as Service Consumer for Web Services

You can extend a relational database's storage, indexing, and searching capabilities to include semistructured and nonstructured data (including Web Services) in addition to enabling federated data. By calling Web Services, the database can track, aggregate, refresh, and query dynamic data produced on-demand, such as stock prices, currency exchange rates, and weather information.

An example of using the database as a service consumer would be to call external Web Services from a predefined database job in order to obtain inventory information from multiple suppliers, then update your local inventory database. Another example is that of a Web Crawler: a database job can be scheduled to collate product and price information from a number of sources.

How to Use

The Web services client code is written in SQL, PL/SQL, or Java to run inside the database, which then invokes the external Web service. Figure 12-2 demonstrates how you can call out to a Web service from a Java client within the database by using one of the following methods:

• SQL and PL/SQL call specs - Invoke a Web service through a user-defined function call (generated through JPublisher) either directly within a SQL statement or view or through a variable.
• Pure Java static proxy class - Use JPublisher to pre-generate a client proxy class. which uses JAX-RPC). This method simplifies the Web service invocation as the location of the service is already known without needing to look up the service in the UDDI registry. The client proxy class does all of the work to construct the SOAP request, including marshalling and unmarshalling parameters.
• Pure Java using DII (dynamic invocation interface) over JAX-RPC - Dynamic invocation provides the ability to construct the SOAP request and access the service without the client proxy.

Which method to use depends on if you want to execute from SQL or PL/SQL or from Java classes.

To call out to any Web service through PL/SQL, use the UTL_DBWS PL/SQL package. This package essentially uses the same APIs as the DII classes. See the PL/SQL Packages and Types Reference for a full description of this package.

You can use a Web Services Data Source to process the results from any Web service request as if it was a real database table.

More on Using Database as Service Provider for Web Services