Samples of PL/SQL in JDBC

The following examples demonstrate the interoperability between PL/SQL and JDBC, contrasting standard SQL92 calling syntax with Oracle PL/SQL block syntax:

• Executing Procedures in PL/SQL Blocks--PLSQL.java

• Calling PL/SQL Stored Procedures--PLSQLExample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

For related discussion, see "PL/SQL Stored Procedures".

Calling PL/SQL Stored Procedures--PLSQLExample.java

This sample defines a stored function and executes it using SQL92 CALL syntax in a callable statement. The function takes an employee name and salary as input and raises the salary by a set amount.

/*
 * This sample shows how to call a PL/SQL stored procedure using the SQL92
 * syntax.  See also the other sample PLSQL.java.
 */

import java.sql.*;
import java.io.*;

class PLSQLExample
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a statement
    Statement stmt = conn.createStatement ();

    // Create the stored function
    stmt.execute ("create or replace function RAISESAL (name CHAR, raise NUMBER)
                 return NUMBER is begin return raise + 100000; end;");

    // Close the statement
    stmt.close();

    // Prepare to call the stored procedure RAISESAL.
    // This sample uses the SQL92 syntax
    CallableStatement cstmt = conn.prepareCall ("{? = call RAISESAL (?, ?)}");

    // Declare that the first ? is a return value of type Int
    cstmt.registerOutParameter (1, Types.INTEGER);

    // We want to raise LESLIE's salary by 20,000
    cstmt.setString (2, "LESLIE");  // The name argument is the second ?
    cstmt.setInt (3, 20000);        // The raise argument is the third ?
  
    // Do the raise
    cstmt.execute ();

    // Get the new salary back
    int new_salary = cstmt.getInt (1);

    System.out.println ("The new salary is: " + new_salary);

    // Close the statement
    cstmt.close();

    // Close the connection
    conn.close();
  }
}

Executing Procedures in PL/SQL Blocks--PLSQL.java

This sample defines PL/SQL stored procedures and functions and executes them from within Oracle PL/SQL BEGIN...END blocks in callable statements. Stored procedures and functions with input, output, input-output, and return parameters are shown.

/*
 * This sample shows how to call PL/SQL blocks from JDBC.
 */

import java.sql.*;

class PLSQL
{
  public static void main (String args [])
       throws SQLException, ClassNotFoundException
  {
    // Load the driver
    Class.forName ("oracle.jdbc.driver.OracleDriver");

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create the stored procedures
    init (conn);

    // Cleanup the plsqltest database
    Statement stmt = conn.createStatement ();
    stmt.execute ("delete from plsqltest");
 
    // Close the statement
    stmt.close();

    // Call a procedure with no parameters
    {
      CallableStatement procnone = conn.prepareCall ("begin procnone; end;");
      procnone.execute ();
      dumpTestTable (conn);
      procnone.close();
    }

    // Call a procedure with an IN parameter
    {
      CallableStatement procin = conn.prepareCall ("begin procin (?); end;");
      procin.setString (1, "testing");
      procin.execute ();
      dumpTestTable (conn);
      procin.close();
    }

    // Call a procedure with an OUT parameter
    {
      CallableStatement procout = conn.prepareCall ("begin procout (?); end;");
      procout.registerOutParameter (1, Types.CHAR);
      procout.execute ();
      System.out.println ("Out argument is: " + procout.getString (1));
      procout.close();
    }
    
    // Call a procedure with an IN/OUT prameter
    {
      CallableStatement procinout = conn.prepareCall 
                                    ("begin procinout (?); end;");
      procinout.registerOutParameter (1, Types.VARCHAR);
      procinout.setString (1, "testing");
      procinout.execute ();
      dumpTestTable (conn);
      System.out.println ("Out argument is: " + procinout.getString (1));
      procinout.close();
    }

    // Call a function with no parameters
    {
      CallableStatement funcnone = conn.prepareCall 
                                   ("begin ? := funcnone; end;");
      funcnone.registerOutParameter (1, Types.CHAR);
      funcnone.execute ();
      System.out.println ("Return value is: " + funcnone.getString (1));
      funcnone.close();
    }

    // Call a function with an IN parameter
    {
      CallableStatement funcin = conn.prepareCall 
                                 ("begin ? := funcin (?); end;");
      funcin.registerOutParameter (1, Types.CHAR);
      funcin.setString (2, "testing");
      funcin.execute ();
      System.out.println ("Return value is: " + funcin.getString (1));
      funcin.close();
    }

    // Call a function with an OUT parameter
    {
      CallableStatement funcout = conn.prepareCall 
                                  ("begin ? := funcout (?); end;");
      funcout.registerOutParameter (1, Types.CHAR);
      funcout.registerOutParameter (2, Types.CHAR);
      funcout.execute ();
      System.out.println ("Return value is: " + funcout.getString (1));
      System.out.println ("Out argument is: " + funcout.getString (2));
      funcout.close();
    }

    // Close the connection
    conn.close();
  }

  // Utility function to dump the contents of the PLSQLTEST table and
  // clear it
  static void dumpTestTable (Connection conn)
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from plsqltest");
    while (rset.next ())
      System.out.println (rset.getString (1));
    stmt.execute ("delete from plsqltest");
    rset.close();
    stmt.close();
  }

  // Utility function to create the stored procedures
  static void init (Connection conn)
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    try { stmt.execute ("drop table plsqltest"); } catch (SQLException e) { }
    stmt.execute ("create table plsqltest (x char(20))");
    stmt.execute ("create or replace procedure procnone 
                   is begin insert into plsqltest values ('testing'); end;");
    stmt.execute ("create or replace procedure procin (y char) 
                   is begin insert into plsqltest values (y); end;");
    stmt.execute ("create or replace procedure procout (y out char) 
                   is begin y := 'tested'; end;");
    stmt.execute ("create or replace procedure procinout (y in out varchar) 
                  is begin insert into plsqltest values (y); 
                  y := 'tested'; end;");

    stmt.execute ("create or replace function funcnone return char 
                   is begin return 'tested'; end;");
    stmt.execute ("create or replace function funcin (y char) return char 
                  is begin return y || y; end;");
    stmt.execute ("create or replace function funcout (y out char) return char
                 is begin y := 'tested'; return 'returned'; end;");
    stmt.close();
  }
}

Accessing a PL/SQL Index-by Table from JDBC--IndexTableExample.java

This code example shows special input binding and output registration methods in the IN, OUT (including function return values), and IN OUT parameter modes.

For a complete discussion of this topic, see Chapter 11, "Accessing PL/SQL Index-by Tables".

/*
 * This sample shows how to access PL/SQL index-by table from JDBC.
 */

import java.sql.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;

class IndexTableExample
{
  public static void main (String args [])
       throws SQLException, ClassNotFoundException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create the stored procedures.
    init (conn);

    // Bind a PL/SQL index-by table IN parameter.
    procin_test (conn);

    // Bind a PL/SQL index-by table OUT parameter, and access
    // the elements using JDBC default mapping.
    procout_test (conn);
    
    // Bind a PL/SQL index-by table IN/OUT parameter, and access
    // the elements using Oracle JDBC mapping.
    procinout_test (conn);
   
    // Call a function that returns a PL/SQL index-by table, and
    // accesses the elements using Java primitive type.
    func_test (conn);

    // Cleanup data structures 
    cleanup (conn);

    // Close the connection.
    conn.close();
  }

  /**
   * Utility function to dump the contents of the "testtab" table
   */
  static void dumpTestTable (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from testtab");
    while (rset.next ())
      System.out.println (rset.getInt (1));
    stmt.execute ("delete from testtab");
    rset.close();
    stmt.close();
  }

  /**
   * Utility function to create the stored procedures
   */
  static void init (Connection conn) throws SQLException
  {
    cleanup (conn);

    Statement stmt = conn.createStatement ();

    stmt.execute ("create table testtab (col1 number)");
    
    stmt.execute ("create or replace package demo_pck as type numbers_t is"+
                  "  table of number index by binary_integer; "+
                  "end;");

    stmt.execute ("create or replace procedure procin"+
                  "("+
                  "  p1 in demo_pck.numbers_t "+
                  ") is "+
                  "begin "+
                  "  for i in p1.FIRST..p1.LAST loop "+
                  "    insert into testtab values (i); "+
                  "  end loop; "+
                  "end;");
   
    stmt.execute ("create or replace procedure procout "+
                  "("+
                  "  p1 out demo_pck.numbers_t"+
                  ") is "+
                  "begin "+
                  "  p1(1):=4; p1(2):=5; p1(3):=6; "+
                  "end;");
    
    stmt.execute ("create or replace procedure procinout "+
                  "("+
                  "  p1 in out demo_pck.numbers_t "+
                  ") is "+
                  "begin "+
                  "  for i in p1.FIRST..p1.LAST loop "+
                  "    p1(i) := p1(i) + 6; "+
                  "  end loop; "+
                  "end;");
    
    stmt.execute ("create or replace function funcnone "+
                  "  return demo_pck.numbers_t is "+
                  "  n demo_pck.numbers_t; "+
                  "begin "+
                  "  n(1) := 10; n(2) := 11; n(3) := 12; "+
                  "  return n; "+
                  "end;");
                 
    stmt.close();
  }

  /**
   * Cleanup data structures created in this example
   */
  static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();

    try { 
      stmt.execute ("drop table testtab"); 
    } catch (SQLException e) {}

    try { 
      stmt.execute ("drop procedure procin"); 
    } catch (SQLException e) {}
    
    try { 
      stmt.execute ("drop procedure procout"); 
    } catch (SQLException e) {}
    
    try { 
      stmt.execute ("drop procedure procinout"); 
    } catch (SQLException e) {}
    
    try { 
      stmt.execute ("drop procedure funcnone"); 
    } catch (SQLException e) {}
    
    try { 
      stmt.execute ("drop package demo_pck"); 
    } catch (SQLException e) {}

    stmt.close ();
  }

  /**
   * Bind a PL/SQL index-by table IN parameter.
   */
  static void procin_test (Connection conn) throws SQLException
  {
    System.out.println ("procin_test () ");

    // Prepare the statement 
    OracleCallableStatement procin = (OracleCallableStatement)
      conn.prepareCall ("begin procin (?); end;");

    // index-by table bind value
    int[] values = { 1, 2, 3 };
    
    // maximum length of the index-by table bind value. This 
    // value defines the maximum possible "currentLen" for batch 
    // updates. For standalone binds, "maxLen" should be the 
    // same as "currentLen".
    int maxLen = values.length;
      
    // actual size of the index-by table bind value
    int currentLen = values.length;

    // index-by table element type
    int elemSqlType = OracleTypes.NUMBER;

    // index-by table element length in case the element type
    // is CHAR, VARCHAR or RAW. This value is ignored for other
    // types.
    int elemMaxLen = 0; 
      
    // set the value
    procin.setPlsqlIndexTable (1, values, 
                               maxLen, currentLen, 
                               elemSqlType, elemMaxLen);

    // execute the call
    procin.execute ();
    
    // verify the result
    dumpTestTable (conn);

    // close the statement
    procin.close();
  }
  
  /**
   * Bind a PL/SQL index-by table OUT parameter, and accesses
   * the value using JDBC default mapping.
   */
  static void procout_test (Connection conn) throws SQLException
  {
    System.out.println ("procout_test () ");

    OracleCallableStatement procout = (OracleCallableStatement)
      conn.prepareCall ("begin procout (?); end;");

    // maximum length of the index-by table bind value. This 
    // value defines the maximum table size in the OUT parameter.
    int maxLen = 10;

    // index-by table element type
    int elemSqlType = OracleTypes.NUMBER;

    // index-by table element length in case the element type
    // is CHAR, VARCHAR or RAW. This value is ignored for other
    // types
    int elemMaxLen = 0;

    // register the OUT parameter
    procout.registerIndexTableOutParameter (1, maxLen, 
                                            elemSqlType, elemMaxLen);

    // execute the call
    procout.execute ();

    // access the value using JDBC default mapping
    BigDecimal[] values = 
      (BigDecimal[]) procout.getPlsqlIndexTable (1);

    // print the elements
    for (int i=0; i<values.length; i++)
      System.out.println (values[i].intValue());
      
    // close the statement
    procout.close();
  }

  /**
   * Bind a PL/SQL index-by table IN/OUT parameter, and access
   * the value using Oracle JDBC mapping (Datum mapping).
   */
  static void procinout_test (Connection conn) throws SQLException
  {
    System.out.println ("procinout_test () ");

    OracleCallableStatement procinout = (OracleCallableStatement)
      conn.prepareCall ("begin procinout (?); end;");

    // index-by table IN bind value
    int[] values = { 1, 2, 3 };

    // maximum length of the index-by table bind value. This 
    // value defines the maximum possible "currentLen" for batch 
    // updates. For standalone binds, "maxLen" should be the 
    // same as "currentLen".
    int maxLen = values.length;
      
    // actual size to bind index-by table
    int currentLen = values.length;

    // index-by table element type
    int elemSqlType = OracleTypes.NUMBER;

    // index-by table element length in case the element type
    // is CHAR, VARCHAR or RAW. This value is ignored for other
    // types.
    int elemMaxLen = 0; 
      
    // set the IN value
    procinout.setPlsqlIndexTable (1, values, 
                                  maxLen, currentLen, 
                                  elemSqlType, elemMaxLen);



    // maximum length of the index-by table OUT bind value. This 
    // value defines the maximum table size in the OUT parameter.
    int maxOutLen = 10;

    // register the OUT parameter
    procinout.registerIndexTableOutParameter (1, maxOutLen, 
                                              elemSqlType, elemMaxLen);
    // execute the call
    procinout.execute ();

    // access the value using Oracle JDBC mapping
    Datum[] outvalues = procinout.getOraclePlsqlIndexTable (1);

    // print the elements
    for (int i=0; i<outvalues.length; i++)
      System.out.println (outvalues[i].intValue());

    // close the statement
    procinout.close();
  }

  /**
   * Call a function that returns a PL/SQL index-by table, and
   * access the value as a Java primitive array.
   */
  static void func_test (Connection conn) throws SQLException 
  {
    System.out.println ("func_test () ");

    OracleCallableStatement funcnone = (OracleCallableStatement)
      conn.prepareCall ("begin ? := funcnone; end;");

    // maximum length of the index-by table value. This 
    // value defines the maximum table size to be returned.
    int maxLen = 10;

    // index-by table element type
    int elemSqlType = OracleTypes.NUMBER;

    // index-by table element length in case the element type
    // is CHAR, VARCHAR or RAW. This value is ignored for other
    // types
    int elemMaxLen = 0;

    // register the return value
    funcnone.registerIndexTableOutParameter (1, maxLen, 
                                             elemSqlType, elemMaxLen);
    // execute the call
    funcnone.execute ();

    // access the value as a Java primitive array.
    int[] values = (int[]) 
      funcnone.getPlsqlIndexTable (1, java.lang.Integer.TYPE);

    // print the elements
    for (int i=0; i<values.length; i++)
      System.out.println (values[i]);
 
    // close the statement 
    funcnone.close();
  }
}

---