(Image by Andrew Martin from Pixabay)

Limiting possible inputs is one of the key practices if you aim for testable, robust code, and especially unexpected NULL parameters are a steady stream of frustrating bugs – no wonder some modern programming languages eliminate the possibility for NULL parameters right from the start.

Since I learned about them, I was always a big fan of PL/SQL subtypes to enforce the not null constraint on parameters in PL/SQL procedures and functions. And while it works nicely in PL/SQL context, I was shocked when I found out this week that it does not work at all from a SQL context.

/* Sometimes it's important to eliminate the possibility for NULL parameters.
   I advocated to use PL/SQL subtypes to enforce that in the past
 */
create or replace package wookie_complimentor as
    subtype name_not_null is varchar2(20) not null;
    function make_compliment( i_wookie name_not_null ) return varchar2;
end;
/

create or replace package body wookie_complimentor as
    function make_compliment( i_wookie name_not_null ) return varchar2 as
    begin
        return 'You got awesome hair, '||i_wookie;
    end;
end;
/

This implementation will cause any call of the function with a NULL parameter to fail with ORA-06553: PLS-567: cannot pass NULL to a NOT NULL constrainted formal parameter.

It also works from SQL, if the parameter is passed directly:

select wookie_complimentor.make_compliment(null) from dual;
-- Throws ORA-06553

But when the parameter comes in the form of a column value, nothing happens:

with data as (
	select cast(null as varchar2(20)) wookie from dual
)
select wookie_complimentor.make_compliment(wookie) from data;
-- Returns: "You got awesome hair, "

But not enough – also the explicit length constraint of 20 chars we defined in the subtype is completely ignored when coming from SQL:

with data as (
	select 'Chewbaccas son with a very long name, way more than the intended 20 chars' wookie from dual
)
select wookie_complimentor.make_compliment(wookie) from data;
-- Returns: "You got awesome hair, Chewbaccas son with a very long name, way more than the intended 20 chars"

This second constraint can be enforced by adding a local variable to the function body:

create or replace package body wookie_complimentor as
    function make_compliment( i_wookie name_not_null ) return varchar2 as
      l_wookie name_not_null := i_wookie;
    begin
        return 'You got awesome hair, '||l_wookie;
    end;
end;
/

Now, the previous try will result in ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

The NOT NULL constraint, however, is still not enforced.

Conclusion

So, if your PL/SQL API is called from SQL and you want be sure to not get NULL parameters, you’ll need to explicitly assert for it.

create or replace package body wookie_complimentor as
    function make_compliment( i_wookie name_not_null ) return varchar2 as
    begin
      if i_wookie is null then raise_application_error(-20000, 'param is NULL'); end if;
      return 'You got awesome hair, '||i_wookie;
    end;
end;
/

Pretty annoying, since the tools are there and work in PL/SQL, but at least good to know.


0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *