RE: Semi-deterministic?
- From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
- To: <vxsmimmcp@xxxxxxxxxx>, "'Oracle Mailinglist'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 05 Feb 2014 15:50:14 -0600
Simply move it into an in-line view, or reference it in a WITH clause, etc. Any
number of ways to have it be called only once. Check
the execution stats to ensure it is only invoked once, seem to remember in some
odd cases where the transformation undid the
intention, and thus I had to ensure it materialized (a bit of an odd
circumstance). Anyway, you get the idea. Resolve it, once, then
join it. And any number of ways you can do that.
Select ..
From big_table, (select
xxcust_profiles_pkg.get_profile_value('LEADTIME_MONTHS')) from dual)
..
With cd (select xxcust_profiles_pkg.get_profile_value('LEADTIME_MONTHS')) as
Select ..
From big_table, cd
Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of McPeak, Matt
Sent: Wednesday, February 05, 2014 3:33 PM
To: Oracle Mailinglist
Subject: Semi-deterministic?
Hi.
I have a database view that needs has a select-list item something like this:
SELECT
.
, ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value('LEADTIME_MONTHS'))
eta_date,
.
FROM big_table;
Now, here is the thing: I want to execute get_profile_value once per call, but
not once per row.
I don't think I can make the function DETERMINISTIC, because it's not (the
profile value is stored in the database and may be
changed).
Also, I am shy about giving xxcust_profiles_pkg the SERIALLY_REUSABLE pragma,
because I cannot guarantee that it won't be called
from places where it will yield an ORA-6534 (Cannot access serially reusable
package.).
I really want it to behave like SYSDATE - where all rows in the query use the
same value but not all queries in the same session do.
Does anyone have any ideas / thoughts?
Thanks,
Matt
Other related posts:
