Function returning boolean fails on "expression is of wrong type"

Question

I am using oracle 11g and I just cant under stand where my problem is. I have made much more difficult stuff but I fail in this simple thing for the last 5 hr :

This is the function body

FUNCTION legal_user(
     level_existance  number
    ,types_with_impel number)
RETURN BOOLEAN
 IS
 v_ret_val BOOLEAN;
 BEGIN
   v_ret_val := FALSE;
   IF (level_existance*types_with_impel>0) then 
     v_ret_val := TRUE;
     DBMS_OUTPUT.PUT_LINE('true');
   else 
     DBMS_OUTPUT.PUT_LINE('false');
   END IF;       
  return v_ret_val;
END legal_user;

This is the spec :

FUNCTION legal_user(
       level_existance number
       ,types_with_impel number)
   RETURN BOOLEAN;

which does logical AND equivlant to

         A*B>0?true:false;   

The error message I am getting is

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

---

This is how I run it in my IDE

 SELECT compt_tree_profile_q.legal_user(1,1)
 FROM dual 

Answer 1

Pure SQL does not recognize a boolean type, although PL/SQL does. So your query does not know what datatype this function is returning..

The function works, so you could in another pl/sql block use

declare
myvar boolean;
begin
   myvar := compt_tree_profile_q.legal_user(1,1);
end;

But you can't use this function in a pure select statement.

Answer 2

Your function returns a boolean. This datatype is known to PL/SQL, but you are using a SQL query. SQL doesn't know how to handle booleans and says "expression is of wrong type".

Regards,
Rob.

Answer 3

Given that you are calling this within SQL, you could use the built-in SIGN function instead of rolling your own.

The function will return -1, 0 or 1, depending on the sign of the parameter (negative, zero or positive respectively).

Here's how you would use it:

SIGN(level_existance*types_with_impel)

And how you would work it into a CASE statement:

SELECT CASE WHEN (SIGN(level_existance*types_with_impel) = 1)
            THEN 'TRUE'
            ELSE 'FALSE'
       END legal_user
FROM ...

In this case, I'm just returning a string ('TRUE' or 'FALSE'), but you can return anything that's valid within your SELECT statement (a column, SYSDATE, etc).