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

Nested blocks with local variable declarations - 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

This is just an extension to the previous section. I will show you the sample example in the previous section, with a few variations in nested blocks. The only issue I wanted to address here is that the nested blocks can have their own local declarations without having any relation to their parent or other friendly (parallel) blocks.

Declare
  v_empno1 emp.empno%Type := &empno1;
  v_empno2 emp.empno%Type := &empno2;
Begin

 

  Declare
    v_ename1 emp.ename%Type;
  Begin
    Select ename into v_ename1
    From emp Where empno=v_empno1;
    dbms_output.put_line('Name: '||v_ename1);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno1);
  End;

 

 

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno2);
  End;

End;

From the above program, you can clearly observe that every nested block has its own DECLARE section. The variable 'v_ename1' in the first nested block cannot be accessed beyond the first block (in this case, cannot be accessed by the parent block or even the second block) and the case of 'v_ename2' is similar. You can also localize all variables (based on the situation) and remove the declarations section from the parent block, as shown in the following example:

Begin

 

  Declare
    v_ename1 emp.ename%Type;
    v_empno1 emp.empno%Type := &empno1;
  Begin
    Select ename into v_ename1
    From emp Where empno=v_empno1;
    dbms_output.put_line('Name: '||v_ename1);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno1);
  End;

 

 

  Declare
    v_ename2 emp.ename%Type;
    v_empno2 emp.empno%Type := &empno2;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno2);
  End;

End;

That is how we can separate every aspect of the program within a PL/SQL block into several blocks. And each of these blocks can be further separated or nested any number of times.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- 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: