PL/SQL   from   Java     
       
          Calling PL/SQL from Java – Tutorial  

 

 

 

Introduction

 

This article outlines how to call a PL/SQL subprogram from a Java program. It’s aimed at those PL/SQL programmers who have no previous experience of Java, and those Java programmers who have no previous experience of PL/SQL (to call Java from PL/SQL see this article).

 

 

Locate the Driver JAR File

 

The methods that allow Java to access PL/SQL (using the JDBC approach) are contained in an Oracle JDBC “jar” file (“ojdbc5.jar” for Java 1.5, and “ojdbc6.jar” for Java 1.6).

 

Most installations of an Oracle product will probably already have both of these “jar” files tucked away in some subdirectory under Oracle Home. The directory is usually called “jdbc\lib”; for example, in the case of installations of OBI and the RDBMS the respective paths should be similar to the following:

 

*  <Oracle BI Home>\Oracle_BI1\jdbc\lib

*  <Oracle RDBMS Home>\product\11.1.0\db_1\jdbc\lib

 

If you can’t locate a local copy, then the Oracle JDBC “jar” files for various versions of Java can be downloaded from Oracle’s JDBC Drivers web page.

 

 

Set the Classpath

 

The java compiler (“javac”) needs to be able to the find the Oracle JDBC “jar” file so that it can resolve references to the classes contained therein. To provide this access, the path to the “jar” file should be added to the classpath (which should also contain the path to the directory containing the java source code of the program that calls the PL/SQL subprogram). For example, if the source code is contained in directory “C:\Scratch” and the path to the “jar” file is “C:\OBI11G\Oracle_BI1\jdbc\lib”, then the class path would be set as follows:

 

*  set classpath=C:\Scratch;C:\OBI11G\Oracle_BI1\jdbc\lib\ojdbc6.jar

 

 

Create the PL/SQL Subprogram

 

Next, either create the PL/SQL subprogram to be called from Java or locate a schema containing an existing subprogram. For this article, we’ll use the following sample subprogram:

            CREATE OR REPLACE FUNCTION javatest ( 
               p_parm1 varchar2, 
               p_parm2 out varchar2 
            ) return varchar2
            IS
               
            BEGIN
               p_parm2 := 'This is the value set for parm2';
               return 'The value received for parm1 is: <' || p_parm1 || '>';
            END;

which contains all the key elements involved in parameter passing: (1) a parameter value that is passed into the PL/SQL subprogram at runtime, “p_parm1”; (2) a parameter value that is passed out of the program using the “out” value passing mechanism, “p_parm2”; and (3) a value that is passed out of the program using the function return value passing mechanism.

 

 

Create the Java Program

 

Imports

 

The import statement:

            import java.sql.*;

is required at the beginning of the Java class file.

 

Driver Registration

 

A new instance of the Oracle JDBC driver must be created and registered with the driver manager:

            DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() ); 

Connect to the Database

 

Create a connection to the Oracle database:

            Connection vDatabaseConnection = 
               DriverManager.getConnection (
                  "jdbc:oracle:thin:@<host>:<port>:<sid>", 
                  "<schema>", 
                  "<schema password>"
               );

where “<host>” is the name of the database server (for example, “localhost”), “<port>” is the listener port (for example, the default “1521”), and “<sid>” is the database SID (for example, the default “orcl”).

 

Prepare a Callable Statement

 

The next step is to provide a template for the PL/SQL subprogram call. There are two formats available, the familiar block syntax used by Oracle and the ANSI 92 standard syntax. In the case of our sample program, the block syntax has the form:

            CallableStatement vStatement = 
               vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" );

while the ANSI 92 syntax has the form:

            CallableStatement vStatement = 
               vDatabaseConnection.prepareCall( "{ ? = call javatest( ?, ? ) }" );

In both these templates, the question mark, “?”, acts a placeholder for data that will be passed either into, or back from, the PL/SQL subprogram at runtime. Subsequently, the occurrences of “?” are referenced numerically left-to-right, starting with an index value of “1”. So, the function return value corresponds to index “1”; the value of “p_parm1” passed into “javatest” corresponds to index “2”, and the value of “p_parm2” passed back from “javatest” corresponds to index “3”.

 

