Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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();
      }
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index