I alluded to the reason above:
... the disabling of function inlining will make it do the call to check_date HAS to be made as a function call ...
actually, I sort of said it outright :)
we know that l_dt is an unreferenced variable. You do not use it anywhere other than that assignment.
we know that to_date is deterministic.
we know that to_date is pure (no side effects).
therefore the code "l_dt := to_date(x,fmt)" may be optimized away as dead code. You are assigning a deterministic value that is the result of a function with no side effects to a variable you never use.
the code could become simply:
begin
return 'Y';
end;
in fact, it will be, for example:
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p
2 authid definer
3 as
4 function Is_Number(x in varchar2) return varchar2
5 is
6 n number;
7 begin
8 n := To_Number(x);
9 return 'Y';
10 exception
11 when value_error then
12 return 'N';
13 end Is_Number;
14
15 begin
16 DBMS_Output.Put_Line('1 - ' || Is_Number('1'));
17 DBMS_Output.Put_Line('a - ' || Is_Number('a'));
18 end p;
19 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec p
1 - Y
a - N
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> alter procedure p compile plsql_optimize_level = 3;
Procedure altered.
ops$tkyte%ORA11GR2> exec p
1 - Y
a - Y
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p
2 authid definer
3 as
4 function Is_date(x in varchar2, fmt in varchar2 default 'yyyy-mm-dd' ) return varchar2
5 is
6 n date;
7 begin
8 n := To_date(x,fmt);
9 return 'Y';
10 exception
11 when others then
12 return 'N';
13 end Is_date;
14
15 begin
16 DBMS_Output.Put_Line('jan 1 - ' || Is_date('2013-01-01'));
17 DBMS_Output.Put_Line('45-45 - ' || Is_date('2013-45-45'));
18 end p;
19 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec p
jan 1 - Y
45-45 - N
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> alter procedure p compile plsql_optimize_level = 3;
Procedure altered.
ops$tkyte%ORA11GR2> exec p
jan 1 - Y
45-45 - Y
PL/SQL procedure successfully completed.
this is not a bug, repeat: this is NOT a bugoptimizing compilers do this all of the time - all of the time.
This is documented:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#i53930 "In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all."
the way to code the "is_datatype" function - knowing that to_date/to_number are pure deterministic functions - is to include that non-inlinable function call to make sure the date/number has to be pushed onto the stack (that the date/number is in fact *used*).
another valid approach would be:
ops$tkyte%ORA11GR2> create or replace procedure p
2 authid definer
3 as
4 function Is_date(x in varchar2, fmt in varchar2 default 'yyyy-mm-dd' ) return varchar2
5 is
6 n date;
7 begin
8 n := To_date(x,fmt);
9 if (n is not null)
10 then
11 return 'Y';
12 else
13 return NULL;
14 end if;
15 exception
16 when others then
17 return 'N';
18 end Is_date;
19
20 begin
21 DBMS_Output.Put_Line('jan 1 - ' || Is_date('2013-01-01'));
22 DBMS_Output.Put_Line('45-45 - ' || Is_date('2013-45-45'));
23 end p;
24 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec p
jan 1 - Y
45-45 - N
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> alter procedure p compile plsql_optimize_level = 3;
Procedure altered.
ops$tkyte%ORA11GR2> exec p
jan 1 - Y
45-45 - N
PL/SQL procedure successfully completed.
Now we have a function that returns Y/N or NULL (which is perhaps better in some respects) and forces N to be used.