RE: Semi-deterministic?
Date: Wed, 05 Feb 2014 15:50:14 -0600
Message-id: <008d01cf22bc$4058ef40$c10acdc0$_at_net>
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_at_verizon.net
Cell: 214.695.8605
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 05 2014 - 22:50:14 CET