Set Input Parameter Values and Declare Return Types

 

The Java program needs to know the values of any parameters that should be passed into the subprogram. In this case, it needs to know the value of “p_parm1”, which we’ll pass in as the text string “input value”:

            vStatement.setString( 2, "input value" );

Note, that the index of “p_parm1” is “2”, as discussed above.

 

The Java program needs to know the data types of any values that will be passed back from the PL/SQL subprogram to Java. In this case, it needs to know the return type of the function:

            vStatement.registerOutParameter( 1, Types.VARCHAR );

and the return type of parameter “p_parm2”:

            vStatement.registerOutParameter( 3, Types.VARCHAR );

Note, that the type for a “varchar2” parameter is “varchar” and not “varchar2” (see the Javadoc documentation for the mappings used by other Oracle types).

 

Call the PL/SQL Subprogram

 

With the template for the PL/SQL subprogram in place, the input parameter value defined, and the return types defined, we can finally make a call to the PL/SQL subprogram:

            vStatement.executeUpdate();

 

Extract the Returned Values

 

We can then copy the values returned from the PL/SQL subprogram into Java variables:

            String vFunctionReturnValue = vStatement.getString( 1 );   
            String vOutParameterValue = vStatement.getString( 3 );

Note, as above, all references to the parameters are with respect to the occurrences of “?” in index order as defined in the template.

 

Close the Connection

 

As we opened a connection to the database at the beginning, now we must close it:

            vDatabaseConnection.close();

 

 

Sample Java Program

 

The complete Java program to call our “javatest” PL/SQL function is as follows:

            import java.sql.*; 
            
            public class PLSQLFromJava { 
            
               public static void main( String[] args) throws SQLException { 
               
                  // INITIALIZE ORACLE DATABASE DRIVER
                     DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver() ); 
            
                  // CONNECT TO DATABASE ( "jdbc:oracle:thin:@<host>:<port>:<sid>", "<schema>", "<schema password>" )
                     Connection vDatabaseConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "test", "testpwd" ); 
            
                  // PREPARE PL/SQL FUNCTION CALL ("?" IS A PLACEHOLDER FOR RETURN VALUES AND PARAMETER VALUES, NUMBERED FROM LEFT TO RIGHT)
                     CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" ); 
                        // USE EITHER THE "BLOCK" SYNTAX ABOVE, OR "ANSI 92" SYNTAX BELOW
                     //CallableStatement vStatement = vDatabaseConnection.prepareCall( "{ ? = call javatest( ?, ? ) }" ); 
            
                  // SET INPUT PARAMETERS AND DECLARE TYPES FOR RETURN VALUES
                     vStatement.registerOutParameter( 1, Types.VARCHAR );   // declare type of function return value "begin ? ..."
                     vStatement.setString( 2, "input value" );              // set value of first function parameter "... javatest( ?, ..."
                     vStatement.registerOutParameter( 3, Types.VARCHAR );   // declare type of second function parameter "..., ? ); end;" 
            
                  // CALL THE PL/SQL FUNCTION
                     vStatement.executeUpdate();
                     
                  // GET THE FUNCTION RETURN VALUE AND THE OUT PARAMETER VALUE      
                     String vFunctionReturnValue = vStatement.getString( 1 );   // get the "?" in "begin ? ..."  
                     String vOutParameterValue = vStatement.getString( 3 );     // get the "?" in "..., ? ); end;"     
            
                  // CLOSE THE DATABASE CONNECTION
                     vDatabaseConnection.close(); 
            
                  // VERIFY SUCCESSFUL EXECUTION
                     System.out.println( vOutParameterValue ); 
                     System.out.println( vFunctionReturnValue ); 
            
               } 
            
            }

Assuming that the program is stored in file “PLSQLFromJava.java”, we can compile it from the command line using:

            javac PLSQLFromJava.java

and run it using:

            java PLSQLFromJava

to produce the output:

            This is the value set for parm2
            The value received for parm1 is: <input value>

 

 

What Next

 

This simple example should be enough to get started when it comes to calling PL/SQL subprograms from Java. For the full details of the functionality available see the API Javadocs.