A Stored Procedure Example

The following sample program shows how to call a PL/SQL stored procedure using the SQL92 syntax. The method prepareCall(String sql) creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure. The following stored function is defined in the program below: 

 SQL> CREATE OR REPLACE FUNCTION  raisesal
   2      ( name CHAR, raise NUMBER )  RETURN NUMBER IS
   3    BEGIN  RETURN raise + 100000;  END;

Raise (args[0]) =

            
// Import the following packages to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.sql.*;
import  oracle.jdbc.*;
import  oracle.jdbc.pool.OracleDataSource;

class  SPExample {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##userid";
    String password = "password";
    String database = "65.52.222.73:1521/cdb1";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      // 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 the web input.
      cstmt.setString( 2, "LESLIE" );  // The name argument is the second ?
      int  raise = new Integer( args[0].trim( ) ).intValue( );
      cstmt.setInt   ( 3, raise );     // The raise argument is the third ?

      // Do the raise.
      cstmt.execute( );

      // Get the new salary back.
      int  new_salary = cstmt.getInt( 1 );
      System.out.print( "The new salary is <i>" + new_salary + "</i>" );

      // Close the statement.
      cstmt.close( );
    }
    catch( SQLException e ) { System.out.println( e ); }

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