Home » SQL & PL/SQL » SQL & PL/SQL » Inner and outer loop in cursor (Oracle 11g)
Inner and outer loop in cursor [message #589649] Tue, 09 July 2013 07:13 Go to next message
na.dharma@gmail.com
Messages: 74
Registered: May 2008
Location: bangalore
Member

BEGIN
FOR i IN 1 .. 10
LOOP
BEGIN
DECLARE
d NUMBER;
BEGIN
SELECT 1
INTO d
FROM DUAL
WHERE ROWNUM = 4;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ROLLBACK;
RAISE;
END;

DBMS_OUTPUT.put_line (i);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Test');
END;
END LOOP;
END;

Assume the above is my code; there is inner and outer block.

Currently if inner block is failed, the outer block also going to exception block

But, my requirement is if inner block is failed it should not go to outer block, still the loop should continue and print 1...10 rows

Please suggest...
Re: Inner and outer loop in cursor [message #589650 is a reply to message #589649] Tue, 09 July 2013 07:16 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Please read and follow How to use [code] tags and make your code easier to read? the above code is really hard to read.
2) What do you think the raise command does?
Re: Inner and outer loop in cursor [message #589652 is a reply to message #589649] Tue, 09 July 2013 07:33 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
In addition, take a look at Reraising Current Exception with RAISE Statement and Exception Propagation.

Regards,
Dariyoosh
Re: Inner and outer loop in cursor [message #589654 is a reply to message #589652] Tue, 09 July 2013 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60052
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, what is the purpose of this ROLLBACK statement?
Do you REALLY want to roll back all work done before you execute this PL/SQL block?

Regards
Michel
Re: Inner and outer loop in cursor [message #589659 is a reply to message #589654] Tue, 09 July 2013 08:55 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
Ok, setting aside any issues about whether or when you should be using ROLLBACK in such a fashion, your question seems to revolve around how to continue an outer loop when an exception is raised in an inner loop. Hopefully you will find the code below of some help:

declare
    procedure foo(x number) is
    begin
        if x=3 then
            raise no_data_found;
        else  
            null;
        end if;
    end;
begin
    <<outer>>
    for i in 1..5 loop
        begin
            <<inner>>
            for j in 1..10 loop
                dbms_output.put_line('i:' || i || ',j: ' || j);
                foo(j);
            end loop inner;
        exception 
                when no_data_found
                then
                    dbms_output.put_line('Yikes: ' || SQLERRM);
                    continue outer;
        end;
    end loop outer;
end;
/

SCOTT@ORCL> @x.sql
i:1,j: 1
i:1,j: 2
i:1,j: 3
Yikes: ORA-01403: no data found
i:2,j: 1
i:2,j: 2
i:2,j: 3
Yikes: ORA-01403: no data found
i:3,j: 1
i:3,j: 2
i:3,j: 3
Yikes: ORA-01403: no data found
i:4,j: 1
i:4,j: 2
i:4,j: 3
Yikes: ORA-01403: no data found
i:5,j: 1
i:5,j: 2
i:5,j: 3
Yikes: ORA-01403: no data found

PL/SQL procedure successfully completed.

[Updated on: Tue, 09 July 2013 08:57]

Report message to a moderator

Re: Inner and outer loop in cursor [message #589660 is a reply to message #589659] Tue, 09 July 2013 09:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
You misread the topic. OP is talking about inner/outer BLOCKs, not about inner/outer LOOPs. So all OP needs is:

BEGIN
    FOR i IN 1 .. 10 LOOP
      DECLARE
          d NUMBER;
      BEGIN
          SELECT  1
            INTO  d
            FROM  DUAL
            WHERE ROWNUM = 4;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN NULL;
     END;
     DBMS_OUTPUT.put_line (i);
   END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: Inner and outer loop in cursor [message #589661 is a reply to message #589660] Tue, 09 July 2013 09:19 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
My apologies. Perhaps my mistake was due to his subject wording ("Inner and outer loop in cursor").
Re: Inner and outer loop in cursor [message #589683 is a reply to message #589661] Wed, 10 July 2013 00:27 Go to previous messageGo to next message
gauravy
Messages: 1
Registered: March 2012
Location: Gurgaon
Junior Member
Hi,

Remove Raise from the exception section of inner block.

Thanks
Re: Inner and outer loop in cursor [message #589686 is a reply to message #589683] Wed, 10 July 2013 00:37 Go to previous messageGo to next message
na.dharma@gmail.com
Messages: 74
Registered: May 2008
Location: bangalore
Member

Thanks for every one supporting, we have fixed this issue using pragama automous_transaction.
Re: Inner and outer loop in cursor [message #589689 is a reply to message #589686] Wed, 10 July 2013 01:26 Go to previous message
Michel Cadot
Messages: 60052
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see the relation with this pragma.
Post your final code (and format it following the link cookiemonster gave you).

Regards
Michel
Previous Topic: Inconsistent execution plans
Next Topic: Rollback issue
Goto Forum:
  


Current Time: Thu Dec 25 08:22:46 CST 2014

Total time taken to generate the page: 0.09476 seconds