Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Question Unanswered: EXCEPTION inside FOR..LOOP

    HI,

    declare
    cursor c1 is
    select sid,scity
    from supplier;
    wrong_city exception;
    begin
    for s_rec in c1 loop
    if s_rec.scity = 'bangalore' then
    raise wrong_city;
    end if;
    end loop;
    exception
    when wrong_city then
    dbms_output.put_line('wrong city');
    end;

    result:

    SQL> /
    wrong city

    PL/SQL procedure successfully completed.


    Here in my table 'SUPPLIER' i've more than 1 record which has the value 'BANGALORE'. But this code ends as sson as it got one record with the value 'BANGALORE'.
    I want continue the loop until all the records are checking.
    How can i go back to that FOR ..LOOP after fetching and displaying the exception?


    can anybody give me the solution?

    thanx .

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: EXCEPTION inside FOR..LOOP

    if you do something of this kind, you entire for loop will get execute:


    declare
    cursor c1 is
    select sid,scity
    from supplier;

    wrong_city exception;

    flg_wrong_city := 'N';
    begin
    for s_rec in c1 loop
    if s_rec.scity = 'bangalore' then
    flg_wrong_city:='Y';
    end if;
    end loop;

    if flg_wrong_city = 'Y' then
    raise wrong_city;
    end if;

    exception
    when wrong_city then
    dbms_output.put_line('wrong city');
    end;


    ----------------------------------------------------------

    Originally posted by raseena
    HI,

    declare
    cursor c1 is
    select sid,scity
    from supplier;
    wrong_city exception;
    begin
    for s_rec in c1 loop
    if s_rec.scity = 'bangalore' then
    raise wrong_city;
    end if;
    end loop;
    exception
    when wrong_city then
    dbms_output.put_line('wrong city');
    end;

    result:

    SQL> /
    wrong city

    PL/SQL procedure successfully completed.


    Here in my table 'SUPPLIER' i've more than 1 record which has the value 'BANGALORE'. But this code ends as sson as it got one record with the value 'BANGALORE'.
    I want continue the loop until all the records are checking.
    How can i go back to that FOR ..LOOP after fetching and displaying the exception?


    can anybody give me the solution?

    thanx .
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: EXCEPTION inside FOR..LOOP

    A syntax correction ...


    declare
    cursor c1 is
    select sid,scity
    from supplier;

    wrong_city exception;

    flg_wrong_city <b>char(1)</b>;
    begin
    flg_wrong_city := 'N';
    for s_rec in c1 loop
    if s_rec.scity = 'bangalore' then
    flg_wrong_city:='Y';
    end if;
    end loop;

    if flg_wrong_city = 'Y' then
    raise wrong_city;
    end if;

    exception
    when wrong_city then
    dbms_output.put_line('wrong city');
    end;


    ---------------------------------------------------------- [/SIZE][/QUOTE]
    Oracle can do wonders !

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    Once an exception is raised, PL/SQL flow control passes to the
    Exception handler. Once the exception is handled, the PL/SQL block
    will be exited. So the control won't flow back again to the FOR LOOP .
    SATHISH .

  5. #5
    Join Date
    Nov 2003
    Posts
    87

    Smile

    hi all,

    I got the solution.I changed my code as follows.

    declare
    cursor c1 is
    select sid,scity
    from supplier;
    wrong_city exception;
    begin
    for s_rec in c1 loop
    begin
    if s_rec.scity = 'bangalore' then
    raise wrong_city;
    end if;
    exception
    when wrong_city then
    dbms_output.put_line('wrong city');
    end;
    end loop;
    end;


    thanx.

Posting Permissions

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