Bool Support Oracle SQL

Bool support Oracle SQL

You can write your own wrapper like this:

CREATE OR REPLACE FUNCTION my_bool_to_str(f varchar2) RETURN VARCHAR2 IS

  b varchar2(2);

BEGIN

  EXECUTE IMMEDIATE 'declare bl boolean; begin bl := ' || f ||
                    '; if bl then :1 := ''y''; else :1 := ''n''; end if; end;'
    using out b;

  return b;

END;

Then you can call it like this:

SELECT part_no,
       my_bool_to_str('stock_pkg.is_in_stock('|| part_no|| ')') in_stock
FROM   parts_table

The difference from your wrapper is that it gets a varchar as input and not a boolean which the SQL engine doesn't recognize

How to use BOOLEAN type in SELECT statement

You can build a wrapper function like this:

function get_something(name in varchar2,
                   ignore_notfound in varchar2) return varchar2
is
begin
    return get_something (name, (upper(ignore_notfound) = 'TRUE') );
end;

then call:

select get_something('NAME', 'TRUE') from dual;

It's up to you what the valid values of ignore_notfound are in your version, I have assumed 'TRUE' means TRUE and anything else means FALSE.

Oracle's lack of a Bit datatype for table columns

I prefer char(1) over number(1), since with some reasonable choice of characters, it is obvious which character has which boolean meaning.

Of course you should fight all the different varations, choose one and ensure it's use by putting check constraints on the columns.

Although it probably is to late in your case, generating the schema from another tool often takes care at least of the consistency issue. I personally prefer hibernate for this purpose, but that is very situation specific.

And of course that is a glaring obmission. To make it worse, PL/SQL has a boolean, but you can't use it in SQL statements.

Boolean Field in Oracle

I found this link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

The most commonly seen design is to imitate the many Boolean-like
flags that Oracle's data dictionary views use, selecting 'Y' for true
and 'N' for false. However, to interact correctly with host
environments, such as JDBC, OCCI, and other programming environments,
it's better to select 0 for false and 1 for true so it can work
correctly with the getBoolean and setBoolean functions.

Basically they advocate method number 2, for efficiency's sake, using

• values of 0/1 (because of interoperability with JDBC's getBoolean() etc.) with a check constraint
• a type of CHAR (because it uses less space than NUMBER).

Their example:

create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`

Boolean giving invalid datatype - Oracle

Oracle does not support the boolean data type at schema level, though it is supported in PL/SQL blocks. By schema level, I mean you cannot create table columns with type as boolean, nor nested table types of records with one of the columns as boolean. You have that freedom in PL/SQL though, where you can create a record type collection with a boolean column.

As a workaround I would suggest use CHAR(1 byte) type, as it will take just one byte to store your value, as opposed to two bytes for NUMBER format. Read more about data types and sizes here on Oracle Docs.

How to convert Boolean to Number in PL/SQL?

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;

How to update a boolean field in Oracle table

It depends on how the field is defined.

If its defined as a CHAR(1) field, then you can store 'Y'/'N' or 'T'/'F' in it. To update the field, you'd use the quotes as it would be a string literal.

UPDATE TestTable set myCharBooleanColumn = 'Y';

If the field is defined as NUMERIC, then the convention is 0=false and 1 or -1 is true (I've seen both).

UPDATE TestTable set myNumericBooleanColumn = 1;

Many people will advocate the CHAR(1) approach, but in the real world - you see both. It depends on how the boolean is implemented.

You can read more in Oracle's docs on Datatypes
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm

---

Related Topics