Converting Boolean to Varchar2

Question

I have a sample code where i'm trying to print Boolean value. It resulted error.

wrong number or types of arguments in call to 'PUT_LINE'

wrong number or types of arguments in call to 'TO_CHAR'

DECLARE
    status BOOLEAN:= false;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(status);
      DBMS_OUTPUT.PUT_LINE(to_char(status));
    END;

By the error message it's clear that Boolean can't be converted to character in both ways (implicit, explicit).

Why its not possible?

Is their any specific reasons? or Oracle just forgot about this type of conversion(Its highly unlikely).

So is their any other way to convert? Or do i have to go for IF or CASE statement to guess what status has.

Answer 1

It seems you cannot concat varchar and boolean.

Define this function:

CREATE OR REPLACE FUNCTION BOOLEAN_TO_CHAR(STATUS IN BOOLEAN)
RETURN VARCHAR2 IS
BEGIN
  RETURN
   CASE STATUS
     WHEN TRUE THEN 'TRUE'
     WHEN FALSE THEN 'FALSE'
     ELSE 'NULL'
   END;
END;

and use it like this:

DBMS_OUTPUT.PUT_LINE('status'|| BOOLEAN_TO_CHAR(status));

Answer 2

As an alternative I have been using the SYS.DIUTIL package's BOOL_TO_INT() function:

DECLARE
status BOOLEAN:= false;
BEGIN
  DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(status));
END;

This will return 1 for true and 0 for false (and null for null).