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

---