Oracle Bulk Exception Error Code
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 save exceptions in oracle 11g AskTom team: KISS video series for Analytic functions Latest Followup You Asked Hi exception handling in bulk collect in oracle Tom, I am working on a datawarehoue project and using BULK COLLECT with SAVE EXCEPTIONS to log errors sql%bulk_exceptions while loading data from source to destination tables. I loop through the BULK_EXCEPTIONS and insert the error_index and error_code with description into my own error table. (eg : ....SQL%BULK_EXCEPTIONS(j).error_index and SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
Dml_errors Exception Oracle
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 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 bulk collect in oracle interview questions 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 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,
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 beyond exceptions. I have been bulk collect in oracle example assigned the job of modifying an existing procedure that applies a complex set
Ora-24381
of rules to a large volume of data in a set of tables. In the past, as soon as an
Bulk Binding In Oracle
error occurred in an update, the procedure would terminate execution. Now I need to change the procedure so that it continues past any exceptions and completes as many updates as possible. What are https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312 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 block. Then add an exception section so that the error is trapped and handled. http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29plsql-085126.html 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_ERRLOG and LOG ERRORS will then write error information out to an error log table. In this column, I take a look at each of these approaches a
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 Oracle Scripts http://www.dba-oracle.com/plsql/t_plsql_exceptions.htm Ion Excel-DB Don Burleson Blog
http://www.java2s.com/Code/Oracle/PL-SQL/TheSAVEEXCEPTIONSclausewillrecordanyexceptionduringthebulkoperationandcontinueprocessing.htm Handling Exceptions in Bulk Operations Oracle Tips by 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 year, 2006: There in oracle 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 created. This is performed using bulk collect in 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 it assigns the value of NULL to the 50th element, therebyExpressionsReport 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