DECLARE
-----------------------------------------------------------------------------
-- NAME: Array Insert Table
-- NOTE: 1. Declare
-- 1.1 .Declare array for cursor
-- 1.2. Declare array for insert table
-- 1.3. Declare record for keep initial value
-- 2. Begin
-- 2.1. open cursor
-- 2.2. default value
-- 2.3. array loop
-- 2.4. insert table
-----------------------------------------------------------------------------
cursor c_cursor is
select 1 rec_id,
'ABC' rec_value
from dual;
TYPE cursor_rec_type IS TABLE OF c_cursor%rowtype INDEX BY PLS_INTEGER;
cursor_rec_temp cursor_rec_type;
TYPE rec_type IS TABLE OF table_name%rowtype INDEX BY PLS_INTEGER;
rec_temp rec_type;
rec_dummy table_name%rowtype;
ii number := 1 ;
BEGIN
-----------------------------------------------------------------------------
-- 2.1. open cursor
-----------------------------------------------------------------------------
open c_cursor;
fetch c_cursor bulk collect into cursor_rec_temp;
close c_cursor;
-----------------------------------------------------------------------------
-- 2.2. default value
-----------------------------------------------------------------------------
rec_dummy.created_by := 'User';
rec_dummy.creation_date := sysdate;
-----------------------------------------------------------------------------
-- 2.3. array loop
-----------------------------------------------------------------------------
for i in 1 .. cursor_rec_temp.count loop
-- initial record value
rec_temp(ii) := rec_dummy;
rec_temp(ii).rec_value := cursor_rec_temp(i).rec_value;
-- validate SQL
begin
null;
end;
-- add count or delete record
if validate success then
ii := ii + 1 ;
else
rec_temp.delete(ii); -- delete record when validate fail
end if;
end loop;
-----------------------------------------------------------------------------
-- 2.4. insert table
-----------------------------------------------------------------------------
forall p in 1 .. rec_temp.count
insert into table_name values rec_temp(p);
EXCEPTION
when others then
rollback;
END;
/
