Stored Function With Record Set

Java Programming Tricks

Stored Function with Record Set:

The PL/SQL package java_demo defines the procedure list_emp(), using the IN parameter p_job and the result parameter as ResultSet, analogous to the attributes of the table EMP (schema scott/tiger). The ResultSet contains all the employees matching the defined job.

  // Prepare a PL/SQL call
     // FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;
     String strQuery = "{ ? = call java_demo.list_emp(?)}";
     CallableStatement cstmt = dbConn.prepareCall(strQuery);
 
     // Select all the SALESMAN person
     // Declare that the first ? is a return value
     // of type OBJECT (Cursor Ref.)
     cstmt.registerOutParameter(1, OracleTypes.CURSOR);
     cstmt.setString(2, "SALESMAN");
     cstmt.execute();
     ResultSet rset = (ResultSet)cstmt.getObject(1);
 
     // Loop the cursor
     while (rset.next()) {
       System.out.println(rset.getString("ename"));
     }
     
 This example shows the easy and smart way how to open and to use a cursor defined in 
 the PL/SQL package. Important in this case is the definition of the public cursor type:
 
 TYPE myreftype IS REF CURSOR RETURN emp%ROWTYPE;
 FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;