Skip to Main Content
  • Questions
  • Oracle CAST function conversion for BOOLEAN TO VARCHAR

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amar.

Asked: February 23, 2017 - 9:33 am UTC

Last updated: August 08, 2019 - 2:27 am UTC

Version: 11g R1

Viewed 50K+ times! This question is

You Asked

Hi Tom,
As per Oracle docs on CAST function,

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html

A BOOLEAN value can be cast explicitly to any of the string types. The result is 'true', 'false', or null. Conversely, string types can be cast to BOOLEAN. However, an error is raised if the string value is not 'true', 'false', 'unknown', or null. Casting 'unknown' to boolean results in a null value.

But,

When I tried to do so it will throw an error

DECLARE
V_FLAG BOOLEAN := TRUE;
V_RESULT VARCHAR2(10);
BEGIN

SELECT CAST(v_flag AS VARCHAR(10))
INTO V_RESULT
FROM DUAL;

DBMS_OUTPUT.put_line('Value for Flag is '||V_RESULT);
END;
/

Error report:
ORA-06550: line 6, column 19:
PLS-00382: expression is of wrong type
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Would you please reach out whether there is deficiency in oracle Docs or the concept I understand.
Please acknowledge,

Thanks,
Amar Borishaha

and Connor said...

A more complete sentence would be:

"As per Oracle docs on CAST function for JavaDB"

which is *not* the same as the Oracle Database.


If you want to cast a boolean, you'll need to take care of it yourself in PL/SQL, eg

my_varchar_variable := case when boolean_var then 'true' else 'false' end;


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

FALSE is not NULL

Andreas WIsmann, August 07, 2019 - 7:40 am UTC

Respectively, in order to distinguish between Boolean FALSE and NULL:

my_varchar_variable := case boolean_var when true then 'true' when false then 'false' else NULL end;
Connor McDonald
August 08, 2019 - 2:27 am UTC

Good point.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here