Chapter 19

Overview

Oracle 8.1.5 introduced the ability to choose Java as a language to implement a stored procedure. PL/SQL has always been the natural choice and for 99 percent of what you need to do, it is still the right choice. Oracle 8.0 previously gave us the ability to implement a stored procedure in C, a feature we covered in Chapter 18, C-Based External Routines. Java-based stored procedures (another form of an external routine) are a natural extension of this capability, providing us with the ability to use Java where before, we might have used C or C++.

In short, this is just another choice. When you set about to develop a stored procedure, you now have at least three choices - PL/SQL, Java and C. I've listed them in my order of preference here. PL/SQL does most of the real database work, Java comes into play for things PL/SQL cannot do (mostly OS interfaces), and C comes into play when I have existing C code, or because there is some reason I cannot do it in Java.

This chapter is not an introduction to Java, JDBC, or SQLJ programming. It assumes you have at least a cursory knowledge of Java and would be able to read through small bits of Java code. It also assumes at least a cursory knowledge of JDBC and SQLJ - although if you have a little Java experience, you should be able to read through the JDBC and SQLJ parts without issue.

Why Use Java Stored Procedures?

Java external routines differ from C-based routines in that, much like PL/SQL, the Java runs natively in Oracle's JVM, right in the address space of the database. With C-based external procedures, we had to configure a listener, set up the TNSNAMES.ORA file, and have a separate process running. None of this is necessary with Java because, as an interpreted language, it is deemed as being 'safe' in much the same way as PL/SQL. It is not possible to develop a Java routine that overwrites some part of the SGA. This has its pros and cons, as we will discuss. The fact that it is running in the same address space allows the interaction between Java and the database to be a little smoother - there is less context switching between processes at the OS level, for example. On the downside, however, the Java code is always running as the 'Oracle software owner', meaning a Java stored procedure could overwrite the database's INIT.ORA parameter file (or some other, even more important set of files, such as the data files) if it has been given the appropriate privileges.

I find myself using a little Java every now and then to do things I cannot do in PL/SQL. For example, in Appendix A, Necessary Supplied Packages, I show how I implemented a TCP/IP socket package using Java. I did this in Oracle 8.1.5 before UTL_TCP was available (which is really written in Java as well), and still find it preferable. I also use Java to send e-mail from the database. Again, a package already exists, UTL_SMTP (built on Java as well), that can send simple e-mails, but Java makes many other options available, including the ability to send (and receive) e-mails with attachments.

I use UTL_FILE a lot, to read and write files in PL/SQL. One of the things missing in UTL_FILE is the ability to get a directory listing. PL/SQL cannot do this - Java can quite easily.

Occasionally, it would be convenient to run an operating system command or program from within the database. Again, PL/SQL won't facilitate this, but Java easily does. On a few occasions, I need to know the time zone on the server - PL/SQL cannot find this out but Java can (we explore that functionality in Appendix A, Necessary Supplied Packages, on UTL_TCP). Need the time down to milliseconds? In Oracle 8i, Java can do it.

