Bulk Exceptions in Oracle

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list. A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown. Let’s look at an example intentionally set up to fail inserts based on data from the EMP table. Table M is created with the EMP columns slightly re-ordered so the data types don’t match up to the source:


SQL> CREATE TABLE M
  2  	    (EMPNO NUMBER(4) NOT NULL,
  3  	     MGR NUMBER(4),
  4  	     JOB VARCHAR2(9),
  5  	     ENAME VARCHAR2(10),
  6  	     HIREDATE DATE,
  7  	     SAL NUMBER(7, 2),
  8  	     COMM NUMBER(7, 2),
  9  	     DEPTNO NUMBER(2));

Table created.

SQL>

The ENAME column is now fourth in the list, rather than second, If an attempt is made to simply ‘shove’ the EMP data into M it’s certain to generate a number of ‘invalid number’ errors, but if the PL/SQL loop and exception handler aren’t coded to take advantage of the BULK COLLECT error trapping at most one of the many errors generated will be reported:


SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6
  7
  8  BEGIN
  9  OPEN EMPINFO;
 10  LOOP
 11
 12  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 13  	EXIT WHEN emp_data.count = 0;
 14  	BEGIN
 15  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 16  	     FORALL i IN 1..emp_data.COUNT
 17  		 INSERT INTO m VALUES emp_data(i);
 18
 19  	EXCEPTION
 20  	       WHEN others THEN -- Now we figure out what failed and why.
 21  		     -- Output desired error message
 22  		     dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
 23  		     -- Output actual line number of error source
 24  		     dbms_output.put(dbms_utility.format_error_backtrace);
 25  		     -- Output the actual error number and message
 26  		     dbms_output.put_line(dbms_utility.format_error_stack);
 27  	END;
 28
 29  END LOOP;
 30  END;
 31  /
Request rows 18
-20999: Sumthin' bad happened -- error stack follows
ORA-06512: at line 16
ORA-01722: invalid number


PL/SQL procedure successfully completed.

SQL>

Taking another route the PL/SQL code is changed to provide a user-defined exception and variables to hold the error messages and numbers generated for each insert error. BULK COLLECT is also instrumented to save all of the exceptions generated. Running the modified code provides the following output:


SQL>
SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6  	errorCnt     number;
  7  	errString    varchar2(4000);
  8  	errCode      number;
  9  	dml_errors   exception;
 10  	pragma exception_init(dml_errors, -24381);
 11
 12
 13  BEGIN
 14  OPEN EMPINFO;
 15  LOOP
 16
 17  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 18  	EXIT WHEN emp_data.count = 0;
 19  	BEGIN
 20  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 21  	     FORALL i IN 1..emp_data.COUNT SAVE EXCEPTIONS
 22  		 INSERT INTO m VALUES emp_data(i);
 23
 24  	EXCEPTION
 25  	       WHEN dml_errors THEN -- Now we figure out what failed and why.
 26  		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
 27  		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
 28  		     dbms_output.put_line(errString);
 29
 30  		     FOR i IN 1..errorCnt LOOP
 31  			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
 32  			 THEN
 33  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 34  			     dbms_output.put_line(errString);
 35  			 ELSE
 36  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 37  			     dbms_output.put_line(errString);
 38  			     RAISE;
 39  			 END IF;
 40  		     END LOOP;
 41  	END;
 42
 43  END LOOP;
 44  END;
 45  /
Request rows 18
Number of statements that failed: 18

Error #1
Error message is ORA-01722: invalid number

Error #2
Error message is ORA-01722: invalid number

Error #3
Error message is ORA-01722: invalid number

Error #4
Error message is ORA-01722: invalid number

Error #5
Error message is ORA-01722: invalid number

Error #6
Error message is ORA-01722: invalid number

Error #7
Error message is ORA-01722: invalid number

Error #8
Error message is ORA-01722: invalid number

Error #9
Error message is ORA-01722: invalid number

Error #10
Error message is ORA-01722: invalid number

Error #11
Error message is ORA-01722: invalid number

Error #12
Error message is ORA-01722: invalid number

Error #13
Error message is ORA-01722: invalid number

Error #14
Error message is ORA-01722: invalid number

Error #15
Error message is ORA-01722: invalid number

Error #16
Error message is ORA-01722: invalid number

Error #17
Error message is ORA-01722: invalid number

Error #18
Error message is ORA-01722: invalid number

PL/SQL procedure successfully completed.

SQL>

There are 18 rows in this slightly modified data set so 18 exceptions were generated. All of the exceptions were reported by the modified exception handler. Listing the changes made to the original code shows that three variables were declared (errorCnt, errString and errCode) and a user-defined exception was provided (dml_errors, initialized to error code -24381 (invalid number). The real ‘magic’ lies in the exception handler itself:


   	EXCEPTION
   	       WHEN dml_errors THEN -- Now we figure out what failed and why.
   		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
   		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
   		     dbms_output.put_line(errString);
   
   		     FOR i IN 1..errorCnt LOOP
   			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
   			 THEN
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			 ELSE
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			     RAISE;
   			 END IF;
   		     END LOOP;
   	END;

Each time the error is thrown in the loop the exception handler goes to work reporting the record number generating the error, the error number and the error text. Using SQL%BULK_EXCEPTIONS() array it’s possible to extract the error code and, by a call to SQLERRM, the associated error message. The handler can process both positive and negative error codes, and on negative error codes makes a call to RAISE to stop normal execution. Since no negative errors were thrown in this example the loop execution continued until all rows were processed, reporting every error encountered during the run.

Coding exception handlers correctly (to provide useful, rather than confusing, messages) is key in being able to let users report errors they encounter. Being able to see which records in a bulk collection are generating errors makes it even easier to diagnose and correct problems in bulk data processing, and knowing how to write such exception handlers provides that information to the end users. Using this template makes that task easier (hopefully) so the development team won’t be ‘shooting in the dark’ when a bulk loop process fails to process all of the desired records.

Using bulk processing when it’s appropriate can save time and effort; writing a proper exception handler can help tremendously in troubleshooting any errors that may arise.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles