Wrong type expression

Question

I am getting a runtime error for my function below. I am not sure why as I am passing in integers but it is complaining about strings.

CREATE OR REPLACE FUNCTION FOO
( 
  col1 IN NUMBER,
  col2 IN NUMBER
)
   RETURN BOOLEAN
IS
   BAR BOOLEAN;
BEGIN
  DECLARE FOO NUMBER; 
  BEGIN 
    SELECT 1 INTO FOO FROM DUAL;
  END;
  RETURN BAR;
END FOO;

SELECT FOO(1, 1) FROM DUAL;

ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type 06552. 00000 - "PL/SQL: %s" *Cause:
*Action: Error at Line: 1 Column: 7

You cannot use BOOLEAN in SQL. But you can use 1 / 0 for true / false — Multisync, Dec 09 '14 at 17:35
Check this out: http://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases — Ascalonian, Dec 09 '14 at 17:37
ok, i switch the boolean to char and am returning 1 or 0; works now. — , Dec 09 '14 at 17:48
The strange thing is that at compile time the compiler does not complain about declaring variables as type Boolean. — , Dec 09 '14 at 18:41
It is not strange, you can declare a variable as BOOLEAN in PLSQL context, but not in SQL. Your function will work without problem in PLSQL context. — Noel, Dec 10 '14 at 08:42

Ascalonian · Answer 1 · 2014-12-09T17:48:22.400

Oracle actually doesn't have BOOLEAN type. You need to use flags instead. It is a little confusing what you are trying to do in your code, since you aren't using col1 or col2 nor even attempting to set BAR.

You can do something like this though, if applicable:

DECODE(flag, 1, 'TRUE', 2, 'FALSE')

OR

CREATE OR REPLACE FUNCTION FOO
( 
  col1 IN NUMBER,
  col2 IN NUMBER
)
   RETURN VARCHAR2
AS
vs_return VARCHAR2(10);

BEGIN
    BEGIN
        SELECT 1 INTO FOO FROM DUAL;

        vs_return := 'TRUE';
    EXCEPTION
        WHEN OTHERS THEN
            vs_return := 'FALSE';
    END;

    RETURN vs_return;
END FOO;

Wrong type expression

1 Answers1