OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » exceptions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

exceptions [message #606494]

Fri, 24 January 2014 16:55

nataliafoster26
Messages: 64
Registered: October 2013

Member

i have been reading online about expections but i still dont got a clear answer
lets say you have something like this

no_credit_card              EXCEPTION;
EXPIRED_CREDIT_CARD         EXCEPTION;
.....
begin
if x is null then

  raise no_credit_card
  elsfif  y is null then
   raise inactive_credit_card
  else  .....................
     if then....
      end if;
   insert_into .......
end if;  --first IF

........
end;

in this code lets say x is null so it will raise no_credit_card
will this still do the insert_into dml?
or will it just exit and raise a exception?

sorry is question doesnt make sense

Report message to a moderator

Re: exceptions [message #606496 is a reply to message #606494]

Fri, 24 January 2014 17:12

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Well, you provided rather a pseudocode, as a result, based on the lines I can read in your code, it seems to me that there is no exception handler, which means that the raised exception (after checking X is null) is propagated and therefore, no, the insert will not be not done.

Maybe you want to have a look at Exception Propagation section at the online documentation in order to see what I'm talking about.

Report message to a moderator

Re: exceptions [message #606850 is a reply to message #606496]

Thu, 30 January 2014 00:35

subhra_88
Messages: 14
Registered: April 2007
Location: Bangalore

Junior Member

When you raise an exception the control is passed on to the 'exception' block and all other lines of code in the normal flow will be bypassed. In the exception block you need to handle the exceptions.

Report message to a moderator

Re: exceptions [message #606851 is a reply to message #606850]

Thu, 30 January 2014 00:52

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

In the exception block you need to handle the exceptions.

Or not, depending on your application requirements.

Report message to a moderator

Re: exceptions [message #606939 is a reply to message #606851]

Thu, 30 January 2014 22:24

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

To understand exception handling you need to grasp the idea of code blocks. Consider this piece of code (concentrate on its layout)

declare
   a number;
begin
   a := 1;
   a := 2;
   begin
      a := 3;
      a := 4;
      begin
         a := 5;
         a := 6;
      end;
   end;
end;
/

From this example you should easily see that this code is composed of three blocks. Additionally these blocks are nested one within the other.

Contrast it with this example:

declare
   a number;
begin
   begin
      a := 1;
      a := 2;
   end;
   begin
      a := 3;
      a := 4;
   end;
   begin
      a := 5;
      a := 6;
   end;
end;
/

This code does exactly the same thing as the previous one. But as you can see, the blocking structure is different.

In this case, the blocking structure is only being shown to you so that you can understand how code always exists in some block. The blocking used here does not actually affect the code, at least not yet.

One reason why blocking is important is that is defines SCOPE rules. SCOPE determines when variables can be referenced, and how exceptions are handled. There are no exceptions defined in any of what has been shown so far. To see the significance of SCOPING rules, consider this code snippet.

set serveroutput on

declare
   a number;
begin
   a := 1;
   declare
      a number;
   begin
      a := 2;
   end;
dbms_output.put_line('a='||a);
end;
/

What will the put_line command show when this anonymous pl/sql block is executed? The answer of course is you will see "a=1". You should understand some things when you see this snippet:

1. it is perfectly legal.

2. there are two different variables, each of which happens to be named A.

3. scoping rules define when the variables exists and do not exist and thus when they can be referenced and not referenced, and when naming conflicts exists how they are resolved.

4. scoping is determined by the block structure.

5. it should be obvious why the output result will be a=1.

Exception handling works in the same fashion. You can define an exception handler for each block of code. When an error occurs, processing of the block ends, and a GOTO is done to the first line of exception handler code that is valid for the block in which the error occurred. SCOPE rules determine what exception handler this will be, in the same way they controlled how variables can be referenced. One key point here is that when an error occurs, processing stops. Rules of rollback control if database changes are kept or not. If the error is not trapped and handled by an exception handler then a rollback will occur which of course means any database changes made before the error that are not yet committed will be undone. You should also understand at this point that since processing stops on an error, DML that follows code with the error has no chance to be executed.

Based on this, for your example the answer is NO.

Hope this gets you started on your question. Kevin

Report message to a moderator

Previous Topic:	Row number for null
Next Topic:	procedure out parameter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:06:01 CDT 2024