Popular Posts
Generate subversion diff report using python bash: svn --diff-cmd "python" --extensions "diff_to_html.py" diff -r 596:671 diff_to_html.py import sys import diff... JSRequest, Get parameters from querystring with javascript in SharePoint Provides method to parse query string, filename, and pathname from URL // Initialize first JSRequest.EnsureSetup(); // Get the current fil... ROBOCOPY: Robust File Copy for Windows -------------------------------------------------------------------------------    ROBOCOPY     ::     Robust File Copy for Windows --------...
Blog Archive
Stats
Java: ResultSet on store procedure
Oracle
CREATE OR REPLACE PROCEDURE get_result (
    start_pattern IN VARCHAR2,
    end_pattern IN VARCHAR2,
    result_set OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN result_set FOR
        SELECT
            sheet_id
        FROM checksheet_with_process
        WHERE
            sheet_id >= start_pattern
            AND sheet_id <= end_pattern
        ORDER BY sheet_id DESC;

END;

-- test 
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE;
    get_sheet_print_list('20100923','20101023',result_set);
    
    LOOP
        FETCH result_set INTO
            sheet_id
        ;
        EXIT WHEN result_set%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(sheet_id);
    END LOOP;
END;
ProcedureResult.java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcedureResult {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("connection string");
            stmt = conn.prepareCall("CALL get_result(?,?,?)");
            stmt.setString(1, "20101001");
            stmt.setString(2, "20101031");
            stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
            stmt.execute();
            rs = (ResultSet) stmt.getObject(3);

            while (rs != null && rs.next()) {
                System.out.println(rs.getString("sheet_id"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e2) {
                    // TODO Auto-generated catch block
                    e2.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

}