If we need to connect to a DB2 database to run a query every now and then, we could do this with the Transparent Gateway to DB2. This would give us full heterogeneous database joins, distributed transactions, transparent two-phase commits, and many other options. But, if we only need to run a simple query or update on DB2, without any of the other fancy features, then we could simply load the DB2 Java JDBC drivers into the database, and do it that way (this doesn't only apply to DB2, of course).

Basically, any of the millions of non-interactive (no user interface) pieces of Java code that are out there, can be loaded into Oracle and used. That is why you will use snippets of Java here and there in your applications.

In short, my philosophy is to use Java only when it is sensible and useful to do so. I still find PL/SQL to be the right choice for the vast majority of my stored procedures. I can write one or two lines of PL/SQL code to achieve the same thing that would need many lines of Java/JDBC code. SQLJ cuts down on the code I might have to write, but it will not perform as well as PL/SQL and SQL together. The run-time performance of PL/SQL interacting with SQL is simply better than that for Java/JDBC, as you might expect. PL/SQL is designed around SQL, the integration between the two is very tight. PL/SQL data types are in general SQL data types and all SQL data types are PL/SQL data types - there is no impedance mismatch between the two. SQL access in Java, on the other hand, is by the means of an API added to the language. Every SQL type must be converted to some Java type and back again, all SQL access is procedural - there is no tight coupling between the two. In short, if you are manipulating data in the database, PL/SQL is the way to go. If you need to jump out of the database for a moment (to send an e-mail perhaps), Java is the most appropriate tool. If you need to search through stored e-mails in your database, use PL/SQL. If you need to get the e-mails into your database in the first place, then use Java.

How They Work

You will find Java external routines ('external routine' being synonymous with 'stored procedure') to be much easier to implement than C-based external routines. For example, in the previous chapter on C-Based External Routines, we had to be concerned with the following issues:

  • State management - External procedures can, and will, 'lose' their state (current values of 'static' or 'global' variables). This is due to the DLL caching that is implemented. Therefore, we need a mechanism for establishing and persisting a state in our C programs.

  • Tracing mechanisms - External procedures run on the server in their own process, outside of the server processes. While it is possible, on various platforms, to debug these routines using a conventional debugger, it is quite difficult and can be impossible if the bugs only arise when many people use the external procedure concurrently. We need a facility to generate copious trace files on demand to 'debug from afar'.

  • Parameter setting - We need a facility that allows us to parameterize our external procedures so that we can easily change their behavior externally, using a parameter file, much like we do with the init.ora file and the database.

  • Generic Error Handling - We need a facility to easily report meaningful errors to the end user.

With Java you'll find that state management, tracing, and generic error handling, are not a problem. For state management, we just declare variables in our Java classes. For simple tracing requirements we can use System.out.println. Generic error handling is taken care of with a call to the RAISE_APPLICATION_ERROR PL/SQL function. All of this is demonstrated in the following code:

tkyte@TKYTE816> create or replace and compile   2  java source named "demo"   3  as   4  import java.sql.SQLException;   5   6  public class demo extends Object   7  {   8   9  static int counter = 0;  10  11  public static int IncrementCounter() throws SQLException  12  {  13      System.out.println( "Enter IncrementCounter, counter = "+counter);  14      if ( ++counter >= 3 )  15      {  16          System.out.println( "Error! counter="+counter);  17          #sql {  18          begin raise_application_error( -20001, 'Too many calls' ); end;  19          };  20      }  21      System.out.println( "Exit IncrementCounter, counter = "+counter);  22      return counter;  23  }  24  }  25  /      Java created. 

We maintain a state through a static counter variable. Our simple demo routine will increment the counter each time it is called and on the third, and any subsequent, call it will raise an error for us.

Notice how for small snippets of code such as this, we can just use SQL*PLUS to load our Java code straight into the database, have it compiled into byte code and stored for us. There is no need for an external compiler, no JDK installs - just a SQL CREATE OR REPLACE statement. I prefer to do most of my Java stored procedures this way. It makes it very easy to install on any platform. I don't have to prompt for a username/password as I would with the LOADJAVA command (a command line tool to load Java source, classes, or jar files into the database). I don't have to worry about classpaths, and so on. In the Appendix A on Necessary Supplied Packages, we will take a look at LOADJAVA, in particular the DBMS_JAVA package interface to LOADJAVA.

This method (using CREATE OR REPLACE) of loading small Java routines into the database is particularly appropriate for people wanting to get their feet wet using this technology. Rather then installing the JDBC drivers, a JDK, an environment to compile in, setting up classpaths - you just compile straight into the database, in exactly the same way you would with PL/SQL. You would find compile time errors in the same way you do with PL/SQL as well, for example:

tkyte@TKYTE816> create or replace and compile   2  java source named "demo2"   3  as   4   5  public class demo2 extends Object   6  {   7   8  public static int my_routine()   9  {  10      System.out.println( "Enter my_routine" );  11  12      return counter;  13  }  14  }  15  /      Warning: Java created with compilation errors.      tkyte@TKYTE816> show errors java source "demo2" Errors for JAVA SOURCE demo2:      LINE/COL ERROR -------- ---------------------------------------------------- 0/0      demo2:8: Undefined variable: counter 0/0      Info: 1 errors 

That shows me that my_routine defined on line 8 is accessing a variable I did not declare - I don't have to guess at the error in the code it is shown to me. I've found many times that the frustrations of trying to get the JDBC/JDK/CLASSPATH setup correctly can be overcome in seconds using this easy approach.

Now, back to the working example. There is another important detail to note in the demo class above. The entry point method that is called from SQL, IncrementCounter, is static. It must be static (not everything must be static. You can use 'regular' methods from then on out). The SQL layer needs at least one method it can call, without having to pass the implicit instance data as a hidden parameter, hence the need for a static method.

Now that I have a small Java routine loaded up, I need to create a call specification for it in PL/SQL. This step is very similar to that seen in Chapter 18, C-Based External Procedures, where we mapped the C data types to the SQL data types. We are doing the same exact thing here; only this time we are mapping Java data types to SQL data types:

tkyte@TKYTE816> create or replace   2  function java_counter return number   3  as   4  language java   5  name 'demo.IncrementCounter() return integer';   6  /      Function created. 

Now we are ready to call it:

tkyte@TKYTE816> set serveroutput on      tkyte@TKYTE816> exec dbms_output.put_line( java_counter ); 1 PL/SQL procedure successfully completed.      tkyte@TKYTE816> exec dbms_output.put_line( java_counter ); 2 PL/SQL procedure successfully completed.      tkyte@TKYTE816> exec dbms_output.put_line( java_counter ); BEGIN dbms_output.put_line( java_counter ); END;      * ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-20001: Too many calls ORA-06512: at line 1 ORA-06512: at "TKYTE.JAVA_COUNTER", line 0 ORA-06512: at line 1 

We can see the state management is done for us as evidenced by the counter being incremented from 1 to 2 to 3. We can see that we can communicate errors easily enough, but where did our System.out.println calls go? By default, they will go into a trace file. If you have access to V$PROCESS, V$SESSION, and V$PARAMETER, we can determine the name of the trace file in a dedicated server configuration this way (this example is setup for Windows - it would be similar on UNIX but the filename you select would be slightly different):

tkyte@TKYTE816>select c.value||'\ORA'||to_char(a.spid,'fm00000')||'.trc'   2        from v$process a, v$session b, v$parameter c   3       where a.addr = b.paddr   4         and b.audsid = userenv('sessionid')   5         and c.name = 'user_dump_dest'   6  /      C.VALUE||'\ORA'||TO_CHAR(A.SPID,'FM00000')||'.TRC' ----------------------------------------------------------- C:\oracle\admin\tkyte816\udump\ORA01236.trc      tkyte@TKYTE816> edit C:\oracle\admin\tkyte816\udump\ORA01236.trc 

When I looked at that file, I found:

Dump file C:\oracle\admin\tkyte816\udump\ORA01236.TRC Tue Mar 27 11:15:48 2001 ORACLE V8.1.6.0.0 - Production vsnsta=0 vsnsql=e vsnxtr=3 Windows 2000 Version 5.0 , CPU type 586 Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option JServer Release 8.1.6.0.0 - Production Windows 2000 Version 5.0 , CPU type 586 Instance name: tkyte816 Redo thread mounted by this instance: 1 Oracle process number: 12 Windows thread id: 1236, image: ORACLE.EXE      *** 2001-03-27 11:15:48.820 *** SESSION ID:(8.11) 2001-03-27 11:15:48.810 Enter IncrementCounter, counter = 0 Exit IncrementCounter, counter = 1 Enter IncrementCounter, counter = 1 Exit IncrementCounter, counter = 2 Enter IncrementCounter, counter = 2 Error! counter=3 oracle.jdbc.driver.OracleSQLException: ORA-20001: Too many calls ORA-06512: at line 1 ... 

I could use the DBMS_JAVA package to redirect this output to the SQL*PLUS screen as well, to avoid having to use the trace files while debugging the routine. We'll come back to the DBMS_JAVA package in this section from time to time, but for a complete overview see the section on it in Appendix A, Necessary Supplied Packages.

One thing that is clear from this small example, is that when compared to a C-based external procedure, this is easy. I need no special setup on the server (other than having Java itself installed in the database). I need no external compiler. Many of the facilities we had to code in C are provided out of the box for us. This is easy.

The one thing that I skipped over, are the parameters to configure your Java routines. The reason for doing this is because Java supplies built-in functionality for this, in the form of the java.util.Properties class. You would simply use the load method of this class to load a previously saved set of properties either from a LOB in a database table, or from an OS file - whichever is more flexible for you.

In the remainder of this section, I will give a couple of useful examples of Java stored procedures, such as the ones listed in the Why Use Java Stored Procedures section of this chapter. Before we do that, I would like to implement the same DEMO_PASSING_PKG for Java that we did for C, just to get a grasp on how to pass the common data types back and forth between SQL and Java external routines.

Passing Data

The routines I would like to implement in this example will be a series of procedures that accept an IN parameter, and an OUT (or IN OUT) parameter. We will write one for each type of interesting data type (the ones we will use frequently). They will demonstrate the correct way to pass as inputs, and receive as outputs, each of these types. Additionally, I would like to have some functions that show how to return some of these interesting types as well. To me the interesting types with Java are:

This list is a little different from that for C-based external routines. Specifically, BOOLEAN is not represented. This is because there currently exists no mapping between the PL/SQL BOOLEAN type, and the Java types. We cannot use BOOLEANs as parameters in our Java external procedures.

There are also the arbitrarily complex data types you can create with the object relational extensions. For those, I would recommend you consider using the Oracle-supplied Java tool JPublisher. This tool will create Java classes that wrap the object types for you automatically. For more information on JPublisher please refer to the 'Oracle8i JPublisher User's Guide', part of the Oracle supplied documentation set. As with C-based external routines, we will not be going into object types in Java external routines, beyond the simple collections of scalar types.

Our Java class will be a re-implementation of the C-based external routine we coded earlier, only this time it is, of course, written in Java. We'll begin with the SQL definition of our three collection types - these are the same definitions we used in the C External Procedures example as well:

tkyte@TKYTE816> create or replace type numArray as table of number; Type created.      tkyte@TKYTE816> create or replace type dateArray as table of date; Type created.      tkyte@TKYTE816> create or replace type strArray as table of varchar2 (255); Type created. 

Now, the PL/SQL call specification for our example will be as follows. It will be a series of overloaded procedures and functions to test passing parameters to and from Java stored procedures. Each routine has an IN and an OUT parameter to show data being sent to and returned from the Java code.

The first routine passes the number type. Oracle Numbers will be passed to Java BigDecimal types. They could be passed to int, string, and other types but could suffer from the loss of precision. BigDecimal can hold an Oracle number safely.

Notice how the OUT parameter is passed as an array of BigDecimal types to the Java layer. This will be true of all OUT parameters passed to Java. In order to modify a parameter passed to Java, we must pass an 'array' of parameters (there will only be a single element in this array) and modify that array element. Below, in the implementation of the Java code, we'll see what that means in our source code.

tkyte@TKYTE816> create or replace package demo_passing_pkg   2  as   3      procedure pass( p_in in number, p_out out number )   4      as   5      language java   6      name 'demo_passing_pkg.pass( java.math.BigDecimal,   7                                   java.math.BigDecimal[] )' 

Next, Oracle Dates are mapped to the Timestamp type. Again, they could have been mapped to a variety of different types - such as a String but in order to avoid any loss of information during the implicit conversions, I chose the Timestamp type, which can accurately reflect the data contained in the Oracle Date type.

  8   9      procedure pass( p_in in date, p_out out date )  10      as  11      language java  12      name 'demo_passing_pkg.pass( java.sql.Timestamp,  13                                   java.sql.Timestamp[] )'; 

VARCHAR2s are very straightforward - they are passed to the java.lang.String type as you might expect.

 14  15      procedure pass( p_in in varchar2, p_out out varchar2 )  16      as  17      language java  18      name 'demo_passing_pkg.pass( java.lang.String,  19                                   java.lang.String[] )'; 

For the CLOB type, we use the Oracle supplied Java type oracle.sql.CLOB. Using this type, we'll easily be able to get the input and output streams used to read and write CLOB types.

 20  21      procedure pass( p_in in CLOB, p_out in out CLOB )  22      as  23      language java  24      name 'demo_passing_pkg.pass( oracle.sql.CLOB,  25                                   oracle.sql.CLOB[] )'; 

Now for the collection types: we see that we will use the same Oracle supplied type regardless of the type of collection we are actually passing. That is why in this case, the Java routines are not overloaded routines as they have all been so far (all of the Java routines have been named demo_passing_pkg.pass so far). Since each of the collection types are passed as the exact same Java type - we cannot use overloading in this case - rather we have a routine named after the type we are actually passing:

 26  27      procedure pass( p_in in numArray, p_out out numArray )  28      as  29      language java  30      name 'demo_passing_pkg.pass_num_array( oracle.sql.ARRAY,  31                                             oracle.sql.ARRAY[] )';  32  33      procedure pass( p_in in dateArray, p_out out dateArray )  34      as  35      language java  36      name 'demo_passing_pkg.pass_date_array( oracle.sql.ARRAY,  37                                              oracle.sql.ARRAY[] )';  38  39      procedure pass( p_in in strArray, p_out out strArray )  40      as  41      language java  42      name 'demo_passing_pkg.pass_str_array( oracle.sql.ARRAY,  43                                             oracle.sql.ARRAY[] )'; 

The next two routines demonstrate the mapping we will use for the RAW and INT types. The SQL RAW type will be mapped to the native Java byte type. Likewise, we will use the native Java type int for simple integers:

 44  45      procedure pass_raw( p_in in RAW, p_out out RAW )  46      as  47      language java  48      name 'demo_passing_pkg.pass( byte[], byte[][] )';  49  50      procedure pass_int( p_in   in number,  51                          p_out  out number )  52      as  53      language java  54      name 'demo_passing_pkg.pass_int( int, int[] )'; 

Lastly, for completeness, we will demonstrate using functions to return the basic scalar types as well:

 55  56      function return_number return number  57      as  58      language java  59      name 'demo_passing_pkg.return_num() return java.math.BigDecimal';  60  61      function return_date return date  62      as  63      language java  64      name 'demo_passing_pkg.return_date() return java.sql.Timestamp';  65  66      function return_string return varchar2  67      as  68      language java  69      name 'demo_passing_pkg.return_string() return java.lang.String';  70  71  end demo_passing_pkg;  72  /      Package created. 

This is basically the same package specification (minus the BOOLEAN) interface that we used for the C-based external routines. In this example, I have put the binding layer right into the specification itself, to avoid having to code an entirely redundant package body (every function is implemented in Java).

Now for the Java code that implements the above. We'll start with the definition of the demo_passing_pkg Java class:

tkyte@TKYTE816> set define off      tkyte@TKYTE816> create or replace and compile   2  java source named "demo_passing_pkg"   3  as   4  import java.io.*;   5  import java.sql.*;   6  import java.math.*;   7  import oracle.sql.*;   8  import oracle.jdbc.driver.*;   9  10  public class demo_passing_pkg extends Object  11  { 

This first routine, shown below, demonstrates the only way to pass an OUT parameter to Java; we actually pass an 'array' and the first element in the array is the only element in the array. When we modify the value in the array, we will have modified the OUT parameter. That is why all of these methods have their second parameter as an array. p_out[0] is something we can set and it will be sent 'out' of the method. Any changes we make to p_in on the other hand will not be returned.

The other interesting thing to note in this routine is the lack of need for an indicator variable! Java supports the concept of null in its object types as does SQL and PL/SQL. It is not tri-valued logic like SQL is, however - there is no X IS NOT NULL operation, we can just compare an object to null directly. Don't get confused and try to code something like p_in <> NULL in PL/SQL, it'll never work correctly!

 12  public static void pass( java.math.BigDecimal p_in,  13                           java.math.BigDecimal[] p_out )  14  {  15      if ( p_in != null )  16      {  17          System.out.println  18          ( "The first parameter is " + p_in.toString() );  19  20          p_out[0] = p_in.negate();  21  22          System.out.println  23          ( "Set out parameter to " + p_out[0].toString() );  24      }  25  } 

The next routine operates on Oracle Date types. This is virtually identical to the above routine but we use the methods of the Timestamp class to manipulate the date. Our goal in this routine is to add one month to the date:

 26  27  public static void pass( java.sql.Timestamp p_in,  28                           java.sql.Timestamp[] p_out )  29  {  30      if ( p_in != null )  31      {  32          System.out.println  33          ( "The first parameter is " + p_in.toString() );  34  35          p_out[0] = p_in;  36  37          if ( p_out[0].getMonth() < 11 )  38              p_out[0].setMonth( p_out[0].getMonth()+1 );  39          else  40          {  41              p_out[0].setMonth( 0 );  42              p_out[0].setYear( p_out[0].getYear()+1 );  43          }  44          System.out.println  45          ( "Set out parameter to " + p_out[0].toString() );  46      }  47  } 

Now for the simplest of data types; the String type. If you remember the C version with six formal parameters, null indicators, strlens, strcpys, and so on - this is trivial in comparison:

 48  49  public static void pass( java.lang.String p_in,  50                           java.lang.String[] p_out )  51  {  52      if ( p_in != null )  53      {  54          System.out.println  55          ( "The first parameter is " + p_in.toString() );  56  57          p_out[0] = p_in.toUpperCase();  58  59          System.out.println  60          ( "Set out parameter to " + p_out[0].toString() );  61      }  62  } 

In the CLOB routine, we have a little bit of work to do. This routine implements a 'copy' routine to show how to pass LOBs back and forth. It shows that in order to modify/read the contents of the LOB, we just use standard Java input/output stream types. In this example is is my input stream and os is the output stream. The logic in this routine does this copy 8K at a time. It just loops, reads, writes, and then exits when there is no more to read:

 63  64  public static void pass( oracle.sql.CLOB p_in,  65                           oracle.sql.CLOB[] p_out )  66  throws SQLException, IOException  67  {  68      if ( p_in != null && p_out[0] != null )  69      {  70          System.out.println  71          ( "The first parameter is " + p_in.length() );  72          System.out.println  73          ( "The first parameter is '" +  74             p_in.getSubString(1,80) + "'" );  75  76          Reader is = p_in.getCharacterStream();  77          Writer os = p_out[0].getCharacterOutputStream();  78  79          char buffer[] = new char[8192];  80          int length;  81  82          while( (length=is.read(buffer,0,8192)) != -1 )  83              os.write(buffer,0,length);  84  85          is.close();  86          os.close();  87  88          System.out.println  89          ( "Set out parameter to " +  90             p_out[0].getSubString(1,80) );  91      }  92  } 

This next routine is a private (internal) routine. It simply prints out meta-data about the oracle.sql.ARRAY that is passed to it. Each of the three array types we send down to Java will make use of this routine just to report what size/type that they are:

 93  94  private static void show_array_info( oracle.sql.ARRAY p_in )  95  throws SQLException  96  {  97      System.out.println( "Array is of type      " +  98                           p_in.getSQLTypeName() );  99      System.out.println( "Array is of type code " + 100                           p_in.getBaseType() ); 101      System.out.println( "Array is of length    " + 102                           p_in.length() ); 103  } 

Now, we will look at the routines that manipulate the arrays. Arrays are easy to use once you figure out how to get the data out of them and then back in. Getting the data out is very easy; the getArray() method will return the base data array for us. We simply need to cast the return value from getArray() to the appropriate type and we then have a Java array of that type. Putting the data back into an array is a little more complex. We must first create a descriptor (metadata) about the array and then create a new array object with that descriptor and the associated values. The following set of routines will demonstrate this for each of the array types in turn. Note that the code is virtually identical - with the exception of the times, we actually access the Java array of data. All these routines do is show us the meta-data of the oracle.sql.ARRAY type, print out the contents of the array, and finally copy the input array to the output array:

104 105  public static void pass_num_array( oracle.sql.ARRAY p_in, 106                                     oracle.sql.ARRAY[] p_out ) 107  throws SQLException 108  { 109      show_array_info( p_in ); 110      java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray(); 111 112      for( int i = 0; i < p_in.length(); i++ ) 113          System.out.println( "p_in["+i+"] = " + values[i].toString() ); 114 115      Connection conn = new OracleDriver().defaultConnection(); 116      ArrayDescriptor descriptor = 117         ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn ); 118 119      p_out[0] = new ARRAY( descriptor, conn, values ); 120 121  } 122 123  public static void 124  pass_date_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out ) 125  throws SQLException 126  { 127      show_array_info( p_in ); 128      java.sql.Timestamp[] values = (Timestamp[])p_in.getArray(); 129 130      for( int i = 0; i < p_in.length(); i++ ) 131          System.out.println( "p_in["+i+"] = " + values[i].toString() ); 132 133      Connection conn = new OracleDriver().defaultConnection(); 134      ArrayDescriptor descriptor = 135         ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn ); 136 137      p_out[0] = new ARRAY( descriptor, conn, values ); 138 139  } 140 141  public static void 142  pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out ) 143  throws java.sql.SQLException,IOException 144  { 145      show_array_info( p_in ); 146      String[] values = (String[])p_in.getArray(); 147 148      for( int i = 0; i < p_in.length(); i++ ) 149          System.out.println( "p_in["+i+"] = " + values[i] ); 150 151      Connection conn = new OracleDriver().defaultConnection(); 152      ArrayDescriptor descriptor = 153         ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn ); 154 155      p_out[0] = new ARRAY( descriptor, conn, values ); 156 157  } 

