Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BEGIN....END   within IF......END IF Construct

Posted on 2011-05-06
7
Medium Priority
?
677 Views
Last Modified: 2013-12-07
Can you have several BEGIN.....END  constructs within a single IF.....END IF? Would it be wise to insert an EXCEPTION Handler within each BEGIN.....END.

For Example:
IF........................ THEN -- Start
BEGIN
     NULL;
END;

BEGIN
    NULL;
END;

BEGIN
      NULL;
END;


END IF; -- End

Yaj
0
Comment
Question by:yajesh
7 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35705952
Your syntax is perfectly valid.

Why do you need to have multiple blocks?  Usually, this is necessary to code for specific exceptions.  Is this the case for you?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35705965
You would need to surround all of the inner block in it's own BEGIN - IF like this:


IF ...
BEGIN

  BEGIN
     NULL;
  END;

  BEGIN
    NULL;
  END;

  BEGIN
      NULL;
  END;

END IF;
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 35706000
knightEknight, it isn't necessary to surround the individual blocks with a BEGIN and END.  The syntax supplied by vajesh will work.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:yajesh
ID: 35706035
A very valid question, Milleniumaire.
First of all I am newbie to PL/SQL, still learning tricks of the trade.

In my IF. THEN...............END IF construct, I have few SELECT followed by UPDATE statements. Say for example I have 3 groups of SELECT....INTO respective variables with its own WHERE criteria followed by UPDATE another TABLE based on SELECT statement.

So my code would look something like this:
IF ................ THEN
  SELECT.................

  UPDATE

  SELECT...................

  UPDATE

  etc..
etc..

END IF;

So I was thinking if I can use BEGIN....END within each SELECT....UPDATE group, then I mighty be able to insert ECEPTION Handles for each group withion each BEGIN.... END, instead of single EXCEPTION Handler before END IF.


yaj
 
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35706072
What you mention is the exact reason to have the ability to nest PL/SQL blocks:  The ability to trap and handle exceptions with a local scope.
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 1000 total points
ID: 35706120
Yes, your idea to include an exception handler for each statement will allow you to trap and cleanly handle the exception, if this is what you need to do, or to simply raise (or re-raise) your own exception to identify exactly which statement failed.

If you simply want to identify which statement failed, then add an exception handler after the statement and get it to call raise_application_error
e.g.
  BEGIN
      UPDATE .....
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error(-20001,'UPDATE failed with:  '||SQLERRM);
  END;
0
 

Author Closing Comment

by:yajesh
ID: 35706206
Thanks, Milleniumaire,
I understand it better now.

Further to that, I was also able to find additional details, that is also most probably in line with your suggestion too.

http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/AcompleteexampleusingRAISEAPPLICATIONERROR.htm

yaj
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question