Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

USING IF IN FORALL AND BULK COLLECT

Uday_NNov 23 2010 — edited Nov 23 2010
Hi All,
I wrote an program..I have doubt whether i can use if condition in FORALL INSERT OR BULK COLLECT? I can't go for 'for loop' ....Is there any way to to do validations in FORALL INSERT and BULK COLLECT like we do in 'for loop' ...

create or replace
PROCEDURE name AS
CURSOR CUR_name IS

SELECT OLD_name,NEW_name FROM DIRECTORY_LISTING_AUDIT;

TYPE V_OLD_name IS TABLE OF DIRECTORY_LISTING_AUDIT.OLD_name%TYPE;
Z_V_OLD_name V_OLD_name ;


TYPE V_NEW_name IS TABLE OF DIRECTORY_LISTING_AUDIT.NEW_name%TYPE;
Z_V_NEW_name V_NEW_name ;

BEGIN

OPEN CUR_name ;
LOOP
FETCH CUR_name BULK COLLECT INTO Z_V_OLD_name,Z_V_NEW_name;

IF Z_V_NEW_name <> NULL THEN
Z_V_OLD_name := Z_V_NEW_name ;
Z_V_NEW_name := NULL;
END IF;

FORALL I IN Z_V_NEW_name.COUNT


INSERT INTO TEMP_DIREC_AUDIT (OLD_name,NEW_name) VALUES (Z_V_OLD_name(I),Z_V_NEW_name(I));

EXIT WHEN CUR_name%NOTFOUND;
END LOOP;
CLOSE CUR_name;
END name;
This post has been answered by Dom Brooks on Nov 23 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2010
Added on Nov 23 2010
6 comments
2,299 views