Please help ~
I have a cursor read from a collection that passed in, while I'm doing validation to the collection,
How do I write a statement to remove the bad records from the collection (so the second validation will not include the records that already found invalid)?
PROCEDURE main is my_collection mytabletype;
begin
UPDATE my_queue q
SET ...
WHERE ....
RETURNING q.queue_id BULK COLLECT INTO my_collection;
validate_my_file (my_collection);
end;
/
PROCEDURE validate_my_file (my_collection IN mytabletype ) IS
CURSOR my_invalid_file_cursor IS
SELECT q.queue_id,
q.queue_status,
q.file_name
d.doc_name
FROM MY_queue q,
XYZ_documents d
WHERE q.queue_id NOT IN
(SELECT h.queue_id
FROM my_headers h
WHERE h.status = 'XYZ')
AND q.queue_status = 'XYZ'
AND d.doc_key = q.doc_key
AND q.QUEUE_ID IN (SELECT QUEUE_ID FROM TABLE(my_collection) X)
cursor another_invalid_cursor is ...
begin
OPEN my_invalid_file_cursor;
FETCH my_invalid_file_cursor INTO l_invalid_file_rec;
WHILE my_invalid_file_cursor%FOUND LOOP
create_exception (on error_log table directly)....;
update_queue_status (on queue table directly) ....
--- can this be done here ?
Delete from v_my_collection ... ???
FETCH my_invalid_file_cursor INTO l_invalid_file_rec;
END LOOP;
--- so when I open another_invalid_cursor the collection does not contain previous bad records
OPEN another_invalid_cursor;
....
End validate_my_file;