RE: Semi-deterministic?

From: McPeak, Matt <>
Date: Wed, 5 Feb 2014 21:50:37 +0000
Message-ID: <>

Not sure that works, but I'll try it.

By the way I should mention that my best approach so far is to access the functions in a WITH clause with the /*+ MATERIALIZE */ hint. I'm looking to improve on that. (The drawback there is that the materialized temp table gets created pretty big for some reason - the query takes 76 buffer gets instead of 5 buffer gets if I just hard-code the values).


From: Riyaj Shamsudeen [] Sent: Wednesday, February 05, 2014 4:45 PM To: McPeak, Matt
Cc: Oracle Mailinglist
Subject: Re: Semi-deterministic?

Hello Matt
  Join that as an inline view. For example:


, et.eta_date,

FROM big_table

   join (select ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value('LEADTIME_MONTHS')) eta_date from dual) et



Riyaj Shamsudeen
Principal DBA,
Ora!nternals -<> - Specialists in Performance, RAC and EBS Blog: Oracle ACE Director and OakTable member<>

Co-author of the books: Expert Oracle Practices<>, Pro Oracle SQL, <> Expert RAC Practices 12c.<> Expert PL/SQL practices<>

On Wed, Feb 5, 2014 at 1:32 PM, McPeak, Matt <<>> wrote: Hi...

I have a database view that needs has a select-list item something like this:


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


Received on Wed Feb 05 2014 - 22:50:37 CET

Original text of this message