Passing RAW data is much like the String type in other words it is trivial. It is a very easy type to work with:

158 159  public static void pass( byte[] p_in, byte[][] p_out ) 160  { 161      if ( p_in != null ) 162          p_out[0] = p_in; 163  } 

To pass an int is problematic and I do not recommend it. There is no way to pass Null - an int is a 'base data type' in Java they are not objects - hence they cannot be Null. Since there is no concept of a Null indicator here, we would have to actually pass our own if we wanted to support nulls and the PL/SQL layer would have to check a flag to see if the variable was Null or not. This is here for completeness but is not a good idea, especially for in parameters - the Java routine cannot tell that it should not be reading the value since there is no concept of Nulls!

164 165  public static void pass_int( int p_in, int[] p_out ) 166  { 167      System.out.println 168      ( "The in parameter was " + p_in ); 169 170      p_out[0] = p_in; 171 172      System.out.println 173      ( "The out parameter is " + p_out[0] ); 174  } 

Finally, we come to the function. If you recall from the C based external procedures - this was hard to implement in C. We had memory allocations, nulls to deal with, manual conversions from C types to Oracle types and so on. Each C routine was ten or more lines of code. Here, it is as simple as a return statement:

175 176  public static String return_string() 177  { 178      return "Hello World"; 179  } 180 181  public static java.sql.Timestamp return_date() 182  { 183      return new java.sql.Timestamp(0); 184  } 185 186  public static java.math.BigDecimal return_num() 187  { 188      return new java.math.BigDecimal( "44.3543" ); 189  } 190 191  } 192  /      Java created      tkyte@TKYTE816> set define on 

