BULK UPDATE with FORALL

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Fri, 30 Nov 2007 23:23:47 GMT
Message-ID: <7Y04j.74649$YL5.35386@newssvr29.news.prodigy.net>

Oracle 10.2.0.3, Windows Server 2003

Can anybody explain to me why I'm getting an error stating that element at index does not exist with the code below? I'm using INDICES OF which I thought was supposed to take care of that.

DECLARE   TYPE t_source_rec IS RECORD
  (

    clm_src    clm_hdr.clm_src%TYPE,
    clm_sub_type   sndbx.mcare_clm_hdr.clm_sub_type%TYPE,
    clm_tob    sndbx.mcare_clm_hdr.clm_tob%TYPE,
    clm_tob_4    sndbx.mcare_clm_hdr.clm_tob_4%TYPE,
    clm_type    sndbx.mcare_clm_hdr.clm_type%TYPE,
    row_id    UROWID

   );

  TYPE t_source_tab IS TABLE OF t_source_rec

      INDEX BY PLS_INTEGER;   TYPE t_clm_sub_type IS TABLE OF clm_hdr.clm_sub_type%TYPE

      INDEX BY PLS_INTEGER;   TYPE t_clm_tob_tab IS TABLE OF clm_hdr.clm_tob%TYPE

      INDEX BY PLS_INTEGER;   TYPE t_clm_tob_4_tab IS TABLE OF clm_hdr.clm_tob_4%TYPE

      INDEX BY PLS_INTEGER;   TYPE c_clm_type_tab IS TABLE OF clm_hdr.clm_type%TYPE

      INDEX BY PLS_INTEGER;   TYPE t_rowid_tab IS TABLE OF UROWID

      INDEX BY PLS_INTEGER;   CURSOR cr_load

      RETURN t_source_rec
  IS

    SELECT b.clm_src,
           a.clm_sub_type,
           a.clm_tob,
           a.clm_tob_4,
           a.clm_type,
           b.ROWID
      FROM sndbx.mcare_clm_hdr a
      JOIN clm_hdr b
        ON ( a.sys_clmhdr_id = b.sys_clmhdr_id );

  aa_source_rec t_source_tab;

  aa_clm_sub_type   t_clm_sub_type_tab;
  aa_clm_tob    t_clm_tob_tab;
  aa_clm_tob_4    t_clm_tob_4_tab;
  aa_clm_type    t_clm_type_tab;
  aa_rowid    t_rowid_tab;

  v_cnt     PLS_INTEGER := 0;
  v_row     PLS_INTEGER;

  v_update_cnt PLS_INTEGER := 0;

• --------------------------------------------------------------------------------------------------------------

BEGIN

• Open cursor

  OPEN cr_cursor;

• Retrieve rows from cursor

  LOOP

• Fetch Claim Header rows in chunks of 300,000 until no more rows to retrieve

    FETCH cr_cursor

      BULK COLLECT INTO aa_source_rec
      LIMIT 300000;

    EXIT WHEN aa_source_rec.COUNT = 0;

• Process rows

    FOR v_row IN aa_source_rec.FIRST .. aa_source_rec.LAST     LOOP       IF ( aa_source_rec(v_row).clm_src = 'M' ) THEN

        aa_clm_sub_type(v_row) := aa_source_rec(v_row).clm_sub_type;
        aa_clm_tob(v_row)      := aa_source_rec(v_row).clm_tob;
        aa_clm_tob_4(v_row)    := aa_source_rec(v_row).clm_tob_4;
        aa_clm_type(v_row)     := aa_source_rec(v_row).clm_type;

        aa_rowid(v_row)        := aa_source_rec(v_row).row_id;

      END IF;

    END LOOP;

• Update claim header table

    FORALL v_row IN INDICES OF aa_rowid

      UPDATE clm_hdr
         SET clm_sub_type = aa_clm_sub_type(v_row),
             clm_tob      = aa_clm_tob(v_row),
             clm_tob_4    = aa_clm_tob_4(v_row),
             clm_type     = aa_clm_type(v_row),
       WHERE ROWID = aa_rowid(v_row);

    v_cnt := v_cnt + aa_rowid.COUNT;
    v_update_cnt := v_update_cnt + aa_rowid.COUNT;

• Clear memory

    aa_source_rec.DELETE;

    aa_clm_sub_type.DELETE;
    aa_clm_tob.DELETE;
    aa_clm_tob_4.DELETE;
    aa_clm_type.DELETE;

• Perform incremental commit (commit after every 1 million rows)

    IF ( v_cnt > 1000000 ) THEN

      COMMIT;
      v_cnt := 0;

    END IF;   END LOOP;

• Close cursor

  CLOSE cr_cursor;

• End and commit transaction

  COMMIT;

• Display counts

  dbms_output.put_line( '--' );
  dbms_output.put_line( 'CLM_HDR rows updated - ' || TO_CHAR(v_update_cnt,'999,999,999') );
  dbms_output.put_line( '--' );

EXCEPTION
  WHEN OTHERS THEN
    IF ( cr_cursor%ISOPEN ) THEN
      CLOSE cr_cursor;
    END IF;

    aa_source_rec.DELETE;
    aa_clm_sub_type.DELETE;
    aa_clm_tob.DELETE;
    aa_clm_tob_4.DELETE;
    aa_clm_type.DELETE;

    ROLLBACK;
    dbms_output.put_line( 'CLM_HDR_UPDATE' );     dbms_output.put_line( dbms_utility.format_error_backtrace );     RAISE; END;
.
RUN; Received on Fri Nov 30 2007 - 17:23:47 CST