CODEWITHSUNDEEP
javaplsql
Samson
Samson

Reputation: 2821

Java call PL/SQL procedure with table of records

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?

Upvotes: 1

Views: 897

Answers (1)

Vasily Komarov
Vasily Komarov

Reputation: 1425

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 .......
}

Upvotes: 2

Related Questions