Re: Are PL/SQL variable values runtime only?

From: Mathias Magnusson <>
Date: Wed, 22 Jul 2009 22:30:51 +0200
Message-ID: <>

Why would you expect a non deterministic function to be evaluated during compile time? Wouldn't you expect the value retrieved from the table to change every time you commit a new value unless you explicitly make it so it is only read the first time it is executed in a session?

Or did I misunderstand what you expect? I think you say that you would expect a call to a function that reads a table to be static after the code is compiled.

A local declaration will always be evaluated, but even if it was a session variable, it would still be evaluated once per session calling the function. In which case do you get a deterministic function to be evaluated during a compile? And why would you want it to be?


On Wed, Jul 22, 2009 at 6:28 PM, Rich Jesse <
> wrote:

> Hey all,
> In, I have a procedure like this:
> v_test VARCHAR2(50) := my_pkg.my_value('ABC');
> 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.
> TIA,
> Rich
> --

Received on Wed Jul 22 2009 - 15:30:51 CDT

Original text of this message