How to transform the index in a integer in a forall loop?

user_8458083 0
Viewed 64
CREATE TYPE o_A AS OBJECT (A1 VARCHAR2 (4000));

CREATE OR REPLACE TYPE t_A IS TABLE OF o_A;

CREATE TABLE table_o_A
(
      oA_id                INTEGER GENERATED BY DEFAULT AS IDENTITY,
      oA                   o_A,
      PRIMARY KEY (oA_id)
    );

I would to add the lines of a t_A in the table_oA.

CREATE PROCEDURE aa (query_result    t_A)
is
    v_i   INTEGER;
begin
    SELECT MAX (oA_id)
          INTO v_i
          FROM table_o_A;
          
    FORALL i IN INDICES OF query_result
            INSERT INTO table_o_A(
                            oA_id,
                            oA)
                 VALUES ( v_i+i, query_result (i));
                 
end;

[Error] Compilation (887: 26): PLS-00430: FORALL iteration variable I is not allowed in this context

I thinks it doesn't work because i isn't an integer. I could use a for loop and use a real integer but it make a switch between the pl/sql and sql context. How do I do that with the ForAll loop?

code

solution from Alex Poole

oracle
1 Answers

I thinks it doesn't work because i isn't an integer.

No, this is a restriction on the forall syntax; from the documentation, your i index is (emphasis added):

Name for the implicitly declared integer variable that is local to the FORALL statement. Statements outside the FORALL statement cannot reference index. Statements inside the FORALL statement can reference index as an index variable, but cannot use it in expressions or change its value. After the FORALL statement runs, index is undefined.

Your oA_id is a sequence-backed identity column - albeit only default, not always - so you don't have to specify a value. Unless you're already mixing manual and auto-assigned ID values, the max you get will be compatible with the sequence value (there may be gaps, of course); but having set ID values manually, the sequence will then be out of step and you'd get clashes from auto-assigned values, unless it was reset to the current limit.

Unless you have a good reason to mix manual and auto, you can simplify what you are doing to just:

CREATE PROCEDURE a (query_result    t_A)
is
begin
    FORALL i IN INDICES OF query_result
            INSERT INTO table_o_A(oA)
                 VALUES (query_result (i));
end;
/

... and only provide the objects in the insert statement.

db<>fiddle

user_2663040
Related