Suppose I have a function like
create or replace function f(n integer) return integer as
begin
return n;
end;
/
I can call this function like this: f(4.3) or f('100') and in both cases the function will return something, instead of throwing an error.
PL/SQL ignores the "integer" directive; it doesn't even coerce 4.3 to an integer, either on input or on output. The value returned by f(4.3) is 4.3. If something else in the code relies on the return value being an integer, that wil then fail, and the error message will point to the wrong place.
I won't bore you further with the reasons I am interested in this. I believe PL/SQL does not have a simple mechanism to require strict(er) enforcement of data type compatibility; we are left with tricks, hacks and workarounds. I am curious what some of those may be. Pointers to writings on this (either in book form or online) would be appreciated too.
For the example I gave: dealing with non-integer values is easier. PL/SQL doesn't round (or truncate) the argument - that would make things a little harder. Since it simply ignores the "integer" label and it accepts the input number as-is, without coercion, I can simply check that the input is indeed an integer, and if it isn't, throw an error.
The second example is different. PL/SQL converts the string to a number (implicitly and without warning), if it can. If it can't, it throws an error. I would rather want it to throw an error on all string inputs, even on those that can be converted implicitly to number. I believe the only way to do that is to write an overload taking a string input, which does nothing but throw an error. (Which means both functions must be in a package.)
I end up with something like this:
create or replace package mypack as
function f(n integer) return integer;
function f(n varchar2) return integer;
end mypack;
/
create or replace package body mypack as
function f(n integer) return integer is
begin
if n != trunc(n) then
raise_application_error(-20001, 'Invalid input: ' || n || ' is not an integer', true);
end if;
return n;
end;
function f(n varchar2) return integer is
begin
raise_application_error(-20002, 'Invalid input: expected integer, found string', true);
return null;
end;
end mypack;
/
And then:
exec dbms_output.put_line(mypack.f(-6));
-6
PL/SQL procedure successfully completed.
exec dbms_output.put_line(mypack.f(-6.9));
Error starting at line : 30 in command -
BEGIN dbms_output.put_line(mypack.f(-6.9)); END;
Error report -
ORA-20001: Invalid input: -6.9 is not an integer
ORA-06512: at "MATHGUY.MYPACK", line 5
ORA-06512: at line 1
exec dbms_output.put_line(mypack.f('100'));
Error starting at line : 40 in command -
BEGIN dbms_output.put_line(mypack.f('100')); END;
Error report -
ORA-20002: Invalid input: expected integer, found string
ORA-06512: at "MATHGUY.MYPACK", line 11
ORA-06512: at line 1
exec dbms_output.put_line(mypack.f(null));
Error starting at line : 49 in command -
BEGIN dbms_output.put_line(mypack.f(null)); END;
Error report -
ORA-06550: line 1, column 28:
PLS-00307: too many declarations of 'F' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I get the last one for free. I like it; I can't just call the function with a NULL argument, I must cast NULL as a specific data type before the function will accept it. This agrees with my desire for stricter data typing.
Thoughts? Thank you, ---- mathguy