1

Given the function

DECLARE
TYPE t_ids IS TABLE OF PGD.ITEM_CHANGE.id%TYPE;
l_ids t_ids;
BEGIN
    UPDATE PGD.ITEM_CHANGE
    SET    IN_PROCESS = 1
    RETURNING id BULK COLLECT INTO l_ids;
COMMIT;
END;

How do I call this in groovy anonymously (without CREATE PROCEDURE) to get a hold of L_IDS?

I'm trying this by putting the above in a String, PROC, and then calling:

List<Object> params = [Sql.ARRAY]
sql.callWithRows(PROC, params) {
    result ->
        println result
}

And I get the exception:

java.sql.SQLException: Invalid column index at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
...

Can someone help me with this syntax?

Fustov
  • 11
  • 2

1 Answers1

0

First of all the thing that you pasted is not PL/SQL function. It's unnamed block, you cannot extract PL/SQL collection out of unnamed block.

Please convert it to stored function with RETURN statement at the end or procedure with OUT parameter.

Kamil
  • 506
  • 4
  • 9
  • 21
  • Thank you. Is there a way to do this all in the SQL String passed to JDBC, without expressly calling CREATE PROCEDURE or CREATE FUNCTION? I do not have the CREATE PROCEDURE privilege. – Fustov Jan 20 '16 at 14:48