CODEWITHSUNDEEP
plsql
learner1
learner1

Reputation: 116

Convert data type CHAR to data type boolean in plsql

I have one table in oracle, in which a column can contain two values either 0 or 1, datatype of column in CHAR, i have created one procedure to get the records by id from that table and i want to get value of that CHAR column in boolean, means if column value is 0 then false, if 1 then true, how to do conversion between CHAR and boolean in oracle? i have tried following but did not worked,

 CASE (TCA.IS_CUSTOMER) 
             WHEN '0' then FALSE
             WHEN '1' then true 

             else NULL END AS IsCustomer

any help please?

Upvotes: 0

Views: 2142

Answers (3)

KetanC
KetanC

Reputation: 1

It is available in Oracle Database 23c.

SELECT TO_BOOLEAN(0), TO_BOOLEAN('true'), TO_BOOLEAN('no');

TO_BOOLEAN( TO_BOOLEAN( TO_BOOLEAN(
----------- ----------- -----------
FALSE       TRUE        FALSE

SELECT TO_BOOLEAN(1) FROM DUAL;

TO_BOOLEAN( 
----------- 
TRUE

Upvotes: 0

yellowvamp04
yellowvamp04

Reputation: 141

Not quite sure what you are needing inside your procedure, but if you are just reading the data and would just like to interpret it as boolean, and use that boolean data within your procedure then you could: First, declare a boolean variable in your procedure. (You'll used this inside your case statement.) Second, in the THEN statement, you could assign the declared boolean with either FALSE or TRUE dependent on the value it read.

See below example. Hope this helps. :)

EXAMPLE:

DECLARE
  v_boolean BOOLEAN := FALSE /*default value for the boolean*/
  ...

BEGIN
  ...

  /*this case will return v_boolean as TRUE if value of TCA.IS_CUSTOMER is 1, else it will return FALSE*/
  CASE (TCA.IS_CUSTOMER) 
     WHEN 1 then 
        v_boolean := TRUE;
     ELSE
        v_boolean := FALSE; 
  END CASE;

  ...

END;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142958

Boolean won't work on SQL, but will work in PL/SQL (as that datatype exists there).

Here's an example: creating a table first, then doing some (PL/)SQLs:

SQL> create table tca (is_customer varchar2(1));

Table created.

SQL> insert into tca values ('1');

1 row created.

SQL>

Selecting directly into a Boolean variable:

SQL> declare
  2    is_customer boolean;
  3  begin
  4    select case tca.is_customer when '0' then false
  5                                when '1' then true
  6                                else null
  7           end
  8      into is_customer
  9      from tca
 10      where rownum = 1;
 11  end;
 12  /
                              when '1' then true
                                            *
ERROR at line 5:
ORA-06550: line 5, column 45:
PL/SQL: ORA-00904: "TRUE": invalid identifier
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Selecting column value into a local variable first, then detecting its Boolean value:

SQL> declare
  2    tca_is_customer varchar2(1) := '1';
  3    is_customer     boolean;
  4  begin
  5    select is_customer
  6      into tca_is_customer
  7      from tca
  8      where rownum = 1;
  9
 10    is_customer := case tca_is_customer when '0' then false
 11                                        when '1' then true
 12                                        else null
 13                   end;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions