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.