How to bulk return in FORALL insert?

BULK Return from FORALL insert.


CREATE TABLE ashish_bulk_ret
(
   id     NUMBER GENERATED AS IDENTITY PRIMARY KEY,
   name   VARCHAR2 (31)
);


DECLARE
   TYPE l_rec_typ IS RECORD
   (
      id     NUMBER,
      name   VARCHAR2 (32)
   );

   TYPE l_ret_list_tbl_typ IS TABLE OF l_rec_typ;

   l_ret_list_tbl   l_ret_list_tbl_typ;



   TYPE l_data_rec_typ IS RECORD (name VARCHAR2 (32));

   TYPE l_data_tbl_typ IS TABLE OF l_data_rec_typ;

   l_data_tbl       l_data_tbl_typ;
BEGIN
       /*Fetch Data to Table TYPE*/
       SELECT DBMS_RANDOM.string ('l', 12)
         BULK COLLECT INTO l_data_tbl
         FROM DUAL
   CONNECT BY LEVEL <= 11;


   FORALL i IN l_data_tbl.FIRST .. l_data_tbl.LAST
      INSERT INTO ashish_bulk_ret (name)
           VALUES (l_data_tbl (i).name)
        RETURNING id, name
             BULK COLLECT INTO l_ret_list_tbl;


   FOR i IN l_ret_list_tbl.FIRST .. l_ret_list_tbl.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'ID #'
         || TO_CHAR (i)
         || ':  '
         || TO_CHAR (l_ret_list_tbl (i).id)
         || CHR (13)
         || 'Name  #'
         || TO_CHAR (i)
         || ':  '
         || TO_CHAR (l_ret_list_tbl (i).name)
         || CHR (13));
   END LOOP;
END;
/


SELECT * FROM ashish_bulk_ret;

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *