Oracle REF CURSOR
With the REF_CURSOR you can return a recordset/cursor from a stored
procedure.
There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and
length need
to be known at compile time.
For the weak ref cursor the structure does not need to be known at
compile time.
The strong ref_cursor and until Oracle 9i also the weak-type need to be
declared in a package structure lik this:
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
The pl/sql procedure that returns a ref-cursor looks like this:
/** until Oracle 9 */
create or replace procedure test( p_deptno IN number
, p_cursor OUT
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning
REF_CURSOR.
/** From Oracle 9 */
create or replace procedure test( p_deptno IN number
, p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
/* Strong type */
create or replace procedure test( p_deptno IN number
, p_cursor OUT REFCURSOR_PKG.STRONG
REF_CURSOR)
is
begin
open p_cursor FOR
select *
from emp
where deptno = p_deptno;
end test;
Selecting the ref_cursor from JDBC
To get the cursor from Java you can use the following JDBC-code:
public void method() throws SQLException{
Connection conn = getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
int deptno = 10;
Object temp;
try{
cstmt = conn.prepareCall("begin test(?,?); end;");
cstmt.setInt(1, deptno);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(2);
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
while (rs.next()){
for (int j=0;j< columnCount;j++){
temp = rs.getObject(j+1);
}
}
} finally {
if (!rs==null){
rs.close();
}
if (!stmt==null){
stmt.close();
}
if (!conn==null){
conn.close();
}
}
}
Calling ref-cursor from pl/sql
create or replace procedure test_call is
c_cursor REFCURSOR_PKG.STRONG REF_CURSOR;
r_emp c_emp%rowtype;
begin
test(10,c_cursor);
loop
fetch c_cursor into r_emp;
exit when c_cursor%notfound;
dbms_output.put_line(r_emp.name);
end loop;
close c_cursor;
end test_call;
|
for Oracle developers