wood burning stoves*
The moose likes JDBC and the fly likes Calling oracle pl/sql procedure that has an in out ref cursor parameter Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Calling oracle pl/sql procedure that has an in out ref cursor parameter" Watch "Calling oracle pl/sql procedure that has an in out ref cursor parameter" New topic
Author

Calling oracle pl/sql procedure that has an in out ref cursor parameter

Michael ness
Greenhorn

Joined: Sep 09, 2002
Posts: 7
Hi,
Im trying to call a pl/sql procedure from jsp.
The procedure is defined as follows.
get_cc_type_hierarchy
( p_parent_type_id IN ccr_cc_type.cc_type_id%TYPE := NULL,
p_exclude_type_id IN ccr_cc_type.cc_type_id%TYPE := NULL,
p_prequal_required_flag IN ccr_cc_type.prequal_required_flag%TYPE := NULL,
p_indent_chars IN NUMBER := 0,
p_cursor IN OUT ccr_global.t_ref_cursor )
I call the procedure as follows
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("jdbc/pool/OracleDS");
Connection conn = ds.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
cs = conn.prepareCall("call lookup.get_cc_type_hierarchy(?,null,?,?,?)");
cs.setInt(1,(int) 1);
cs.setString(2,(String) "N" );
cs.setInt(3,(int) 2 );
cs.registerOutParameter(4, OracleTypes.CURSOR);
cs.execute();
rs = ((OracleCallableStatement)cs).getCursor(4);
while(rs.next())
{
out.print( rs.getString(1));
}
My problem is whenever it gets to cs.execute I get the following oracle error
ORA-01001: invalid cursor
I am assuming this is because the procedure wants an in out parameter passed but im only using an out parameter.
Does anyone know if it's possible (and how?) to call a procedure like this or whether Im doing something else wrong?
Thanks.
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8903

Why don't u make it as just OUT parameter.


Groovy
Michael ness
Greenhorn

Joined: Sep 09, 2002
Posts: 7
The procedure also needs to be called for visual basic which apparently requires an in out parameter.
It doesn't matter anyway it seems the problem has something to do with trying to call the procedure across a database link. I created the procedure in my local database and could run it with the code I posted earlier no problem.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling oracle pl/sql procedure that has an in out ref cursor parameter
 
Similar Threads
How to use IN OUT parameter
Oracle wrong number or types of arguments...
Cannot get Oracle CURSOR from Oracle procedure in Websphere
Retrieiving OUT parameters & return value from a Sybase Stored Procedure
How to create Oracle stored procedure and return ResultSet as OUT param...?