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.