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.

PLS-00382: expression is of wrong type

There are at least two distinct situations where this error can be returned, the former scenario involves collections and the latter is a relatively "simple" implicit conversion problem.

PLS-00382: expression is of wrong type

Note that PLS-00382 can be raised when you try assign a value that cannot be converted implicitly into the target data type, as in the following example:

declare
a   integer;
b   timestamp := systimestamp;
begin
a := b;
dbms_output.put_line(b);
end;

Error report:
ORA-06550: line 5, column 8:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

See the following Oracle 10G document about allowed implicit conversions, where, for some reason, the implicit conversion between timestamps and dates is not mentioned and the new 11G document doesn't include it either, but is indeed perfectly working:

DECLARE
a   DATE;
b   TIMESTAMP WITH TIME ZONE := systimestamp;
begin
a := b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

09-JAN-08
09-JAN-08 02.37.02.339176 PM +01:00

However you should always avoid using implicit conversions as they are affected by the server and client locale settings and a program that works in your environment may stop working altogether when executed from another client.