PL/SQL Subtype Constraints don’t work from SQL

(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.