How to convert Boolean to Number in PL/SQL?

Question

I have a boolean value and I am trying to convert it to number in PL/SQL.

PROCEDURE Test(
   contract_ IN VARCHAR2,
   mch_code_ IN BOOLEAN)   
IS
  val_ NUMBER;
BEGIN
  val_ := mch_code_.to_number();
...some code
END Test;

This is not working.

How to convert to number from boolean in PL/SQL?

Possible duplicate: https://stackoverflow.com/questions/1465405/how-to-use-boolean-type-in-select-statement — Pitto, Jul 12 '18 at 09:21

score 8 · Accepted Answer · answered Jul 12 '18 at 09:21

8

You need a case:

create PROCEDURE Test(
   contract_ IN VARCHAR2,
   mch_code_ IN BOOLEAN)   
IS
 val_ NUMBER;
BEGIN
  val_ := case when mch_code_ then 1 else 0 end;
...some code
END Test;

answered Jul 12 '18 at 09:21

Actually the `case` should preserve `NULL` and not assume that NULL is FALSE: `case when mch_code_ then 1 when NOT mch_code_ then 0 end` – Marmite Bomber Feb 15 '19 at 14:58
Hi, i tried this code in the select. But can't work :). why plsql can't support bool? – Oğuz Mar 22 '21 at 12:32
1

@Oğuz: PL/SQL supports boolean, but Oracle's SQL does not – Mar 22 '21 at 12:53

score 8 · Answer 2 · answered Jul 12 '18 at 09:29

8

How to convert to number from boolean in plsql?

You need to use sys.diutil.bool_to_int to convert a boolean to a number. See below demo.

DECLARE

  var BOOLEAN :=TRUE;
  var1 varchar2(10);
BEGIN 
  --Converting boolean to int here and checking.
  var1:=CASE WHEN (sys.diutil.bool_to_int(var)) = 1 THEN 'TRUE'
             WHEN (sys.diutil.bool_to_int(var)) = 0 THEN  'FALSE'
             END;  
    dbms_output.put_line(var1);
END;

Output:

anonymous block completed

In your case it becomes like:

PROCEDURE Test(
        contract_ IN VARCHAR2,
        mch_code_ IN BOOLEAN)
IS
    val_ NUMBER;
BEGIN
    val_ := sys.diutil.bool_to_int(mch_code_);
    ...SOME code
END Test;

answered Jul 12 '18 at 09:29

XING

9,608
4
22
38

do we really need to use this function? Case will be the better option here correct? – Gaj Jul 12 '18 at 09:44
@Gaj that's already shared by horse. I cannot post duplicate answers. :-) – XING Jul 12 '18 at 09:46
@Gaj This function is better for me because I want to pass in multiple boolean values into a cursor. I just pass in 1 or 0 directly from the function and that makes my code cleaner. – Pluto Dec 17 '21 at 18:08

How to convert Boolean to Number in PL/SQL?

2 Answers2