Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Wednesday, 6 April 2016

Nested Blocks in PL/SQL

A block within a block called as a Nested Block

The variable declared in any block is live as long as the block in which it is declared is getting executed.

In the Nested Block above, the variable X is declared in the outer block and hence it is available in the inner as well as the outer block however the variable Y is declared in the inner block hence it is available only to the inner block.

Now suppose the variable declared in the inner block is also given the same name as the variable in outer block say X then in the inner block when we refer to X it will be always taken as the local variable X and not X of outer block. This effect is called as “Local Screening”. In such situation if one wants to refer to the variable of the outer block then the outer block is given a label and in the inner block when one wants to refer to the outer block variable then it is prefixed with the outer block label.

Example: Labeled Nested Block

-- Outer Block Starts here

<<l_outer>>                                  -- Label given to the outer block
   v_empname   VARCHAR2 (20) := 'Neeta';
   v_salary    NUMBER (4)    := 1000;
   DECLARE                                         -- Inner Block Starts Here
      v_empname   VARCHAR2 (30) := 'Richa';
      v_address   VARCHAR2 (20) := 'Pune';
   END;                                                    -- Inner block ends

END;                                                       -- Outer Block Ends
Share this article :


  1. Halo,

    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Nested Blocks in PL/SQL!

    I have data in a column

    I need to retrieve values between '#'. I need to get output as below

    var1 = abcd
    var2 = efghi
    var3 = jlmn
    var4 = opqrs

    Similary if column value is


    var1 = abcd
    var2 = efghi
    var3 = jlmnopqrs
    var4 = null

    But nice Article Mate! Great Information! Keep up the good work!

    Thanks and Regards

  2. Hello,

    11/10!! Your blog is such a complete read. I like your approach with. Clearly, you wrote it to make learning a cake walk for me.

    how to find first nonspace character from end of the string

    ex: 'abc def g '

    I should get 4 in this case. (the first nonspace character from backwards in the above example is g and the position is 4 from backwards.

    Could you help me in getting this?

    I am so grateful for your blog. Really looking forward to read more.

    Best Regards,

  3. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check

  4. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.