PL/SQL function input value seems not matching function input parameter

Question

I've created this afternoon this PL/SQL function :

CREATE OR REPLACE FUNCTION CHECK_CIN_CLOSED_LINE
    (combination IN VARCHAR2 DEFAULT '') 

   RETURN BOOLEAN 

IS 
    solution BOOLEAN := false;

    cursor c1 is
        SELECT (1)
        FROM RS2QTCIN cin, RS2QTGIN gin
        WHERE cin.group_id = gin.id
        AND cin.cin_value = combination
        and cin.date_end is not null;
    my_c1 c1%rowtype;
BEGIN
   open c1;

   FETCH c1 INTO my_c1;

   IF c1%NOTFOUND THEN
        IF c1%ROWCOUNT != 0 THEN
            solution := true;
        END IF;
   END IF;

   close c1;

RETURN solution;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

The problem comes when testing in with a simple call. For example :

select check_cin_closed_line('1PW2+UB07') from dual;
select CHECK_CIN_CLOSED_LINE('') from dual;

Both test calls return an error of invalid datatype (ORA-00902 - 00902. 00000 - "invalid datatype") I dont't understand the reason of this error, the input parameter is varchar2 variable and the input value is a varchar value.

What if your return type is varchar2? I think boolean is not the correct data type for sql to handle — Popeye
@BarbarosÖzhan I use this kind of query to check if exist an row that satisfy the conditions. — Jorge Vega Sánchez
but it should produce error, maybe SELECT COUNT(1) might be preferred. Btw, a boolean value cannot be invoked within a SQL Statement, but might be assigned wthin a from PL/SQL Block as @Patrick Bacon stated. — Barbaros Özhan

Patrick Bacon Patrick Bacon · Accepted Answer · 2020-03-18T16:41:52

The Boolean Data Type is Only Available in PLSQL, not in SQL

Notice that if you comment most of the body of the function, you will receive the same error:

CREATE OR REPLACE FUNCTION check_cin_closed_line (
    combination IN VARCHAR2 DEFAULT ''
) RETURN BOOLEAN IS
    solution BOOLEAN := false;

--    cursor c1 is
--        SELECT (1)
--        FROM RS2QTCIN cin, RS2QTGIN gin
--        WHERE cin.group_id = gin.id
--        AND cin.cin_value = combination
--        and cin.date_end is not null;
--    my_c1 c1%rowtype;
BEGIN
--   open c1;
--
--   FETCH c1 INTO my_c1;
--
--   IF c1%NOTFOUND THEN
--        IF c1%ROWCOUNT != 0 THEN
--            solution := true;
--        END IF;
--   END IF;
--
--   close c1;
    RETURN solution;
EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20001, 'An error was encountered - '
                                        || sqlcode
                                        || ' -ERROR- '
                                        || sqlerrm);
END;

Check:

SELECT
    check_cin_closed_line('1PW2+UB07')
FROM
    dual;


SELECT
    check_cin_closed_line('')
FROM
    dual;

You can used the BOOLEAN data type in PLSQL, but not as the return type.

What Can Be Done?

Since you can use BOOLEAN data types in PLSQL and you want to see the results of this function invocation, perhaps use an anonymous PLSQL block and use a CASE statement to reveal the value of the BOOLEAN return value:

BEGIN
    dbms_output.put_line(
        CASE
            WHEN check_cin_closed_line('1PW2+UB07') THEN
                'true'
            ELSE 'false'
        END
    );
END;

PL/SQL function input value seems not matching function input parameter

1 Answers