Bulk Insert Error Handling Oracle
Contents |
Tom Kyte – Last updated: July 01, 2013 - 6:45 pm UTC Category: Database – Version: 10.2.0 Latest Followup You Asked Hi Tom, I am working on a datawarehoue project and using BULK COLLECT with
Bulk Insert Exception Handling Oracle
SAVE EXCEPTIONS to log errors while loading data from source to destination tables. I sql bulk insert error handling 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
Sql Server Bulk Insert Error Handling
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 goes to check in the source the table, bulk insert oracle 9i 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 go back to bulk insert example oracle 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Bulk Insert In Oracle Sql Developer
Learn more about Stack Overflow the company Business Learn more about hiring developers or bulk insert in oracle from one table to another posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow
Bulk Insert In Oracle 11g
Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle 11gR2 FORALL SAVE EXCEPTIONS on database view https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312 not working up vote 0 down vote favorite I want to take advantage of Oracle bulk DML operation and exception handling. My requirement is to perform DML operation on a database view, where it perform some validations through view trigger, then finally insert/update the underlying table. However, Oracle's FORALL .. SAVE EXCEPTIONS doesn't seem to catch validation error raised in the view. Is this the limitation/restriction of SAVE http://stackoverflow.com/questions/26209184/oracle-11gr2-forall-save-exceptions-on-database-view-not-working EXCEPTION where it only work with database table but not view? Oracle documentation doesn't seem to mention this as well. Below are my test codes (based on modification from Handling Exceptions in Bulk Operations): Create table: create table exception_test ( id number(10) not null ); Create view on the table: create or replace view exception_test_v as select exception_test.id id ,sysdate daytime from exception_test; Create trigger on the view: create or replace trigger iud_exception_test instead of insert or update or delete on exception_test_v for each row declare begin if inserting then if nvl(:new.id, 0) = 0 then RAISE_APPLICATION_ERROR(-20815, 'ID must not be null!'); end if; insert into exception_test (id) values (:new.id); end if; end; / Test code of DML on the database view: declare TYPE t_tab IS TABLE OF exception_test_v%ROWTYPE; l_tab t_tab := t_tab(); l_error_count NUMBER; ex_dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381); BEGIN -- Fill the collection. FOR i IN 1 .. 100 LOOP l_tab.extend; l_tab(l_tab.last).id := i; END LOOP; -- Cause a failure. l_tab(50).id := NULL; l_tab(51).id := NULL; EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test'; -- Perform a bulk operation. BEGIN FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS INSERT INTO exception_test_v (id) VALUES (l_tab(i).id); EXCEPTION WHEN ex_dml_errors THEN l_error_count := SQL%BULK_EXCEPTION
RESOURCES Database Tools SQL Scripts & Samples Links » Database Forum » Slideshows » Sitemap Free Newsletters: DatabaseDaily News Via RSS Feed Database Journal |DBA Support |SQLCourse |SQLCourse2 Featured Database Articles Oracle Posted May 14, http://www.databasejournal.com/features/oracle/bulk-exceptions-in-oracle.html 2015 Bulk Exceptions in Oracle By David Fitzjarrell 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 bulk insert 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 bulk insert error 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 n