FORALL Bulk Collect - Need to add a sequence to insert statement without using a loop?

Question

So I have a FORALL bulk collection that I am using to insert values into a table.

Usually I would use a loop for the sequence field that has to be populated. Example would be like this........

seqno = seqno +1

.....and then I would loop through for each record it would increase by 1 as they are inserted.

SELECT bis_part, bis_part_org, bis_store, bis_bin, bis_lot, bis_qty
BULK COLLECT INTO V_STTK_CLTN 
FROM table1
WHERE bis_bin = 'DIRECT'
AND bis_store = p_org;


FORALL INDX IN 1 .. V_STTK_CLTN.COUNT 

  INSERT INTO table2
   (stl_part,
    stl_part_org,
    stl_trans,
    stl_store,
    stl_bin,
    stl_lot,
    stl_expqty,
    stl_phyqty,
    stl_rtype,
    stl_type,
    stl_line ) 
   VALUES
   (V_STTK_CLTN(INDX).bis_part,
    V_STTK_CLTN(INDX).bis_part_org,
    ctrans,
    V_STTK_CLTN(INDX).bis_store,
    V_STTK_CLTN(INDX).bis_bin,
    V_STTK_CLTN(INDX).bis_lot,
    V_STTK_CLTN(INDX).bis_qty,
    '',
    'STTK',
    'STTK',
     seqno);

How could I do this with a collection since I am using FORALL without a loop?

The seqno value is where i need the sequence. Each time this is run it will insert these records into the same table but the sequence must start with one each time as it is a sequence for the collection not the whole table....make sense?

create a sequence and use it. SEQ_X.nexval? https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314 — OracleDev, May 09 '18 at 13:42
Is the `seqno` column an attribute of the collection from which you drive your FORALL statement? A bit more actual code would make your scenario clearer to us. — APC, May 09 '18 at 13:56
Use a sequence on the table you're inserting in to, or in 12c onward use an IDENTITY column? *(So that the receiving table takes care of creating/managing your sequential values.)* — MatBailie, May 09 '18 at 13:58
Hi thanks everyone for the help here......I added some code to show you what I am working with. I was using a loop and just looping through the records but I have to use FORALL insert now....no loops allowed. Is this even possible? I thought using ROWNUM in place of the variable seqno but I get this error.....ORA-00976: Specified pseudocolumn or operator not allowed here. — jck7282008, May 09 '18 at 18:17
Hi Kaushik Nayak I checked your answer. Thank you for your willingness to help — jck7282008, May 21 '18 at 18:54

score 1 · Answer 1 · answered May 09 '18 at 14:07

It is simple

Create a sequence

CREATE SEQUENCE seq;

Let's say you want to insert into table t

create table t(id INT, rowname VARCHAR2(20));

This block will insert some dummy rows in a FORALL block and will use a sequence. Instead of your loop, bulk collect into a collection from a SELECT query or a CURSOR

DECLARE 
    TYPE ctype 
      IS TABLE OF t%ROWTYPE; 
    ct ctype; 
BEGIN 
    SELECT seq.NEXTVAL AS id, 
           'ROW' 
           ||LEVEL     AS rowname 
    bulk   collect INTO ct 
    FROM   dual 
    CONNECT BY LEVEL <= 100; 

    forall i IN ct.first..ct.last 
      INSERT INTO t 
      VALUES ct(i); 
END; 

/

Db fiddle demo

score 1 · Answer 2 · answered Oct 02 '19 at 23:26

As others have mentioned, perhaps a sequence is the best method to meet the use-case requirement.

But to strictly answer your question on how to replicate an incrementing counter variable with a FORALL statement, you can do the following:

L_OFFSET := 0;

FORALL INDX IN 1 .. V_STTK_CLTN.COUNT 
  INSERT INTO table2
   (stl_part,
    stl_part_org,
    stl_trans,
    stl_store,
    stl_bin,
    stl_lot,
    stl_expqty,
    stl_phyqty,
    stl_rtype,
    stl_type,
    stl_line ) 
   VALUES
   (V_STTK_CLTN(INDX).bis_part,
    V_STTK_CLTN(INDX).bis_part_org,
    ctrans,
    V_STTK_CLTN(INDX).bis_store,
    V_STTK_CLTN(INDX).bis_bin,
    V_STTK_CLTN(INDX).bis_lot,
    V_STTK_CLTN(INDX).bis_qty,
    '',
    'STTK',
    'STTK',
     L_OFFSET+INDX);

In this manner you can set L_OFFSET to the initial offset and let the INDX increment it once for each record in the collection.

score 0 · Answer 3 · answered May 10 '18 at 14:39

So I just had to add a number column to the record for the collection.

 TYPE STTK_RECORD IS RECORD(

BIS_PART T1.BIS_PART%TYPE ,BIS_PART_ORG T1.BIS_PART_ORG%TYPE ,BIS_STORE T1.BIS_STORE%TYPE ,BIS_BIN T1.BIS_BIN%TYPE ,BIS_LOT T1.BIS_LOT%TYPE ,BIS_QTY T1.BIS_QTY%TYPE ,RN NUMBER );

Then I added the row_number function to my select statement for the bulk collect:

SELECT bis_part, bis_part_org, bis_store, bis_bin, bis_lot, bis_qty, ROW_NUMBER() OVER (PARTITION BY bis_store ORDER BY bis_bin desc) RN

BULK COLLECT INTO V_STTK_CLTN;

Finally I inserted the new column:

FORALL INDX IN V_STTK_CLTN.FIRST .. V_STTK_CLTN.LAST

  INSERT INTO T2
   (stl_part,
    stl_part_org,
    stl_trans,
    stl_store,
    stl_bin,
    stl_lot,
    stl_expqty,
    stl_phyqty,
    stl_rtype,
    stl_type,
    stl_line ) 
   VALUES
   (V_STTK_CLTN(INDX).bis_part,
    V_STTK_CLTN(INDX).bis_part_org,
    ctrans,
    V_STTK_CLTN(INDX).bis_store,
    V_STTK_CLTN(INDX).bis_bin,
    V_STTK_CLTN(INDX).bis_lot,
    V_STTK_CLTN(INDX).bis_qty,
    '',
    'STTK',
    'STTK',
    V_STTK_CLTN(INDX).rn);

FORALL Bulk Collect - Need to add a sequence to insert statement without using a loop?

3 Answers3