• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic