RE: Are PL/SQL variable values runtime only?

From: Ric Van Dyke <>
Date: Wed, 22 Jul 2009 13:43:17 -0500
Message-ID: <>

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-----

[] On Behalf Of Rich Jesse Sent: Wednesday, July 22, 2009 12:29 PM
To: Oracle L
Subject: Are PL/SQL variable values runtime only?

Hey all,

In, I have a procedure like this:


    v_test VARCHAR2(50) := my_pkg.my_value('ABC'); BEGIN
END bleah;

...where "my_pkg.my_value" is a function that reads a table and returns a
column value of a row based on the supplied parameter, in this case 'ABC'. From testing, I can change the column value for row 'ABC' in that table (and
COMMIT) and the above procedure will reflect the change.

I would have suspected the v_test variable value to be retrieve at compile
time, but I suppose that would be a maintenance nightmare since an invalid
referenced object forces a recompile, right?

Also the CONSTANT keyword in the variable declaration does not appear to effect this behavior.

Is this documented somewhere? I've poked around the PL/SQL User's Guide,
but can't find it.



-- Received on Wed Jul 22 2009 - 13:43:17 CDT

Original text of this message