package examples.jdbc.oracle;

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

/**
 * This example demonstrates syntax and usage for Oracle
 * stored procedures and functions. The code shows how
 * to create and call stored procedures and functions
 * and how to register parameters.
 *<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% storedprocs.java</b></font>
 *<p>	
 *
 *<li>Run this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ java examples.jdbc.oracle.storedprocs</b></font>
 *
 *</ol> *
 * @author Copyright (c) 1996-2000 by BEA Systems, Inc. All Rights Reserved.
 */
public class storedprocs {
    
    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);
        
        // Create a stored proc
        Statement stmt1 = conn.createStatement();
        stmt1.execute("CREATE OR REPLACE PROCEDURE proc_squareInt " +
                      "(field1 IN OUT INTEGER, " +
                      " field2 OUT INTEGER) IS " + 
                      "BEGIN field2 := field1 * field1; " +
                      "field1 := field1 * field1; END proc_squareInt;");
        stmt1.close();
        
        // Native Oracle SQL is commented out here
        // String sql = "BEGIN proc_squareInt(?, ?); END;";
        
        // This is correct syntax as specified in the JDBC spec
        String sql = "{call proc_squareInt(?, ?)}";
        CallableStatement cstmt1 = conn.prepareCall(sql);
        
        // Register out parameters
        cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
        for (int i = 0; i < 5; i++) {
            cstmt1.setInt(1, i);
            cstmt1.execute();
            System.out.println(i + " " + cstmt1.getInt(1) + " " + cstmt1.getInt(2));
        }
        cstmt1.close();
        
        // Create a function
        Statement stmt2 = conn.createStatement();
        stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt (field1 IN INTEGER) " +
                      "RETURN INTEGER IS " +
                      "BEGIN return field1 * field1; END func_squareInt;");
        stmt2.close();
        
        // Native Oracle SQL is commented out here
        // sql = "BEGIN ? := func_squareInt(?); END;";
        
        // This is correct syntax as specified in the JDBC spec
        sql = "{ ? = call func_squareInt(?)}";
        CallableStatement cstmt2 = conn.prepareCall(sql);
        
        // Register out parameters
        cstmt2.registerOutParameter(1, java.sql.Types.INTEGER);
        for (int i = 0; i < 5; i++) {
            cstmt2.setInt(2, i);
            cstmt2.execute();
            System.out.println(i + " " + cstmt2.getInt(1) + " " + cstmt2.getInt(2));
        }
        cstmt2.close();
        
        // Always close the connection when work is done
        conn.close();
    }
    
}

