Re: Semi-deterministic?

  Join that as an inline view. For example:

SELECT ...

, 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

;

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

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

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