Saturday, July 23, 2011

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.


0 comments:

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Deep's | Bloggerized by Deep - Deep's Templates | ElearSQL-Server