Home » SQL & PL/SQL » SQL & PL/SQL » Using If statment in Bulk Collect & ForAll Problem (Oracle 9.012)
Using If statment in Bulk Collect & ForAll Problem [message #360351] Thu, 20 November 2008 10:38 Go to next message
PeteC12
Messages: 2
Registered: November 2008
Location: BRISTOL
Junior Member
Hi I was wondering if someone could assist me please.


I need to know how to use an IF statement within a FORALL loop on a collection of records I have bulk binded.(Oracle 9)


If this is not possible , then please could someone point me in the right direction or tell me where I see an example to the solution.

Many Thanks
Pete
Re: Using If statment in Bulk Collect & ForAll Problem [message #360354 is a reply to message #360351] Thu, 20 November 2008 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not possible.
Now you have to post a more precise question, if possible with an example of you want to do.

Regards
Michel
Re: Using If statment in Bulk Collect & ForAll Problem [message #360379 is a reply to message #360354] Thu, 20 November 2008 14:47 Go to previous messageGo to next message
PeteC12
Messages: 2
Registered: November 2008
Location: BRISTOL
Junior Member
Hi ,
This is a cut down example of what I am trying to do.
Basically,I am selecting into a bulk collect.
Then in the FORALL loop I want to use one of the values to pass into a cursor to retrieve me another value.
If this retrieved value exists, then do an update, else do an insert.

Any assistance would be greatly appreciated.

CREATE OR REPLACE PROCEDURE flood_fill_blpus IS

TYPE typ_pb# IS TABLE OF pd_blpus.pb#%TYPE
INDEX BY BINARY_INTEGER;

TYPE typ_uprn IS TABLE OF pd_blpus.uprn%TYPE
INDEX BY BINARY_INTEGER;


CURSOR c_gb_pk(p_uprn NUMBER) IS
  SELECT gb.gb_pk
  FROM gh_blpus gb
  WHERE gb.uprn = p_uprn;

t_typ_pb# 			typ_pb#;
t_typ_uprn			typ_uprn;

BEGIN
SELECT pb.PB#,
       pb.UPRN
 BULK COLLECT INTO 
    t_typ_pb#, 
    t_typ_uprn 
FROM bs7666.pd_blpus pb;

FORALL rec IN t_typ_pb#.FIRST..t_typ_pb#.LAST 

  -- Do we have this record already in gh_blpus
  -- Use uprn to find out
  -- If so then update that record

  OPEN c_gb_pk(t_typ_uprn(rec)); 
    FETCH c_gb_pk INTO v_gb_pk;
  CLOSE c_gb_pk;

  IF v_gb_pk IS NOT NULL THEN
    UPDATE ....
  ELSE
    INSERT ...
  END IF;

END;


[Edit MC: Added code tags]

[Updated on: Fri, 21 November 2008 00:57] by Moderator

Report message to a moderator

Re: Using If statment in Bulk Collect & ForAll Problem [message #360446 is a reply to message #360379] Fri, 21 November 2008 01:00 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without speaking about the goal of the procedure (study if you can't do it with a single MERGE statement), your FORALL is actually a FOR LOOP.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: DAL & GF generator for packages in Oracle ...
Next Topic: select count(*)
Goto Forum:
  


Current Time: Wed Dec 07 12:53:33 CST 2016

Total time taken to generate the page: 0.08282 seconds