Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks

The most efficient solution - Oracle

This is part 7 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to exception handling and went through some predefined exceptions. In this article, we will focus on user defined exceptions in PL/SQL and some tips for working with exceptions efficiently.

  1. Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks
  2. A frequently faced problem in exception handling
  3. The solution
  4. The most efficient solution
  5. Nested blocks with local variable declarations
By: Jagadish Chatarji
Rating: starstarstarstarstar / 36
July 05, 2005

print this article



This section not only introduces you the best solution to the problems defined in the previous sections, but also introduces you to nested blocks in the PL/SQL programming structure. Before directly looking into the program, let me define for you the concept of nested blocks.

Everybody knows that a typical PL/SQL block structure starts with DECLARE (the declaration section is actually optional), continues through BEGIN, handles exceptions through the EXCEPTION section(which is also optional), and finally ends at END. This PL/SQL block can be further nested inside with its own nested PL/SQL blocks (again with DECLARE, BEGIN, EXCEPTION and END sections). And they can be nested any number of times.

If properly nested and defined, each nested block will have its own boundary of variable declarations or exceptions, which will not be seen (or accessed) by other parallel nested blocks. The following example introduces you to two parallel nested blocks inside a main PL/SQL block. The example also gives you the best solution to solve the problems explained in the previous two sections.

  v_empno1 emp.empno%Type := &empno1;
  v_ename emp.ename%Type;
  v_empno2 emp.empno%Type := &empno2;
    Select ename into v_ename
    From emp Where empno=v_empno1;
    dbms_output.put_line('Name: '||v_ename);
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno1);


    Select ename into v_ename
    From emp Where empno=v_empno2;
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno2);

In the above program, every SELECT gets executed within its own block (having no relation to one another) and displays either employee information or an exception message. We also need to observe that the two nested blocks are using variables from the main (parent) block without having their own local declarations. And finally, one should agree that the above style of programming looks clearer and easier to read.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: