• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Michael ness
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't u make it as just OUT parameter.
 
Michael ness
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic