Updating record from cursor oracle
Due to a lack of validation on our front-end, we've received a file to process that contains a zero divisor for two customers.This will cause two iterations of our FORALL statement to error but rather than fail the entire transaction, we will report the errors (in a production system you would probably store these off in an exceptions table) and allow the "clean" data to process through. LAST SAVE EXCEPTIONS 24 UPDATE customers 25 SET credit_limit = credit_limit/aa_divisors(i) 26 WHERE customer_id = aa_ids(i); 27 28 DBMS_OUTPUT.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) 19 ' records fetched.' ); 20 21 END; 22 / DECLARE 2 3 TYPE typ_aa_object IS TABLE OF user_objects%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_objects typ_aa_object; 6 7 v_predicates VARCHAR2(256) := :where_clause; 8 9 cv SYS_REFCURSOR; 10 11 BEGIN 12 13 /* Open a cursor variable for dynamic SQL... COUNT
Bulk binding in PL/SQL was introduced in Oracle 8i as the FORALL statement.