Home » SQL & PL/SQL » SQL & PL/SQL » Goto option not working in EXCEPTION (ORacle 10g Windows XP)
Goto option not working in EXCEPTION [message #576040] Thu, 31 January 2013 00:56 Go to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
Hi Experts,

I have the code like below. I need to execute the lines after the error came in middle.
Simply I need the output as follows
1
2
3
4
5


declare
a number;
b number:=10;
begin
dbms_output.put_line('1');
dbms_output.put_line('2');
dbms_output.put_line('3');
a:=b/0;
<<abcd>>
dbms_output.put_line('4');
dbms_output.put_line('5');
exception when others then
goto abcd;
end;


But the label (abcd) cannot be used in Exception. So any one can you give the solution for this..

Thanks
Veera

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576041 is a reply to message #576040] Thu, 31 January 2013 01:03 Go to previous messageGo to next message
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Move 4 and 5 into the exception handling section, such as
DECLARE
   a   NUMBER;
   b   NUMBER := 10;
BEGIN
   DBMS_OUTPUT.put_line ('1');
   DBMS_OUTPUT.put_line ('2');
   DBMS_OUTPUT.put_line ('3');
   a := b / 0;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('4');
      DBMS_OUTPUT.put_line ('5');
END;

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576042 is a reply to message #576041] Thu, 31 January 2013 01:06 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
Hi Littefoot,

This is an example i wrote here...

In case i have more than 1000 lines after then it is not possible to move to exception.

Also I dont know, from which condition, the error will raise.

Thanks
Veera

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576043 is a reply to message #576042] Thu, 31 January 2013 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ INDENT the code
2/ WHEN OTHERS is a bug

You have to enclose EACH statement in a BEGIN/EXCEPTION/END block, each one trapping the exception that can raise but NOT any exception.

Regards
Michel

[Updated on: Thu, 31 January 2013 01:13]

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576044 is a reply to message #576043] Thu, 31 January 2013 01:18 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
Hello Mr. Michel Cadot, The great tiger of ORAFAQ,

Always dont demotivate who has posted in orafaq.... Try to give the answer, if you cant understand then reply like need more clarity or else leave it.. some one can help us....

1. I wrote the above code is for sample only. I want to know how to resume the error in PLSQL. If you know Visual Basic then one option is there. ON ERROR RESUME NEXT. It will resume the error and proceeed the further line. I want to know the same option is there in Oracle or not.
2. I agree for this
3. I need workaround for this.

Regards
Veera

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576045 is a reply to message #576040] Thu, 31 January 2013 01:19 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Why do you want to use a LABEL ? Simply catch the exception
DECLARE
  a   NUMBER;
  b   NUMBER := 10;
BEGIN
  DBMS_OUTPUT.put_line ('1');
  DBMS_OUTPUT.put_line ('2');
  DBMS_OUTPUT.put_line ('3');

  a := b / 0;
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.put_line ('4');
    DBMS_OUTPUT.put_line ('5');
END;

1
2
3
4
5
PL/SQL procedure successfully completed.

[Updated on: Thu, 31 January 2013 01:21]

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576046 is a reply to message #576045] Thu, 31 January 2013 01:21 Go to previous messageGo to next message
mvmkandan
Messages: 68
Registered: May 2010
Location: Trivendrum
Member
Hi Jum,
I agree with your comment. But in oracle, if the error is occured then it comes to exception part. But In case If i want ignore the error and continue with next line, how can we do. This is my doubt. (Like ON ERROr RESUME NEXT option availble in Visual Basic 6.0)

Thanks
Veera

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576047 is a reply to message #576044] Thu, 31 January 2013 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Always dont demotivate who has posted in orafaq.... Try to give the answer, if you cant understand then reply like need more clarity or else leave it.. some one can help us....


Note that I realise this and modified my post when you were writing (sorry some bad days):

Michel Cadot wrote on Thu, 31 January 2013 08:11
1/ INDENT the code
2/ WHEN OTHERS is a bug

You have to enclose EACH statement in a BEGIN/EXCEPTION/END block, each one trapping the exception that can raise but NOT any exception.


Regards
Michel

[Updated on: Thu, 31 January 2013 01:23]

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576048 is a reply to message #576047] Thu, 31 January 2013 01:28 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
May be you search for something like WHENEVER SQLERROR
--WHENEVER SQLERROR EXIT

WHENEVER SQLERROR CONTINUE

exec  DBMS_OUTPUT.put_line (1);
exec  DBMS_OUTPUT.put_line (2);
exec  DBMS_OUTPUT.put_line (3);
exec  DBMS_OUTPUT.put_line (3/0);
exec  DBMS_OUTPUT.put_line (4);
exec  DBMS_OUTPUT.put_line (5);

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576049 is a reply to message #576048] Thu, 31 January 2013 02:04 Go to previous messageGo to next message
sss111ind
Messages: 636
Registered: April 2012
Location: India
Senior Member
From Oracle Documentation
1.A GOTO statement can branch from an exception handler into an enclosing block.
2.A GOTO statement cannot branch into an exception handler, or from an exception handler into the current block.

You need to handle how _jum and LittleFoot has explained.
DECLARE
  a NUMBER;
  b NUMBER := 10;
BEGIN
  DBMS_OUTPUT.put_line ('1');
  DBMS_OUTPUT.put_line ('2');
  DBMS_OUTPUT.PUT_LINE ('3');
  BEGIN--this is the block where an exception may be raised
    A := B / 0;
  EXCEPTION--handle it
  WHEN OTHERS THEN
    NULL;
    dbms_output.put_line('error handled');
  END;
  --the continue with remaining.
  DBMS_OUTPUT.put_line ('4');
  DBMS_OUTPUT.put_line ('5');
END;


Regards,
Nathan

Report message to a moderator

Re: Goto option not working in EXCEPTION [message #576050 is a reply to message #576049] Thu, 31 January 2013 02:12 Go to previous message
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You need to handle how _jum and LittleFoot has explained.


And also me!


  WHEN OTHERS THEN
    NULL;
    dbms_output.put_line('error handled');

This is silly because:
1/ We already repeated about WHEN OHERS, so NEVER again put it in an example here (and don't tell OP did it)
2/ What is the purpose of "NULL;" here? Silly isn't it?

Regards
Michel

[Updated on: Thu, 31 January 2013 02:12]

Report message to a moderator

Previous Topic: accessing xml values from a table
Next Topic: Order by Month
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 17 00:57:17 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.