The bulk binding feature was introduced in Oracle version 8i as the FORALL statement, which is used for the batch processing of large DML statements without the need of switching between the SQL and the PL/SQL engine for every single row. Even though the performance was increased using this feature, there is also a downside to it. When a single row from the DML operation fails, the FORALL statement fails entirely. To address this issue, Oracle has introduced the SAVE EXCEPTIONS clause. This clause instructs the system to skip the error prone rows and to process the rest perfectly fine rows rather failing the entire statement.
When the SAVE EXCEPTIONS clause is added to the FORALL statement, all the errors spotted during the batch processing are saved in the cursor SQL%BULK_EXCEPTIONS, which stores a collection of records with two attributes as shown below.
· ERROR_INDEX: The index value of the collection element which failed during the DML operation is stored in this attribute.
· ERROR_CODE: The appropriate error code for the exception raised corresponding to the error index is stored in this attribute.
The exceptions raised during a FORALL DML operation can be captured by using the Oracle error code ORA-24381, when the SAVE EXCEPTIONS clause is used. During this time, the rest of the program code will not be continued and the control directly gets into the exception section once after the FORALL clause completes its operation successfully.
To portray the exceptions in bulk binding, an object type TYPE_OBJ is created with 6 attributes pertaining to an employee’s information is created as shown below,
CREATE OR REPLACE type type_obj
IS
object
(
emp_id VARCHAR2(50),
emp_first_name VARCHAR2(50),
emp_last_name VARCHAR2(50),
emp_email VARCHAR2(50),
emp_hire_date VARCHAR2(50),
emp_job_id VARCHAR2(50));
/
The below code depicts the complete functionality of the SAVE EXCEPTIONS clause in a FORALL statement. The code begins by assigning a name to the error code ORA-24381, which is the Oracle assigned error code for the bulk binding exceptions. The nested table typeTYPE_NTT is created over the object type to store more than one employee information in a collective way. An instance of the nested table type is then created and assigned with 7 employees information. Here, the employee details for the index value [3] has a Null job ID, [5] has an invalid hire date and [6] has a Null last name, which all results into exceptions when they are inserted into the EMPLOYEES table having these validations.
When the employee information stored in the nested table is bulk inserted into the EMPLOYEES table with the SAVE EXCEPTIONS clause, the DML operation successfully completes where the error-prone data are formally collected into the system defined collection type BULK_EXCEPTIONS as shown in the below example.
The failed records can then be collected for analysis from the exception block by iterating the error prone records using the BULK_EXCEPTIONS.COUNT attribute. The index value and the error code of the failed records can be retrieved through the BULK_EXCEPTIONS.ERROR_INDEX and BULK_EXCEPTIONS.ERROR_CODE attributes as mentioned earlier. The corresponding error message for the error code can be retrieved by negating the error code and passing it to the SQLERRM function as its only formal parameter.
DECLARE
bulk_error EXCEPTION;
pragma exception_init(bulk_error, -24381);
type type_ntt
IS
TABLE OF type_obj;
l_ntt_var1 type_ntt:=type_ntt();
BEGIN
l_ntt_var1.extend(7);
l_ntt_var1(1):= type_obj('207', 'Marie', 'John', 'MJOHN1', '10-April-2005', 'AD_PRES');
l_ntt_var1(2):= type_obj('208', 'Abdul', 'Kalam', 'AKALAM2',
'05-September-1995', 'IT_PROG');
l_ntt_var1(3):= type_obj('209A', 'Rahul', 'Dravid', 'RDRAVID6',
'02-August-2001', '');
l_ntt_var1(4):= type_obj('210', 'Mike', 'Thompson', 'MTHOMS3',
'29-December-1999', 'AD_VP');
l_ntt_var1(5):= type_obj('211', 'Paul', 'Livingston', 'PLVNGS8',
'22B-June-2008', 'FI_MGR');
l_ntt_var1(6):= type_obj('212', 'King', '', 'KRAM8', '10-October-2005',
'PU_CLERK');
l_ntt_var1(7):= type_obj('213', 'Daniel', 'Morrison', 'DMORR4',
'07-February-2011', 'SA_REP');
FORALL loop_ntt IN indices OF l_ntt_var1 SAVE exceptions
INSERT
INTO
employees
(
employee_id,
first_name,
last_name,
email,
hire_date,
job_id
)
VALUES
(
l_ntt_var1(loop_ntt).emp_id,
l_ntt_var1(loop_ntt).emp_first_name,
l_ntt_var1(loop_ntt).emp_last_name,
l_ntt_var1(loop_ntt).emp_email,
l_ntt_var1(loop_ntt).emp_hire_date,
l_ntt_var1(loop_ntt).emp_job_id
);
dbms_output.put_line('This message is printed only if there is no exception raised in the FORALL statement!');
EXCEPTION
WHEN bulk_error THEN
dbms_output.put_line('Total number of inserted rows: '||sql%rowcount);
dbms_output.put_line('Total number of rejected rows: '||
sql%bulk_exceptions.count);
FOR loop_error IN 1..sql%bulk_exceptions.count
LOOP
dbms_output.put_line
('Error Index: '||sql%bulk_exceptions(loop_error).error_index||
'. Error Code: '||sql%bulk_exceptions(loop_error).error_code||
'. Error Message: '||sqlerrm(-sql%bulk_exceptions(loop_error).error_code));
END LOOP loop_error;
END;
/
Result:
Total number of inserted rows: 4
Total number of rejected rows: 3
Error Index: 3. Error Code: 1722. Error Message: ORA-01722: invalid number
Error Index: 5. Error Code: 1861. Error Message: ORA-01861: literal does not match format string
Error Index: 6. Error Code: 1400. Error Message: ORA-01400: cannot insert NULL into ()
The error report shows us the number of successfully inserted records, the number of failed records and then lists all the failed records with supporting reasons for their failure.
Thank you,
Boobal Ganesan
No comments:
Post a Comment