Updating record from cursor oracle

We will create a table with an "embedded" nested table of column information. We can now create a table to hold information on all of our tables and their column attributes.

Each of our tables will occupy one record in this table.

The performance improvements were significant, although on the "downside" each FORALL statement either succeeded or failed in its entirety.

Oracle 9i has addressed this issue and now enables us to skip any records that would have previously caused FORALL to fail. In the following example we will UPDATE a CUSTOMERS table in our warehouse by passing in a divisor to reduce our customers' credit limits.

First we'll create a CUSTOMERS table from a few USER_OBJECTS rows: DECLARE 2 3 TYPE typ_aa_id IS TABLE OF credit_limits.customer_id%TYPE 4 INDEX BY PLS_INTEGER; 5 6 TYPE typ_aa_divisor IS TABLE OF credit_limits.limit_adjustor%TYPE 7 INDEX BY PLS_INTEGER; 8 9 aa_ids typ_aa_id; 10 aa_divisors typ_aa_divisor; 11 12 x_bad_iteration EXCEPTION; 13 PRAGMA EXCEPTION_INIT ( 14 x_bad_iteration, -24381 ); 15 16 17 BEGIN 18 19 SELECT customer_id, limit_adjustor 20 BULK COLLECT INTO aa_ids, aa_divisors 21 FROM credit_limits; 22 23 FORALL i IN aa_ids. PUT_LINE ( 29 'Do we make it here ' || 30 ' if we hit any exceptions ?

' ); 31 32 EXCEPTION 33 34 WHEN x_bad_iteration THEN 35 36 DBMS_OUTPUT. PUT_LINE ( 48 'Error occurred on iteration ' || 49 SQL%BULK_EXCEPTIONS(i).

For example, if we wanted to INSERT a record into a table pre-9i, we would have to build a potentially lengthy VALUES statement containing all the relevant "record.attribute" listings.

Now in 9i we can simply supply the record, assuming it meets with the INSERT target's column definition.

Using record-based PL/SQL we can now BULK COLLECT into one associative array and FORALL INSERT the entire record into a target table without ever having to reference either a column from the staging or target tables or an attribute from a record element in the associative array. DECLARE 2 3 TYPE typ_aa_allobj IS TABLE OF all_objects%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_allobjs typ_aa_allobj; 6 7 BEGIN 8 9 SELECT * BULK COLLECT INTO aa_allobjs 10 FROM all_objects; 11 12 FORALL i IN aa_allobjs. PUT_LINE ( 17 TO_CHAR(SQL%ROWCOUNT) Tags: , ,