Re: Are PL/SQL variable values runtime only?

  • From: Tony van Lingen <tony_vanlingen@xxxxxxxxxxxxxxxxxxxxx>
  • To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
  • Date: Thu, 23 Jul 2009 10:33:42 +1000

Rich,
I get the feeling that you want to create the package with a constant assignment, but have your create script retrieve the constant at create time, is that right? 

In that case you could use SQL*Plus like this:

rem begin script

col a new_value b noprint

select my_pkg.my_value('ABC') as a
  from dual
/

create or replace procedure bleah as
   v_test VARCHAR2(50) := &b;
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_test);
END bleah;
/

rem end script

hth,
Tony

Rich Jesse wrote:

Hi Ric,

I'm not questioning the scope of the variable, but the timing of the
evaluation of the "my_pkg.my_value" function.

Contrast this example with my original:

CREATE OR REPLACE PROCEDURE new_bleah AS
    v_test VARCHAR2(50);
BEGIN
    v_test := my_pkg.my_value('ABC');
    DBMS_OUTPUT.PUT_LINE(v_test);
END new_bleah;
/

This function clearly sets the value of "v_test" at runtime.

However, the original "bleah" function also sets the value of "v_test" at
runtime, even though the assignment is in the DECLARE (variable) section and
not in the BEGIN (code) section.  I would have expected it to happen at
compile time.

Hopefully that's a little clearer explanation?

Thanks!
Rich



Hi Rich,

The short answer is yes.  The life of a variable is only within the
block that it's defined. You can create variables in a package header
that have a more global like life span.  Although I'm not sure I totally
understand what you are asking so maybe I'm off the mark here.

I think this is covered in the scoping rules of the PL/SQL guide.

Ric Van Dyke


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Jesse

CREATE OR REPLACE PROCEDURE bleah AS
    v_test VARCHAR2(50) := my_pkg.my_value('ABC');
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_test);
END bleah;
/



--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2009