How to call a PL/SQL procedure with %ROWTYPE and RECORD as OUT parameters in Java?

80 views Asked by At

PROCEDURE has 2 OUT parameters, that CallableStatement wants registered. One is of RECORD TYPE, this is the OUT value that actually interests me.

create PACKAGE         my_package IS

  TYPE my_rec_type IS RECORD
  ( field1                                 VARCHAR2(40 CHAR)  DEFAULT '0'
  , field2                                 VARCHAR2(240 CHAR)
  , field3                                 VARCHAR2(1 CHAR)  DEFAULT '1'
    );
END my_package;

The other one is of %ROWTYPE.

The procedure itself looks like this:

PROCEDURE myProcedure(p_my_tbl1_rec          IN     schema2.my_table1%ROWTYPE
                    , p_my_tbl2_rec          IN     schema2.my_table2%ROWTYPE
               
                    , p_to_my_table1_rec     OUT    schema2.my_table1%ROWTYPE
                    , p_my_rec               OUT    schema2.my_package.my_rec_type)
  BEGIN 
      ...
  END myProcedure;

Is used a workaround to map my Java objects to %ROWTYPE inputs that the Oracle procedure can use.

Connection con = dataSource.getConnection();

        String sql = """
                declare p_my_tbl1_rec      schema2.my_table1%ROWTYPE; 
                        p_my_tbl2_rec      schema2.my_table2%ROWTYPE;
                        
                        p_to_my_table1_rec schema2.my_table1%ROWTYPE;
                        p_my_rec           schema2.my_package.my_rec_type;
                        
                begin 
                    p_my_tbl1_rec.TABLE_FIELD1                  :=  :table1TableField1;
                    p_my_tbl1_rec.TABLE_FIELD2                  :=  :table1TableField2;
                    ...
                    p_my_tbl2_rec.TABLE_FIELD1                  :=  :table2TableField1; 
                    p_my_tbl2_rec.TABLE_FIELD2                  :=  :table2TableField2;
                    ...
                    
                    p_to_my_table1_rec                       :=  ???
                    p_my_rec                                 :=  ???
                    
                    myProcedure( p_my_tbl1_rec         => p_my_tbl1_rec
                               , p_my_tbl2_rec         => p_my_tbl2_rec
                        
                               , p_to_my_table1_rec    => p_to_my_table1_rec
                               , p_my_rec              => p_my_rec);
                end;
                """;
        CallableStatement cs = con.prepareCall(sql);
        // This is how I can map a Java object to %ROWTYPE TYPE
        cs.setLong  ("table1TableField1", myObject1.getTable1TableField1());
        cs.setString("table1TableField2", myObject1.getTable1TableField2());
        ...
        cs.setLong  ("table2TableField1", myObject2.getTable2TableField1());
        cs.setString("table2TableField2", myObject2.getTable2TableField2());
        ...

        cs.registerOutParameter("p_to_my_table1_rec", ???);
        cs.registerOutParameter("p_my_rec", ???);
        cs.execute();

Expectation: I call the procedure, I can verify the database, that the procedure has indeed been called. Ideally I can map p_my_rec to a Java object, for future use.

Reality: i have tried different things. STRUCT seems to be the thing I am loking for, but I do not know how to define it correctly. Sometimes, when I tried calling cs.registerOutParameter("schema2.my_package.my_rec_type", STRUCT); it did not find schema2.my_package.my_rec_type. Could it be, because it is in a PACKAGE? If you can help me I would be very grateful. Also, if you can provide me an alternative way to call procedures with %ROWTYPE parameter values, that would also help a lot.

0

There are 0 answers