Oracle Bulk Error Exception
Contents |
Tom Kyte � Last updated: July 01, 2013 - 6:45 pm UTC Category: Database � Version: 10.2.0 Whilst you are here, check out some content from the AskTom team: Beyond Init.ora and SYS Latest Followup You Asked Hi Tom, save exceptions in oracle 11g I am working on a datawarehoue project and using BULK COLLECT with SAVE EXCEPTIONS to
Bulk Collect In Oracle Interview Questions
log errors while loading data from source to destination tables. I loop through the BULK_EXCEPTIONS and insert the error_index and error_code with bulk collect in oracle example description into my own error table. (eg : ....SQL%BULK_EXCEPTIONS(j).error_index and SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE) Logging the array index does not help me, since by the time the support person queries my error log table and takes the error index and bulk bind in oracle goes to check in the source the table, he might not be looking at the same erroneous row at all, because the source table might have had many updates and deletes during that time span and trying to match the error index to the rownum will be mostly futile. What i need is a way to capture the primary key value of the source table while using BULK COLLECT...SAVE EXCEPTIONS.....? Or do i have to
Sql%bulk_exceptions
go back to CURSOR processing just for this purpose and compromise on execution time.....? Please help. Thanks Jaya and we said... why not use dml error logging instead? You have the primary key - you have the index into your array of values you were using in the forall (you use save exceptions with forall, not with bulk collect). The array you were processing with "forall" has this information. you will have code that looks like, bits in bold are what you are looking for, I was loading FROM a table that is a copy of all objects to a table that is a copy of all objects: declare cursor C is select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, decode( mod(rownum,100000), 1, rpad('*',20,'*'), OBJECT_TYPE ) object_type, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY from big_table; type array is table of c%rowtype; l_data array; dml_errors EXCEPTION; PRAGMA exception_init(dml_errors, -24381); l_errors number; l_errno number; l_msg varchar2(4000); l_idx number; begin open c; loop fetch c bulk collect into l_data limit 100; begin forall i in 1 .. l_data.count SAVE EXCEPTIONS insert into t2 values l_data(i); exception when DML_ERRORS then l_errors := sql%bulk_exceptions.count; for i in 1 .. l_errors loop l_errno := sql%bulk_exceptions(i).error_code; l_msg := sqlerrm(-l_errno); l_idx := sql%bulk_exceptions(i).error_index; insert into err$_t2 ( ora_err_number$, ora_err_mesg$, ora_err_optyp$, ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, object_type, CREATED, LAST_DDL_TIM
March 2009 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 As Published In March/April 2009 DEVELOPER: PL/SQL Practices On Avoiding Termination By Steven Feuerstein Continue PL/SQL execution bulk collect in oracle with example pdf beyond exceptions. I have been assigned the job of modifying an existing bulk insert in oracle stored procedure procedure that applies a complex set of rules to a large volume of data in a set of tables.
Bulk Collect Vs Cursor In Oracle
In the past, as soon as an error occurred in an update, the procedure would terminate execution. Now I need to change the procedure so that it continues past any exceptions https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312 and completes as many updates as possible. What are my options in PL/SQL for doing this? In Oracle Database 10g and higher, PL/SQL offers three options for “continuing past an exception,”which really means avoiding termination of the execution of the current block. 1. The nested block. You enclose the lines of code that may raise the exception inside a BEGIN END nested http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29plsql-085126.html block. Then add an exception section so that the error is trapped and handled. Then keep on going. 2. The FORALL SAVE EXCEPTIONS clause. Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL. FORALL SAVE EXCEPTIONS suppresses exceptions at the generated statement level, so if that statement’s change of a row raises an error, changes to other rows already completed by that generated statement are also rolled back. When the FORALL statement is completed, Oracle Database will then raise the ORA-21438 error if at least one exception was encountered. 3. Data manipulation language (DML) error logging. Use the DBMS_ERRLOG package and LOG ERRORS with any insert, update, delete, or merge statements (within or independent of FORALL) to suppress exceptions at the row level. If a statement’s change of one row raises an error, changes to other rows already made by that statement will not be rolled back and Oracle Database will continue to change any remaining rows identified by that statement. DBMS_ER
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books http://www.dba-oracle.com/plsql/t_plsql_exceptions.htm Oracle Scripts Ion Excel-DB Don Burleson Blog
Handling Exceptions in Bulk Operations Oracle Tips by http://www.java2s.com/Code/Oracle/PL-SQL/TheSAVEEXCEPTIONSclausewillrecordanyexceptionduringthebulkoperationandcontinueprocessing.htm Burleson Consulting The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the in oracle year, 2006: There are a number of issues regarding exception handling that must be considered when using bulk operations. In this section, rollback behavior of bulk operations and the methods available to control this behavior is examined. In order to demonstrate this functionality, a simple test table containing a single mandatory column must first be bulk collect in created. This is performed using the exception_test.sql script listed below. exception_test.sql CREATE TABLE exception_test ( id NUMBER(10) NOT NULL ); After the table is created, the way unhandled exceptions are treated during bulk operations can be examined. Unhandled Exceptions Unhandled exceptions during the execution of a bulk operation cause the entire operation to be rolled back. This functionality is demonstrated using the unhandled_exception.sql script listed below. unhandled_exception.sql DECLARE TYPE t_tab IS TABLE OF exception_test.id%TYPE; l_tab t_tab := t_tab(); BEGIN -- Fill the collection. FOR i IN 1 .. 100 LOOP l_tab.extend; l_tab(l_tab.last) := i; END LOOP; -- Cause a failure. l_tab(50) := NULL; EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test'; -- Perform a bulk operation. FORALL i IN l_tab.first .. l_tab.last INSERT INTO exception_test VALUES (l_tab(i)); END; / SET ECHO ON SELECT COUNT(*) FROM exception_test; SET ECHO OFF The unhandled_exception.sql script first creates and populates a collection. Next itExpressionsReport Column PageResult SetSelect QuerySequenceSQL PlusStored Procedure FunctionSubquerySystem PackagesSystem Tables ViewsTableTable JoinsTriggerUser PreviliegeViewXMLThe SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing. : FORALL«PL SQL«Oracle PL / SQLOracle PL / SQLPL SQLFORALLThe SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing. SQL> SQL> CREATE TABLE MyTable ( 2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 ); Table created. SQL> SQL> SQL> DECLARE 2 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE 5 INDEX BY BINARY_INTEGER; 6 v_Strings t_Strings; 7 v_Numbers t_Numbers; 8 v_NumErrors NUMBER; 9 BEGIN 10 DELETE FROM MyTable; 11 FOR v_Count IN 1..10 LOOP 12 v_Strings(v_Count) := '123456789012345678901234567890'; 13 v_Numbers(v_Count) := v_Count; 14 END LOOP; 15 16 FORALL v_Count IN 1..10 17 INSERT INTO MyTable (num_col, char_col) 18 VALUES (v_Numbers(v_Count), v_Strings(v_Count)); 19 20 v_Strings(6) := v_Strings(6) || 'a'; 21 22 FORALL v_Count IN 1..10 SAVE EXCEPTIONS 23 UPDATE MyTable 24 SET char_col = char_col || v_Strings(v_Count) 25 WHERE num_col = v_Numbers(v_Count); 26 EXCEPTION 27 WHEN OTHERS THEN 28 DBMS_OUTPUT.PUT_LINE('Got exception: ' || SQLERRM); 29 v_NumErrors := SQL%BULK_EXCEPTIONS.COUNT; 30 DBMS_OUTPUT.PUT_LINE( 31 'Number of errors during processing: ' || v_NumErrors); 32 FOR v_Count IN 1..v_NumErrors LOOP 33 DBMS_OUTPUT.PUT_LINE('Error ' || v_Count || ', iteration ' || 34 SQL%BULK_EXCEPTIONS(v_Count).error_index || ' is: ' || 35 SQLERRM(0 - SQL%BULK_EXCEPTIONS(v_Count).error_code)); 36 END LOOP; 37 38 COMMIT; 39 END; 40 / Got exception: ORA-24381: error(s) in array DML Number of errors during processing: 1 Error 1, iteration 6 is: ORA-12899: value too large for column (actual: , maximum: ) PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table MyTable; Table dropped. SQL> Related examples in the same category1.forall from 1 to 502.Insert all 1000 elements using a single FORALL statement3.An exception will stop the bulk insert.4.Use a FORALL to move an as