How can I print the value of a variable declared in an outer Oracle PL/SQL block from an inner one?

I have a variable, (say 'A') declared in a pl/sql block. In an inner pl/sql block I declared another variable 'A'. Now how can i print the value of outer 'A' from the inner pl/sql block?


It sounds as if you want to do something like this:

DECLARE
   i INTEGER;
BEGIN
   i:= 1;
   DECLARE
      i INTEGER;
   BEGIN
      i:=2;
      DBMS_OUTPUT.PUT_LINE(outer.i);
      DBMS_OUTPUT.PUT_LINE(inner.i);
   END;
   DBMS_OUTPUT.PUT_LINE(i);
END;


Unfortunately you can't do that. Attempting to compile the above code generates an "ORA-6550" Oracle PL/SQL compilation error. However there are two ways around this. You can either label your anonymous blocks or you can convert them into procedures which of course have to be given names.

Labeling Anonymous PL/SQL blocks

By prefixing each anonymous PL/SQL block with a label we are effectively naming them and we can use these labels inside the code to distinguish one variable "i" from another as in the following example:

<<BLOCK1>>
DECLARE
   i INTEGER;
BEGIN
   i:= 1;
   <<BLOCK2>>
   DECLARE
      i INTEGER;
   BEGIN
      i:=2;
      DBMS_OUTPUT.PUT(BLOCK1.i||' ');
      DBMS_OUTPUT.PUT(BLOCK2.i||' ');
   END;
   DBMS_OUTPUT.PUT_LINE(i);
END;


The above code will compile and when run will produce the following results: 1 2 1. Extra spaces have been added just for readability.

Note that the normal rules in respect of the scope of variables still apply. The following PL/SQL code won't compile as "block2" is out of scope in the last dbms_output statement in this example:

<<BLOCK1>>
DECLARE
   i INTEGER;
BEGIN
   i:= 1;
   <<BLOCK2>>
   DECLARE
      i INTEGER;
   BEGIN
      i:=2;
      DBMS_OUTPUT.PUT(BLOCK1.i||' ');
      DBMS_OUTPUT.PUT(BLOCK2.i||' ');
   END;
   DBMS_OUTPUT.PUT_LINE(i);
   DBMS_OUTPUT.PUT(BLOCK2.i||' ');
END;

Using Named PL/SQL Procedures

To change from using anonymous PL/SQL blocks to using named procedures we would have to rearrange the code slightly as so:-

DECLARE
PROCEDURE block1 IS
   i INTEGER;
   PROCEDURE block2 IS
      i INTEGER;
   BEGIN
      i:=2;
      DBMS_OUTPUT.PUT(BLOCK1.i||' ');
      DBMS_OUTPUT.PUT(BLOCK2.i||' ');
   END;
   /* block1 */
   block2;
   DBMS_OUTPUT.PUT_LINE(i);
END;
BEGIN
block1;
END;

In the above example the outer block is just a wrapper for the procedure. This would produce the same results as before. Note that procedures have to be called explicitly (the order of declaration is independent of the order of execution) whereas with anonymous blocks they are executed in the order in which they are declared.

On a final note, just because you can do this does not mean that you should do this. The PL/SQL code would probably be easier to maintain on the whole if the variable in the inner block were given a different name. This would make the code easier to read, of course in this simple example it doesn't make a great deal of difference but it is something that should be taken into account.

For more help with PL/SQL see our Oracle PL/SQL tutorial.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Comments for How can I print the value of a variable declared in an outer Oracle PL/SQL block from an inner one?

Average Rating starstarstarstarstar

Click here to add your own comments

Mar 13, 2011
Rating
starstarstarstarstar
why do we opt for variable declarations in anonymous outer blocks vs global declarations in package specification?
by: ck

The lesson on using a variable from outside block to inside block is good to show how the scope of a variable prevails. But, usually, in a practical situation we develop stored procedures and packages so as to manage the code efficiently as well as reduce the recompilation. Also, we define the variables in the package spec in case if we want to make them globally accessible, right? So, I would like to know in which case it's best to use variable declarations vs global declarations in packages?

There's 2 questions and 1 assertion that we need to address here.

First the assertion. It's true that we usually develop PL/SQL stored procedures and packages and these do help with the management of code, but anonymous blocks shouldn't be dismissed. Anonymous PL/SQL blocks can be created inside stored procedures (but there's no real reason to) and are also useful for testing pieces of code (either as a formal test harness or just checking assumptions).

Next the questions. "we define the variables in the package spec in case if we want to make them globally accessible, right?" It depends on how "global" you want your variables to be. Anything defined in the package spec is available to all users with execute privilege on the package. Therefore all variables declared in the package spec can be modified by any other procedure/function/anonymous block owned by any user of your Oracle database with execute privilege on the package.

On the other hand, anything declared in the PL/SQL package body is private to the package and not directly accessible by any code outside of the package body. Anything not declared inside a procedure or function is global to the whole package.

Therefore, if you want variables to be accessible to everyone for reading and writing, you should declared in the PL/SQL package specification. However, even if you do want variables to be able to be accessible from outside the package it is better practice to make the variables private to the package and to provide an interface to them - specifically one procedure to set the value and another procedure or a function to get the value. This gives you a little more control over the variables and makes your code a little more independent so that if you change the type of a variable for example you may not have to change so much code.

To answer the second question specifically - "...in which case it's best to use variable declarations vs global declarations in packages?" - it's better to declare your variables as locally as possible, and this applies not just Oracle PL/SQL but to other programming languages also. Obviously some variables need to be declared globally but by doing this you expose your code to unpredictable bugs when you overwrite a global variable in one part of the code without realising that its value was required in another part of teh code.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.