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


CREATE OR REPLACE FUNCTION GET_BOOL
RETURN BOOLEAN
IS
BEGIN
RETURN TRUE;
END;

SELECT GET_BOOL() FROM DUAL;

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

Cause :
There is BOOLEAN datatype is supported by PL/SQL and NOT supported by SQL
So you cannot use this function in a straight select statement.

However, this function work in another PL/SQL block

DECLARE
VBOOL BOOLEAN;
BEGIN
VBOOL := GET_BOOL();
IF( VBOOL = TRUE ) THEN
DBMS_OUTPUT.PUT_LINE( ‘TRUE’ );
END IF;
END;

So, we can change the function to return number(FALSE = 0, TRUE = 1)

CREATE OR REPLACE FUNCTION GET_BOOL
RETURN NUMBER
IS
BEGIN
IF TRUE THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

Advertisements

2 Responses

  1. Thank you so much! A half of day I tried to fetch the trouble. 🙂

  2. хаха жжёте я аж возбудилась (;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: