Using Java

Using Java Stored Procedures in Oracle 9i By Bulusu Lakshman

T

his article highlights the method of using Java Stored Procedures in Oracle9i, including new techniques introduced in Oracle9i. It also briefly discusses the capabilities of Java Stored Procedures in Oracle Forms 6i. Code samples are provided to help developers implement the concepts.

File I/O has greater capabilities with Java than with UTL_FILE such as: • More fine-grained permission policy while reading and writing files to the OS. • Recursive directories can be specified • Ability to handle both text and binary files unlike UTL_FILE that can handle only text files. • Ability to create directory or list contents of directory

Defining the Database Schema Along with Sample Data This article will use the following tables in a schema named plsql9i/plsql9i: create table site_tab ( site_no number(4) not null, site_descr varchar2(20) not null); alter table site_tab add primary key (site_no); create table hrc_tab (hrc_code number(4) not null, hrc_descr varchar2(20) not null); alter table hrc_tab add primary key (hrc_code);

Java Stored Procedures were introduced along with Java in the Oracle 8i database. Oracle 8i extended PL/SQL capabilities with the ability to store Java in the database in two ways: Java Stored Procedures and JDBC. Oracle 9i enhanced the capability of Java Stored Procedures to return a ResultSet. Forms 6i’s enhanced features include the ability to call Java Stored Procedures from Forms. Forms can also use JDBC calls from Java Stored procedures. This article discusses how to create and execute Java Stored Procedures in Oracle 9i and well as return a ResultSet from a Java Stored Procedure in Oracle 9i. It also highlights the use of Java Stored Procedures and JDBC from Forms. Code examples are provided to help readers implement the technique.

Why Java in the Database? Oracle 9i provides a seamless environment where Java and PL/SQL can interact as two major database languages. There are many advantages to using both languages are many. PL/SQL advantages include: • Intensive Database Access – It is faster than Java. • Oracle Specific Functionality that has no equivalent in Java such as using DBMS_LOCK and DBMS_ALERT. • Using the same data types and language constructs as SQL providing seamless access to the database. Advantages of using Java in the database include: • Automatic garbage collection, polymorphism, inheritance, multi-threading • Access to system resources outside of the database such as OS commands, files, sockets • Functionality not available in PL/SQL such as OS commands, fine-grained security policies, image generation, easy sending of e-mails with attachments using JavaMail. As an example, sending e-mail using UTL_SMTP has several limitations. First, E-mail messages should conform to RFC 822 Internet specification which requires prerequisite knowledge. Second, attachments should be identified with a specific MIME header followed by file contents. This requires more changes and is error prone. JavaMail hides the details of MIME message structure and can be used to send e-mail for any type of protocol, not just over SMTP protocol as with UTL_SMTP.

Page 30

◆ Select

create table org_tab (hrc_code number(4) not null, org_id NUMBER(8) not null, org_short_name varchar2(30) not null, org_long_name varchar2(60) ); alter table org_tab add primary key (hrc_code, org_id); alter table org_tab add constraint org_tab_uk unique (org_id); alter table org_tab add constraint org_tab_fk foreign key (hrc_code) references hrc_tab(hrc_code); create table org_site_tab ( org_id number(8) not null, site_no number(4) not null ); alter table org_site_tab add primary key (org_id, site_no); alter table org_site_tab add constraint org_site_tab_fk1 foreign key (org_id) references org_tab(org_id); alter table org_site_tab add constraint org_site_tab_fk2 foreign key (site_no) references site_tab(site_no);

The data for the tables is created by means of the following INSERT statements: insert insert insert insert insert

into into into into into

site_tab site_tab site_tab site_tab site_tab

insert insert insert insert insert

into into into into into

hrc_tab hrc_tab hrc_tab hrc_tab hrc_tab

values values values values values values values values values values

insert into org_tab values ABC Inc.’); insert into org_tab values XYZ Inc.’); insert into org_tab values CEO DataPro Inc.’); insert into org_tab values VP Sales ABC Inc.’); insert into org_tab values VP Mktg ABC Inc.’); insert into org_tab values VP Tech ABC Inc.’); insert insert insert insert insert insert insert insert insert insert

into into into into into into into into into into

org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab org_site_tab

(1, (2, (3, (4, (5, (1, (2, (3, (4, (5,

‘New York’); ‘Washington’); ‘Chicago’); ‘Dallas’); ‘San Francisco’); ‘CEO/COO’); ‘VP’); ‘Director’); ‘Manager’); ‘Analyst’);

(1, 1001, ‘Office of CEO ABC Inc.’,’Office of CEO (1, 1002, ‘Office of CEO XYZ Inc.’,’Office of CEO (1, 1003, ‘Office of CEO DataPro Inc.’,’Office of (2, 1004, ‘Office of VP Sales ABC Inc.’,’Office of (2, 1005, ‘Office of VP Mktg ABC Inc.’,’Office of (2, 1006, ‘Office of VP Tech ABC Inc.’,’Office of

values values values values values values values values values values

(1001, (1002, (1003, (1004, (1004, (1004, (1005, (1005, (1005, (1006,

1); 2); 3); 1); 2); 3); 1); 4); 5); 1);

In addition, a minimal setup of the environment for Java and JDBC to work should be done as follows: • Set the CLASSPATH to include the current working directory and [OracleHome]\jdbc\lib\classes12.zip. • Set the PATH to include [JavaHome]\bin and [OracleHome]\bin directories.

Procedures can be used for void Java methods and functions can be used for methods returning values. Also packaged procedures and functions can be used as call specs. Only top-level and packaged procedures and functions can be used as call specs. Java methods published as procedures and functions must be invoked explicitly. They can accept arguments and are callable from SQL CALL statements, anonymous PL/SQL blocks, stored procedures, functions and packages. Java methods published as functions are also callable from SQL DML statements (i.e., INSERT, UPDATE, DELETE and SELECT statements). Database triggers can use the CALL statement to invoke a Java method to perform a particular action based on the triggering event. Object Types can be defined with attributes and methods that operate on these attributes. The methods can be written in Java. Java stored procedures can also be called from a Java client via JDBC or SQLJ, a Pro*, OCI or ODBC client or an Oracle Developer Forms client. Developing a Java stored procedure involves the following steps: 1. Writing the Java method by creating a custom Java class to achieve the desired functionality. 2. Loading the Java method into the RDBMS and resolving external references. 3. Publishing the Java method into the RDBMS by writing a PL/SQL call spec. 4. If needed, granting the appropriate privileges to invoke the Java stored procedure. 5. Calling the Java stored procedure from SQL and/or PLSQL. Each of these steps is explained in detail below.

1. Writing the Java method to achieve the desired functionality The following code shows a method that lists the contents of a directory passed to it as an argument:

Defining and Executing Java Stored Procedures A Java stored procedure is a Java method published to SQL and stored in an Oracle 9i database. Once a Java method is written and compiled as a Java class, it can be published by writing call specifications abbreviated as call specs. The call specs map Java method names, parameter types, and return types to their SQL counterparts. A call spec simply publishes the existence of a Java method. The Java method is called through its call spec and the runtime system dispatches the call with minimum overhead. A call spec is not a wrapper program since it does not add a second layer of code to be executed. It just publishes the existence of the Java method. Once published, the Java stored procedure is callable by client applications. When called, the stored procedure can accept arguments, reference Java classes, and return Java return values. The type of the application that can invoke a Java stored procedure is determined by the runtime context. Any Java method that does not include the GUI methods can be stored and run in the RDBMS as a stored procedure. The run-time contexts are as follows: • Functions and procedures • Database triggers • Object-relational methods

continued on page 32

4th Qtr 2002

◆ Page 31

Using Java Stored Procedures in Oracle 9i continued from page 31

import java.io.*; import java.sql.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class Directory { public static oracle.sql.ARRAY list(String dirName) throws SQLException { Connection conn = null; int ret_code ; String[] files = null ; File file = new File(dirName); if (file.exists()) { if (file.isDirectory()) { if (file.canRead()) { files = file.list(); } } } try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

To verify that the class has been loaded, query the data dictionary view USER_OBJECTS as shown below:

SQL> SQL> 2 3 4 5 6 7 8 9

column object_name format a30; select object_name, object_type, status, timestamp from user_objects where (object_name not like ‘SYS_%’ and object_name not like ‘CREATE$%’ and object_name not like ‘JAVA%’ and object_name not like ‘LOADLOB%’) and object_type like ‘JAVA %’ order by object_type, object_name /

OBJECT_NAME OBJECT_TYPE ——————————————— ————————— ———- —————————Directory JAVA CLASS RefCursor JAVA CLASS

STATUS TIMESTAMP VALID VALID

2002-03-16:00:01:26 2001-11-01:01:52:09

conn = DriverManager.getConnection( “jdbc:oracle:thin:@ASSOCIAT-SZECHG:1521:Oracle9”, “plsql9i”, “plsql9i”); ArrayDescriptor x_ad = ArrayDescriptor.createDescriptor(“X_NESTED_TABLE”, conn); ARRAY x_array = new ARRAY(x_ad, conn, files); conn.close(); return x_array; }catch (SQLException e) {ret_code = e.getErrorCode(); System.err.println(ret_code + e.getMessage()); conn.close(); return null;} } } }

Once the custom class is written, it is saved as a .java source file. The next step is to compile and test it before loading it into the RDBMS. To compile this class use the javac command from the command line with the source file name as the argument. Note that the current directory should be the directory where the source file resides. C:\> javac Directory.java

This results in a Directory.class file. 2. Loading the Java method into the RDBMS and resolving external references. Once the above Java method has been tested, it has to be loaded into the RDBMS using loadjava. Loadjava is a command line utility provided by Oracle to load Java code files (.java source files, .class files, .jar files) into the database. The command when invoked looks like the following: C:\> loadjava –user PL/SQL9i/PLSQL9i –oci8 –resolve Directory.class

3. Publishing the Java method into the RDBMS After loading into the RDBMS, the Java method must be published in the Oracle Data Dictionary by writing a PL/SQL call specification. The call spec maps the Java method names, parameter types and return types to their SQL counterparts. The call spec is written using the CREATE PROCEDURE or CREATE FUNCTION, CREATE PACKAGE or CREATE TYPE statements. Methods with return values are published as functions and void Java methods are published as procedures. Inside the function or procedure body, the LANGUAGE JAVA clause is specified. This clause maintains information about the name, parameter types and return type of the Java method. The following code illustrates the publishing of the Directory.list Java method described above: create or replace type x_nested_table is table of varchar2(100); / create or replace package pkg_dir is function list_dir(p_dir_name VARCHAR2) return x_nested_table; end pkg_dir; / create or replace package body pkg_dir is function list_dir(p_dir_name VARCHAR2) return x_nested_table is language java name ‘Directory.list(java.lang.String) return oracle.sql.ARRAY’; end pkg_dir; /

4. Granting the appropriate privileges to invoke the Java stored procedure Two roles are defined by Oracle 9i to be granted permissions to schemas accessing resources outside of the database. These are JAVAUSERPRIV and JAVASYSPRIV. These provide general access control. To provide fine-grained access control, the procedure dbms_java.grant_permission can be used.

Page 32

◆ Select

For the Directory.list routine to be callable from PL/SQL, the following finegrain permissions should be granted: SQL> connect system/manager Connected. SQL> begin 2 dbms_java.grant_permission( ‘PLSQL9I’, ‘SYS:java.io.FilePermission’, 3 ‘<>’, ‘read,write,execute,delete’ ); 4 end; 5 / PL/SQL procedure successfully completed. SQL> begin 2 dbms_java.grant_permission( ‘PLSQL9I’, ‘SYS:java.net.SocketPermission’, 3 ‘ASSOCIAT-SZECHG’, ‘resolve’ ); 4 end; 5 / PL/SQL procedure successfully completed. SQL> 2 3 4 5

begin dbms_java.grant_permission( ‘PLSQL9I’, ‘SYS:java.net.SocketPermission’, ‘127.0.0.1:1521’, ‘connect,resolve’ ); end; /

Writing the Java method that returns a ResultSet and compiling it to obtain a .class file. The steps involved to define a class RefCursor containing a Java function RefCursor_func() are as follows: 1. Import the JDBC specific packages such as java.sql.*, oracle.jdbc.driver.* and oracle.sql.*. import java.sql.*; import oracle.jdbc.driver.*; import oracle.sql.*;

2. Get the default server-side Oracle connection. Connection conn = new OracleDriver().defaultConnection();

3. Create the Statement or PreparedStatement as a REF CURSOR. This is done by calling the method setCreateStatementAsRefCursor(true) on the Connection object cast to an OracleConnection object.

PL/SQL procedure successfully completed. ((OracleConnection)conn).setCreateStatementAsRefCursor(true);

5. Calling the Java stored procedure from SQL and/or PLSQL. Once published, the Java method is called from SQL and/or PLSQL using the standard procedure for calling PL/SQL procedures or functions. The code for calling the Directory.list java method is as follows: SQL> connect plsql9i/plsql9i Connected. SQL> 2 3 4 5 6 7 8 9

declare v_tab x_nested_table; begin v_tab := pkg_dir.list_dir(‘c:\lax\plsql9i’); for i in 1..v_tab.COUNT loop dbms_output.put_line(v_tab(i)); end loop; end; /

4. Define the Statement object. Statement sql_stmt = conn.createStatement();

5. Define a ResultSet object to execute the appropriate query. This query returns the resultset so desired. ResultSet rset = sql_stmt.executeQuery( “SELECT hrc_descr, org_long_name FROM org_tab o, hrc_tab h where o.hrc_code = h.hrc_code”);

6. Return the ResultSet object (as a REF CURSOR). return rset;

Defining the Java Stored Procedure that Returns a ResultSet The functionality of Java Stored Procedures in Oracle 8i was limited by the fact that it was not possible to return a ResultSet directly from them. There was no mapping defined of the type ResultSet->REF CURSOR. Oracle9i has incorporated this mapping thus allowing returning a ResultSet from a function or as an OUT parameter to a procedure. This conversion is made possible by creating the Statement or PreparedStatement as a REF CURSOR. This is done by calling the setCreateStatementAsRefCursor() method on the Connection object with an argument as true, before creating that statement.

continued on page 34

4th Qtr 2002

◆ Page 33

Using Java Stored Procedures in Oracle 9i continued from page 33

The complete program (saved as RefCursor.java) is shown below: //Import JDBC packages import java.sql.*; import oracle.jdbc.driver.*; import oracle.sql.*; public class RefCursor { public static ResultSet RefCursor_func() throws SQLException { try { Connection conn = new OracleDriver().defaultConnection(); ((OracleConnection)conn).setCreateStatementAsRefCursor(true); //Create a Statement object Statement sql_stmt = conn.createStatement(); //Create a ResultSet object, execute the query and return a // resultset ResultSet rset = sql_stmt.executeQuery( “SELECT hrc_descr, org_long_name FROM org_tab o, hrc_tab h where o.hrc_code = h.hrc_code”); return rset; } catch (SQLException e) {System.out.println(e.getMessage()); return null;} } }

Publishing the Java Method in Oracle 9i Using a PL/SQL Call Specification (call spec) A packaged function to correspond to the Java function is shown below: create or replace package pkg_rc as TYPE rc IS REF CURSOR; function f_rc return rc; end pkg_rc; / create or replace package body pkg_rc as function f_rc return rc is language java name ‘RefCursor.RefCursor_func() return java.sql.Resultset’; end pkg_rc; /

The return type of the PL/SQL packaged function is of type REF CURSOR. The Java method should specify the fully qualified type name for its return type. So, java.sql.ResultSet and not just ResultSet.should be specified.

Calling the Java Stored Procedure using the PL/SQL call spec This is a simple PL/SQL program to call the packaged function pkg_rc.f_rc as shown below:

Tips: 1. If it is a function, the Java method should return a ResultSet of type java.sql.ResultSet. If it is a procedure, it should be declared as a parameter of type java.sql.ResultSet[], i.e. an array of type java.sql.ResultSet. 2. Only OUT parameters in the call spec are allowed. IN or IN OUT parameters cannot be used as there is no mapping from REF CURSOR to ResultSet. 3. The method setCreateStatementAsRefCursor(true) should be invoked prior to creating the Statement or PreparedStatement object. Otherwise, the following error message is generated when the Java Stored Procedure is executed: ORA-00932: inconsistent datatypes. Once defined, this Java program can be compiled into a .class file as follows:

declare TYPE rc IS REF CURSOR; r1 rc; v_hrc_descr varchar2(20); v_org_long_name varchar2(60); begin r1:= pkg_rc.f_rc; dbms_output.put_line(‘Hierarchy Org Long Name’); dbms_output.put_line(‘————- ——————-’); fetch r1 into v_hrc_descr, v_org_long_name; while r1%FOUND loop dbms_output.put_line(rpad(v_hrc_descr, 9)||’ ‘||v_org_long_name); fetch r1 into v_hrc_descr, v_org_long_name; end loop; close r1; end; /

javac RefCursor.java

Loading the .class file into the Oracle 9i database.

The Java Stored Procedure is executed producing the following output, based on the data in the org_tab table:

This is done using the loadjava utility as follows: loadjava -user plsql9i/plsql9i -r -oci8 RefCursor.class

Hierarchy ————————CEO/COO CEO/COO CEO/COO VP VP VP

Org Long Name ——————Office of CEO ABC Inc. Office of CEO XYZ Inc. Office of CEO DataPro Inc. Office of VP Sales ABC Inc. Office of VP Mktg ABC Inc. Office of VP Tech ABC Inc.

PL/SQL procedure successfully completed.

Page 34

◆ Select

Forms 6i and Java – Calling Java Stored Procedures from Forms One of the ways to incorporate Java in Forms 6i is by means of using Java Stored Procedures in Forms code. Using a Java Stored Procedure in Forms 6i is as simple as calling a stored subprogram from Forms. However, the data type and parameter-mode matching between Forms data types and the Java stored procedure data types should be in sync. Forms 6i even allows using JDBC calls. This allows for complex logic to be embedded in a Java Stored Procedure involving JDBC calls and then used from within Forms. A Java Stored Procedure using JDBC calls can be invoked from Forms in the same way a stored sub-program is invoked from Forms.

About the Author Bulusu Lakshman is the author of Oracle and Java Development and Oracle Developer Forms Techniques from SAMS publishing. He holds an Oracle Masters credential from Oracle Corporation, is an OCPCertified Application Developer, and is a double Honors graduate in Computer Science & Engineering and Mathematics. He has more than ten years experience in application development using Oracle and its various tools including Oracle and Java, both in the client/server and Web environments. Bulusu has presented papers at numerous national and international conferences and also contributed to lead technical journals in the U.S and U.K. He is employed by Compunnel Software Group Inc., a leading technical consulting firm in New Jersey and can be reached at [email protected].

4th Qtr 2002

◆ Page 35