package examples.jdbc.oracle;

import java.sql.*;
import java.util.Properties;

/**
 * This type of cursor support exists only in Oracle 7.2 
 * and later. Refer to the file "spcursors.sql" for the
 * PL/SQL that defines these stored procedures.
 *
 *<h3>To set up this example:</h3>
 *<ol>
 *<li>Set up your development shell as described in <a
 *href=../../../techstart/environment.html>Setting up your environment</a>.
 *<p>
 *<li> Change connection parameters to correspond to your Oracle configuration.
 * If you need more help, check the section on connecting
 *to a database in the Developers Guide, <a
 *href=../../../classdocs/API_joci.html>Using WebLogic jDriver for Oracle</a>.
 *<p>
 *
 *<li>Compile this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ javac -d %CLIENT_CLASSES%
 *spcursors.java</b></font> <p> <li>Execute the stored procedures in the
 *file <font face="Courier New" size=-1>spcurslrs.sql</font> in the same
 *directory as this example using Oracle SQL/Plus. Use the DEMO
 *database.
 *
 *<p>
 *<li>Run this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ java examples.jdbc.oracle.spcursors</b></font>
 *
 *</ol>
 * @author Copyright (c) 1996-2000 by BEA Systems, Inc.  All Rights Reserved.
 */

public class spcursors {
  
  public static void main(String[] argv)
       throws Exception
  {

      Properties props = new Properties();
      props.put("user","scott");
      props.put("password","tiger");
      props.put("server","DEMO");
      
      Driver myDriver = (Driver)
          Class.forName("weblogic.jdbc.oci.Driver").newInstance();
      Connection conn = myDriver.connect("jdbc:weblogic:oracle", props);
    
    // Here we prepare a CallableStatement using a WebLogic extension
    // to JDBC that supports binding an Oracle cursor to an output
    // parameter.  Register the output parameter type as OTHER . . . 
    weblogic.jdbc.common.OracleCallableStatement cstmt = 
      (weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN OPEN ? FOR select * from emp; END;");
    cstmt.registerOutParameter(1, java.sql.Types.OTHER);
    
    // . . . and execute it. Retrieve the results into a ResultSet,
    // print it and close it. Then close the CallableStatement.  Note
    // that the getResultSet(int) method is a WebLogic extension for
    // retrieving the results of a stored procedure.
    cstmt.execute();
    ResultSet rs = cstmt.getResultSet(1);
    printResultSet(rs);
    rs.close();
    cstmt.close();
    
    weblogic.jdbc.common.OracleCallableStatement cstmt2 = 
      (weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN single_cursor(?, ?); END;");
    cstmt2.registerOutParameter(1, java.sql.Types.OTHER);
    
    // You can also set input parameters, execute the statement,
    // retrieve the query into a ResultSet, and print it
    // for each set of input parameters.
    cstmt2.setInt(2, 1);  
    cstmt2.execute();
    rs = cstmt2.getResultSet(1);
    printResultSet(rs);
    
    cstmt2.setInt(2, 2);  
    cstmt2.execute();
    rs = cstmt2.getResultSet(1);
    printResultSet(rs);
    
    cstmt2.setInt(2, 3);  
    cstmt2.execute();
    rs = cstmt2.getResultSet(1);
    printResultSet(rs);
    
    // Close the CallableStatement
    cstmt2.close();
    
    // Here we prepare the CallableStatement,
    // register its output parameters (note the use of type OTHER
    // to bind the parameter to an Oracle cursor) . . . and execute it.
    weblogic.jdbc.common.OracleCallableStatement cstmt3 = 
      (weblogic.jdbc.common.OracleCallableStatement)conn.prepareCall("BEGIN multi_cursor(?, ?, ?); END;");
    cstmt3.registerOutParameter(1, java.sql.Types.OTHER);
    cstmt3.registerOutParameter(2, java.sql.Types.OTHER);
    cstmt3.registerOutParameter(3, java.sql.Types.OTHER);
    
    cstmt3.execute();
    
    // Create a ResultSet for each set of parameters
    ResultSet rs1 = cstmt3.getResultSet(1);
    ResultSet rs2 = cstmt3.getResultSet(2);
    ResultSet rs3 = cstmt3.getResultSet(3);
    
    // Print the ResultSets
    printResultSet(rs1);
    printResultSet(rs2);
    printResultSet(rs3);
    
    // Always close objects like Statements and ResultSets
    // when you've finished using them.
    rs1.close();
    rs2.close();
    rs3.close();
    
    // Close the CallableStatement
    cstmt3.close();
    
    // Close the connection to the DBMS
    conn.close();
  }
  
  static void printResultSet(ResultSet rs) 
       throws SQLException
  {
    int rowcount = 0;
    while(rs.next()) rowcount++;
    System.out.println("Records = " + rowcount  );
  }
}

