Re: Should we use Subquery caching or result cache here

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 29 Oct 2021 21:42:32 -0400
Message-ID: <c71b8489-2fa6-be3b-1c92-9da965dedc38_at_gmail.com>

First of all I would replace

 SELECT TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' || TO_CHAR(IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM') INTO o_pdt FROM DUAL;

to

o_pdt  :=  TO_DATE(TO_CHAR(INPDT,'MM/DD/YYYY') || ' ' || TO_CHAR(IN_STDT, 'HH:MI:SS AM'), 'MM/DD/YYYY HH:MI:SS AM');

and remove dbms_output.put_line. After this, this function will become a simple deterministic function without SQL queries, so I would add 'deterministic' and 'pragma UDF;'.

And pin the function into SGA using DBMS_SHARED_POOL.KEEP.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

-- http://www.freelists.org/webpage/oracle-l Received on Sat Oct 30 2021 - 03:42:32 CEST