ORA-06553: PLS-382: expression is of wrong type

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you are a skilled developer, feel free to skip reading this entry aimed to Oracle newbies ;-)

SELECT apex_util.is_username_unique('BOB')
FROM dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Packaged function APEX_UTIL.IS_USERNAME_UNIQUE returns a BOOLEAN type value.
Booleans are not allowed inside SQL expressions, they are only allowed within PL/SQL expressions.

If you need to call a function returning a boolean value inside a SQL query, you'll need to create a wrapper function returning either a number or a character value.
The following sample function wraps APEX_UTIL.IS_USERNAME_UNIQUE returning a single byte character value (either Y or N, corresponding to TRUE and FALSE respectively):

CREATE OR REPLACE
FUNCTION IS_USERNAME_UNIQUE_YN(
p_username IN VARCHAR2
) RETURN CHAR
AS
BEGIN
RETURN CASE WHEN APEX_UTIL.IS_USERNAME_UNIQUE(p_username)
THEN 'Y'
ELSE 'N' END;
END;
/

SELECT is_username_unique_yn('BOB') as "UNIQUE"
FROM dual;

UNIQUE
------
N

Note also that booleans parameter passing is forbidden even in nested functions inside SQL queries, so, imagine to have two functions, one returning a boolean (boolean_fn) value and another one accepting a boolean value (boolean2yn):

create or replace
function boolean_fn
return boolean
is
begin
 return true;
end;
/

create or replace
function boolean2yn(p in boolean)
return char
is
begin
 return case when p then 'Y' else 'N' end;
end;
/

select boolean2yn(boolean_fn) from dual;

SQL Error: ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Oracle Application Express allows the developer to choose from SQL expressions or PL/SQL expressions when writing validation rules or computations. This flexibility is a time saver because there is no need to create such wrapper functions in most cases.
You must use SQL expressions when you reference functions like NVL2 and DECODE. PL/SQL expressions are mandatory when boolean return values or boolean input parameters are involved.

See message translations for ORA-06553 and PLS-00382 and search additional resources.