ORA-22167 when using RETURNING... BULK COLLECT INTO feature
153933Jun 15 2006 — edited Jun 16 2006I posted this on the General Board, but I'm still having the issue. Any help would be appreciated!
I'm loading raw data into a staging table, and want to perform a series of validations before moving it into the system. Records that fail are marked invalid in the stage table, and then an error message written to another table. When updating the 'invalid' flag in the (database) stage table, I return the record identifier into a pl/sql table, then iterate through said table to insert into my (database) error table. This works great for my first validation, but when I use the pl/sql table for the second validation, I get ORA-22167, which basically complains about the subscript supplied to TRIM being out of range. I've tried DELETEing the pl/sql table, setting it to NULL, re-initializing it... none of which works. I have also tried using a brand new table for each validation, but I still get the same message. What am I missing?
Psuedo-code is like this (please ignore any syntax errors... real code is syntactically correct) :
DECLARE
TYPE RECORD_SEQ_TBL IS TABLE OF fcsf_arc.record_seq_nbr%TYPE;
lt_bad_fcsf_tbl RECORD_SEQ_TBL := NULL;
BEGIN
UPDATE <stg_tbl>
SET valid_ind = 'N'
WHERE <error condition 1>
RETURNING seq_nbr BULK COLLECT INTO lt_bad_fcsf_tbl;
FORALL j IN lt_bad_fcsf_tbl.FIRST..lt_bad_fcsf_tbl.LAST
INSERT INTO <error_tbl>
VALUES ('message', lt_bad_fcsf_tbl(j));
-- works ok so far...
-- I've tried various combinations of the next three comments to no avail (including doing nothing at all)
-- lt_bad_fcsf_tbl.DELETE;
-- lt_bad_fcsf_tbl := NULL;
-- lt_bad_fcsf_tbl := RECORD_SEQ_TBL();
-- now I want to do my second validation
UPDATE <stg_tbl>
SET valid_ind = 'N'
WHERE <error condition 2>
RETURNING seq_nbr BULK COLLECT INTO lt_bad_fcsf_tbl;
-- and THAT is where I get the ORA-22167