javaoracle-databasestored-proceduresrowtype

java call oracle procedure with %rowtype parameter


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


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.