Window 2 World

This program shows how to return the cursor from the oracle function and how to access it in java as a resultset

ORACLE CODE
------------
step 1

package TestORA;
CREATE OR REPLACE PACKAGE TYPES
AS
TYPE cursorType IS REF CURSOR;
END;


step 2

package TestORA;
CREATE OR REPLACE FUNCTION temp_comp RETURN TYPES.cursortype
AS
l_cursor TYPES.cursortype ;
BEGIN
OPEN l_cursor FOR SELECT USER_GROUP,company_code FROM COMPANY;
RETURN l_cursor;
END;


JAVA CODE
----------


import java.sql.*;
import oracle.jdbc.driver.*;

class TestResult{

public static void main(String args[]){

TestResult objTestResult = new TestResult();

}

TestResult(){

try{

String driver = "oracle.jdbc.driver.OracleDriver";
String constr = "jdbc:oracle:thin:@ipaddress:1521:servicename;
Connection conn;
Class.forName(driver);
conn = DriverManager.getConnection(constr, "username", "password");

String query = "begin :1 := temp_comp; end;";

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);

while (rset.next ())
System.out.println( rset.getString (1) );

cstmt.close();

System.out.println("Coonected to data base");

conn.close();

}catch(ClassNotFoundException eClassNotFoundException){

eClassNotFoundException.printStackTrace();

}catch(SQLException eSQLException){

eSQLException.printStackTrace();
}

}

}