CODEWITHSUNDEEP
oracle-databaseplsql
Delin Mathew
Delin Mathew

Reputation: 279

PL/SQL: testing functions that return Boolean value

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;

Upvotes: 0

Views: 13141

Answers (2)

MT0
MT0

Reputation: 168806

There are two errors in your code:

  1. 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;
/

  2. 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;
/

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143163

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>

Upvotes: 4

Related Questions