Access% ROWTYPE stored procedure with Java

Question

I have a stored procedure that looks like :

PROCEDURE get_curx( p_buf IN ni_imsi%ROWTYPE, p_bufx IN ni_imsi%ROWTYPE, p_cur OUT CurTyp, p_where IN VARCHAR2 DEFAULT '', p_orderby IN VARCHAR2 DEFAULT '', p_max IN NUMBER DEFAULT 0, p_lock IN NUMBER DEFAULT 0, p_hint IN VARCHAR2 DEFAULT 'NONE', p_debug IN NUMBER DEFAULT 0, p_count IN BOOLEAN DEFAULT FALSE); 

I call this procedure from a JAVA program like this :

 CallableStatement cs = connection.prepareCall("{call ni_imsi_pkg.get_curx(?,?,?,?,?,?)}"); cs.setObject( 1, ? ); // i have no clue what to mention here cs.setObject( 2, ? ); //i have no clue what to mention here cs.registerOutParameter(3, OracleTypes.CURSOR); cs.setString(4, " WHERE current_state = 60000 AND rownum <= 2 "); cs.setString(5, " ORDER BY imsi_number"); cs.setInt(6, 5); 

But I have no idea how to set the first two parameters . Please help me. thanks

Answer 1

As Mike said, you cannot directly refer to a row type in a JDBC call, since row types are valid only in PL / SQL, and all types used by the driver must be defined at the SQL level.

You can define your own type of SQL object that obscures your table structure (which you will need to remember in order to update if the table has been modified), and a wrapper procedure that accepts this type and converts it into a call to your real procedure. This is a double based demo, since I don't know your real table structure:

 create type ni_imsi_rowtype as object (dummy varchar2(1)) -- use your real table columns/types / create package ni_imsi_pkg as procedure get_curx(p_buf dual%rowtype, p_cur out sys_refcursor); procedure get_curx_wrapper(p_buf ni_imsi_rowtype, p_cur out sys_refcursor); end ni_imsi_pkg; / create package body ni_imsi_pkg as -- original procedure, simplified for demo procedure get_curx(p_buf dual%rowtype, p_cur out sys_refcursor) is begin open p_cur for select * from dual where dummy = p_buf.dummy; end; -- wrapper procedure taking new type instead of rowtype procedure get_curx_wrapper(p_buf ni_imsi_rowtype, p_cur out sys_refcursor) is l_buf dual%rowtype; begin l_buf.dummy := p_buf.dummy; get_curx(l_buf, p_cur); end; end ni_imsi_pkg; / 

Then, on the Java side, you can populate and send this as STRUCT:

 // Object array containing the values corresponding to your row type Object[] rowObj = { "X" }; // Struct based on the SQL type you created StructDescriptor structDesc = StructDescriptor.createDescriptor("NI_IMSI_ROWTYPE", conn); STRUCT rowStruct = new STRUCT(structDesc, conn, rowObj); // Call wrapper function instead of real one cs = conn.prepareCall("{ call ni_imsi_pkg.get_curx_wrapper(?,?) }"); // Pass the struct defined earlier cs.setObject(1, rowStruct); cs.registerOutParameter(2, OracleTypes.CURSOR); // and other arguments for your real calll 

If you cannot change your real package, you can create a new one for the wrapper or a simple procedure; or you can even make the conversion in an anonymous block, although this makes Java code more complex:

 cs = (OracleCallableStatement) conn.prepareCall( "declare l_typ ni_imsi_rowtype; l_buf dual%rowtype; " + "begin l_typ := ?; l_buf.dummy := l_typ.dummy; ni_imsi_pkg.get_curx(l_buf, ?); " + "end;" ); 

... still binds the same structure, so the SQL type is still required. Only the statement is changed, but now it can call the original procedure without a shell.