Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Procedure returning Ref Cursor.

 
Pratibha Malhotra
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am looking for a procedure that returns ref cursor as return type.
I have Oracle8i Enterprise Edition Release 8.1.7.0.0 installed.
after executing following program I get PLS-00905: object APS.JAVA_REFCURSOR is invalid as error
In which version was ref cursor introduced

class RefCursorExample
{
public static void main (String args []) throws SQLException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(........);
init (conn);
CallableStatement call = conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "SALESMAN");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1);
while (rset.next ())
System.out.println (rset.getString ("ENAME"));
rset.close();
call.close();
conn.close();
}
// Utility function to create the stored procedure
static void init (Connection conn) throws SQLException
{ Statement stmt = conn.createStatement ();
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;");
stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from emp where job = j; " +
" return rc; " +
" end; " +
"end java_refcursor;");
stmt.close();
System.out.println("Procedure Created");
}
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic