Hi Experts,
version details
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
desc example1
---- ---- ------
LID NUMBER
KID NUMBER
I created a table example1 as above and inserted 1,00,000 rows with numeric values from 1 to 100000 rows with for loop.
By commenting FORALL statement I am able to execute below code and got below output.
If I un comment the FORALL statement it is not executing( session got hanged), please suggest me on this.
declare
type tabtype is table of number index by pls_integer;
l_tab tabtype;
cursor c1 is select lid FROM example1;
begin
open c1;
loop
fetch c1 BULK COLLECT INTO l_tab limit 10000;
-- forall i in l_tab.first..l_tab.last SAVE EXCEPTIONS update example1 set kid = l_tab(i);
dbms_output.put_line('count'||l_tab.count);
exit when l_tab.exists(1) = false;
end loop;
close c1;
commit;
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -24381
THEN
FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line (
SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ': '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
END LOOP;
ELSE
RAISE;
END IF;
end;
/
anonymous block completed
count10000
count10000
count10000
count10000
count10000
count10000
count10000
count10000
count10000
count10000
count0