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;