I am trying to test a PL/SQL function that returns a Boolean value. When I try to do this using the code below I get the error:
ORA-06550: Expression is of wrong type.
declare
v1 boolean;
begin
select plsql_package.plsql_function('arg1', 'arg2') into v1 from dual;
dbms_output.put_line('result = ' || v1);
end;
It won't work like that; DBMS_OUTPUT.PUT_LINE expects strings, not Boolean datatype, so you have to "convert" a Boolean datatype variable's value to a string - for example, using CASE (as I did). Have a look at this example:
SQL> create or replace function plsql_function return boolean as
2 begin
3 return false;
4 end;
5 /
Function created.
SQL> set serveroutput on
SQL> declare
2 v1 boolean;
3 begin
4 v1 := plsql_function;
5 dbms_output.put_line('result ' || case when v1 then 'true' else 'false' end);
6 end;
7 /
result false
PL/SQL procedure successfully completed.
SQL>
There are two errors in your code:
The BOOLEAN data type is a PL/SQL data type and does not exist in the SQL scope so
CREATE FUNCTION test_fn RETURN BOOLEAN IS BEGIN RETURN FALSE; END;
/
declare
v1 boolean;
begin
select test_fn() into v1 from dual;
end;
/
will fail with
ORA-06550: line 4, column 25:
PLS-00382: expression is of wrong type
because you are calling the function returning a PL/SQL only data type using SQL.
Instead, just assign the function result to the variable without using SQL:
declare
v1 boolean;
begin
v1 := test_fn();
end;
/
The both string concatenation operator || and DBMS_OUTPUT.PUT_LINE function do not accept a BOOLEAN argument.
BEGIN
DBMS_OUTPUT.PUT_LINE( FALSE );
END;
/
will fail with:
ORA-06550: line 2, column 3
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
and
DECLARE
v2 VARCHAR2(20);
BEGIN
v2 := 'result = ' || FALSE;
END;
/
will fail with:
ORA-06550: line 4, column 9
PLS-00306: wrong number or types of arguments in call to '||'
Instead, you need to convert the BOOLEAN data type to something it does accept like a VARCHAR2/CHAR:
DECLARE
v1 BOOLEAN := FALSE;
BEGIN
DBMS_OUTPUT.PUT_LINE( 'result = ' || CASE WHEN v1 THEN 'true' ELSE 'false' END );
END;
/
'result = ' || v1 is also invalid because the concatenation operator accepts strings (or values that can be implicitly converted to strings), not Booleans.