I have a record type and a procedure (which recieves as IN parameter a table of records) defined in a PL/SQL package How can this procedure be called within Java code?
Is it better to avoid this type of IN parameters? maybe a REF CURSOR?
Add a comment
|
1 Answer
The usual whay is to use a temporary table.
0 Set autocommit off if nessesary (It's off by default)
1 Fill a temporary table whith some values.
2 Call your procedure. Read a values from temporary table. (No records in IN parameters)
3 commit
//A function to put a value in temporary table
public void addValueToTmp(Connection conn, String value) throws NamingException, SQLException {
CallableStatement cs = conn.prepareCall("{call plsql_function_put_value_in_tmp(?)}");
....... other code .......
....... other code .......
....... other code .......
}
//A function to do something whit a data in temporary table
public void doAllWork(Connection conn) throws NamingException, SQLException {
CallableStatement cs = conn.prepareCall("{call plsql_function_do_something_whith_tmp}");
....... other code .......
....... other code .......
....... other code .......
}
public void mainFunction() throws NamingException, SQLException {
....... other code .......
//Get connection
Connection conn = anyFunctionToGetConnection();
//Fill temporary table
addValueToTmp(conn, value1);
addValueToTmp(conn, value2);
addValueToTmp(conn, value3);
//Do something whith data
doAllWork(conn);
//Commit
conn.commit;
....... other code .......
}
