java call oracle procedure with %rowtype parameter

java, oracle, rowtype, stored-procedures

Solution

This is possible but it's not really straightforward. You have to create something of type USER%ROWTYPE at runtime and use that to call your stored procedure. Take a look here for details.

To get output values as well, you have to do something extra, along the line of Sumit's comment. Basically, after your procedure call, you open a cursor that selects the relevant data from the `USER` parameter.

So you get a database statement as follows (pseudocode):

string sql =
   "declare
        user_param user%rowtype;
    begin
        -- Set necessary parameters 
        user_param.col0 := :p0In;
        user_param.col1 := :p1In;
        ...

        -- Call procedure.
        cleansing(io_user => user_param);

        -- Read necessary output values into cursor.
        open :pOut for select user_param.col99 as col99
                              user_param.col98 as col98
                              ...
                       from dual;
    end;"

You call this entire statement the usual way, but you register a cursor out parameter (unfortunately, Java is a very long time ago for me so I'm not sure on the exact syntax).

callableStatement.registerOutParameter("pOut", OracleTypes.CURSOR);
...
callableStatement.execute();
...
ResultSet rs = (ResultSet) callableStatement.getObject("pOut");
// Read from result set.

EDIT: I turned this into a blogpost. Code examples are in C# but the idea is the same.

Problem

I have been given an oracle procedure with the in out parameter %rowtype,like: ``` CREATE OR REPLACE PROCEDURE cleansing( io_user IN OUT USER%rowtype ) IS BEGIN --some pl/sql code END cleansing; ``` USER is a table with more than 100 columns, I want to call the procedure by Java. I can't change the procedure, because they are already used by other project. I can't add procedure to database, because I don't have the permission to do it. I google it, but can't find a good way to handle this. what I want to do is: 1. pass the parameter. 2. get the parameter. some java demo code: ``` String sql = "{call cleansing(?)}"; try { dbConnection = getDBConnection(); callableStatement = dbConnection.prepareCall(sql); callableStatement.setXXX()//I don't know callableStatement.registerOUTParameter(1, //I don't know the type.); ``` can anyone help me and give some demo code? no change to database and in out parameter mapping with java

Original source