Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Looping in a pl/sql block

    Hi All,

    I have a procedure which does the following. I'm not able to determine how to write the loop here.
    Procedure test(pi_full_id in varchar2)
    is
    begin
    l_str := pi_full_id; --where pi_full_id is 'abcd,abc,d'
    --I want to loop through for all the values in the string 'abcd,abc,d' - how can I do that.
    l_cnt: = instr(l_str,',')-1;
    l_substr := substr(l_substr,1,1_cnt);
    if l_cnt = -1 then
    l_substr := l_str;
    end if;

    bunch of updates and inserts


    l_substr := substr(l_substr,l_cnt+2);
    if l_cnt = -1 then
    exit;
    end if;

    end;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by nandinir
    --I want to loop through for all the values in the string 'abcd,abc,d' - how can I do that.
    You'll be surprised, but PL/SQL has the LOOP statement precisely for this purpose.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I know I'll be able to use loop....end loop; but can it be used without a condition?

    l_str := pi_full_id; --where pi_full_id is 'abcd,abc,d'
    loop?? -- this will go into an infinite loop.
    l_cnt: = instr(l_str,',')-1;
    l_substr := substr(l_substr,1,1_cnt);

    if l_cnt = -1 then
    l_substr := l_str;
    end if;

    bunch of inserts and updates...

    l_substr := substr(l_substr,l_cnt+2);
    if l_cnt = -1 then
    exit;
    end if;

    end loop;
    Last edited by nandinir; 07-28-09 at 16:28.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    LOOP Statements

    http://asktom.oracle.com contains many fine coding examples.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    for pnt in 1 .. length(pi_full_id) loop
    ...
    end loop;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    Thanks Beilstwh! It work fine.

    But there can be situations where pi_full_id can be null. In such a case it fails. what can I do for this scenario?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what can I do for this scenario?
    What do you want to happen when pi_full_id is null?

    Do you know how to use NVL() function?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jul 2005
    Posts
    276
    but how can i use an NVL here--

    for i in 1..length(pi_full_id) ??

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post functioning solution for NOT NULL strings.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    for pnt in 1 .. length(nvl(pi_full_id,0)) loop
    ...
    end loop;


    like anacedent suggested.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •