Please see following example which I borrow now from one
of my previous assignments:
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class retrefcur_samp
{
public static void main (
String args [])
// throws SQLException
{
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connection URL.
Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@pavilion03:1529:ora8i", "you", "me");
init (conn);
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "*");
call.execute ();
OracleResultSet rset = (OracleResultSet)call.getObject (1);
while (rset.next ()){
System.out.println (rset.getString ("JOB"));
}
rset.close();
call.close();
} catch(SQLException esql) {
System.out.println("Trouble in river city ");
System.out.println(esql.toString());
}
}
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; " +
" return rc; " +
" end; " +
"end java_refcursor;");
stmt.execute ("create or replace package java_ref_clean as " +
" type myrctype is ref cursor; " +
" function cleanup (cc myrctype) return number; " +
"end java_ref_clean;");
stmt.execute ("create or replace package body java_ref_clean as " +
" function cleanup (cc myrctype) return number is "+
" result number; " +
" begin " +
" close cc; " +
" end; " +
"end java_ref_clean;");
}
}
------------------
andre van winssen
awinssen@xs4all.nl