PL/SQL variable scope in nested blocks

Question

I need to run some SQL blocks to test them, is there an online app where I can insert the code and see what outcome it triggers? Thanks a lot! More specific question below:

<<block1>>
DECLARE 
var NUMBER;
BEGIN
var := 3;
DBMS_OUTPUT.PUT_LINE(var);

  <<block2>>
  DECLARE 
  var NUMBER;
  BEGIN
  var := 200;
  DBMS_OUTPUT.PUT_LINE(block1.var);
  END block2;
DBMS_OUTPUT.PUT_LINE(var);
END block1;

Is the output: 3 3 200

or is it: 3 3 3 I read that the variable's value is the value received in the most recent block so is the second answer the good one? I'd love to test these online somewhere if there is a possibility. Also, is <<block2>> really the correct way to name a block??

Later edit: I tried this with SQL Fiddle, but I get a "Please build schema" error message: Thank you very much, Dave! Any idea why this happens?

create table log_table
( message varchar2(200)
)


<<block1>>
DECLARE 
var NUMBER;
BEGIN
var := 3;

insert into log_table(message) values (var)
select * from log_table

  <<block2>>
  DECLARE 
  var NUMBER;
  BEGIN
  var := 200;
  insert into log_table(message) values (block1.var || ' 2nd') 
  select * from log_table
  END block2;
insert into log_table(message) values (var || ' 3rd') 
select * from log_table
END block1;

Dave Lyndon · Accepted Answer · 2014-10-28 05:28:56Z

4

In answer to your three questions.

You can use SQL Fiddle with Oracle 11g R2: http://www.sqlfiddle.com/#!4. However, this does not allow you to use dbms_output. You will have to insert into / select from tables to see the results of your PL/SQL scripts.
The answer is 3 3 3. Once the inner block is END-ed the variables no longer exist/have scope. You cannot access them any further.
The block naming is correct, however, you aren't required to name blocks, they can be completely anonymous.

EDIT:

So after playing with SQL Fiddle a bit, it seems like it doesn't actually support named blocks (although I have an actual Oracle database to confirm what I said earlier).

You can, however, basically demonstrate the way variable scope works using stored procedures and inner procedures (which are incidentally two very important PL/SQL features).

Before I get to that, I noticed three issues with you code:

You need to terminate the insert statements with a semi-colon.
You need to commit the the transactions after the third insert.
In PL/SQL you can't simply do a select statement and get a result, you need to select into some variable. This would be a simple change, but because we can't use dbms_output to view the variable it doesn't help us. Instead do the inserts, then commit and afterwards select from the table.

In the left hand pane of SQL Fiddle set the query terminator to '//' then paste in the below and 'build schema':

create table log_table
( message varchar2(200)
)
//

create or replace procedure proc1 as
var NUMBER;

   procedure proc2 as 
      var number;
   begin
      var := 200;
      insert into log_table(message) values (proc1.var || ' 2nd'); 
   end;

begin
   var := 3;

   insert into log_table(message) values (var || ' 1st');

   proc2;

   insert into log_table(message) values (var || ' 3rd');

   commit;

end;
//

begin
proc1;
end;
//

Then in the right hand panel run this SQL:

select * from log_table

You can see that proc2.var has no scope outside of proc2. Furthermore, if you were to explicitly try to utilize proc2.var outside of proc2 you would raise an exception because it is out-of-scope.

edited Oct 28, 2014 at 5:28

answered Oct 26, 2014 at 23:57

Dave Lyndon

7865 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Samy Over a year ago

Thank you very much, Dave! Any idea why SQL Fiddle throws an error message - "please build schema"? I edited my original question to show the code I tried with Fiddle.

Dave Lyndon Over a year ago

Hi Samy, I just edited my post to give you a bit more guidance around SQL fiddle and variable scoping. Hope it helps :D

Collectives™ on Stack Overflow

PL/SQL variable scope in nested blocks

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related