Oracle Pl Sql Bulk Insert Error Handling
Contents |
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » 9i » Here
Save Exceptions In Oracle 11g
Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle This article is sql%bulk_exceptions an update of one written for Oracle 8i (Bulk Binds) which includes new features available in Oracle 9i Release 2 and beyond. save exception in oracle example Introduction BULK COLLECT FORALL SQL%BULK_ROWCOUNT SAVE EXCEPTIONS and SQL%BULK_EXCEPTION Bulk Binds and Triggers Updates Related articles. FORALL Support for Sparse Collections (10gR1) PLS-00436 Restriction in FORALL Statements Removed (11gR1) APPEND_VALUES Hint (11gR2) Collections in Oracle
Dml_errors Exception Oracle
PL/SQL Introduction Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine. There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk
Bulk Collect In Oracle Interview Questions
binding the whole collection to the DML statement in one operation. In Oracle8i a collection must be defined for every column bound to the DML which can make the code rather long winded. Oracle9i allows us to use Record structures during bulk operations so long as we don't reference individual columns of the collection. This restriction means that updates and deletes which have to reference inividual columns of the collection in the where clause are still restricted to the collection-per-column approach used in Oracle8i. BULK COLLECT Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this create the following table. CREATE TABLE bulk_collect_test AS SELECT owner, object_name, object_id FROM all_objects; The following code compares the time taken to populate a collection manually and using a bulk bind. SET SERVEROUTPUT ON DECLARE TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE; l_tab t_bulk_collect_test_tab := t_bulk_collect_test_tab(); l_start NUMBER; BEGIN -- Time a regular population. l_start := DBMS_UTILITY.get_time; FOR cur_rec IN (SELECT * FROM bulk_collect_test) LOOP l_tab.extend; l_tab(l_tab.last) := cur_rec; END LOOP; DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start)); -- Time bulk population. l_start := DBMS_UTILITY.get_time; SELECT * BULK COLLECT INTO l_tab FROM bulk_
RESOURCES Database Tools SQL Scripts & Samples Links » Database Forum » Slideshows » Sitemap Free Newsletters: DatabaseDaily News Via RSS Feed bulk collect in oracle example Database Journal |DBA Support |SQLCourse |SQLCourse2 Featured Database Articles Oracle Posted
Ora-24381
May 14, 2015 Bulk Exceptions in Oracle By David Fitzjarrell Using BULK COLLECT in PL/SQL blocks and procedures sql%bulk_rowcount 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 https://oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i 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 http://www.databasejournal.com/features/oracle/bulk-exceptions-in-oracle.html 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: S
ExpressionsReport 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 http://www.java2s.com/Code/Oracle/PL-SQL/TheSAVEEXCEPTIONSclausewillrecordanyexceptionduringthebulkoperationandcontinueprocessing.htm 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 in oracle 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 oracle example 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 associative array into a tablejava2s.com |Email:info at java2s.com|© Demo Source and Support. All rights reserved.