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.

Wowser

What should a Boolean to string conversion be? Should TRUE go to 'TRUE', 'True', 'true', 'T', 't', 'YES', 'Yes', 'yes', 'Y', 'y' or maybe it goes to a number instead: 1? Basically, it's up to the individual to determine what the string (or number!) representation of a Boolean is, because it may vary depending on the context. – Vancouver

Make sure you accept the answers if they are correct :) – Chaworth

None of the arguments as to why this is make any sense to me. Loads of languages simply do this without any issue. They decide what the string representation is and then we all deal with it (in the same way). It also does not prevent anybody from doing it any other way if they like to. In PLSQL we are simply forced to do this over and over again. I do not know any sane reason for this. – Incubation

Answer 1

It seems that there is a new variant TO_CHAR(boolean) in Oracle 23c. https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/to_char-boolean.html

Acevedo

Answer 2

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));

Chaworth

Answer 3

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).

Scalage

Answer 4

You cannot print a boolean variable. For that you can either create a function or use case statement.

In Oracle SQL there is no Boolean data type. Its a PL/SQL data type. Which means you cannot have it as the data type of a table column.

Why its not possible?

Maybe because its not there is ANSI.

Abbate

Answer 5

If you use it in APEX, please check APEX_DEBUG.TOCHAR() https://docs.oracle.com/en/database/oracle/application-express/20.2/aeapi/TOCHAR-Function.html

-- https://docs.oracle.com/en/database/oracle/application-express/20.2/aeapi/TOCHAR-Function.html
SET SERVEROUTPUT ON SIZE 2000
BEGIN
    DBMS_OUTPUT.PUT_LINE(APEX_DEBUG.TOCHAR(TRUE));
END;

Afore