Accessing %ROWTYPE of Stored Procedure from Java

Question

I have a stored procedure which 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 am calling 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 can't directly reference a row type in a JDBC call, as row types are only valid within PL/SQL and all the types used by the driver have to be defined at SQL level.

You could define your own SQL object type that shadows your table structure (which you'll have to remember to update if the table is altered), and a wrapper procedure that takes that type and converts it into a call to your real procedure. This is a demo based on dual, since I don't know your real table structure:

create type ni_imsi_rowtype as object (dummy varchar2(1)) -- use your real table's 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 a 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 can't modify your real package then you could create a new one for the wrapper, or a simple procedure; or you could even do the conversion in an anonymous block, though that makes the Java code more complicated:

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 binding the same struct so the SQL type is still required. Only the statement changes, but it can now call the original procedure without a wrapper.