In general, it is easier to code than in C due to the fact that Java does a lot of work under the covers for us. In the C example, there were about 1000 lines to provide similar functionality. The memory allocation that we had to be so careful with in C is not a factor in Java it'll throw an exception for us if we do something wrong. The Null indicators that were prevalent in C are non-existent in Java. This does raise a problem if you bind to a non-object Java type, but as noted above in the PASS_INT routine, I would recommend against that if Nulls are part of your environment.

Now we are ready to call the routines, since everything is in place. For example I can:

tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output( 1000000 )      tkyte@TKYTE816> declare   2      l_in strArray := strArray();   3      l_out strArray := strArray();   4  begin   5      for i in 1 .. 5 loop   6          l_in.extend;   7          l_in(i) := 'Element ' || i;   8      end loop;   9  10      demo_passing_pkg.pass( l_in, l_out );  11      for i in 1 .. l_out.count loop  12          dbms_output.put_line( 'l_out(' || i || ') = ' || l_out(i) );  13      end loop;  14  end;  15  / Array is of type      SECOND.STRARRAY Array is of type code 12 Array is of length    5 p_in[0] = Element 1 p_in[1] = Element 2 p_in[2] = Element 3 p_in[3] = Element 4 p_in[4] = Element 5 l_out(1) = Element 1 l_out(2) = Element 2 l_out(3) = Element 3 l_out(4) = Element 4 l_out(5) = Element 5      PL/SQL procedure successfully completed. 

The first eight lines of output were generated by the Java routine, the last five by PL/SQL. This shows that we were able to pass the array from PL/SQL to Java, and receive an array back just as easily. The Java routine simply copied the input array to the output array after printing out the array metadata and values.

Useful Examples

I firmly believe that if you can do something in a single SQL statement you should. Never use a CURSOR FOR loop, for example, when a simple update will do. I also believe that when you cannot do it in SQL, you should attempt to do it in PL/SQL. Never write a Java or C external routine unless it is impossible to accomplish your task in PL/SQL, or if the speedup you get with C is overwhelming. If you cannot do it for technical reasons in PL/SQL, Java should be the next choice. However, there is overhead associated with Java, in terms of memory required, CPU used, and the JVM startup time. PL/SQL has some of this as well, but it is already running - it is not yet another thing to run.

That aside, there are certain things that you quite simply cannot do in PL/SQL, but at which Java excels. The following are some of the truly useful snippets of Java I rely on every day. You should not look at this as an exhaustive list of what can be done, rather just as the tip of the iceberg. Later, in Appendix A on Necessary Supplied Packages, we'll see some larger examples of Java usage in Oracle.

Getting a Directory Listing

UTL_FILE, a utility we use in a couple of places in this book, is great for reading and writing text files. A very common requirement, however, is to process all of the files in a given directory. For that task, it falls short. There are no built-in methods anywhere in SQL or PL/SQL to read a directory listing. Well, Java can do that for us easily. Here is how:

tkyte@TKYTE816> create global temporary table DIR_LIST   2  ( filename varchar2(255) )   3  on commit delete rows   4  / Table created. 

In this implementation, I have chosen to use a temporary table as the way for the Java stored procedure to return its results. I found this most convenient as it allows me to sort and select the returned filenames easily.

The snippet of Java code we need is:

tkyte@TKYTE816> create or replace   2     and compile java source named "DirList"   3  as   4  import java.io.*;   5  import java.sql.*;   6   7  public class DirList   8  {   9  public static void getList(String directory)  10                     throws SQLException  11  {  12      File path = new File( directory );  13      String[] list = path.list();  14      String element;  15  16      for(int i = 0; i < list.length; i++)  17      {  18          element = list[i];  19          #sql { INSERT INTO DIR_LIST (FILENAME)  20                 VALUES (:element) };  21      }  22  }  23  24  }  25  /      Java created. 

I chose to use SQLJ here for programming efficiency. I'm already connected to the database, and doing this through JDBC would have taken quite a few lines of code. SQLJ makes doing SQL in Java almost as easy as in PL/SQL. Now, of course, we need to create our call specification:

tkyte@TKYTE816> create or replace   2  procedure get_dir_list( p_directory in varchar2 )   3  as language java   4  name 'DirList.getList( java.lang.String )';   5  /      Procedure created. 

One last detail here before we run this procedure. We need to give it permissions to do what it wants to do; read a directory. Now, in this example I am the DBA so I can grant this to myself but normally, you will have to request this ability from the DBA. If you recall in the introduction to this section I said:

Note 

"... the Java code is always running as the 'Oracle software owner', meaning a Java stored procedure could overwrite the database's INIT.ORA parameter file (or some other, even more important set of files, such as the data files) if it has been given the appropriate privileges."

This is how Oracle protects itself from this, you must explicitly be given a privilege in order to do many things that would be damaging. If we had attempted to use this procedure before getting the privileges, we would have received the following error:

tkyte@TKYTE816>  exec get_dir_list( 'c:\temp' ); BEGIN get_dir_list( 'c:\temp' ); END;      * ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission c:\temp read) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE)) ORA-06512: at "TKYTE.GET_DIR_LIST", line 0 ORA-06512: at line 1 

So, we'll authorize ourselves to do this:

tkyte@TKYTE816> begin   2          dbms_java.grant_permission   3          ( USER,   4           'java.io.FilePermission',   5           'c:\temp',   6       'read');   7  end;   8  /      PL/SQL procedure successfully completed. 

and we are ready to go:

tkyte@TKYTE816> exec get_dir_list( 'c:\temp' );      PL/SQL procedure successfully completed.      tkyte@TKYTE816> select * from dir_list where rownum < 5;      FILENAME ---------------------------------- a.sql abc.dat activation activation8i.zip 

The permissions are part of the Java2 Standard Edition (J2SE) and you can read more about them at http://java.sun.com/j2se/1.3/docs/api/java/security/Permission.html In Appendix A, Necessary Supplied Packages, we'll explore DBMS_JAVA and its uses in more detail as well.

There is one other thing of which you should be aware. Oracle 8.1.6 was the first version of Oracle to support the J2SE permissions. In Oracle 8.1.5 this would have been accomplished via a role. Unfortunately when I say 'a role' I mean basically a single role; JAVASYSPRIV. It would be similar to granting DBA to every user just because they needed to create a view - it is far too powerful. Once I have JAVASYSPRIV, I can do anything I want. Use caution with this role if you have 8.1.5 and consider using a later release with the infinitely more granular privilege model.

Running an OS Command

If I had a nickel for every time I was asked how to run an OS command, I would not be writing this book - I'd be on vacation! Before Java, this was really hard. Now it is almost trivial. There are probably a hundred ways to implement the following snippet of code but this works well:

tkyte@TKYTE816> create or replace and compile   2  java source named "Util"   3  as   4  import java.io.*;   5  import java.lang.*;   6   7  public class Util extends Object   8  {   9  10    public static int RunThis(String[] args)  11    {  12    Runtime rt = Runtime.getRuntime();  13    int        rc = -1;  14  15    try  16    {  17       Process p = rt.exec(args[0]);  18  19       int bufSize = 4096;  20       BufferedInputStream bis =  21        new BufferedInputStream(p.getInputStream(), bufSize);  22       int len;  23       byte buffer[] = new byte[bufSize];  24  25       // Echo back what the program spit out  26       while ((len = bis.read(buffer, 0, bufSize)) != -1)  27          System.out.write(buffer, 0, len);  28  29       rc = p.waitFor();  30    }  31    catch (Exception e)  32    {  33       e.printStackTrace();  34       rc = -1;  35    }  36    finally  37    {  38       return rc;  39    }  40    }  41  }  42  /      Java created. 

It is set up to run any program and capture the output to either a TRACE file on the server or, if you use DBMS_JAVA, to the DBMS_OUTPUT buffer. Now, this is a pretty powerful feature - we could run any command as the Oracle software account using this - were it not for the privileges we need. In this case, I want to be able to get a process listing - using /usr/bin/ps on UNIX and \bin\tlist.exe on Windows. In order to do that I need two privileges:

tkyte@TKYTE816> BEGIN   2      dbms_java.grant_permission   3      ( USER,   4       'java.io.FilePermission',   5       -- '/usr/bin/ps',  -- for UNIX   6       c:\bin\tlist.exe',  -- for WINDOWS   7       'execute');   8   9      dbms_java.grant_permission  10      ( USER,  11       'java.lang.RuntimePermission',  12       '*',  13       'writeFileDescriptor' );  14  end;  15  /      PL/SQL procedure successfully completed. 
Note 

You may not have the tlist.exe on your system. It is part of the Windows Resource Toolkit and not available on all Windows systems. This is purely illustrative of what you can do - not having access to tlist.exe is not a 'show stopper' for us here. You can use this technique to run any program. Beware however, you should consider carefully what programs you grant execute on using DBMS_JAVA. For example, if you granted execute on c:\winnt\system32\cmd.exe, you would be in effect granting execute on ALL programs - a very dangerous idea indeed.

The first allows me to run a very specific program. If I was daring, I could put * in place of the program name. This would let me run anything. I do not think that it would be wise, however; you should explicitly list only the fully qualified paths to programs you are sure of. The second privilege allows my run-time to produce output. Here I must use a *, as I do not know what output might be created (stdout for example).

Now we need our binding layer:

tkyte@TKYTE816> create or replace   2  function RUN_CMD( p_cmd  in varchar2) return number   3  as   4  language java   5  name 'Util.RunThis(java.lang.String[]) return integer';   6  /      Function created.      tkyte@TKYTE816> create or replace procedure rc(   2  as   3    x number;   4  begin   5    x := run_cmd(p_cmd);   6    if ( x <> 0 )   7    then   8          raise program_error;   9    end if;  10  end;  11  /      Procedure created. 

Here, I created a small layer on top of the binding layer to let me run this easily as a procedure. Now we will see how this works:

tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output(1000000)      PL/SQL procedure successfully completed.      tkyte@TKYTE816> exec rc('C:\WINNT\system32\cmd.exe /c dir') Volume in drive C has no label. Volume Serial Number is F455-B3C3 Directory of C:\oracle\DATABASE 05/07/2001  10:13a      <DIR>          . 05/07/2001  10:13a      <DIR>          .. 11/04/2000  06:28p      <DIR>          ARCHIVE 11/04/2000  06:37p                  47 inittkyte816.ora 11/04/2000  06:28p              31,744 ORADBA.EXE 05/07/2001  09:07p               1,581 oradim.log 05/10/2001  07:47p               2,560 pwdtkyte816.ora 05/06/2001  08:43p               3,584 pwdtkyte816.ora.hold 01/26/2001  11:31a               3,584 pwdtkyte816.xxx 04/19/2001  09:34a              21,309 sqlnet.log 05/07/2001  10:13a               2,424 test.sql 01/30/2001  02:10p             348,444 xml.tar 9 File(s)        415,277 bytes 3 Dir(s)  13,600,501,760 bytes free      PL/SQL procedure successfully completed. 

And that's it, we have just got a directory listing from the OS.

Getting Time Down to the Milliseconds

The examples are getting smaller, shorter, and faster. That's the point actually. With a tiny bit of Java functionality dropped in here and there at the right points, you can achieve a great deal of functionality.

In Oracle 9i, this function will be rendered moot; support for timestamps more granular than one second is provided. Until then, if you need it, we can get it:

tkyte@TKYTE816> create or replace java source   2  named "MyTimestamp"   3  as   4  import java.lang.String;   5  import java.sql.Timestamp;   6   7  public class MyTimestamp   8  {   9      public static String getTimestamp()  10      {  11        return (new  12           Timestamp(System.currentTimeMillis())).toString();  13      }  14  };  15 /      Java created.      tkyte@TKYTE816>  create or replace function my_timestamp return varchar2   2  as language java   3  name 'MyTimestamp.getTimestamp() return java.lang.String';   4  /      Function created.      tkyte@TKYTE816>  select my_timestamp,   2  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual   3  /      MY_TIMESTAMP              TO_CHAR(SYSDATE,'YY ------------------------- ------------------- 2001-03-27 19:15:59.688   2001-03-27 19:15:59

Possible Errors

Most of the errors you will encounter using this feature are related to compiling code, and parameter mismatches. Some of the more frequent ones are listed here.

ORA-29549 Java Session State Cleared

You will hit errors like this:

select my_timestamp, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual                                                                    * ERROR at line 1: ORA-29549: class TKYTE.MyTimestamp has changed, Java session state cleared 

continuously when developing. All it means is that the class you had used earlier in the session was recompiled, typically by you. Any state associated with that class is wiped out. You may simply re-run the statement that failed again, and a new state will be instantiated.

You should avoid reloading Java classes on an active production system for this reason. Any session that used the Java class will receive this error the next time they use the Java class again.

Permissions Errors

Just as we received previously:

ERROR at line 1: ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.io.FilePermission c:\temp read) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE)) ORA-06512: at "TKYTE.GET_DIR_LIST", line 0 ORA-06512: at line 1 

Fortunately, the error message has the explicit privileges you must obtain in order to be successful listed right there. You must have an appropriately privileged user use the DBMS_JAVA GRANT_PERMISSION routine for you.

ORA-29531 no method X in class Y

Taking the RunThis example from above and changing the call specification to:

tkyte@TKYTE816> create or replace   2  function RUN_CMD( p_cmd  in varchar2) return number   3  as   4  language java   5  name 'Util.RunThis(String[]) return integer';   7  /      Function created. 

will raise this error for me. Note that in the parameter list to Util.RunThis, I specified STRING, not java.lang.String.

tkyte@TKYTE816> exec rc('c:\winnt\system32\cmd.exe /c dir') java.lang.NullPointerException at oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions.java) at oracle.aurora.util.JRIExtensions.getMaximallySpecificMethod(JRIExtensions.java) BEGIN RC('c:\winnt\system32\cmd.exe /c dir'); END;      * ERROR at line 1: ORA-29531: no method RunThis in class Util ORA-06512: at "TKYTE.RUN_CMD", line 0 ORA-06512: at "TKYTE.RC", line 5 ORA-06512: at line 1 

The solution here is that you must use fully qualified type names in the mapping. Even though java.lang is implicitly imported into a Java program, it is not imported into the SQL layer. When you get this error, you need to look at your data type mapping and ensure that you are using fully qualified data types, and that they match the actual types exactly. The Java method to be used is found by its signature, and its signature is built from the data types used. The smallest difference in the inputs, the outputs, or the case of the name will cause the signatures to be different and Oracle will not find your code.

Summary

In this chapter, we explored how to implement stored procedures in Java. This does not mean that you should run out and re-code all of your PL/SQL in Java stored procedures. It does mean that when you hit the wall with what PL/SQL can do, typically when you attempt to reach out of the database and do operating system interactions, Java should be the first thing you think of.

Using the material presented in this chapter you should be able to pass all of the mainstream SQL datatypes from a PL/SQL layer down to Java, and send them back, including arrays of information. You have a handful of useful snippets of Java that can be of immediate use and as you peruse the Java documentation, you'll find dozens more that apply in your installation.

Used judiciously, a little Java can go a long way in your implementation.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net