Help regarding bulk processing.

You will have to fetch into an array  instead of an record.

add to declaration
type t_paramA_type IS table of my_typ INDEX BY BINARY_INTEGER;
t_paramA   t_paramA_type;

change fetch
FETCH p_paramA BULK COLLECT INTO t_paramA;

FORALL  i IN t_paramA LOOP

flow01:
I have made modification in the code and now I am recieving the p_paramA into an associative array called v_paramA

Now i am getting an error ORA-228096: not an object or REF.

It seems that oracle does not consider v_paramA as an object at all.

Though i have declared v_paramA as an associative array!..

************************************
PROCEDURE my_proc (p_paramA      IN        sys_refcursor,
               p_paramB       OUT               NUMBER
)
IS
TYPE my_rec_typ IS RECORD
(col1      my_tab.col1%TYPE,
col2       my_tab.col2%TYPE);


type t_paramA_type IS table of my_rec_typ
INDEX BY BINARY_INTEGER;

v_paramA   t_paramA_type;

BEGIN

FETCH p_paramA BULK COLLECT INTO v_paramA;

     
FORALL  idx IN v_paramA LOOP
      INSERT INTO inst_activity
      VALUES ( v_paramA(idx).col1, <-- Getting error here! ORA-228096: not an object or REF.
               v_paramA(idx).col2, <-- Getting error here! ORA-228096: not an object or REF.
               TRUNC (SYSDATE));
END LOOP;
END my_proc;

flow01:
In the privious post, there is a syntax typo.

FORALL does not come with a loop.

Next, Surprisingly, the follwoing works fine..

but, i want this to work with FORALL not with FOR LOOP as given below:

************************************
PROCEDURE my_proc (p_paramA      IN        sys_refcursor,
               p_paramB       OUT               NUMBER
)
IS
TYPE my_rec_typ IS RECORD
(col1      my_tab.col1%TYPE,
col2       my_tab.col2%TYPE);


type t_paramA_type IS table of my_rec_typ
INDEX BY BINARY_INTEGER;

v_paramA   t_paramA_type;

BEGIN

FETCH p_paramA BULK COLLECT INTO v_paramA;

     
FOR  idx IN v_paramA LOOP
      INSERT INTO inst_activity
      VALUES ( v_paramA(idx).col1,
               v_paramA(idx).col2,
               TRUNC (SYSDATE));
END LOOP;

END my_proc;

flow01:
In a nutshell, I want to know why is it that the object v_paramA is treated as an object in a FOR loop and not as an object in the FORALL stmt.


How can i overcome it?


The following stmt works fine:
FOR  idx IN v_paramA LOOP
      INSERT INTO inst_activity
      VALUES ( v_paramA(idx).col1,
               v_paramA(idx).col2,
               TRUNC (SYSDATE));
END LOOP;

But the following stmt gives an error:

FORALL  idx IN v_paramA
      INSERT INTO inst_activity
      VALUES ( v_paramA(idx).col1, <-- Getting error here! ORA-228096: not an object or REF
               v_paramA(idx).col2, <-- Getting error here! ORA-228096: not an object or REF
               TRUNC (SYSDATE));

PROCEDURE my_proc (p_paramA      IN        sys_refcursor,
               p_paramB       OUT               NUMBER
)
IS

 TYPE col1 IS TABLE OF my_tab.col1%TYPE INDEX BY PLS_INTEGER;
 TYPE col2 IS TABLE OF my_tab.col2%TYPE INDEX BY PLS_INTEGER;

pcol1  col1;
pcol2  col2;

BEGIN

FETCH p_paramA BULK COLLECT INTO pcol1,pcol2;
     
FORALL  idx IN 1 .. pcol1.count LOOP
      INSERT INTO inst_activity
      VALUES ( col1(idx),
               col2(idx),
               TRUNC (SYSDATE));
END LOOP;
END my_proc;

Henka:Thanks for your help. I have incorporated your solution.

I want to update my_tab with timestamp and status='N' (record not inserted) for records that could not be inserted.

I have added this in the exception section to the forall block.

Now the problem is that the exception section does not seem to recognize the idx variable
of the idx forall stmt.




PROCEDURE my_proc (p_paramA      IN        sys_refcursor,
                     p_paramB       OUT               NUMBER
)
IS
TYPE my_typ IS RECORD
(
      col1       mytab.col1%TYPE,
      col2       mytab.col2%TYPE
);
 my_rec my_typ;

 TYPE col1_table_type IS TABLE OF my_rec.col1%TYPE
 INDEX BY PLS_INTEGER;
 v_col1 col1_table_type;

 TYPE col2_table_type IS TABLE OF my_rec.col2%TYPE
 INDEX BY PLS_INTEGER;
 v_col2 col2_table_type;
BEGIN
        BEGIN
            FETCH p_paramA BULK COLLECT INTO v_col1 , v_col2;


           FORALL  idx IN 1 .. v_col1.count
               INSERT INTO my_tab
               VALUES ( v_col1(idx),
                        v_col2(idx),
                        TRUNC (SYSDATE) );
       EXCEPTION
            WHEN DUP_VAL_ON_INDEX
            THEN
            UPDATE my_tab
            SET update_ts = SYSTIMESTAMP,
                status_cd = 'N'
            WHERE  col1 = v_col1(idx) <--Error: PLS-00201, IDX must be declared.
            AND col2 = v_col2(idx);
      
            END;

      COMMIT;
EXCEPTION
      WHEN OTHERS
      THEN
            NULL;
END my_proc;