In the previous chapter, we explored the Java runtime in the database. You are now well armed to tackle your initial goal, which is developing and running Java in the database. I’ll walk you through (1) the most-used techniques for putting Java sources and/or classes in the database; (2) how to expose/map Java procedures, functions, their parameters, and return values to SQL; and (3) the various mechanisms for invoking Java in the database; then, you can get busy!
This section explains the various techniques for creating or loading Java in the database, how to remove Java from the database, setting or querying environment variables or system properties, and the Java compiler within the database.
As already mentioned, one of the key benefits of Java is the availability of a huge set of libraries that can be reused as is or with minor changes, resulting in a substantial productivity gain. In general, any J2SE Java program may be reused within OracleJVM; however, you want to run Java in the database because you are doing data-related manipulations that are more efficient in the database than from the middle tier or client tier. Which changes are required for running a standard JDBC application in the database? We’ll answer that question in the following section.
Because you are already connected to a database session when you run Java in the database, the JDBC connection, also known as the default connection, is in reality just a handle to the session. OracleJVM does not yet support data-source lookup in JNDI; instead, you have to use the following syntaxes:
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");or:
Connection conn =
DriverManager.getConnection ("jdbc:oracle:kprb:");or:
OracleDriver t2server = new OracleDriver(); Connection conn = t2server.defaultConnection();
Only public static methods can be exposed to the external world using either a PL/SQL wrapper or a client-side stub. However, you can simply wrap the instance method with a public static wrapper as follows:
public static void <instance_method_wrapper>() { new <myclass>().<instance_method>(); }
Invoking the wrapper will create a new instance and the class and invoke the method in question; that’s it. Now that you know how to turn JDBC applications into Java stored procedures that run faster in the database, how do you get these into the database? The following section answers this question.
There are several ways to create or upload Java into OracleJVM-managed structures. Java sources, classes, and resources can be created interactively during a SQL*Plus session, uploaded from files residing on your development client machine, uploaded from files residing on the server machine(same machine as the database instance), or created from database storage structures such as CLOB/BLO/BFILE.
First of all, the session must have the following database privileges to load classes: CREATE PROCEDURE, CREATE TABLE, CREATE ANY PROCEDURE, CREATE ANY TABLE and JServerPermission. As explained in the security section in Chapter 2, at database creation all schemas have been granted JServerPermission permission through the following command: dbms_java.grant_permission(’PUBLIC’, ‘SYS:oracle.aurora.security.JServerPermission’, ‘LoadClassInPackage.*’, null);.
Let’s look at the most commonly used techniques for creating Java in the database.
This is the quickest way of creating Java in the database. The SQL syntax for creating a Java source, class, or resource in the database is:
CREATE [ OR REPLACE ] [ AND { RESOLVE | COMPILE } ] [ NOFORCE ] JAVA { { SOURCE | RESOURCE } NAMED [ schema. ]primary_name | CLASS [ SCHEMA schema ] } [ invoker_rights_clause ] [ RESOLVER ((match_string [,] { schema_name | - }) [ (match_string [,] { schema_name | - }) ]... ) ] AS source_text } ;
Using SQL*Plus, enter the text of your Java source directly, as shown in Listing 3.1:
create or replace java source named Workers as
/*
* Adapted from existing JDBC demo
* this code sample retrieves a worker
* from a database, then updates its position and salary.
*/
import java.sql.*;
import oracle.jdbc.driver.*;
public class Workers
{
public static void main (String args []) throws SQLException
{
String name = null;
String pos = null;
int sal;
int id;
long t0,t1;
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
if ( args.length < 1 ) {
System.err.println("Usage: Java Workers <wid> <new position>
<new salary>");
System.exit(1);
}
// Get parameters value
id = Integer.parseInt(args[0]);
pos = args[1];
sal = Integer.parseInt(args[2]);
/*
* Where is your code running: in the database or outside?
*/
if (System.getProperty("oracle.jserver.version") != null)
{
/*
* You are in the database, already connected, use the default
* connection
*/
conn = DriverManager.getConnection("jdbc:default:connection:");
System.out.println ("Running in OracleJVM,in the database!");
}
else
{
/*
* You are not in the database, you need to connect to
* the database
*/
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:",
"scott", "tiger");
System.out.println ("Running in JDK VM, outside the
database!");
// Disable autocommit - Not suppoted in OracleJVM
conn.setAutoCommit(false);
}
// Start timing
t0=System.currentTimeMillis();
/*
* find the name of the workers given his id number
*/
// create statement
stmt = conn.createStatement();
// find the name of the worker
ResultSet rset = stmt.executeQuery(
"SELECT WNAME FROM workers WHERE wid = " + id);
// retrieve and print the result (we are only expecting 1 row
while (rset.next())
{
name = rset.getString(1);
}
// return the name of the worker who has the given worker number
System.out.println ("Worker Name: "+ name);
/*
* update the position and salary of the retrieved worker
*/
// prepare the update statement
pstmt = conn.prepareStatement("UPDATE WORKERS SET WPOSITION
= ?, " +
" WSALARY = ? WHERE WNAME = ?");
// set up bind values and execute the update
pstmt.setString(1, pos);
pstmt.setInt(2, sal);
pstmt.setString(3, name);
pstmt.execute();
// double-check (retrieve) the updated position and salary
rset = stmt.executeQuery(
"SELECT WPOSITION, WSALARY FROM WORKERS WHERE WNAME = '" +
name + "'");
while (rset.next())
{
pos = rset.getString ("wposition");
sal = rset.getInt ("wsalary");
}
System.out.println ("Worker: Id = " + id + ", Name = " + name +
", Position = " + pos + ", Salary = " + sal);
// Close the ResultSet
rset.close();
// Close the Statement
stmt.close();
// Stop timing
t1=System.currentTimeMillis();
System.out.println ("====> Duration: "+(int)(t1-t0)+ "
Milliseconds");
// Close the connection
conn.close();
}
}
/
Java createdProvided you have been granted ALTER ANY PROCEDURE privilege and EXECUTE object privilege on the Java sources/classes in question, you can use either of the following commands (these are synonymous) to compile the loaded Java source in the database (the embedded compiler is covered later):
SQL> alter java source Workers resolve; SQL> alter java source Workers compile;
When everything goes well, you get:
Java created
Otherwise, you get:
Warning: Java altered with compilation errors.
In this case, you can check the errors using the following command:
SQL> select text from user_errors;
The following syntax combines the creation of the Java source follow-up with the compilation within a single command:
SQL>create or replace and resolve java source named Workers as // // -> Insert the above code, here / SQL>show errors;
However, command-line tools such as SQL*Plus offer limited editing capabilities; therefore, this method is only convenient for small or short programs.
Oracle furnishes client-side command-line utilities, loadjava and dropjava, for loading or removing Java sources (.java), Java classes (.class), Java resources (.properties), and JAR and ZIP files into/from the database. This is the most-used technique, because it allows you to upload already debugged/validated Java sources and Java classes from your development environment into OracleJVM structures; furthermore, this method allows you not only to upload an individual Java source, class, or resource but also JAR and ZIP files—which are convenient and fast ways of loading an entire application or framework into the database.
Syntax for invoking the “loadjava” (command-line) utility:
loadjava {-user | -u} <user>/<password>[@<database>] [options]
<file>.java | <file>.class | <file>.jar | <file>.zip |
<file>.sqlj | <resourcefile> ...
[-action][-andresolve][-casesensitivepub][-cleargrants] [-noaction]
[-debug]
[-d | -definer]
[-dirprefix <prefix>]
[-e | -encoding <encoding_scheme>]
[-fileout <file>]
[-f | -force]
[-genmissing] [-genmissingjar <jar_file>]
[-g | -grant <user> [, <user>]...] [-nogrant]
[-help]
[-jarasresource]
[-nocasesensitivepub]
[-nocleargrants]
[-nodefiner]
[-norecursivejars] [-recursivejars]
[-noschema] [-S | -schema <schema>]
[-noserverside]
[-nosynonym] [-s | -synonym]
[-nousage]
[-noverify]
[-o | -oci | oci8]
[-optionfile <file>]
[-optiontable <table_name>]
[-publish <package>]
[-r | -resolve] [-R | -resolver "resolver_spec"] [-resolveonly]
[-stdout]
[-stoponerror]
[-tableschema <schema>]
[-t | -thin]
[-time]
[-unresolvedok]
[-v | -verbose]Loadjava offers way too many options, and not all of these are needed or used; Table 3.1 describes the most important options. See the Oracle Database Java Developer Guide for a comprehensive list of all options.
Table 3.1. Loadjava Options
Argument | Description |
|---|---|
| By default, class schema objects run with the privileges of their invoker. This option confers the privileges of the definers instead. Chapter 2 explains definer’s rights and invoker’s rights. |
| Forces files to be loaded, even if they match digest table entries. |
| genmissing is an option of loadjava that lets you deal with nonexistent classes. It instructs loadjava to create and load placeholder definitions of classes that are referenced but not defined. By resolving the references to the missing classes, the genmissing option allows the Java code to run, but because the missing classes have placeholder definitions, their methods cannot be invoked. |
| Prints the usage message on how to use the |
| Used along with–resolver, this option causes the classes to be loaded without bytecode verification. The following privilege is required: |
| Look up the loadjava options in <file>, instead. All of the command-line options (except -thin, -oci, -user, -password) can be specified in the file in question. |
| By default (see [-jarasresource), |
| Compiles (if necessary) and resolves external references in classes after all classes on the command line have been loaded. If you do not specify - |
| Creates a PUBLIC synonym for loaded classes, making them accessible to all schemas. You must have the CREATE PUBLIC SYNONYM privilege to exeute this option. |
When already running in the database, DBMS_JAVA.loadjava() allows loading Java sources, classes, or resources residing on the same machine as the database, using the same options as the client tool. When operating from the server side, because you are already in the database, there is no need to provide connection credentials (username/password) or specify the driver type to be used. Running the following SQL script in the database will load an existing Java source file, /tmp/foo.java, into the scott/tiger schema.
connect scott/tiger
set echo on
set serveroutput on
call dbms_java.set_output(1000000);
Rem
Rem Call server-side loadjava. For the loation of file to upload,
Rem Use either absolute or relative pathname to $ORACLE_HOME
Rem
call dbms_java.loadjava('-v –r –f /tmp/foo.java'),The Oracle JDeveloper and third-party IDE allow you to develop and debug Java applications on the JDK, and then upload the resulting classes (and sources) to the database, using method 2, under the covers!
As explained in Chapter 2, OracleJVM operates only on managed objects, which are stand-alone objects with no external references. As a result, even if you have existing Java sources, Java classes, and Java resources already stored in regular database structures such as CLOB or BLOB columns, or an external file system (on the same system as the database), you still need to create the corresponding managed objects using the following syntax:
. CREATE [ OR REPLACE ] [ AND { RESOLVE | COMPILE } ] [ NOFORCE ] JAVA { { SOURCE | RESOURCE } NAMED [ schema. ]primary_name | CLASS [ SCHEMA schema ] } [ invoker_rights_clause ] [ RESOLVER ((match_string [,] { schema_name | - }) [ (match_string [,] { schema_name | - }) ]... ) ] {USING { BFILE (directory_object_name, server_file_name) | { CLOB | BLOB | BFILE } subquery | 'key_for_BLOB' };
Example:
Assume you have an existing Foo.java file in the /java/test directory. A BFILE (covered in detail in Part II) is an Oracle database mechanism rather than a data type, which allows manipulating the content of external files. The BFILE definition associates an alias (stored in table column) with the actual external file. You can create a managed Java source in the database from the external Java file, using the following steps:
The schema must have been granted “
CREATE ANY DIRECTORY” privilege, by another schema (i.e.,SYSTEM).SQL> grant create any directory to scott; Grant succeeded.Create the alias name (“bfile_dir”) for the existing external directory.
SQL> connect scott/tiger Connected. SQL> create or replace directory bfile_dir as '/java/ test'; Directory created.
Create a new Java source using the content of the existing external Java source file.
SQL> create or replace java source named "Bar.java" using bfile (bfile_dir, 'Foo.java') 2 / Java created. SQL>
Similarly, you may create a new Java class or Java resource from existing external files.
SQL>CREATE JAVA CLASS USING BFILE (bfile_dir, 'Bar.class'), SQL>CREATE JAVA RESOURCE NAMED "config.properties" USING BFILE (bfile_dir, 'properties.dat'),
XDB is Oracle’s native support for XML in the database.[1] XDB supports direct HTTP, WebDAV, and FTP access (the database listener has been enhanced to listen for FTP requests on port 2100 and HTTP requests on port 8080). The following example assumes you have an existing Java source or Java class in an XDB folder; otherwise, you can use either one of the following (1), (2), or (3) techniques to populate the XDB folder with a valid Java source text. See the XDB documentation[2] for more details:
Drag and drop Java source from your desktop or client machine to the WebDAV-mapped XDB folder.
Use FTP to push Java source from your desktop or client machine to the WebDAV-mapped XDB folder.
Use the PL/SQL-supplied package dbms_xdb.createResource() as described hereafter. Use SQL*Plus to create a Java source in the XDB folder using dbms_xdb.createResource() (replace the Java skeleton with a valid Java source).
SQL> declare source CLOB := 'public class Class1 { public Class1() { } public static void main(String[] args) {System.out.println("Hello"); } }'; res boolean; begin if dbms_xdb.existsResource('/public/test.java') then dbms_xdb.deleteResource('/public/test.java'), end if; res := dbms_xdb.createResource('/public/test.java',source); end; / SQL> commit;
Assuming you have enabled the listener to listen to HTTP requests, point your browser to http://localhost:8080; you should see the newly created Java source within the XDB /public folder.
From an existing Java source stored in XDB[3] folders, here is how you can create managed Java source in OracleJVM from it:
Browse the XDB repository (Note: this mechanism is only available with 10g R2); point your browser to http://localhost:8080 to browse the
/publicfolder to list existing Java sources.Create a Java source in OracleJVM from an XDB folder.
SQL> create or replace and resolve java source named "Class1" 2 using clob 3 ( 4 select xdburiType('/public/ test.java').getClob() from dual 5 ); 6 / SQL>Select object_name from user_objects;
You should see the newly created Java source. At this stage you may compile it, create a PL/SQL wrapper, and execute it.
You can use the following PL/SQL interface to dump the bytecode of the Java class(es) managed by the OracleJVM into a class file(s):
procedure dumpclass(arg1 varchar2, arg2 varchar2, arg3
varchar2);
arg1: the longname of the class to be dumped
arg2: the defining schema
arg3: where to dump
Usage:
SQL>call dumpclass('foo', 'SCOTT', '/tmp/foo.class'),Alternatively, Java code running in the database may directly invoke the underlying Java method, which implements the PL/SQL interface.
oracle.aurora.server.tools.dumpclass.ServerDump.dumpClassToFile(
java.lang.String, java.lang.String, java.lang.String);See section 2.6.6 for another method for dumping classes.
Once you’ve created your Java classes in the database, run the following self-explanatory SQL queries to check their status:
SQL>select count(*) from user_objects where object_type='JAVA CLASS';
SQL>select object_name from user_objects
where object_type = 'JAVA CLASS'
SQL>select count(*) from user_objects
where object_type = 'JAVA CLASS' and status = 'VALID';
SQL>select object_name from user_objects
where object_type = 'JAVA CLASS' and status != 'VALID';Dropjava is a client-side utility for dropping Java sources, classes, and resources from the database. From within the database, use dbms_java.loadjava(). Loadjava is invoked directly from the OS prompt as follows:
dropjava [options] {<file>.java | <file>.class | file.sqlj |
<file>.jar | <file.zip> | <resourcefile>} ...Dropjava syntax
dropjava [options] {<file>.java | <file>.class | file.sqlj |
<file>.jar | <file.zip> | <resourcefile>} ...
-u | -user <user>/<password>[@<database>]
[-jarasresource]
[-noserverside]
[-o | -oci | -oci8]
[-optionfile <file>]
[-optiontable <table_name>]
[-S | -schema <schema>]
[ -stdout ]
[-s | -synonym]
[-t | -thin]
[-time]
[-v | -verbose]From SQL and PL/SQL, and Java in the database, use:
SQL>call dbms_java.dropjava (foo.java);
From Java in the database, use:
oracle.aurora.server.tools.loadjava.DropJavaMain.serverMain( java.lang.String);
See the Oracle Database Java Developer’s Guide for more details, especially the required precautions when using dropjava.
Alternatively, from SQL, you may use the DDL command:
DROP JAVA SOURCE|CLASS|RESOURCE <java object name>; SQL> drop java class "Method_nc";
You must have EXECUTE permission on the object in question and DROP ANY PROCEDURE if the Java object does not belong to your schema. However, DROP JAVA may throw ORA- 29537 if the Java class or Java resource has dependencies on a Java source(s) within the database.
OracleJVM currently allows setting few compiler options.
OracleJVM allows you to specify: Encoding, Online, and Debug options on Java sources, as follows:
Encoding: Similar to javac–encoding, this option specifies the file encoding of the Java source, such as 8859_x, SJIS, and so on. The Java source is converted from the file encoding to Unicode.
Online: A Boolean value that turns on (true) or off (false), the SQL semantic check (i.e., correctness) of SQLJ programs in the database. The default value is true.
Debug: A Boolean value, similar to javac–g, which turns the debug mode on (true) or off (false); the default value is false.When the debug mode is on, Java classes are compiled with debugging information.
From SQL, PL/SQL, and JDBC, use the DBMS_JAVA PL/SQL interfaces with the following signature:
FUNCTION get_compiler_option(what VARCHAR2, optionName VARCHAR2) RETURN varchar2; PROCEDURE set_compiler_option(what VARCHAR2, optionName VARCHAR2, value VARCHAR2); PROCEDURE reset_compiler_option(what VARCHAR2, optionName VARCHAR2);
Examples:
First, let’s initialize Java in the session using the following basic SQL query (assuming Java has never been invoked since the creation of the session):
SQL> select dbms_java.longname('foo') from dual;
DBMS_JAVA.LONGNAME('FOO')
---------------------------
fooThen, let’s get the default values for Encoding, Online, and Debug, using dbms_java.get_compiler_option(name VARCHAR2, option VARCHAR2).
An empty NAME string causes the same options to be applied to all Java sources in the schema.
SQL> select dbms_java.get_compiler_option('', 'encoding') from dual;
DBMS_JAVA.GET_COMPILER_OPTION('','ENCODING')
---------------------------------------------
8859_1The default value is the value of the system property file.encoding, which can be queried using System.getProperty("file encoding").
SQL> select dbms_java.get_compiler_option('', 'online') from dual;
DBMS_JAVA.GET_COMPILER_OPTION('','ONLINE')
-------------------------------------------
trueThe default values can be reset for all schema objects (using a null name string) or for a specific Java source object (inputting the object name).
SQL> SQL> execute dbms_java.reset_compiler_option('', 'online'),
PL/SQL procedure successfully completed.
SQL> select dbms_java.get_compiler_option('', 'online') from dual;
DBMS_JAVA.GET_COMPILER_OPTION('','ONLINE')
-----------------------------------------------
true
SQL> select dbms_java.get_compiler_option('', 'debug') from dual;
DBMS_JAVA.GET_COMPILER_OPTION('','DEBUG')
-------------------------------------------
falseJava in the database may directly use the Java methods, which implement the PL/SQL interfaces.
oracle.aurora.jdkcompiler.CompilerOptions.get(java.lang.String, java.lang.String) return java.lang.String ; oracle.aurora.jdkcompiler.CompilerOptions.set(java.lang.String, java.lang.String, java.lang.String); oracle.aurora.jdkcompiler.CompilerOptions.reset(java.lang.String, java.lang.String);
Specifying Compiler Options Using Loadjava: The encoding option can also be specified/changed through loadjava.
loadjava [-e | -encoding <encoding_scheme>]
Specifying Compiler Options Using the JAVA$OPTION table: A compiler option table (JAVA$OPTION) is also created in each schema, upon the first invocation of:
dbms_java.set_compiler_option('<name>','<option>','<value>'),
SQL> execute dbms_java.set_compiler_option('', 'online', 'false'),
PL/SQL procedure successfully completed.
SQL> select dbms_java.get_compiler_option('', 'online') from dual;
DBMS_JAVA.GET_COMPILER_OPTION('','ONLINE')
------------------------------------------
falseSQL> desc java$options;
Name Null? Type
----------------------------------------- -------- ----------------------------
WHAT VARCHAR2(128)
OPT VARCHAR2(20)
VALUE VARCHAR2(128)
SQL>
SQL> col what format a40
SQL> col opt format a15
SQL> col value format a15
SQL> select * from java$options
WHAT OPT VALUE
---------------------------------------- ------------- -----------------
online falseEach row of the JAVA$OPTION table contains the name of a Java source to which a setting applies. Different options can be set for different Java sources in the same schema, using multiple entries/rows in the JAVA$OPTION table. However, the command-line setting (loadjava) has precedence over the JAVA$OPTION table setting.
The bytecode verifier is part of the Java language security mechanisms. It ensures that bytecodes have a valid storage format, valid instruction set, and meet other security checks. The following PL/SQL interface and Java method allow you to turn the bytecode verifier on (‘0’) or off (‘1’).
The schema must have been granted JServerPermission("Verifier") permission (see Chapter 2).
The PL/SQL interface:
procedure set_verifier(flag number);The Java method:
oracle.aurora.rdbms.Compiler.sessionOptionController(int);
The OracleJVM also embeds a Java compiler, which is almost identical to Sun’s standard JDK compiler. It allows explicit compilation at development time and implicit/automatic compilation at runtime.
In the previous section, we covered the setting of the compiler options. There are several ways to invoke the compiler in the OracleJVM:
Using the “resolve” (command-line) option of the “loadjava” utility:
loadjava –resolve ... <name>
Using the
DBMS_JAVA(PL/SQL) package to upload, compile, and resolve external references:dbms_java.loadjava('<blank separated options>','<resolver options>'),Using the
CREATE JAVA SOURCE(DDL) statement to explicitly request the compilation of a Java source that is entered interactively (by the user):SQL> create or replace and compile java source named <name> AS <Java source> SQL> create or replace and resolve java source named <name> AS <Java source>
Using the
ALTER JAVA SOURCE(DDL) statement to force the compilation:SQL> alter java source <name> compile;
Let’s compare Java class dependency resolution with the PL/SQL dependency resolution (between packages).
A PL/SQL package has two parts: a specification part and a body part. Let’s assume we have two PL/SQL packages, A and B, A depending on B.
At the
SPEClevel, if the spec of B is changed, then A is invalidated, because A depended on the old version of B’s spec. Therefore, if you try to run A, its spec must either be explicitly rewritten before it could run again or implicitly recompiled first, then run; if the recompilation fails, it won’t run.At the package
BODYlevel, if B is changed, A will not be invalidated; there is no dependency at the package body level.
Unlike PL/SQL, Java programs do not have a specification part and a body part. The dependency mechanism is set in such a way that when a class changes in a way that might make another class “un”-runnable without re-resolution, then the other class is marked invalid.
Assume we have two Java classes: A.class and B.class. Class A is said to be dependent on B if B is mentioned in any way in A.class file, or if B is a superclass of A.
If class A depends on class B and B is changed, then A will be invalidated. Similarly to PL/SQL, an attempt to run class A will result in implicit revalidation (i.e., compilation and resolution) of A and recursively of B if it hasn’t previously been re-resolved. If the revalidation is successful, it will run.
In summary:
Both PL/SQL and Java have the same invalidation model. When an object is changed, its dependents are marked as invalid, thus leaving them in a state where a subsequent attempt to run them will cause revalidation.
Both PL/SQL and Java have the same automatic recompilation model. If an object is invalid when invoked, an automatic (implicit) attempt to revalidate is done. If successful, the object is then run; otherwise, an error message is thrown.
But PL/SQL and Java differ in the definition of “dependency” (i.e., the relationship among objects).
Once you’ve developed, loaded, compiled, and NCOMPed your Java applications in the database, the next step consists of “publishing” them to the SQL world. In other words, make the Java procedures and functions, their parameters/arguments, and return types known to the database dictionary as stored procedures and functions, callable from SQL, PL/SQL, and Java/ J2EE in the middle tier (through JDBC) and any language that supports stored procedures/functions. Publishing is accomplished by means of user-defined or JPublisher-generated[4] PL/SQL wrappers, known in Oracle literature as Call Spec (short for Call Specification). Once published through the Call Spec, the mapping between the Java methods, the Java types, and SQL or PL/SQL types happens automatically, at runtime. The most challenging part in writing Call Spec is mapping SQL types or PL/SQL types to Java types and vice versa. This section describes the various Call Spec types; in the next section, we’ll dive deeper into type mapping.
This section briefly describes the various types of call specifications, including top-level Call Spec, PL/SQL-packaged Call Spec, and object type Call Spec. Then, section 3.3 provides an extensive list of call specification examples.
Top-level Call Specs publish Java static public methods as entry points to OracleJVM. These can be viewed as interfaces implemented by the Java methods. They are created either interactively, using command-line tools such as SQL*Plus, or offline (in SQL scripts files).
Syntax:
CREATE [OR REPLACE] { PROCEDURE procedure_name [(param[, param]...)] | FUNCTION function_name [(param[, param]...)] RETURN sql_type} [AUTHID {DEFINER | CURRENT_USER}] [PARALLEL_ENABLE] [DETERMINISTIC] {IS | AS} LANGUAGE JAVA NAME 'method_fullname (java_type_fullname[, java_type_fullname]...) [return java_type_fullname]';
procedure_name uniquely identifies the Java method; it may have the same name as the Java method name; however, procedure_name must be different when publishing the Java method name. The full Java method names use dot notation; long names can be broken across lines at dot boundaries:
this.is.a.long.java.class. full.name()param represents the following syntax: parameter_name [
IN|OUT|IN OUT]sql_type. There is a one-to-one correspondence between the Java method signature and the Call Spec parameters, which are mapped by position.the
AUTHIDclause determines whether a stored procedure executes with the privileges of its definer or invoker (the default).Note: The unqualified references to schema objects are resolved in the schema of the definer or invoker.
The
PARALLEL_ENABLEoption declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. See the Oracle Database SQL reference documentation for more details.The hint
DETERMINISTIChelps the optimizer avoid redundant function calls. See the Oracle Database SQL reference documentation for more details.Example of a Top-level Call Spec (from a SQL*Plus session):
create or replace procedure TrimLobSp as language java name 'TrimLob.main(java.lang.String[])'; / show errors; set serveroutput on call dbms_java.set_output(50000); call TrimLobSp();
PL/SQL Packaged Call Specs allow the grouping of multiple Call Spec methods belonging to the same Java class or belonging to the same functional package—or just for convenience—into a PL/SQL package. Unlike Top-level Call Specs, in a PL/SQL Packaged Call Spec, the procedure_name cannot have the same name as the java method name. The package specification contains only the signature of the methods to be published.
Syntax:
CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [type_definition [type_definition] ...] [cursor_spec [cursor_spec] ...] [item_declaration [item_declaration] ...] [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...] END [package_name];
The package body contains the full Call Spec of the Java methods to be published.
[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} [type_definition [type_definition] ...] [cursor_body [cursor_body] ...] [item_declaration [item_declaration] ...] [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...] [BEGIN sequence_of_statements] END [package_name];]
Example: The following user-defined PL/SQL Packaged PL/SQL Call Spec is from the “JDBC Callout to Non-Oracle Databases,” covered in Chapter 4.
create or replace package JDBC_PKG is -- Public function and procedure declarations PROCEDURE setConnection (Driver IN VARCHAR2, Url IN VARCHAR2, User IN VARCHAR2, dbPassword IN VARCHAR2, dbSchema IN VARCHAR2); PROCEDURE open; PROCEDURE execSQL (sqlString IN VARCHAR2, isQuery IN Boolean); FUNCTION afetch RETURN boolean; FUNCTION getColumnValue ( col IN number ) RETURN VARCHAR2; FUNCTION getColumnCount RETURN NUMBER; PROCEDURE commit; PROCEDURE rollback; PROCEDURE close; end JDBC_PKG;
The Package Body:
create or replace package body JDBC_PKG is -- Function and procedure implementations PROCEDURE setConnection (Driver IN VARCHAR2, Url IN VARCHAR2, User IN VARCHAR2, dbPassword IN VARCHAR2, dbSchema IN VARCHAR2 ) AS LANGUAGE JAVA NAME 'jdbcConnection.setConnection(java.lang.String,java.lang.String,java. lang.String,java.lang.String,java.lang.String)'; PROCEDURE open as LANGUAGE JAVA NAME 'jdbcConnection.open()'; PROCEDURE execSQL (sqlString IN VARCHAR2, isQuery IN Boolean ) AS LANGUAGE JAVA NAME 'jdbcConnection.execSQL(java.lang.String,boolean)'; FUNCTION afetch RETURN boolean AS LANGUAGE JAVA NAME 'jdbcConnection.fetch() return java.lang.String'; FUNCTION getColumnValue ( col IN number ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'jdbcConnection.getColumnValue(int) return java.lang.String'; FUNCTION getColumnCount RETURN NUMBER AS LANGUAGE JAVA NAME 'jdbcConnection.getColumnCount() return int'; PROCEDURE commit AS LANGUAGE JAVA NAME 'jdbcConnection.commit()'; PROCEDURE rollback AS 0LANGUAGE JAVA NAME 'jdbcConnection.rollback()'; PROCEDURE close AS LANGUAGE JAVA NAME 'jdbcConnection.close()'; end JDBC_PKG;
The DBMS_JAVA package used abundantly throughout this book is an example of an Oracle-supplied PL/SQL Packaged Call Spec.
Top-level Call Spec and PL/SQL Packaged Call Spec can only publish public static Java methods. The data structures of user-defined object types are known as attributes. The member functions (or procedures) that define the behavior are known as methods. These can be written in Java (see section 3.3). Object Type Call Spec can publish member methods of object types that are either public static methods using the STATIC keyword or nonstatic methods (instance methods) using the MEMBER keyword. Nonstatic methods can accept or reference SELF, a built-in parameter that refers to the active instance of the object type. For SQLJ Object types (see section 3.3), the Java class must implement the standard java.sql.SQLData interface,[5] more specifically, the getSQLTypeName(), readSQL(), and writeSQL() methods.
Object Type Specification:
CREATE [OR REPLACE] TYPE type_name
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT (
attribute_name datatype[, attribute_name datatype]...
[{MAP | ORDER} MEMBER {function_spec | call_spec},]
[{MEMBER | STATIC} {subprogram_spec | call_spec}
[, {MEMBER | STATIC} {subprogram_spec | call_spec}]...]
);[CREATE [OR REPLACE] TYPE BODY type_name {IS | AS}
{ {MAP | ORDER} MEMBER function_body;
| {MEMBER | STATIC} {subprogram_body | call_spec};}
[{MEMBER | STATIC} {subprogram_body | call_spec};]...
END;]
Basicobjtyp.sql
===============
/*
* Basic Object type (scalar data types)
*
*/
create or replace and resolve java source named BasicObjTyp as
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class BasicObjTyp
{
public static String fString (String s) { return s; }
public static int fint (int n) { return n; }
public static DATE fDATE (DATE d) { return d; }
public static RAW fRAW (RAW r) { return r; }
}
/
set serveroutput on
create or replace type myobjtyp as object (
num_attr number,
chr_attr varchar2(20),
dat_attr date,
raw_attr raw(20),
static function intfunc (x number) return number,
static function strfunc (x varchar2) return varchar2,
static function datfunc (x date) return date,
static function rawfunc (x raw) return raw
);
/
create or replace type body myobjtyp as
static function intfunc (x number) return number
as language java name 'BasicObjTyp.fint(int)
return int';
static function strfunc (x varchar2) return varchar2
as language java
name 'BasicObjTyp.fString(java.lang.String)
return java.lang.String';
static function datfunc (x date) return date
as language java
name 'BasicObjTyp.fDATE(oracle.sql.DATE)
return oracle.sql.DATE';
static function rawfunc (x raw) return raw
as language java
name 'BasicObjTyp.fRAW(oracle.sql.RAW)
return oracle.sql.RAW';
end;
/
rem
rem Test Object type Call Spec
rem
declare
n number;
c varchar2(20);
d date;
r raw(20);
obj myobjtyp;
begin
n := 469;
c := 'Basic Object Type';
d := '12-JUN-2005';
r := '0102030405';
obj := myobjtyp(n, c, d, r);
dbms_output.put_line('*** Print Object Attributs ***'),
dbms_output.put_line(obj.num_attr);
dbms_output.put_line(obj.chr_attr);
dbms_output.put_line(obj.dat_attr);
dbms_output.put_line(obj.raw_attr);
dbms_output.put_line('** Invoke Object Methods *** '),
dbms_output.put_line(myobjtyp.intfunc(n));
dbms_output.put_line(myobjtyp.strfunc(c));
dbms_output.put_line(myobjtyp.rawfunc(r));
dbms_output.put_line(myobjtyp.datfunc(d));
dbms_output.put_line('**** '),
end;
/
*** Print Object Attributs ***
469
Basic Object Type
12-JUN-05
0102030405
*** Invoke Object Methods ***
469
Basic Object Type
0102030405
12-JUN-05
****
PL/SQL procedure successfully completed.
SQL>Defining a Call Spec that matches the Java signature can be challenging and is the root cause of the most frequent error message when invoking Java in the database (see section 3.4.5). It is important to understand type mapping, but you can avoid the pain, the hassle, and the mistakes by just using JPublisher to do it for you, as we will see in Part II, III, and IV (covering JDBC, SQLJ, and JPublisher). This section provides an extensive but still not exhaustive list of mapping the different SQL and PL/SQL types to Java (and vice versa). Let’s start by looking at the mapping matrix.
Tables 3.2 and 3.3 provide the mapping of SQL and PL/SQL types to Java/ JDBC types supported by the Oracle Database, including Java in the database, JDBC, SQLJ, and JPublisher. These tables will serve as a reference in the following examples.
Table 3.2. Mapping SQL and PL/SQL Types to Java and JDBC Types
SQL Types, and PL/ SQL Types | Oracle JDBC Mapping[*] | Standard Java Mapping |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
[*] The | ||
Table 3.3. Mapping Java Types to Oracle Types
Java Types | Oracle Types (SQL and PL/SQL) | |
|---|---|---|
Primitive (Built-in) Types | ||
|
| |
|
| |
|
| |
|
| |
|
| |
Reference Types (Wrapper Classes) | ||
|
| |
JDBC 2.0 Mapping | ||
|
| |
|
| |
|
| |
|
| |
|
| |
Oracle Extensions | ||
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
oracle.sql.NCHAR,oracle.sql.NCLOB, andoracle.sql.NStringare not part of JDBC but are distributed with the JPublisher runtime (see Part IV) to represent the NCHAR form oforacle.sql.CHAR,oracle.sql.CLOBandjava.lang.String.See JPublisher “Type Map” in Part IV of this book.
SQL URL types, also known as “data links,” are mapped to
java.net.URL.Mapping of PL/SQL
BOOLEANtoSQL NUMBERand Java boolean is defined in the default JPublisher type map.Java classes implementing the
oracle.sql.ORADatainterface.See JPublisher “Type Mapping Support for PL/SQL RECORD and Indexed by Table Types” in Part IV.
See JPublisher “Type Mapping Support for Scalar Indexed by Tables” in Part IV.
In this section, you will find code segments that will help you map both simple and complex SQL and PL/SQL types to the corresponding Java types, including Strings, Numbers, Int, Date, Float, BLOB LOCATOR, CLOB LOCATOR, Opaque types, VARRAY (Scalar, ADT), NESTED TABLE (Scalar, ADT), and so on. The purpose of these code segments is to show the mapping between SQL and Java—which can be challenging—not to show the functionality of the data types in question.
Listing 3.2 will create a table, TypesTab, with the various column types that we will use later to map Java states to SQL columns.
Example 3.2. TypesTab.sql
connect scott/tiger
set echo on
drop table TypesTab;
create table TypesTab(
num number,
bfloat binary_float,
bdouble binary_double,
vchar2 varchar2(24),
xchar char(24),
xraw raw(24),
xdat date);
insert into TypesTab values (
111,
10000000.000001,
1000000000000.000000002,
'this is a varchar2',
'this is a char',
hextoraw (lpad ('b', 24, 'b')),
'19-Apr-2005'),
commit;
SQL> select * from TypesTab;
NUM BFLOAT BDOUBLE VCHAR2
---------- ---------- ---------- ------------------------
XCHAR XRAW
------------------------ --------------------------------------------
----
XDAT
---------
111 1.0E+007 1.0E+012 this is a varchar2
this is a char BBBBBBBBBBBBBBBBBBBBBBBB
19-APR-05
SQL>This code sample maps a SQL DATE column to java.sql.Date and vice versa. The same technique can be utilized to map java.sql.Time, java.sql.Timestamp, and java.lang.String to DATE (see Table 3.2). Mapping oracle.sql.DATE to SQL DATE is shown in Listing 3.3. This example uses JDBC PreparedStatement and executeQuery, which will be described in the Part II.
Example 3.3. javasqlDate.sql
create or replace java source named javasqlDate as
/*
* Mapping java.sql.Date to/from SQL DATE
*/
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class javasqlDate
{
/*
* Update SQL DATE column with java.sql.Date
*/
public static void xputDat (Date x) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
PreparedStatement ps =
conn.prepareStatement("UPDATE TypesTab SET xdat = ?");
ps.setDate (1, x);
ps.execute();
ps.close();
}
/*
* Rerieve SQL DATE column as java.sql.Date
*/
public static String xgetDat() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
Statement stmt = conn.createStatement();
Date dat = null;
ResultSet rs = stmt.executeQuery("SELECT xdat FROM TypesTab");
while (rs.next())
{
dat = rs.getDate(1);
}
stmt.close();
return dat.toString();
}
}
/
show errors;
alter java source javasqlDate compile;
show errors;
create or replace procedure putjavaDate (x DATE)
as language java
name 'javasqlDate.xputDat (java.sql.Date)';
/
show errors;
create or replace function getjavaDate return VARCHAR2
as language java
name 'javasqlDate.xgetDat() return java.lang.String';
/
show errors;
call putjavaDate(sysdate);
set serveroutput on
select getjavaDate from dual;Listing 3.4 is similar to Listing 3.3, except that it maps the SQL DATE column to oracle.sql.DATE instead of java.sql.Date.
Example 3.4. orasqlDATE.sql
create or replace java source named orasqlDATE as
/*
* Mapping oracle.sql.DATE to/from SQL DATE
*/
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class orasqlDATE
{
/*
* Update SQL DATE with oracle.sql.DATE
*/
public static void xputDat (DATE x) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OraclePreparedStatement ops = (OraclePreparedStatement)
conn.prepareStatement("UPDATE TypesTab SET xdat = ?");
ops.setDATE (1, x);
ops.execute();
ops.close();
}
/*
* Retrieve SQL DATE column as oracle.sq.DATE
*/
public static DATE xgetDat() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OracleStatement ostmt = (OracleStatement) conn.createStatement();
DATE dat = new DATE();
OracleResultSet ors =
(OracleResultSet) ostmt.executeQuery("SELECT xdat FROM
TypesTab");
while (ors.next())
{
dat = ors.getDATE(1);
}
ostmt.close();
return dat;
}
}
/
show errors;
alter java source orasqlDATE compile;
show errors;
create or replace procedure putoraDATE (x DATE)
as language java
name 'orasqlDATE.xputDat (oracle.sql.DATE)';
/
show errors;
create or replace function getoraDATE return DATE
as language java
name 'orasqlDATE.xgetDat() return oracle.sql.DATE';
/
show errors;
call putoraDATE(sysdate);
set serveroutput on
select getoraDATE() from dual;Listing 3.5 maps an int and/or java.lang.Integer to SQL NUMBER and vice versa. The same technique can be utilized to map java.lang.Byte, java.lang.Short, java.lang.Long, java.sql.Float, java.lang.Double, java.math.BigDecimal, byte, short, long, float, and double (see Table 3.2). Mapping oracle.sql.NUMBER to SQL NUMBER is shown in Listing 3.5.
Example 3.5. javalangInt.sql
create or replace java source named javalangInt as
/*
* Mapping int, java.lang.Integer to/from SQL NUMBER
*/
import java.sql.*;
import java.io.*;
import java.lang.Integer;
import oracle.sql.*;
import oracle.jdbc.*;
public class javalangInt
{
/*
* Update SQL NUMBER column with java.lang.Integer
*/
public static void xputInt (Integer x) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
PreparedStatement ps =
conn.prepareStatement("UPDATE TypesTab SET num = ?");
int n = x.intValue();
ps.setInt (1, n);
ps.execute();
ps.close();
}
/*
* Retrieve SQL NUMBER column as int
*/
public static int xgetInt() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
Statement stmt = conn.createStatement();
int n = 0;
ResultSet rs = stmt.executeQuery("SELECT num FROM TypesTab");
while (rs.next())
{
n = rs.getInt(1);
}
stmt.close();
return n;
}
}
/
show errors;
alter java source javalangInt compile;
show errors;
create or replace procedure putjavaInt (n NUMBER)
as language java
name 'javalangInt.xputInt (java.lang.Integer)';
/
show errors;
create or replace function getjavaInt return NUMBER
as language java
name 'javalangInt.xgetInt() return int';
/
show errors;
call putjavaInt(888);
set serveroutput on
select getjavaInt from dual;Listing 3.6 is similar to Listing 3.5, except that it maps SQL NUMBER to oracle.sql.NUMBER instead of java.lang.Integer.
Example 3.6. orasqlNUMB.sql
create or replace java source named orasqlNUMB as
/*
* Mapping oracle.sql.NUMBER to/from SQL NUMBER
*/
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class orasqlNUMB
{
/*
* Map oracle.sql.NUMBER to SQL NUMBER
*/
public static void xputNum (NUMBER n) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OraclePreparedStatement ops = (OraclePreparedStatement)
conn.prepareStatement("UPDATE TypesTab SET num = ?");
ops.setNUMBER (1, n);
ops.execute();
ops.close();
}
/*
* Map SQL NUMBER column to oracle.sq.NUMBER
*/
public static NUMBER xgetNum() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OracleStatement ostmt = (OracleStatement) conn.createStatement();
NUMBER onb = new NUMBER();
OracleResultSet ors =
(OracleResultSet) ostmt.executeQuery("SELECT num FROM
TypesTab");
while (ors.next())
{
onb = ors.getNUMBER(1);
}
ostmt.close();
return onb;
}
}
/
show errors;
alter java source orasqlNUMB compile;
show errors;
create or replace procedure putoraNUMB (n NUMBER)
as language java
name 'orasqlNUMB.xputNum (oracle.sql.NUMBER)';
/
show errors;
create or replace function getoraNUMB return NUMBER
as language java
name 'orasqlNUMB.xgetNum() return oracle.sql.NUMBER';
/
show errors;
call putoraNUMB(999);
set serveroutput on
select getoraNUMB from dual;Listing 3.7 maps a CHAR column to oracle.sql.CHAR and vice versa. The same technique can be used to map CHARACTER, LONG, STRING, and VARCHAR2 to the corresponding standard Java mapping (see Table 3.2).
create or replace java source named orasqlCHAR as
/*
* Mapping oracle.sql.CHAR to/from SQL CHAR
*/
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class orasqlCHAR
{
/*
* Update SQL CHAR column with oracle.sql.CHAR
*/
public static void xputChar (CHAR c) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OraclePreparedStatement ops = (OraclePreparedStatement)
conn.prepareStatement("UPDATE TypesTab SET xchar = ?");
ops.setCHAR (1, c);
ops.execute();
ops.close();
}
/*
* Retrieve SQL CHAR column as oracle.sq.CHAR
*/
public static CHAR xgetChar() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OracleStatement ostmt = (OracleStatement) conn.createStatement();
String ochar = null;
OracleResultSet ors =
(OracleResultSet) ostmt.executeQuery("SELECT xchar FROM
TypesTab");
while (ors.next())
{
ochar = ors.getString(1);
}
ostmt.close();
return new CHAR(ochar, null);
}
}
/
show errors;
alter java source orasqlCHAR compile;
show errors;
create or replace procedure putoraCHAR (c CHAR)
as language java
name 'orasqlCHAR.xputChar (oracle.sql.CHAR)';
/
show errors;
create or replace function getoraCHAR return CHAR
as language java
name 'orasqlCHAR.xgetChar() return oracle.sql.CHAR';
/
show errors;
call putoraCHAR('Fooooooooooo'),
set serveroutput on
select getoraCHAR from dual;Listing 3.8 maps a RAW column to oracle.sql.RAW, and the same technique can be used to map LONG RAW to a java byte array (see Table 3.2).
create or replace java source named orasqlRAW as
/*
* Mapping oracle.sql.RAW to/from SQL RAW
*/
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class orasqlRAW
{
/*
* Update a SQL RAW column to oracle.sql.RAW
*/
public static void xputRAW (RAW r) throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OraclePreparedStatement ops = (OraclePreparedStatement)
conn.prepareStatement("UPDATE TypesTab SET xraw = ?");
ops.setRAW (1, r);
ops.execute();
ops.close();
}
/*
* Retrieve a SQL RAW column as oracle.sq.RAW
*/
public static RAW xgetRAW() throws SQLException
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
OracleStatement ostmt = (OracleStatement) conn.createStatement();
RAW oraw = new RAW();
OracleResultSet ors =
(OracleResultSet) ostmt.executeQuery("SELECT xraw FROM
TypesTab");
while (ors.next())
{
oraw = ors.getRAW(1);
}
ostmt.close();
return oraw;
}
}
/
show errors;
alter java source orasqlRAW compile;
show errors;
create or replace procedure putoraRAW (c RAW)
as language java
name 'orasqlRAW.xputRAW (oracle.sql.RAW)';
/
show errors;
create or replace function getoraRAW return RAW
as language java
name 'orasqlRAW.xgetRAW() return oracle.sql.RAW';
/
show errors;
call putoraRAW('Fooooooooooo'),
set serveroutput on
select getoraRAW from dual;This script creates and populates a table of BLOB, CLOB, and BFILE columns used in the next three examples. Per the Oracle Database Glossary,[6] these are defined as follows:
Binary FILE (BFILE) is a LOB data type that represents a binary file residing in the file system, outside of the database datafiles and tablespace (also referred to as an external LOB).
Binary Large Object (BLOB) is a LOB data type that contains binary data and is used to hold unstructured data (also referred to as persistent LOBs because it resides in the database).
Character Large Object (CLOB) is a LOB data type that contains character data in the database character set. A CLOB may be indexed and searched by the interMedia Text search engine.
Copy and paste a random pdf file as
Figure1.pdf, Figure2.pdf,andFigure3.pdf, underC: empor equivalent directory (i.e.,/tmp).Connect under a DBA account (i.e., system or sys-as-sysdba) and grant the SCOTT schema the right to create an alias directory in the database as follows:
SQL> grant create any directory to scott; SQL> exit
Run the following script as SCOTT. Notice the use of
EMPTY_BLOBandEMPTY_CLOBSQL function for returning an empty LOB locator to initialize the LOB variable or column before populating the LOB in question.
XobTypesTab.sql
===============
connect scott/tiger
rem
rem Create a file system directory alias in the database
rem
create or replace directory bfiledir as 'C:TEMP';
drop table Xobtab;
create table XobTab (id number, blob_col blob, clob_col clob,
bfile_col bfile);
insert into XobTab values (1, empty_blob(), empty_clob(),
bfilename('BFILEDIR', 'Figure1.pdf'));
insert into XobTab values (2, hextoraw(lpad('b', 1500, 'b')),
lpad('c', 3500, 'c'), bfilename('BFILEDIR', 'Figure2.pdf'));
insert into XobTab values (3, hextoraw(lpad('b', 2000, 'b')),
lpad('c', 4000, 'c'), bfilename('BFILEDIR', 'Figure3.pdf'));
commit;In reality, all LOB data types store a locator, which points to the actual storage inside or outside of the database. Listing 3.9 maps a CLOB locator column to java.sq.Clob.
Inserts a new CLOB into the database using JDBC
PreparedStatement.Appends text to the inserted CLOB using JDBC
CallableStatementand PL/SQLDBMS_LOB_WRITEAPPENDprocedure.Returns the locator of the updated/modified CLOB and the augmented
NUMBER. The PL/SQL procedure Clobwrap wraps the method Clobproc and takes aNUMBERand aCLOBas IN/OUT parameters. The same technique can be used to map CLOB tooracle.sql.CLOB. The JDBC techniques for inserting and retrieving LOB will be covered in Part II.
Example 3.9. ClobMap.sql
create or replace and resolve java source named ClobClass as
/*
*ClobClass.java
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class ClobClass
{
/*
* Insert a Clob into XobTab and returns the modified Clob
* IN and OUT parameters are passed as arrays (of NUMBER, Blob)
*/
public static void Clobproc (NUMBER id[], Clob cl[]) throws
SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO XobTab (ID, Clob_col)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setClob (2, cl[0]);
ps.execute ();
ps.close();
String buf = "This is an Outbound CLOB";
long amount = buf.length();
OracleCallableStatement cs =
(OracleCallableStatement)
conn.prepareCall ("begin dbms_lob.writeappend (?, ?, ?);
end;");
cs.setClob (1, cl[0]);
cs.setLong (2, amount);
cs.setString (3, buf);
cs.registerOutParameter (1, OracleTypes.CLOB);
cs.execute ();
cl[0] = cs.getClob (1);
id[0] = new NUMBER(id[0].intValue() + 1000);
cs.close();
}
}
/
show errors;
create or replace procedure Clobwrap (x IN OUT number, y IN OUT Clob)
as language java
name 'ClobClass.Clobproc(oracle.sql.NUMBER[], java.sql.Clob[])';
/
show errors;
set serveroutput on
declare
x Clob;
a number;
begin
select id+200, Clob_col into a, x from Xobtab where id = 1 for
update;
dbms_output.put_line('IN ID NUMBER = ' || a);
dbms_output.put_line('IN CLOB length = ' || dbms_lob.getlength(x));
Clobwrap(a, x);
dbms_output.put_line('OUT ID NUMBER = ' || a);
dbms_output.put_line('OUT CLOB length = ' ||
dbms_lob.getlength(x));
end;
/Listing 3.10 maps a BLOB locator column to java.sq.Blob.
Inserts a new BLOB into the database using JDBC
PreparedStatement.Appends text to the inserted BLOB using JDBC
CallableStatementand PL/SQLDBMS_LOB.WRITEAPPENDprocedure.Returns the locator of the updated/modified BLOB and the augmented
NUMBER. The PL/SQL procedure Blobwrap wraps the method Blobproc and takes a NUMBER and a BLOB as IN/OUT parameters. The same technique can be used to map BLOB tooracle.sql.BLOB.
create or replace and resolve java source named BlobClass as
/*
*BlobClass.java
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class BlobClass
{
/*
* Insert a BLOB into XobTab and returns the modified BLOB
* Notice IN and OUT parameters are passed as array of NUMBER
andarray of Blob
*/
public static void Blobproc (NUMBER id[], Blob bl[]) throws
SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO XobTab (ID, blob_col)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setBlob (2, bl[0]);
ps.execute ();
ps.close();
byte[] buf = { 00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05,
00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05,
00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05 };
long amount = buf.length;
OracleCallableStatement cs =
(OracleCallableStatement)
conn.prepareCall ("begin dbms_lob.writeappend (?, ?, ?);
end;");
cs.setBlob (1, bl[0]);
cs.setLong (2, amount);
cs.setRAW (3, new RAW(buf));
cs.registerOutParameter (1, OracleTypes.BLOB);
cs.execute ();
bl[0] = cs.getBlob (1);
id[0] = new NUMBER(id[0].intValue() + 1000);
cs.close();
}
}
/
show errors;
create or replace procedure blobwrap (x IN OUT number, y IN OUT blob)
as language java
name 'BlobClass.Blobproc(oracle.sql.NUMBER[], java.sql.Blob[])';
/
show errors;
set serveroutput on
declare
x blob;
a number;
begin
select id+100, blob_col into a, x from Xobtab where id = 1 for
update;
dbms_output.put_line('IN ID NUMBER = ' || a);
dbms_output.put_line('IN BLOB length = ' || dbms_lob.getlength(x));
Blobwrap(a, x);
dbms_output.put_line('OUT ID NUMBER = ' || a);
dbms_output.put_line('OUT BLOB length = ' ||
dbms_lob.getlength(x));
end;
/Listing 3.11 illustrates mapping the BFILE data type to JDBC type oracle.sql.BFILE. The PL/SQL procedure Bfilewrap wraps the method Bfileproc and takes a NUMBER and a BFILE as IN/OUT parameters and a BFILE as an IN parameter. This method uses the PL/SQL DBMS_LOB.FILEOPEN, which opens the bfile in read-only mode (remember we are manipulating PDF files). It also uses JDBC PreparedStatement and CallableStatement, which are described in Part II.
Example 3.11. BfileMap.sql
create or replace and resolve java source named BfileClass as
/*
*BfileClass.java
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class BfileClass
{
/*
* Insert a Bfile into XobTab and returns the modified Bfile
* Notice IN and OUT parameters are passed as array of NUMBER
* and array of Blob
*/
public static void Bfileproc (NUMBER id[], BFILE bf[], BFILE bf2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO XobTab (ID, Bfile_col)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setBFILE (2, bf[0]);
ps.execute ();
ps.close();
OracleCallableStatement cs =
(OracleCallableStatement)
conn.prepareCall ("begin dbms_lob.fileopen (?, 0); end;");
cs.setBFILE (1, bf2);
cs.registerOutParameter (1, OracleTypes.BFILE);
cs.execute ();
bf[0] = cs.getBFILE (1);
id[0] = new NUMBER(id[0].intValue() + 1000);
cs.close();
}
}
/
show errors;
Rem
Rem Call Spec for the Bfileproc method, mapping Bfile to
Rem oracle.sql.BFILE
Rem
create or replace procedure Bfilewrap (x IN OUT number, y IN OUT
Bfile, z bfile) as language java
name 'BfileClass.Bfileproc(oracle.sql.NUMBER[], oracle.sql.BFILE[],
oracle.sql.BFILE)';
/
show errors;
set serveroutput on
declare
x bfile;
y bfile;
a number;
begin
select id+300, bfile_col into a, x from XobTab where id = 1;
select bfile_col into y from XobTab where id = 3;
dbms_output.put_line('IN ID NUMBER = ' || a);
dbms_output.put_line('IN BFILE length = ' ||
dbms_lob.getlength(x));
Bfilewrap(a, x, y);
dbms_output.put_line('OUT ID NUMBER = ' || a);
dbms_output.put_line('OUT BFILE length = ' ||
dbms_lob.getlength(x));
end;
/This script (XADTTab.sql) creates a user-defined object type, ADTTYP2, and then creates a table, ADTTAB2, of ADT TYP2 objects, which will be used to map ADTTYP2 objects to oracle.sql.STRUCT.
XADTTab.sql =========== create type ADTTYP2 as object (n1 number, n2 varchar2(30), n3 date) / create table ADTTAB2 (id number, adtcol ADTTYP2) / insert into ADTTAB2 values (1, ADTTYP2(101, 'Row One', '01-JAN- 2001')); insert into ADTTAB2 values (2, ADTTYP2(102, 'Row Two', '02-JAN- 2002')); insert into ADTTAB2 values (3, null); insert into ADTTAB2 values (4, ADTTYP2(104, 'Row Four', '04-JAN- 2004')); insert into ADTTAB2 values (5, ADTTYP2(105, 'Row Five', '05-JAN- 2005')); commit;
The following code sample, ADTTab.sql, creates and compiles ADTTabClass directly into the database. The class has two methods: ADTTabProc() and ADTTabFunc(); the corresponding Call Specs have the same name as the Java methods.
The method ADTTabClass.ADTTabProc takes an array of NUMBER as IN/OUT parameter, an array of STRUCT as IN/OUT parameter, a STRUCT as IN parameter, and inserts an oracle.sql.STRUCT into the ADTTyp2 type table.
The function
ADTTabClass.ADTTabFunctakes an array ofNUMBERas IN/OUT parameter, an array ofSTRUCTas IN/OUT parameter, aSTRUCTas IN parameter, and retrieves and returns an instance ofADTTyp2asoracle.sql.STRUCT.
ADTTab.sql
==========
create or replace and resolve java source named ADTTabClass as
/*
*Mapping ADT Table to oracle.sql.STRUCT
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class ADTTabClass
{
/*
* inserts an oracle.sql.STRUCT object into an ADT Type table
*/
public static void ADTTabProc (NUMBER id[], STRUCT adt[], STRUCT
adt2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO ADTTAB2 (ID, ADTCOL)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setSTRUCT (2, adt[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
adt[0] = adt2;
}
/*
* retrieves an ADT as oracle.sql.STRUCT
*/
public static STRUCT ADTTabFunc (NUMBER id[], STRUCT adt[], STRUCT
adt2) throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT ADTCOL FROM ADTTAB2 WHERE ID =
?");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
STRUCT st = null;
while (rs.next())
{
st = (STRUCT) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
adt[0] = adt2;
return st;
}
}
/
show errors;
create or replace procedure ADTTabProc (x IN OUT number, y IN OUT
ADTTYP2, z IN ADTTYP2)
as language java
name 'ADTTabClass.ADTTabProc(oracle.sql.NUMBER[],
oracle.sql.STRUCT[],
oracle.sql.STRUCT)';
/
show errors;
create or replace function ADTTabFunc (x IN OUT number, y IN OUT
ADTTYP2, z IN ADTTYP2)
return ADTTYP2 as language java
name 'ADTTabClass.ADTTabFunc(oracle.sql.NUMBER[],
oracle.sql.STRUCT[],
oracle.sql.STRUCT) return oracle.sql.STRUCT';
/
show errors;
set serveroutput on
declare
a number;
x ADTTYP2;
y ADTTYP2;
z ADTTYP2;
begin
dbms_output.put_line('Calling ADT Type Procedure '),
a := 11;
x := ADTTYP2(11, 'JAVA record in', '11-MAY-2001'),
y := ADTTYP2(81, 'JAVA record OUT', '18-MAY-2001'),
dbms_output.put_line('IN number = ' || a);
dbms_output.put_line('n1 = ' || x.n1 || ', n2 = ' || x.n2 || ', n3 =
' || x.n3);
ADTTabProc(a, x, y);
dbms_output.put_line('OUT number = ' || a);
dbms_output.put_line('n1 = ' || x.n1 || ', n2 = ' || x.n2 || ', n3 =
' || x.n3);
dbms_output.put_line('Calling ADT Table Function'),
a := 1;
x := ADTTYP2(11, 'JAVA record in', '11-MAY-2001'),
y := ADTTYP2(81, 'JAVA record OUT', '18-MAY-2001'),
dbms_output.put_line('IN number = ' || a);
dbms_output.put_line('n1 = ' || x.n1 || ', n2 = ' || x.n2 || ', n3 =
' || x.n3);
z := ADTTabFunc(a, x, y);
dbms_output.put_line('OUT number = ' || a);
dbms_output.put_line('n1 = ' || x.n1 || ', n2 = ' || x.n2 || ', n3 =
' || x.n3);
end;
/REF types are built-in data types that represent references to objects of a specified type. To create a REF, you select the object from the table and apply the REF operator (see example). Similarly, to access the object referenced by a REF, you dereference the REF, which can be done using the Oracle-supplied DEREF operataor (see example). This script (XREFADTTab.sql) creates a user-defined object type ADTtyp, and then creates a table ADTtab of ADTtyp objects, and then a table of REF ADTtyp. We will use this table for mapping the REF of ADTtyp to oracle.sql.REF.
XREFADTTab.sql ============== create type ADTtyp as OBJECT (a1 number, a2 varchar2(20), a3 date) / create table ADTtab of ADTtyp / create table REFtab (id number, refcol REF ADTtyp) / insert into ADTtab values (ADTtyp(1, 'One', '01-JAN-2001')); insert into ADTtab values (ADTtyp(2, 'Two', '02-JAN-2002')); insert into ADTtab values (ADTtyp(3, 'Three', '03-JAN-2003')); insert into ADTtab values (ADTtyp(4, 'Four', '04-JAN-2004')); insert into ADTtab values (ADTtyp(5, 'Five', '05-JAN-2005')); insert into REFtab select 1, REF(R2) from ADTtab R2 where R2.a1 = 1; insert into REFtab select 2, REF(R2) from ADTtab R2 where R2.a1 = 2; insert into REFtab values (3, NULL); insert into REFtab select 4, REF(R2) from ADTtab R2 where R2.a1 = 4; insert into REFtab select 5, REF(R2) from ADTtab R2 where R2.a1 = 5; commit;
The following code sample, REFADTTab.sql, creates and compiles REFADTTabClass directly into the database. The class has two methods: REFADTTabProc() and REFADTTabFunc(); the corresponding Call Specs have the same name as the Java methods.
The method
REFADTTabClass.REFADTTabProctakes an array ofNUMBERas IN/OUT parameter, an array ofREFas IN/OUT parameter, aREFas IN parameter, and inserts anoracle.sql.REFinto theREFtabtable.The function
REFADTTabClass.REFADTTabFunctakes an array ofNUMBERas IN/OUT parameter, an array ofREFas IN/OUT parameter, aREFas IN parameter, and retrieves and returns an instance ofREF ADTTypasoracle.sql.REF.
REFADTTab.sql
============
create or replace and resolve java source named REFADTTabClass as
/*
*Mapping REF of ADTtype to oracle.sql.REF
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class REFADTTabClass
{
/*
* inserts oracle.sql.REF object as REF of ADTtyp
*/
public static void REFADTTabProc (NUMBER id[], REF rf[], REF rf2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO REFTAB (ID, REFCOL)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setREF (2, rf[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
rf[0] = rf2;
}
/*
* retrieves an return a REF of ADTtyp as oracle.sqlREF
*/
public static REF REFADTTabfunc (NUMBER id[], REF rf[], REF rf2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT REFCOL FROM REFTAB WHERE ID =
?");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
REF r = null;
while (rs.next())
{
r = (REF) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
rf[0] = rf2;
return r;
}
}
/
show errors;
create or replace procedure REFADTTabProc(x IN OUT number, y IN OUT
REF ADTTYP, z IN REF ADTTYP) as language java
name 'REFADTTabClass.REFADTTabProc(oracle.sql.NUMBER[],
oracle.sql.REF[], oracle.sql.REF)';
/
create or replace function REFADTTabfunc(x IN OUT number, y IN OUT REF
ADTTYP, z IN REF ADTTYP) return REF ADTTYP as language java
name 'REFADTTabClass.REFADTTabfunc(oracle.sql.NUMBER[],
oracle.sql.REF[], oracle.sql.REF) return oracle.sql.REF';
/
show errors;
set serveroutput on
declare
a number;
x REF ADTTYP;
y REF ADTTYP;
z REF ADTTYP;
t1 ADTTYP;
begin
a := 11;
select refcol into x from reftab where id = 1;
select refcol into y from reftab where id = 2;
dbms_output.put_line('Input number = ' || a);
select deref(x) into t1 from dual;
dbms_output.put_line('Input: a1= ' || t1.a1 || ', a2= ' || t1.a2 ||
', a3= ' || t1.a3);
dbms_output.put_line('Calling REF of ADT Table Procedure '),
REFADTTabProc(a, x, y);
select deref(x) into t1 from dual;
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Ouput: a1= ' || t1.a1 || ', a2= ' || t1.a2 ||
', a3= ' || t1.a3);
a := 5;
dbms_output.put_line('Calling REF of ADT Table Function '),
z := REFADTTabfunc(a, x, y);
select deref(x) into t1 from dual;
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Output: a1= ' || t1.a1 || ', a2= ' || t1.a2 ||
', a3= ' || t1.a3);
select deref(z) into t1 from dual;
dbms_output.put_line('Output Z = '),
dbms_output.put_line('Output: a1= ' || t1.a1 || ', a2= ' || t1.a2 ||
', a3= ' || t1.a3);
end;
/Cursors are database areas where the results of a query are stored. REF cursor (or cursor variable) allows sharing query result sets on the server side (i.e., between PL/SQL and Java in the database) and also passing the query results from the server side to the client side (i.e., as a return object). The following code sample illustrates mapping the REF cursor to java.sql.ResultSet. It uses the EMP table in the SCOTT schema. Setting setCreateStatementAsRefCursor() to true turns any statements created from this connection into a REF CURSOR; this is required for returning Resultset from Java in the database. The same technique can be used for mapping REF CURSOR to oracle.jdbc.OracleResultSet.
Resultset.sql
=============
create or replace and resolve java source named refcur as
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
/*
* Mapping REF CURSOR to java.sql.Resultset
*/
public class refcur
{
/*
* Procedure returning a REF CURSOR via OUT parameter
*/
public static void refcurproc (ResultSet rs[])
throws SQLException
{
Connection conn = null;
conn = DriverManager.getConnection("jdbc:oracle:kprb:");
((OracleConnection)conn).setCreateStatementAsRefCursor(true);
Statement stmt = conn.createStatement();
((OracleStatement)stmt).setRowPrefetch(1);
ResultSet rset = stmt.executeQuery("select * from EMP order by
empno");
rs[0] = rset;
// fetch one row
if (rset.next())
{
System.out.println("Ename = " + rset.getString(2));
}
}
/*
* Function returning a REF CURSOR
*/
public static ResultSet refcurfunc ()
throws SQLException
{
Connection conn = null;
conn = DriverManager.getConnection("jdbc:oracle:kprb:");
((OracleConnection)conn).setCreateStatementAsRefCursor(true);
Statement stmt = conn.createStatement();
((OracleStatement)stmt).setRowPrefetch(1);
ResultSet rset = stmt.executeQuery("select * from EMP order by
empno");
// fetch one row
if (rset.next())
{
System.out.println("Ename = " + rset.getString(2));
}
return rset;
}
}
/
show errors;
create or replace package refcur_pkg as
type EmpCurTyp IS REF CURSOR;
function rcfunc return EmpCurTyp;
procedure rcproc (rc OUT EmpCurTyp);
end refcur_pkg;
/
show errors;
create or replace package body refcur_pkg as
procedure rcproc(rc OUT EmpCurTyp)
as language java
name 'refcur.refcurproc(java.sql.ResultSet[])';
function rcfunc return EmpCurTyp
as language java
name 'refcur.refcurfunc() returns java.sql.ResultSet';
end refcur_pkg;
/
show errors;
set serveroutput on
call dbms_java.set_output(50000);
declare
type EmpCurTyp IS REF CURSOR;
rc EmpCurTyp;
employee emp%ROWTYPE;
begin
dbms_output.put_line(' ** Calling REF CURSOR PROCEDURE' );
refcur_pkg.rcproc(rc);
---
--- Alternatively the refcurfunc could be called as follows
--- rc = refcur_pkg.refcurfunc();
---
LOOP
fetch rc into employee;
exit when rc%notfound;
dbms_output.put_line(' Name = ' || employee.ENAME ||
' Department = ' || employee.DEPTNO);
end loop;
close rc;
end;
/
show errors;Along with Nested Tables (covered later), VARRAY, or Variable-Length Array, are parts of user-defined SQL collection types. They define a type, which represents an ordered set of elements of the same type. VARRAY columns are stored inline within the same tablespace; however, when they are too large (i.e., over 4 K), the Oracle database stores these as a BLOB. The following script defines a VARRAY of number type, a VARRAY of varchar2 type, and a VARRAY of date type. It then creates and populates a table using the defined VARRAY types as columns.
XVARRAY.sql
===========
drop table VarrayTab;
rem
rem VARRAY of Number type
rem
create or replace type NVARRAY as VARRAY(10) of number
/
rem
rem VARRAY of VARCHAR2 type
rem
create or replace type VC2VARRAY as VARRAY(10) of varchar2(30)
/
rem
rem VARRAY of date type
rem
create or replace type DATVARRAY as VARRAY(10) of date
/
rem
rem Table of VARRAYs
rem (number varray, varchar2 varray, date varray)
rem
create table VarrayTab (id int, nva nvarray,
vc2va vc2varray, datva datvarray);
insert into VarrayTab values (1,
NVARRAY(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
VC2VARRAY('One', 'Two', 'Three', 'Four', 'Five', 'Six',
'Seven','Eight', 'Nine', 'Ten'),
DATVARRAY('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-JAN-
2005',
'05-JAN-2005', '06-JAN-2005', '07-JAN-2005', '08-JAN-
2005',
'09-JAN-2005', '10-JAN-2005')
);
insert into VarrayTab values (2, null, null, null);
insert into VarrayTab values (3,
NVARRAY(31, 32, 33, 34, 35, 36, 37, 38, 39, 40),
VC2VARRAY('Thirty One', 'Thirty Two', 'Thirty Three',
'Thirty Four', 'Thirty Five', 'Thirty Six',
'Thirty Seven', 'Thirty Eight'),
DATVARRAY('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-MAR-
2005',
'05-MAR-2005', '06-MAR-2005', '07-MAR-2005', '08-MAR-
2005',
'09-MAR-2005', '10-MAR-2005')
);
commit;The following code sample, NumVarray.sql, creates and compiles NVArrayClass directly into the database. The class has two methods: nvaproc() and nvafunc(); the corresponding Call Specs have the same name as the Java methods.
The method
nvaprocinserts ajava.sql.Arrayrow into VarrayTab table asNVARRAY; it takes an array ofNUMBERas IN/OUT parameter, an array ofNVARRAYas IN/OUT parameter, and anNVARRAYas IN parameter.The method
nvafuncretrieves and returns aNVARRAYas ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofNVARRAYas IN/OUT parameter, and anNVARRAYas IN parameter.
NumVarray.sql
=============
create or replace and resolve java source named NVarrayClass as
/*
*Mapping NUMBER VARRAY to/from java.sql.Array
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class NVarrayClass
{
public static void nvaproc (NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO VarrayTab (ID, NVA)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY)va[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
}
public static Array nvafunc(NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT NVA FROM VarrayTab WHERE ID = ?
");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
return a;
}
}
/
show errors;
create or replace procedure nvaproc (x IN OUT number, y IN OUT
NVARRAY,
z IN NVARRAY)
as language java
name 'NVarrayClass.nvaproc(oracle.sql.NUMBER[], java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function nvafunc (x IN OUT number, y IN OUT NVARRAY,
z IN NVARRAY) return NVARRAY
as language java
name 'NVarrayClass.nvafunc(oracle.sql.NUMBER[], java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x NVARRAY;
y NVARRAY;
z NVARRAY;
begin
dbms_output.put_line('Calling Number VARRAY Procedure '),
a := 11;
x := NVARRAY(101, 102, 103, 104, 105, 106);
y := NVARRAY(201, 202, 203, 204, 205, 206);
dbms_output.put_line('IN number = ' || a);
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
nvaproc(a, x, y);
dbms_output.put_line('OUT ID = ' || a);
dbms_output.put_line('OUT X = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Calling Number VARRAY Function '),
a := 1;
x := NVARRAY(101, 102, 103, 104, 105, 106);
y := NVARRAY(201, 202, 203, 204, 205, 206);
dbms_output.put_line(' '),
z := nvafunc(a, x, y);
dbms_output.put_line('OUT ID = ' || a);
dbms_output.put_line('OUT X = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('OUT Z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/The following code sample, VC2Varray.sql, creates and compiles VC2VArrayClass directly into the database. The class has two methods: vc2vaproc() and vc2vafunc(); the corresponding Call Specs have the same name as the Java methods.
The method
vc2vaprocinserts ajava.sql.Arrayrow into VarrayTab table asVC2VARRAY; it takes an array ofNUMBERas IN/OUT parameter, an array ofVC2VARRAYas IN/OUT parameter, and aVC2VARRAYas IN parameter.The method
vc2vafuncretrieves and returns aVC2VARRAYas ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofVC2VARRAYas IN/OUT parameter, and aVC2VARRAYas IN parameter.
VC2Varray.sql
=============
create or replace and resolve java source named VC2VarrayClass as
/*
*VARCHAR2 VARRAY
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class VC2VarrayClass
{
public static void vc2vaproc (NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO VarrayTab (ID, VC2VA)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY)va[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
}
public static Array vc2vafunc(NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT VC2VA FROM VarrayTab WHERE ID =
? ");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
return a;
}
}
/
show errors;
create or replace procedure vc2vaproc (x IN OUT number, y IN OUT
VC2Varray,
z IN VC2Varray)
as language java
name 'VC2VarrayClass.vc2vaproc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function vc2vafunc (x IN OUT number, y IN OUT
VC2Varray,
z IN VC2Varray) return VC2Varray
as language java
name 'VC2VarrayClass.vc2vafunc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x VC2Varray;
y VC2Varray;
z VC2Varray;
begin
dbms_output.put_line('Calling VARCHAR2 VARRAY Procedure '),
a := 12;
x := VC2VARRAY('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX'),
y := VC2VARRAY('one', 'two', 'three', 'four', 'five', 'six'),
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input VC2ARRAY = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
vc2vaproc(a, x, y);
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Ouput VC2ARRAY = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line(' '),
dbms_output.put_line('Calling VARCHAR2 VARRAY Function '),
a := 1;
x := VC2VARRAY('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX'),
y := VC2VARRAY('one', 'two', 'three', 'four', 'five', 'six'),
dbms_output.put_line(' '),
z := vc2vafunc(a, x, y);
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Ouptput x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Output Z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/The following code sample, DATVarray.sql, creates and compiles DATVArrayClass directly into the database. The class has two methods: DATvaproc() and DATvafunc(); the corresponding Call Specs have the same name as the Java methods.
The method
DATvaprocinserts ajava.sql.Arrayrow into VarrayTab table asDATVARRAY; it takes an array ofNUMBERas IN/ OUT parameter, an array ofDATVARRAYas IN/OUT parameter, and aDATVARRAYas IN parameter.The method
DATvafuncretrieves and returns aDATVARRAYas ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofDATVARRAYas IN/OUT parameter, and aDATVARRAYas IN parameter.
DATVarray.sql
=============
create or replace and resolve java source named DATVarrayClass as
/*
*DATVarrayClass.java
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class DATVarrayClass
{
public static void DATvaproc (NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO VarrayTab (ID, DATVA)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY)va[0]);
ps.execute ();
ps.close();
conn.commit();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
}
public static Array DATvafunc(NUMBER id[], Array va[], Array va2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT DATVA FROM VarrayTab WHERE ID =
? ");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
va[0] = va2;
return a;
}
}
/
show errors;
create or replace procedure DATvaproc (x IN OUT number, y IN OUT
DATVarray,
z IN DATVarray)
as language java
name 'DATVarrayClass.DATvaproc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function DATvafunc (x IN OUT number, y IN OUT
DATVarray,
z IN DATVarray) return DATVarray
as language java
name 'DATVarrayClass.DATvafunc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x DATVarray;
y DATVarray;
z DATVarray;
begin
dbms_output.put_line('Calling DATE VARRAY Procedure '),
a := 13;
x := DATVARRAY('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-
JAN-2005'),
y := DATVARRAY('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-
MAR-2005'),
dbms_output.put_line('Iinput number = ' || a);
dbms_output.put_line('Input DATARRAY = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
DATvaproc(a, x, y);
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Ouput DATARRAY = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line(' '),
dbms_output.put_line('Calling DATE VARRAY Function '),
a := 1;
x := DATVARRAY('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-
JAN-2005'),
y := DATVARRAY('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-
MAR-2005'),
dbms_output.put_line(' '),
z := DATvafunc(a, x, y);
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Ouptput x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Output Z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/Like VARRAY, Nested Tables, or tables within a table, are parts of the user-defined SQL collection types. They define a type, which represents an unordered set of elements of the same type. The nested table columns of a table are stored out of line from the rows of the parent table, using the “store as” clause. Unlike VARRAY, which has fixed boundaries, Nested Tables offer more flexibility because their size can dynamically grow or shrink at the cost of inefficient storage. The following script defines a Nested Table of number type, a Nested Table of varchar2 type, and a Nested Table of date type. It then creates and populates a table using the defined Nested Table types as columns.
XNTABLE.sql
===========
rem
rem Nested Table of Number type
rem
create or replace type NTab_Num as TABLE of number
/
rem
rem Nested Table of VARCHAR2 type
rem
create or replace type NTab_Vc2 as TABLE of varchar2(30)
/
rem
rem Nested Table of date type
rem
create or replace type NTab_Dat as TABLE of date
/
drop table NSTableTab;
rem
rem Table of Nested Tables
rem (nested table of number, nested table of vachar2, neted table of
date)
rem
create table NSTableTab (id int, numnt NTab_Num, vc2nt NTab_Vc2, datnt
NTab_Dat)
nested table numnt store as NSTabNum,
nested table vc2nt store as NSTabVc2,
nested table datnt store as NSTabDat;
insert into NSTableTab values (1,
NTab_Num(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
NTab_Vc2('One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven',
'Eight', 'Nine', 'Ten'),
NTab_Dat('01-JAN-2003', '02-JAN-2003', '03-JAN-2003', '04-JAN-
2003',
'05-JAN-2003', '06-JAN-2003', '07-JAN-2003', '08-JAN-2003',
'09-JAN-2003', '10-JAN-2003')
);
insert into NSTableTab values (2, null, null, null);
insert into NSTableTab values (3,
NTab_Num(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15),
NTab_Vc2('Sixty One', 'Sixty Two', 'Sixty Three', 'Sixty Four',
'Sixty Five', 'Sixty Six', 'Sixty Seven'),
NTab_Dat('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-MAR-
2005',
'05-MAR-2005', '06-MAR-2005', '07-MAR-2005', '08-MAR-2005',
'09-MAR-2005', '10-MAR-2005', '11-MAR-2005', '12-MAR-2005')
);
commit;The following code sample, NumNSTable.sql creates and compiles NumNTabClass directly into the database. The class has two methods: NumNTabProc() and NumNTabFunc(); the corresponding Call Specs have the same name as the Java methods.
The method
NumNTabProcinserts ajava.sql.Arrayrow intoNSTableTabasNTab_Num; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Numas IN/OUT parameter, and anNTab_Numas IN parameter.The method
NumNTabFuncretrieves and returns anNTab_Numas ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Numas IN/OUT parameter, and anNTab_Numas IN parameter.
NumNSTable.sql
==============
create or replace and resolve java source named NumNTabClass as
/*
*NUMBER NESTED TABLE
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class NumNTabClass
{
public static void NumNTabProc(NUMBER id[], Array nt[], Array nt2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO NSTableTab (ID, NUMNT)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY) nt[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
}
public static Array NumNTabFunc (NUMBER id[], Array nt[],
Array nt2) throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT NUMNT FROM NSTableTab WHERE ID =
?");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
return a;
}
}
/
show errors;
create or replace procedure NumNTabProc (x IN OUT number, y IN OUT
NTab_Num, z IN NTab_Num) as language java
name 'NumNTabClass.NumNTabProc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function NumNTabFunc (x IN OUT number, y IN OUT
NTab_Num, z IN NTab_Num) return NTab_Num as language java
name 'NumNTabClass.NumNTabFunc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x NTab_Num;
y NTab_Num;
z NTab_Num;
begin
dbms_output.put_line('Calling NUMBER Nested Table Procedure '),
a := 11;
x := NTab_Num(11, 101, 102, 103, 104, 105, 106);
y := NTab_Num(21, 201, 202, 203, 204, 205, 206);
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_Num x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
NumNTabProc(a, x, y);
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Output NTab_Num x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Calling NUMBER Nested Table Function '),
a := 1;
x := NTab_Num(101, 102, 103, 104, 105, 106);
y := NTab_Num(201, 202, 203, 204, 205, 206);
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_Num x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
z := NumNTabFunc(a, x, y);
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Output NTab_Num x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Output NTab_Num z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/The following code sample VC2NSTable.sql creates and compiles NumNTabClass directly into the database. The class has two methods: VC2NTabProc() and VC2NTabFunc(); the corresponding Call Specs have the same name as the Java methods.
The method
VC2NTabProcinserts a java.sql.Array row intoNSTableTabasNTab_Vc2; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Vc2as IN/OUT parameter, and anNTab_Vc2as IN parameter.The method
VC2NTabFuncretrieves and returns anNTab_Vc2as ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Vc2as IN/OUT parameter, and anNTab_Vc2as IN parameter.
VC2NSTable.sql
==============
create or replace and resolve java source named VC2NTabClass as
/*
*VARCHAR2 NESTED TABLE
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class VC2NTabClass
{
public static void VC2NTabProc(NUMBER id[], Array nt[], Array nt2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO NSTableTab (ID, VC2NT)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY) nt[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
}
public static Array VC2NTabFunc (NUMBER id[], Array nt[],
Array nt2) throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT VC2NT FROM NSTableTab WHERE ID =
?");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
return a;
}
}
/
show errors;
create or replace procedure VC2NTabProc (x IN OUT number, y IN OUT
NTab_Vc2, z IN NTab_Vc2) as language java
name 'VC2NTabClass.VC2NTabProc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function VC2NTabFunc (x IN OUT number, y IN OUT
NTab_Vc2, z IN NTab_Vc2) return NTab_Vc2 as language java
name 'VC2NTabClass.VC2NTabFunc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x NTab_Vc2;
y NTab_Vc2;
z NTab_Vc2;
begin
dbms_output.put_line('Calling VARCHAR2 Nested Table Procedure '),
a := 12;
x := NTab_Vc2('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX'),
y := NTab_Vc2('one', 'two', 'three', 'four', 'five', 'six'),
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_Vc2 x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
VC2NTabProc(a, x, y);
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Output NTab_Vc2 x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Calling VARCHAR2 Nested Table Function '),
a := 1;
x := NTab_Vc2('ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX'),
y := NTab_Vc2('one', 'two', 'three', 'four', 'five', 'six'),
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_Vc2 x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
z := VC2NTabFunc(a, x, y);
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Output NTab_Vc2 x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Output NTab_Vc2 z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/The following code sample DATNSTable.sql creates and compiles DATNTabClass directly into the database. The class has two methods: DATNTabProc() and DATNTabFunc(); the corresponding Call Specs have the same name as the Java methods.
The method
DATNTabProcinserts ajava.sql.Arrayrow intoNSTableTabasNTab_Dat; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Datas IN/OUT parameter, and anNTab_Datas IN parameter.The method
DATNTabFuncretrieves and returns anNTab_Datas ajava.sql.Array; it takes an array ofNUMBERas IN/OUT parameter, an array ofNTab_Datas IN/OUT parameter, and anNTab_Datas IN parameter.
DATNSTable.sql
==============
create or replace and resolve java source named DATNTabClass as
/*
*DATE NESTED TABLE
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class DATNTabClass
{
public static void DATNTabProc(NUMBER id[], Array nt[], Array nt2)
throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("INSERT INTO NSTableTab (ID, DATNT)
VALUES(?, ?)");
ps.setNUMBER (1, id[0]);
ps.setARRAY (2, (ARRAY) nt[0]);
ps.execute ();
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
}
public static Array DATNTabFunc (NUMBER id[], Array nt[],
Array nt2) throws SQLException
{
Connection conn = DriverManager.getConnection
("jdbc:oracle:kprb:");
Statement stmt = conn.createStatement();
OraclePreparedStatement ps = (OraclePreparedStatement)
conn.prepareStatement ("SELECT DATNT FROM NSTableTab WHERE ID =
?");
ps.setNUMBER (1, id[0]);
OracleResultSet rs = (OracleResultSet) ps.executeQuery();
Array a = null;
while (rs.next())
{
a = (Array) rs.getObject(1);
}
ps.close();
id[0] = new NUMBER(id[0].intValue() + 1000);
nt[0] = nt2;
return a;
}
}
/
show errors;
create or replace procedure DATNTabProc (x IN OUT number, y IN OUT
NTab_DAT,
z IN NTab_DAT) as language java
name 'DATNTabClass.DATNTabProc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array)';
/
show errors;
create or replace function DATNTabFunc (x IN OUT number, y IN OUT
NTab_DAT,
z IN NTab_DAT) return NTab_DAT as language java
name 'DATNTabClass.DATNTabFunc(oracle.sql.NUMBER[],
java.sql.Array[],
java.sql.Array) return java.sql.Array';
/
show errors;
set serveroutput on
declare
a number;
x NTab_DAT;
y NTab_DAT;
z NTab_DAT;
begin
dbms_output.put_line('Calling DATE Nested Table Procedure '),
a := 13;
x := NTab_DAT('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-JAN-
2005'),
y := NTab_DAT('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-MAR-
2005'),
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_DAT x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
DATNTabProc(a, x, y);
dbms_output.put_line('Output number = ' || a);
dbms_output.put_line('Output NTab_DAT x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Calling DATE Nested Table Function '),
a := 1;
x := NTab_DAT('01-JAN-2005', '02-JAN-2005', '03-JAN-2005', '04-JAN-
2005'),
y := NTab_DAT('01-MAR-2005', '02-MAR-2005', '03-MAR-2005', '04-MAR-
2005'),
dbms_output.put_line('Input number = ' || a);
dbms_output.put_line('Input NTab_DAT x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
z := DATNTabFunc(a, x, y);
dbms_output.put_line('Ouput number = ' || a);
dbms_output.put_line('Output NTab_DAT x = '),
for i IN 1 .. x.COUNT loop
dbms_output.put(x(i) || ' '),
end loop;
dbms_output.put_line(' '),
dbms_output.put_line('Output NTab_DAT z = '),
for i IN 1 .. z.COUNT loop
dbms_output.put(z(i) || ' '),
end loop;
dbms_output.put_line(' '),
end;
/The Oracle JPublisher utility (covered in Part IV), allows mapping or “publishing” database entities such as used-defined SQL types, SQL object types, and PL/SQL types and packages to Java. Conversely, SQLJ object types, specified by ANSI SQLJ Part II allow creating user-defined SQL types, using Java classes that implement the SQLData interface (covered in Part II), as templates. Once published to SQL, the defined SQLJ object types can be used as column types or as subtypes of a coarse-grained object type, as illustrated in the following example. Furthermore, the static fields of the instances of the Java class can be accessed or changed using SQL functions, which invoke their Java implementation under the covers. You can consider SQLJ object types as a pragmatic mechanism for persisting and retrieving Java objects to/from RDBMS tables; however, these are not a substitute for an O/R mapping framework such as Toplink, which offers a complete and transparent mapping of Java objects (POJO) and their relationship (i.e., graph of objects) to RDBMS tables. The Oracle SQL syntax (i.e., the DDL) has been extended with EXTERNAL NAME and USING clauses to accommodate SQLJ object types.
The steps involved in creating a SQLJ object type are as follows:
Assume an existing or a new Java class, which implements
java.sql.SQLDatainterface (oracle.sql.ORADatainterface[7] is covered in Part II). In summary, the Java class must:Implement a
readSQL()method to retrieve the state of the Java object, using SQLInput stream.Implement a
writeSQL()method to store the state of the Java object, using SQLOutput stream.The attributes, the setters (mutators), and the getters of the Java class can be mapped to SQL through the
EXTERNAL NAMEclause; more specifically, public class methods (i.e.,public static) are exposed asSTATIC FUNCTION, and public instance methods (i.e.,public) are exposed asMEMBER FUNCTION.
Load the Java class into the database—if not already present.
Use the SQL
CREATE TYPEcommand to create the SQLJ object type that represents the Java type.CREATE TYPE <SQLJ object> AS OBJECT EXTERNAL NAME '<Class name>' LANGUAGE JAVA USING SQLData ...;All attributes of the SQLJ object type must map to a Java field using the
EXTERNAL NAMEclause; however, not all Java fields are exposed to SQL. Furthermore, the Java methods may not be exposed at all.Example of SQLJ object type(s).
In this example, we will define a simple SQLJ object type (
AddressObj), and then a complex SQLJ object type (PersonObj), which uses theAddressObjtype; then create and populate aPersonObjtable and perform the mapping between the user-defined type andjava.sql.Array. Finally, theSQLJ_ObjectJava program inserts and retrieves an instance ofPersonObjtype.This code sample comprises four files:
AddressObj.sql,PersonObj.sql,PersonObtTab.sql, andSQLJ_Object.sql(which contains both the Java code and the test code).
The following example defines:
a
New SQL object type
address_tusing theAddressObjJava classAddressObj.sql ============== create or replace and resolve java source named AddressObj as /* *Template for the adress_t SQLJ Object Type */ import java.sql.*; import java.io.*; import java.util.Date; import oracle.sql.*; public class AddressObj implements SQLData { String sql_type ="address_t"; public String street1; public String street2; public String city; public int zip; // Constructors public AddressObj() {} public AddressObj(String sql_type, String street1, String street2, String city, int zip) { this.sql_type = sql_type; this.street1= street1; this.street2= street2; this.city= city; this.zip= zip; } // Methods implementing the SQLData interface public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; SQLInput istream = (SQLInput) stream; street1 = istream.readString(); street2 = istream.readString(); city = istream.readString(); zip = istream.readInt(); } public void writeSQL(SQLOutput stream) throws SQLException { SQLOutput ostream = (SQLOutput) stream; ostream.writeString (street1); ostream.writeString (street2); ostream.writeString(city); ostream.writeInt(zip); } } / show errors;New SQL object type
person_tusing thePersonObjJava classPersonObj.sql ============= create or replace and resolve java source named PersonObj as /* *Template for the person_t SQLJ Object * the previously created address_t Object type is used to map * an attribute of PersonObj type */ import java.sql.*; import java.io.*; import java.util.Date; import oracle.sql.*; public class PersonObj implements SQLData { String sql_type = "person_t"; public String name; public int age; public AddressObj addrObj; // Constructors public PersonObj () {} public PersonObj(String sql_type, String name, int age, AddressObj addrObj) { this.sql_type = sql_type; this.name = name; this.age = age; this.addrObj = addrObj; } // Methods implementing the SQLData interface public String getSQLTypeName() throws SQLException { return sql_type; } public void readSQL(SQLInput stream, String typeName) throws SQLException { sql_type = typeName; SQLInput istream = (SQLInput) stream; name = istream.readString(); age = istream.readInt(); addrObj = (AddressObj) istream.readObject(); } public void writeSQL(SQLOutput stream) throws SQLException { SQLOutput ostream = (SQLOutput) stream; ostream.writeString (name); ostream.writeInt (age); ostream.writeObject(addrObj); } } / show errors;Java class for exchanging (insert and retrieve) Java objects with SQL objects
SQLJ_object.sql =============== create or replace and resolve java source named SQLJ_Objetc as /* *Inserting and Retrieving a UDT to/from the PersonObjTab *(using person_t and address_t types) */ import java.sql.*; import java.io.*; import java.util.*; import oracle.sql.*; import oracle.jdbc.*; import java.math.*; public class SQLJ_Object { /* * Retrieving an instance of Person object type */ public static PersonObj getPersonObj(int id) throws SQLException { OracleConnection oconn = (OracleConnection)DriverManager.getConnection("jdbc:ora cle:kprb:"); java.util.Dictionary map = (java.util.Dictionary) (oconn.getTypeMap()); Statement stmt = oconn.createStatement(); try { map.put("person_t", Class.forName("PersonObj")); map.put("address_t", Class.forName("AddressObj")); oconn.setTypeMap((Map)map); oconn.commit(); } catch(Exception e) { throw new SQLException(e.getMessage()); } PersonObj pobj = null; PreparedStatement ps = oconn.prepareStatement("SELECT adtcol1 from PersonObjTab where id = ?"); ps.setInt(1,id); OracleResultSet ors =(OracleResultSet) ps.executeQuery(); java.util.Hashtable ht = new java.util.Hashtable(); try { ht.put("person_t",Class.forName("PersonObj")); ht.put("address_t",Class.forName("AddressObj")); } catch(Exception e) { throw new SQLException(e.getMessage()); } while(ors.next()) { oconn.commit(); pobj =(PersonObj)ors.getObject(1,(Map)ht); } ps.close(); oconn.close(); stmt.close(); return pobj; } /* * Inserting an instance of Person object type */ public static void insPersonObj(int id, PersonObj personin, PersonObj [] personout) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:"); OracleConnection orconn = (OracleConnection)conn; PreparedStatement ps = conn.prepareStatement ("insert into PersonObjTab values(?,?)"); java.util.Dictionary map = (java.util.Dictionary)(orconn.getTypeMap()); try { map.put("person_t",Class.forName("PersonObj")); map.put("Address_t",Class.forName("AddressObj")); orconn.setTypeMap((Map)map); } catch(Exception e) { throw new SQLException(e.getMessage()); } ps.setInt(1,id); ps.setObject(2,personin); ps.executeUpdate(); ps.close(); PreparedStatement ps1 = conn.prepareStatement ("select adtcol1 from PersonObjTab where id = ? "); ps1.setInt(1,id); OracleResultSet ors1 = (OracleResultSet) ps1.executeQuery(); while(ors1.next()) { personout[0] = (PersonObj) ors1.getObject(1); } ors1.close(); conn.close(); } } / show errors;Defining the
SQL Object typesand creating the corresponding tableCREATE OR REPLACE TYPE address_t AS OBJECT EXTERNAL NAME 'AddressObj' LANGUAGE JAVA USING SQLDATA ( street1 varchar2(25) external name 'street1', street2 varchar2(25) external name 'street2', city varchar2(25) external name 'city', zip number external name 'zip' ); / show errors; CREATE OR REPLACE TYPE person_t AS OBJECT EXTERNAL NAME 'PersonObj' LANGUAGE JAVA USING SQLDATA ( name varchar2(25) external name 'name', age number external name 'age', addrObj address_t external name 'addrObj' ); / show errors; drop table PersonObjTab; create table PersonObjTab (id number,adtcol1 person_t); insert into PersonObjTab values(10,person_t('Jack',25,Address_t(' 10 Embarcadero',' Ferry Plazza', 'San Francisco',93126))); insert into PersonObjTab values(11,person_t('Bob',26,Address_t('12 Jr MLK','Alley3','Chicago',1090))); insert into PersonObjTab values(12,person_t('Doug',27,Address_t('10 Alley1','Alley2','Denvers',1091))); commit;Defining the
Call Specfor publishing the Java methods to SQLcreate or replace function getPersonObj (id IN number) return person_t as language java name 'SQLJ_Object.getPersonObj(int) returns PersonObj'; / show errors; create or replace procedure insPersonObj(id IN number, personin IN person_t, personout IN OUT person_t) as language java name 'SQLJ_Object.insPersonObj(int, PersonObj, PersonObj [])'; / show errors;
Running the example from a SQL*Plus command
declare m3 person_t; m4 person_t; par number := 25; cnt number := 0; begin m3 := person_t('Jane',31,address_t('Oracle Parkway','of 101','Redwood Shores',94065)); dbms_output.put_line('*** Calling insPersonObj Procedure *** '), select count(*) into cnt from PersonObjtab; dbms_output.put_line(' Number of Records is ' || cnt); insPersonObj(par,m3,m4); dbms_output.put_line(' After calling the procedure '), select count(*) into cnt from PersonObjTab; dbms_output.put_line('Number of Records is ' || cnt); dbms_output.put_line(' *name is '|| m4.name ||'*age is '||m4.age || ' *street1 is '|| m4.addrObj.street1 ||' *street2 is ' || m4.addrObj.street2 ||' *city is '||m4.addrObj.city || ' *zip is ' || m4.addrObj.zip); dbms_output.put_line('*** Calling getPersonObj Function*** '), m4 := getPersonObj (11); dbms_output.put_line(' *name is '|| m4.name ||'*age is '||m4.age || ' *street1 is '|| m4.addrObj.street1 ||' *street2 is ' || m4.addrObj.street2 || ' *city is '||m4.addrObj.city || ' *zip is '|| m4.addrObj.zip); end; /
Voilà! This extensive but not exhaustive coverage of Call Spec should have armed you to tackle the most common publishing requirements. Having loaded, compiled, NCOMPed, and published Java in the database, the next step is how to invoke it.
Currently, there are three methods for invoking Java in the database: (1) the OJVMJAVA command-line approach, (2) the PL/SQL wrapper approach, and (3) the client-side stub approach. Let’s examine each of these.
We need a database table and a Java program.
Let’s reuse the Workers table. The following SQL script will prepare a table to be used by our code sample:
rem
rem Workers.sql
rem
connect scott/tiger
rem
rem Set up the Workers table
rem
drop table workers;
create table workers (wid int, wname varchar2(20),
wposition varchar2(25), wsalary int);
insert into workers values (103, 'Adams Tagnon', 'Janitor',
10000);
insert into workers values (201, 'John Doe', 'Secretary',
20000);
insert into workers values (323, 'Racine Johnson', 'Junior
Staff Member', 30000);
insert into workers values (418, 'Abraham Wilson', 'Senior
Staff Member', 40000);
insert into workers values (521, 'Jesus Nucci', 'Engineer',
50000);
insert into workers values (621, 'Jean Francois', 'Engineer',
60000);
commit;At this stage, the “workers” table has been created and populated (feel free to add/change).
We’ll reuse the same JDBC program we used at the beginning of this chapter.
Example 3.1. Workers.java
create or replace and resolve java source named Workers as
/*
* The following code retrieves from the Worker table, a worker
* based on his id then updates position and salary.
* Adapated from existing JDBC demo
*/
import java.sql.*;
import oracle.jdbc.*;
public class Workers
{
public static void main (String args []) throws SQLException {
String name = null;
String pos = null;
int sal;
int id;
long t0,t1;
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
if ( args.length < 1 ) {
System.err.println("Usage: Java Workers <wid> <new position>
<new salary>");
System.exit(1);
}
// Get parameters value
id = Integer.parseInt(args[0]);
pos = args[1];
sal = Integer.parseInt(args[2]);
/*
* Where is your code running: in the database or outside?
*/
if (System.getProperty("oracle.jserver.version") != null)
{
/*
* You are in the database, already connected, use the default
* connection
*/
conn = DriverManager.getConnection("jdbc:default:connection:");
System.out.println ("Running in OracleJVM, in the database!");
// Disable autocommit - Not suppoted in OracleJVM
conn.setAutoCommit(false);
}
else
{
/*
* You are not in the database, you need to connect to
* the database
*/
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:",
"scott", "tiger");
System.out.println ("Running in JDK VM, outside the
database!");
}
/*
* Auto commit is off by default in OracleJVM (not supported)
*/
conn.setAutoCommit (false);
// Start timing
t0=System.currentTimeMillis();
/*
* find the name of the workers given his id number
*/
// create statement
stmt = conn.createStatement();
// find the name of the worker
ResultSet rset = stmt.executeQuery(
"SELECT WNAME FROM workers WHERE wid = " + id);
// retrieve and print the result (we are only expecting 1 row
while (rset.next())
{
name = rset.getString(1);
}
// return the name of the worker who has the given worker number
System.out.println ("Worker Name: "+ name);
/*
* update the position and salary of the retrieved worker
*/
// prepare the update statement
pstmt = conn.prepareStatement("UPDATE WORKERS SET WPOSITION
= ?, " +
" WSALARY = ? WHERE WNAME = ?");
// set up bind values and execute the update
pstmt.setString(1, pos);
pstmt.setInt(2, sal);
pstmt.setString(3, name);
pstmt.execute();
// double-check (retrieve) the updated position and salary
rset = stmt.executeQuery(
"SELECT WPOSITION, WSALARY FROM WORKERS WHERE WNAME = '" +
name + "'");
while (rset.next())
{
pos = rset.getString ("wposition");
sal = rset.getInt ("wsalary");
}
System.out.println ("Worker: Id = " + id + ", Name = " + name +
", Position = " + pos + ", Salary = " + sal);
// Close the ResultSet
rset.close();
// Close the Statement
stmt.close();
// Stop timing
t1=System.currentTimeMillis();
System.out.println ("====> Duration: "+(int)(t1-t0)+ "
Milliseconds");
// Close the connection
conn.close();
}
}
/
show errors;At this stage, assuming there are no errors, the Workers.class has been created in the schema and is ready to be used. Let’s see the various ways of invoking Java in the database.
OracleJVM furnishes an interactive command-line utility called OJVMJAVA, which can run Java classes in the database (i.e., classes with a public static main() method) from the client machine. In reality, OJVMJAVA is made of a client (let’s call it ojvmjava-client) and a server (let’s call it ojvmjava-server).
Here is how it works:
The ojvmjava-client uses the provided JDBC connection to create a database session, and then starts the ojvmjava-server.
Then the ojvmjava-client passes a byte array (LONG RAW) across the wire to the ojvmjava-server.
The ojvmjava-server looks up the user class (i.e., the first argument) and passes the remaining arguments as a string to its main method. The user class must have at least one method.
When the method completes, the results are sent back to the client across the wire using a byte array (LONG RAW).
The end user starts ojvmjava, and then issues valid commands.
Here is the syntax:
ojvmjava {-user <user>[/<password>@database] [options]The valid options are:
[@<filename>] [-batch] [-c | -command <command> <args>] [-debug] [-d | -database <conn_string>] [-fileout <filename>] [-o | -oci | -oci8] [-oschema <schema>] [-t | -thin] [-version | -v]
where command can be: echo, exit, help, java, version, whoami.
See the Oracle Database Java Developer’s Guide for more details; for the purpose of this chapter, we’ll only consider the java command.
Example:
Invoking the Workers program in the database, using OJVMJAVA:
C: est>ojvmjava -thin -user scott/tiger --OJVMJAVA-- --type "help" at the command line for help message $ java Workers 621 "Senior VP" 650000 Running in OracleJVM, in the database! Worker Name: Jean Francois Worker: Id = 621, Name = Jean Francois, Position = Senior VP, Salary = 650000 ====> Duration: 10 Milliseconds $ java Workers 201 "Senior VP" 650000 Running in OracleJVM, in the database! Worker Name: John Doe Worker: Id = 201, Name = John Doe, Position = Senior VP, Salary = 650000 ====> Duration: 10 Milliseconds $ exit
Calling Java in the database using a user-defined or JPublisher-generated PL/SQL wrapper (also known as Call Spec) is the traditional and most-used approach, for the following reasons:
It hides the implementation language of the stored procedures—could be Java, PL/SQL, or any other supported language.
It can be invoked by SQL, PL/SQL, client-side JDBC, server-side JDBC, and middle-tier Java/J2EE.
Invoking Java through the PL/SQL wrapper involves two steps:
Publishing the Java method to SQL (this step is also known as defining the Call Spec and has been covered extensively in the previous section.
Calling the PL/SQL wrapper.
Example:
The Call Spec is registered in the system dictionary, which maintains the correspondence with the actual Java method. The parameters and the arguments of the published Java methods and the PL/SQL wrapper must correspond one to one; however, this rule does not apply to the main() method, which only has an array of String as parameter (String[]). Because Array of String can only be mapped to CHAR or VARCHAR2 types, both worker id and worker salary, which hold int values, are mapped to VARCHAR2.
SQL>create or replace procedure WorkerSP (wid IN varchar2,
wpos IN
varchar2, wsal IN varchar2) as
language java name 'Workers.main(java.lang.String[])';
/
Procedure created.
SQL> show errors;
No errors.C:My_data>sqlplus scott/tiger
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Feb 2
17:10:08 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput on
SQL> call dbms_java.set_output(50000);
Call completed.
SQL> call WorkerSp('621', 'Senior VP', '650000'),
Running in OracleJVM, in the database!
Worker Name: Jean Francois
Worker: Id = 621, Name = Jean Francois, Position = Senior VP,
Salary = 650000
====> Duration: 12 Milliseconds
Call completed.
SQL> call WorkerSp('201', 'Senior VP', '650000'),
Running in OracleJVM, in the database!
Worker Name: John Doe
Worker: Id = 201, Name = John Doe, Position = Senior VP,
Salary = 650000
====> Duration: 10 Milliseconds
Call completed.
SQL>Call Specs publish methods on Java classes in the database, allowing these methods to be invoked from different languages, including Java/JDBC, non-Java languages, and SQL and PL/SQL. However, Call Specs suffer from the following limitations:
JDBC is the least common denominator across all RDBMS vendors, and, as such, it does not support all Oracle-specific complex types. Invoking stored procedures that use complex native database types, such as user-defined SQL types, user-defined SQL collection types, object types, VARRAYS, nested tables types, PL/SQL RECORD types, and PL/SQL INDEXED-BY tables, is challenging, unless you use JPublisher (see Part IV).
For each procedure or function, a wrapper has to be manually published with a SQL signature, which only supports SQL types that have direct mapping.
Exceptions in Java in the database are not properly returned to the caller.
Starting with Oracle Database 10g Release 1, a new mechanism allows calling methods on Java classes in the database, without the provision of a user-defined Call Spec, and by the same token, and probably more important, avoids the limitations of Call Spec. The solution consists of letting Java/ J2EE invoke methods locally on the client-side stub,[8] resulting in transparent transfer of Java objects between Java in the middle tier and Java in the database and better exceptions returning. However, only serializable parameters and return types are supported; methods that take nonserializable types as parameter or return type will not be published; also, only invoker’s rights are supported.
If you need to support JDBC types, arrays of supported types, JavaBeans, and serializable Java types, Oracle Database 10g Release 2 furnishes a new -dbjava option of JPublisher. It generates a server-side Java wrapper for instance methods, which converts Java types into types that can be exposed to the PL/SQL call specification and then the PL/SQL wrapper in question. See the Oracle Database JPublisher User’s Guide 10g Release 2 (10.2) for more details.
The process consists of two steps:
Generating the client stub. In Oracle Database 10g Release 1, JPublisher provides the
-javaoption for generating the stub; however, it supports only primitive and serializable Java types.jpub -u scott/tiger -java=<Class>:<StubImpl>#<Stub>
When using 10g JPublisher against pre-10g databases, the
sqljutl.jarmust be preinstalled in the SYS schema.Creating an instance of the stub. The client application or middle-tier Java/J2EE creates a new instance of the stub.
<Class>Stub <handler> = new <Class>StubImpl(conn);
This step is in fact two substeps:
A new database session is created using the connection object passed to the stub in step 1. An instance of the Java class in the server is created, and its handle is returned to the stub.
The client application invokes methods on the stub; the stub in turn calls the corresponding methods of the server-side Java class, using the provided JDBC connection (it uses JDBC but does not have the limitations mentioned previously).
<handler>.method ((new String[]{<parameter list>});
<handler>.main(new String[]{arg[0], arg[1], ...,
arg[n]});Example:
Let’s see a complete step-by-step example using the Workers class previously loaded in the database.
The Oracle Database 10g JPublisher utility[9] generates a client-side stub class with the following characteristics:
Two constructors: one that uses a JDBC connection and another that uses the SQLJ default connection context instance.
Public static methods correspond to the public static methods of the server class
An extra method,
newinstance()in the stub class, is used to create a new instance of the server class and its handle is returned; the server-side class must have an empty public constructor.Public methods corresponding to public methods of the server class, but with an extra “handle” parameter, which refers to the instance of the class in question.
Only serializable Java types (as parameters and arguments) are supported.
Given a Java class X, the following command:
jpub -u scott/tiger -java=X:XStubImpl#XStub
JPublisher generates the stub interface (WorkersStub.java) and its implementation (WorkersStubImpl.java):
C:My_Datajpub -u scott/tiger - java=Workers:WorkersStubImpl#WorkersStub WorkersStub.java WorkersStubImpl.java
WorkersStub.java(generated code)
public interface WorkersStub
{
public void main(java.lang.String[] p0)
throws java.sql.SQLException;
}WorkersStubImpl.java (generated code)
public class WorkersStubImpl implements WorkersStub
{
public WorkersStubImpl(java.sql.Connection conn) throws
java.sql.SQLException
{ m_ctx = new sqlj.runtime.ref.DefaultContext(conn); }
public WorkersStubImpl(sqlj.runtime.ref.DefaultContext
ctx)
{ m_ctx = ctx; }
public WorkersStubImpl() throws java.sql.SQLException
{}
public void
_setConnectionContext(sqlj.runtime.ref.DefaultContext ctx)
{ m_ctx = ctx; }
protected sqlj.runtime.ref.DefaultContext _context()
{ return m_ctx; }
public void _close(boolean closeJdbcConnection) throws
java.sql.SQLException
{ m_ctx.close(closeJdbcConnection); }
private sqlj.runtime.ref.DefaultContext m_ctx;
public void main(java.lang.String[] p0)
throws java.sql.SQLException
{
Object __jRt_0 = null;
__jRt_0 =
oracle.jpub.reflect.Client.invoke(_context(),"Workers","main"
,
"[Ljava/lang/String;",new
Object[]{p0,});
if (__jRt_0 instanceof java.sql.SQLException) throw
((java.sql.SQLException)__jRt_0);
}
}import java.sql.*;
import oracle.jdbc.*;
public class WorkersClient
{
public static void main(String[] arg) throws Exception{
long t0,t1;
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:","scott","tige
r");
System.out.println ("==> Calling Workers from CLient
Stub");
WorkersStub wk = new WorkersStubImpl(conn);
/*
* We should use a session where JavaVM is already
initialized
* This first method call initializes Java in the database
session
* Workers.java does not commit its changes so this is a no-
op
*/
wk.main(new String[]{arg[0], arg[1], arg[2]});
// Start client-side timing
t0=System.currentTimeMillis();
// Invoke server-side Workers.main method
wk.main(new String[]{arg[0], arg[1], arg[2]});
// Stop client-side timing
t1=System.currentTimeMillis();
System.out.println ("==> Client-Side Duration:
"+(int)(t1-t0)+ " Milliseconds");
}
}C:My_DataJpub>java WorkersClient 621 "Senior VP" 650000 ==> Calling Workers from CLient Stub ==> Client-Side Duration: 30 Milliseconds C:My_DataPMBookJpub>java WorkersClient 201 "Senior Director" 250000 ==> Calling Workers from CLient Stub ==> Client-Side Duration: 20 Milliseconds C:My_DataPMBookJpub>
Depending on the number of parameters and return values, there might be a significant overhead incurred by marshaling/unmarshaling (i.e., serialization/deserialization). The client stub approach makes sense when the server-side processing and the flexibility of supported types outweigh the cost of serialization/deserialization; reducing the number of parameters will greatly reduce the overhead.
Java programs can retrieve SQL error code and the text of the message of SQL exceptions through the getErrorCode() and getMessage() methods of the java.sql.SQLException class. However, when Java in the database is invoked through the PL/SQL wrapper, Java exceptions such as NullPointerException, IllegalArgumentException, DivisionByZero, and so on are not properly returned to the caller; instead, the following error message is returned:
ORA-29532 Java call terminated by uncaught Java exceptionWhen invoking Java in the database through the client-side stub instead (10.1.0.3 upward), exceptions objects are properly returned to the caller.
Example:
Create a basic Java class in the database that throws a user-defined exception.
SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ExceptionTest AS public class ExceptionTest { public static void exception1() throws Exception { throw new Exception("Test User-defined Exception"); } }; / 2 3 4 5 6 7 Java created. SQL> show errors; No errors. SQL> exitCreate a PL/SQL wrapper for
ExceptionTest.exception1()and invoke it.SQL> CREATE OR REPLACE PROCEDURE exceptiontest AS LANGUAGE JAVA NAME 'ExceptionTest.exception1()'; / 2 3 Procedure created. SQL> set serveroutput on SQL> call dbms_java.set_output(50000); SQL> call exceptiontest(); java.lang.Exception: Test User-defined Exception at ExceptionTest.exception1(EXCEPTIONTEST:3) call exceptiontest() * ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: Test User-defined Exception SQL>Generate the client stub, using the
–dbjavaoption of JPublisher (.java and .sqljstub implementation sources as well as a PL/ SQL wrapper are also generated).$ jpub -u scott/tiger - java=ExceptionTest:XtestStubImpl#XtestStub ./XtestStub.java ./XtestStubImpl.java XtestStub.java ((generated code with hand-made comments) ============== public interface XtestStub { /* * The extra method newInstance(), creates a new instance of * ExceptionTest in the server and returns its handle. */ public long newInstance(); public void exception1() throws java.lang.Exception; } XtestStubImpl.java (generated code) ================== public class XtestStubImpl implements XtestStub { public XtestStubImpl(java.sql.Connection conn) throws java.sql.SQLException { m_ctx = new sqlj.runtime.ref.DefaultContext(conn); } public XtestStubImpl(sqlj.runtime.ref.DefaultContext ctx) { m_ctx = ctx; } public XtestStubImpl() throws java.sql.SQLException {} public void _setConnectionContext(sqlj.runtime.ref.DefaultContext ctx) { m_ctx = ctx; } protected sqlj.runtime.ref.DefaultContext _context() { return m_ctx; } public void _close(boolean closeJdbcConnection) throws java.sql.SQLException { m_ctx.close(closeJdbcConnection); } private sqlj.runtime.ref.DefaultContext m_ctx; // newInstance methods public long newInstance() { Object __jRt_0 = null; try { __jRt_0 = oracle.jpub.reflect.Client.invoke(_context(),null, "ExceptionTest","ExceptionTest","",new Object[]{}); } catch (Throwable e) {e.printStackTrace();} return ((java.lang.Long)__jRt_0).longValue(); } public void exception1() throws java.lang.Exception { Object __jRt_0 = null; try { __jRt_0 = oracle.jpub.reflect.Client.invoke(_context(),null, "ExceptionTest","exception1","",new Object[]{}); } catch (java.lang.Exception e) {throw e;} catch (Throwable e) {e.printStackTrace();} } }Define and compile the client code (could be any middle-tier Java/J2EE).
XtestClient.java ================ import java.sql.*; import oracle.jdbc.*; public class XtestClient { public static void main(String[] arg) throws Exception{ DriverManager.registerDriver(new OracleDriver()); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0. 1:5521:lab","scott","tiger"); /* * get a handle to the client-side stub */ XtestStub xt = new XtestStubImpl(conn); /* * Invoke the main method of the client-stub */ xt.exception1(); } }Invoke the client code, which in turn will invoke Java methods in the database and will return the exception properly, as you would expect.
$ java XtestClient Exception in thread "main" java.lang.Exception: User- defined Exception at ExceptionTest.exception1(EXCEPTIONTEST:3) at sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMeth odAccessorImpl.java) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMetho dAccessorImpl.java) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Delegat ingMethodAccessorImpl.java) at java.lang.reflect.Method.invoke(Method.java) at oracle.jpub.reflect.Server.invoke(Server.java) at oracle.jpub.reflect.Server.invoke(Server.java) ORA-29531 no method %s in class %s
Because PL/SQL doesn’t check the signature of Java methods at wrapper definition time, but rather at runtime, this error is usually caused by data type mismatch between the Call Spec and the Java signature; here is an example:
SQL> create or replace and resolve java source named test as
public class X {static public void foo (java.sql.Date d){}}
2 /
Java created.
SQL> create or replace procedure foo(x date) as language java
name 'X.foo(dummy)';
2 /
Procedure created.
SQL> call foo(sysdate);
call foo(sysdate)
*
ERROR at line 1:
ORA-29531: no method foo in class X
SQL>The reasoning is that the class can be defined at a later time, hence avoiding a hard dependency from the call spec to the Java underlying class. Therefore, the following PL/SQL wrapper is successfully created on a nonexistent Java class:
SQL> create or replace procedure fake as language java name
2 'Dummy.Method()';
3 /
Procedure created.
SQL> call fake();
call fake()
*
ERROR at line 1:
ORA-29540: class Dummy does not exist
ORA-29549 Java Session State ClearedThis error message was covered in Chapter 2. When a Java class being used is recompiled, the in-memory copy is invalidated, and any session using this class receives this message the next time it invokes the class in question and will lose any associated state. Rerun the command that gives this error message to use the newer recompiled version of the class.
In the 9i R2 and 10g R1 database releases, SQL audit can be used to audit the PL/SQL wrappers of Java classes that are published and known to SQL; however, it cannot audit changes to the underlying Java source, class, or resource. Starting with release 10g R2, Java audit allows you to trace any modification of Java sources, Java classes, or Java resources using “audit.” The SQL syntax has been extended to support Java audit.
Example:
SQL> audit create java source by scott; SQL> audit execute on java class sys.myJavaClass by scott;
Tables 3.4 and 3.5 summarize Java audit.
Table 3.4. Java Audit Option
Java Audit Option | SQL/DDL to Be Audited |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The audit options for a session are initialized at session creation. Hence, if you connect as sys and change the audit option after a user session has been started, the change will not be effective until you restart the session. See the Oracle Database Java Developer’s Guide for required privileges and more details on Java audit.
The Oracle Enterprise Manager tool allows managing Java sources and classes through a GUI (Oracle 9i R2) or Web interface (10g). Through these interfaces, you can perform the following actions: Edit, View, Delete, Create, Create Like, Compile, Status, Creation date, Last Modified date, Create Synonym, Grant privileges, Load Java, and Drop Java. The ability to show dependencies is very handy. In addition, the visual error handling gives you an indication of errors and details on the right-hand pane.
This chapter walked you through the various steps for developing, deploying, and running Java in the database. Now that you know how to use Java in the database, let’s see what you can do with it. The next chapter describes interesting usage of Java in the database.
[1] See http://www.oracle.com/technology/tech/xml/xmldb/index.html for more details.
[3] Oracle’s native support for XML support in the database.
[7] Java classes that implement the java.io.Serializable interface are not currently supported.
[8] Old-timers will recall the CORBA client-side stub.