Prev: Sql*Loader, Bequeath and ulimit
Next: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE
From: Dereck L. Dietz on 30 Nov 2007 18:23
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;


From: Preston on 1 Dec 2007 04:07
Dereck L. Dietz wrote:

> 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.

INDICES OF will handle missing 'rows' in a collection (i.e. when you
delete a row during a loop), but it won't take care of a missing (null)
'column'. The error message is telling you that one of the 'columns'
(elements) doesn't exists in the row it's failing on.


--
Preston.
From: Dereck L. Dietz on 1 Dec 2007 09:34

"Preston" <dontwantany(a)nowhere.invalid> wrote in message
news:Dv94j.499$Dh6.15(a)newsfe4-win.ntli.net...
> Dereck L. Dietz wrote:
>
>> 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.
>
> INDICES OF will handle missing 'rows' in a collection (i.e. when you
> delete a row during a loop), but it won't take care of a missing (null)
> 'column'. The error message is telling you that one of the 'columns'
> (elements) doesn't exists in the row it's failing on.
>
>
> --
> Preston.

So if any of the columns in the update below contains a NULL value for one
of the values it would fail? From checking the data I do know that the
AA_CLM_TOB array would have elements which would be NULL.

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);


 | 
Pages: 1
Prev: Sql*Loader, Bequeath and ulimit
Next: Deadly OPTIMIZER_SECURE_VIEW_MERGING=TRUE