How to pass a cursor variable to a Oracle stored procedure

[bheemsen]

Can I pass a cursor variable to a Oracle stored procedure from the host environment ? We are using JDBC to call the stored procedures. Our environment is Unix(AIX), Oracle 8.1.7, Java and JDBC. the host environment is Java. The database server and host environment server are not the same. I need to call a stored procedure using JDBC from Java by passing a cursor. The cursor should be the input parameter. I appreciate if some one can help me on this or refer to any pointers or references.

thanx..
-Bheemsen

 

[vgg]

hi,

Hope this example helps you.


/*
* This sample shows how to call a PL/SQL procedure that opens
* a cursor and get the cursor back as a Java ResultSet.
*/

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

class RefCursorExample
{
public static void main (String args [])
throws SQLException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn =
DriverManager.getConnection ("jdbcracleci8", "scott", "tiger"

// Create the stored procedure
init (conn);

// Prepare a PL/SQL call
CallableStatement call =
conn.prepareCall ("{call java_refcursor.job_listing (?,?)}"

// Find out all the SALES person
call.registerOutParameter (1, OracleTypes.CURSOR);

call.setString (2, "SALESMAN"
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1);

// Dump the cursor
while (rset.next ())
System.out.println (rset.getString ("ENAME&quot);

// Close all the resources
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 refcurtype is ref cursor return EMP%ROWTYPE; " +
" procedure job_listing (a_rc in out refcurtype,a_job in varchar2) ; " +
"end java_refcursor;"

stmt.execute ("create or replace package body java_refcursor as " +
" procedure job_listing (a_rc in out refcurtype, a_job in varchar2) is " +
" v_rc refcurtype; " +
" begin " +
" open a_rc for select * from emp where job = a_job; " +
" end; " +
"end java_refcursor;"
stmt.close();
}
}


regards,
VGG

 

[bheemsen]

Thanx VGG. But I don't see a cursor as an input paramter to the stored procedure. Your example shows only as an output paramter. If passing a cursor (host environment defined) is possible, I would also like to know how to handle that cursor inside the stored procedure. I mean, do I need to declare it again inside the stored procedure, fetch it and open it etc..

thanx for any help.

-Bheem

 

[vgg]

Hi,
Actually I have declared the cursor variable as an IN OUT parameter and I have defined the cursor itself in the stored procedure.
I am returning the cursor to a ResultSet object in the host(java) program.
You can change the cursor definition inside the stored procedure by using an IF..THEN loop.
But I don't think it is possible to declare a cursor variable explicitly and pass it to stored procedure.
You can only open a cursor varible either in a stored procedure or within an anonymous PL/SQL block.

Please let me know if you have some other ideas.

VGG