Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.
If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
TYPE NumList IS TABLE OF NUMBER;
— The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
PRAGMA exception_init(dml_errors, -24381);
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
— SAVE EXCEPTIONS means don’t stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
— Count how many rows were inserted for each department; that is,
— how many employees are in each department.
dbms_output.put_line(‘Dept ‘||deptnums(i)||’: inserted ‘||
dbms_output.put_line(‘Total records inserted =’ || SQL%ROWCOUNT);
— If any errors occurred during the FORALL SAVE EXCEPTIONS,
— a single exception is raised when the statement completes.
WHEN dml_errors THEN — Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of INSERT statements that failed: ‘ || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line(‘Error #’ || i || ‘ occurred during ‘||
‘iteration #’ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(‘Error message is ‘ ||