Furo’s World

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

When you deal with PL/SQL functions, you have sometimes to create a function returning a boolean.

This is an example:

CREATE OR REPLACE FUNCTION I_AM_A_HERO (
name VARCHAR2) RETURN BOOLEAN is
BEGIN
if (name = 'Petrelli') THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

But if you wan’t to execute it like any other PL/SQL function you get that:

>SELECT I_AM_A_HERO('Petrelli') FROM dual;
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Why do we have this error? Oracle proposes a boolean type for the PL/SQL functions BUT this type is not implemented in SQL so basically, you don’t have the right to do that! However that also means that you can use your function into a PL/SQL block (of course, it would be meaningless otherwise!). So you can call you function like that:

DECLARE
RESULT BOOLEAN;
BEGIN
IF (I_AM_A_HERO('Petrelli')) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

Now that you have a good overview of the problem, the solution is obvious. Instead of bothering making a PL/SQL block to access the function, we just have to change the return type:

CREATE OR REPLACE FUNCTION I_AM_A_HERO (
name VARCHAR2) RETURN NUMBER is
BEGIN
if (name = 'Petrelli') THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

And we get:

>SELECT I_AM_A_HERO('Petrelli') FROM dual;
I_AM_A_HERO('PETRELLI')
-----------------------
1