create table big_moments 
( 
   what_happened   varchar2 (100), 
   happened_on     date 
) 

begin     
   insert into big_moments (what_happened, happened_on) 
        values ('Got married', date '2020-01-03'); 
 
   insert into big_moments (what_happened, happened_on) 
        values ('First hole in one', date '2020-03-01'); 
 
   commit; 
end; 

create or replace function what_happened (date_in in date) 
   return varchar2 
   authid definer 
   result_cache 
is 
   l_info   big_moments.what_happened%type; 
begin 
   dbms_output.put_line ( 
         'Fetching row for ' 
      || to_char (date_in, 'YYYY-MM-DD') 
      || ' -> ' 
      || to_char (date_in)); 
 
   select what_happened 
     into l_info 
     from big_moments 
    where happened_on = to_date (to_char (date_in)); 
 
   return l_info; 
end; 

create or replace procedure init (format_in in varchar2) 
   authid definer 
is 
begin 
   execute immediate 
      (q'[ALTER SESSION SET nls_date_format = ']' || format_in || q'[']'); 
 
   dbms_output.put_line ('What''s your big moment? (' || format_in || ')'); 
end; 

create or replace procedure reset_cache 
   authid definer 
is 
begin 
   update big_moments 
      set happened_on = happened_on; 
 
   commit; 
end; 

begin 
   init ('YYYY-DD-MM'); 
   dbms_output.put_line (what_happened (date_in => date '2020-01-03')); 
   init ('YYYY-MM-DD'); 
   dbms_output.put_line (what_happened (date_in => date '2020-01-03')); 
end; 

exec reset_cache 

begin  
   init ('YYYY-MM-DD');  
   dbms_output.put_line (what_happened (date_in => date '2020-01-03'));  
   dbms_output.put_line (what_happened (date_in => date '2020-03-01'));  
end; 

exec reset_cache 

begin  
   init ('YYYY-DD-MM');  
   dbms_output.put_line (what_happened (date_in => date '2020-01-03'));  
   dbms_output.put_line (what_happened (date_in => date '2020-03-01'));  
end; 

exec reset_cache 

begin 
   init ('YYYY-MM-DD'); 
   dbms_output.put_line (what_happened (date_in => date '2020-01-03')); 
   init ('YYYY-DD-MM'); 
   dbms_output.put_line (what_happened (date_in => date '2020-01-03')); 
end;