Home » SQL & PL/SQL » SQL & PL/SQL » Scope of variables
Scope of variables [message #184351] Wed, 26 July 2006 05:31 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi All,

Consider this block

declare
v_num number:=10;
begin
  begin
  v_num:=100;
  dbms_output.put_line(v_num);
  end;
dbms_output.put_line(v_num);
end;


The inner dbms_output.put_line is GIVING 100

The second one is also giving 100(Why is it taking value from the inner begin .. end block)?Can you explain the scope of the variable here.

And how can i use the variable with value 10 in the inner begin ...end block???




Re: Scope of variables [message #184354 is a reply to message #184351] Wed, 26 July 2006 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is pretty straight forwards.

A declare
B   v_num number:=10;
  begin
C   declare
D     v_example  number := 0;
    begin
E     v_num:=100;
F     dbms_output.put_line(v_num);
G   end;
H   dbms_output.put_line(v_num);
I end;

At point B, you define a variable v_num, and set it to a value of 10. This variable has a scope of the entire block it is defined in (in this case, anywhere between A and I.
Similarly, the variable I have added, v_example, defined at point D, has a scop of the block it was defined in, ie C-G. Referring to v_example at H will cause an error.

At point E you set v_num 100.
At point F, you output this value
At point G, you leave the inner block, Anything with a scope of just that block is now history.
At point H, you output v_num, which is still 100, because that's what you set it to.

Re: Scope of variables [message #184355 is a reply to message #184351] Wed, 26 July 2006 05:41 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You change the same memory area. Why is it so difficult to understand?

Is this what you are looking for?

DECLARE
  v_num  NUMBER := 10;
BEGIN

  DECLARE
    v_num  NUMBER;
  BEGIN
    v_num := 100;
    
    dbms_Output.Put_Line(v_num);
  END;
 
  dbms_Output.Put_Line(v_num);
END;
/

Re: Scope of variables [message #184357 is a reply to message #184355] Wed, 26 July 2006 05:54 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I want to use the outer variable in Inner block and do some calculations.How can i do that
Re: Scope of variables [message #184363 is a reply to message #184357] Wed, 26 July 2006 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the example you provide, you ARE using the outer variable v_num. You are setting its value to 100.
If you want to use it's original value, a good start would be not changing that value.
Re: Scope of variables [message #184366 is a reply to message #184357] Wed, 26 July 2006 06:19 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I would suggest that you include a more accurate description of what you are trying to do, instead of a rather vague "I want to do something with a variable"
PS, really NOT an Expert question.

[Updated on: Wed, 26 July 2006 06:19]

Report message to a moderator

Re: Scope of variables [message #184381 is a reply to message #184366] Wed, 26 July 2006 06:47 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I want to print the variable's value (which is defined in the outer block) in the inner block!

How can i do that???
Re: Scope of variables [message #184382 is a reply to message #184381] Wed, 26 July 2006 06:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why don't you start SQL*Plus yourself?

How's this:
DECLARE
  v_num  NUMBER := 10;
BEGIN
  BEGIN   
    dbms_Output.Put_Line(v_num);
  END;
 
  dbms_Output.Put_Line(v_num);
END;
/


MHE
Re: Scope of variables [message #184383 is a reply to message #184381] Wed, 26 July 2006 06:51 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
OK. Either
1. This is really what you want to do, in which case, you should probably look over your class notes.
Or
2. You are trying to do something more complex, but supplying us with a simplified example. In which case, you should supply us with a more accurate example.

You say:

Quote:

I want to print the variable's value (which is defined in the outer block) in the inner block


That's what you are doing. If you want the two variables to behave differently, give them different names.

Jim
Re: Scope of variables [message #184388 is a reply to message #184383] Wed, 26 July 2006 07:01 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I too told the interviwer to use different names.He didnt agree for that.He said using labels we can do that?I dunno how?
Re: Scope of variables [message #184389 is a reply to message #184388] Wed, 26 July 2006 07:04 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Did you consider doing a search on the Oracle documentation for the keyword 'Label'

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems4.htm#32902
Re: Scope of variables [message #184392 is a reply to message #184389] Wed, 26 July 2006 07:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, I've been doing Oracle for longer than I'm happy thinkin about, and while I've seen and used that block notation, I had no idea you could use it to reference variables with.
I didn't expect this thread to teach me anything, so thanks for the suprise!
Re: Scope of variables [message #184393 is a reply to message #184388] Wed, 26 July 2006 07:12 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I'm gonna give you another hint. This is how your original question should have been written:

Hi, I'm not very experienced with PL/SQL but I went for an interview that would involve PL/SQL in the job description. While in the interview, the interviewer asked me the following:

given the following code:

declare
v_num number:=10;
begin
begin
v_num:=100;
dbms_output.put_line(v_num);
end;
dbms_output.put_line(v_num);
end;


Quote:


How would you output the value from the variable v_num within the inner block but have it output the value from the outer block (i.e. the value of 10)



I replied that I would use different names for the 2 variables, however he said that you could use labels. I have no idea how to do this, could someone give me a hint or even a link as to where I can find this information (or even if the intervewer was talking rubbish Smile )

Thanks in advance

end question.


Now you would have got an immediately correct reply to that with just 1 answer. Smile

Moral, give full info on what you are trying to do.

Jim
Re: Scope of variables [message #184394 is a reply to message #184389] Wed, 26 July 2006 07:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I have never used labels for this. The only time I've used labels was in loops.

MHE
Re: Scope of variables [message #184408 is a reply to message #184394] Wed, 26 July 2006 07:51 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
it is not big things to use label. just use like given below
it will suite for your question.

<<outer>>
declare
v_num number:=10;
begin
<<inner>>
declare 
v_num number;
begin
v_num:=100;
dbms_output.put_line(inner.v_num);
dbms_output.put_line(outer.v_num);
end;
dbms_output.put_line(outer.v_num);
end;
Re: Scope of variables [message #184547 is a reply to message #184408] Thu, 27 July 2006 00:46 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Hi gold_orcl,

At last I got the correct explanation from you.Thanks!

Ravi
Re: Scope of variables [message #184577 is a reply to message #184547] Thu, 27 July 2006 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you saying that you were really too idle to go and read the link that @JSI2001 provided, which took you to (unless I'm mistaken) the page that @gold_orcl got that information from.

That truly is laziness above and beyond the call of duty.

(Link again, because I like links)
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems4.htm#32902

Re: Scope of variables [message #184616 is a reply to message #184577] Thu, 27 July 2006 04:18 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
Sorry Jrowbottom,

I didnt check out that link u provided,I started from the last.Anyway thanks to you only who provided the link.

Ravi
Re: Scope of variables [message #184623 is a reply to message #184616] Thu, 27 July 2006 04:35 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

I didnt check out that link u provided,I started from the last.Anyway thanks to you only who provided the link.


Unbelievable. ..... Oh I can't be bothered. Mad
Re: Scope of variables [message #184629 is a reply to message #184623] Thu, 27 July 2006 04:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And he gave me the credit for your link Laughing

It really does beggar belief, doesn't it.
Re: Scope of variables [message #184630 is a reply to message #184629] Thu, 27 July 2006 04:51 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Why would someone ask a question and then not read all the answers? I don't get it, ... at all. ah you've got to laugh Laughing
Check out the recent 'clarification' on the post titled 'Ref Cursor' Brilliant ..... I was like "Ahhh, yes, that's much clearer now" Mad

Laughing

Jim

[Updated on: Thu, 27 July 2006 04:51]

Report message to a moderator

Re: Scope of variables [message #184632 is a reply to message #184630] Thu, 27 July 2006 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Amazing.
"I was pretending to have a slightly complex problem in the hope you'd spoonfeed me knowledge."

I'm tempted to start putting deliberate bugs in my examples to get them to think more. Cool
Re: Scope of variables [message #184635 is a reply to message #184632] Thu, 27 July 2006 04:55 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Yeah, but then you'll get a follow up:
I ran it but it doesn't work! Sad Please write it again for me. It's really urgent I expect your answer immediately.
Re: Scope of variables [message #184940 is a reply to message #184635] Fri, 28 July 2006 16:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or worse, someone will correct it for you, taking you for stupid...
Re: Scope of variables [message #184974 is a reply to message #184940] Sat, 29 July 2006 05:47 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Note to anyone reading My code from now on. If there is a mistake, it's because I meant it. Feel free to correct of course, but it was deliberate Laughing
Re: Scope of variables [message #185065 is a reply to message #184974] Sun, 30 July 2006 18:06 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I didn't bother to read this thread. Could you summarise it for me?
Re: Scope of variables [message #185094 is a reply to message #185065] Mon, 31 July 2006 00:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sure.

First 17 posts were about the thread subject.

The rest was about not believing the fact that OP reads only the last message and ignores previous ones, his laziness and not following suggested link which would provide solution if he bothered to read it.
Re: Scope of variables [message #185139 is a reply to message #185065] Mon, 31 July 2006 03:02 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

I didn't bother to read this thread. Could you summarise it for me?
Laughing Thumbs Up
Jim

PS LittleFoot, thanks for the summary, I couldn't be bothered Smile
Re: Scope of variables [message #185143 is a reply to message #185139] Mon, 31 July 2006 03:24 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Yeah, I didn't read it either Wink
Re: Scope of variables [message #185145 is a reply to message #185143] Mon, 31 July 2006 03:34 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
LMAO Laughing
Re: Scope of variables [message #185169 is a reply to message #185145] Mon, 31 July 2006 06:12 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member


Ok ,the entire credit goes to JS---- whatever..... but not to jrowbottom!
I dont bother people making fun of me having not read the given link.

RAVI
Re: Scope of variables [message #185172 is a reply to message #185169] Mon, 31 July 2006 06:30 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Just to clarify Ravi, this was not a post 'looking for the credit' My post was to show my frustration at posters who do not read the answers given to them.

Jim
Previous Topic: Can a procedure be called from a function
Next Topic: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Goto Forum:
  


Current Time: Thu Dec 08 16:36:02 CST 2016

Total time taken to generate the page: 0.12687 seconds