Bool support Oracle SQL

Question

It's a constant frustration of mine that Oracle PL/SQL supports the bool data-type, while Oracle SQL does not. It's a big pain in the proverbial when you want to process a PL/SQL boolean return-value back into your everyday SQL (example below).

Even the ask-Tom website is blasé about this misfit, reporting that you should code boolean columns as fixed-values 'Y'/'N' CHAR columns, which is a such a bad cop-out answer on so many different levels that I don't know where to start criticising it. In fact, the only redeeming quality of this response is the fact that (as far as I've recently discovered), many other database-engines don't support the boolean data-type either.

Anyhow - the question...

I have a work-around for the following problem (albeit messy and verbose), so I'm asking this question out of curiosity rather than necessity. But one of the few things that surprises me any more is the ingenuity of clever programmers, so here's hoping that one of you can come up with a solution to the following.

In the following sample, the function stock_pkg.is_in_stock() (which is an inherent part of my application) returns a BOOL value, rendering the SQL invalid (remember, SQL doesn't support BOOL):

SELECT part_no, stock_pkg.is_in_stock(part_no) in_stock
FROM   parts_table

What I need is to find a way of using the above function-call to generate a valid string (varchar) output of the format:

PART_NO IN_STOCK
------- ------------
AA      YES
BB      NO
CC      NO

(You may substitute 'yes/no' for 'true/false', 'green/red', 'tory/labour' or even numeric 1/0 for all I care - just so long as the output falls into one of two distinct categories.)

Unfortunately, I don't have the privilege to rewrite the original function to return a different data-type. And besides, there are thousands of functions like this dotted around the larger application, making it impractical to rewrite them all.

So in this sense, the solution must be a 'generic' one (i.e. not specific to this function call). For example, it is not sufficient to rewrite the function as stock_pkg.is_in_stock_chr(), because that would mean having to re-write all the other similar functions in my application too.

I've already tried:

SELECT part_no,
       CASE WHEN stock_pkg.is_in_stock(part_no) THEN 'y' ELSE 'n' END in_stock
FROM   parts_table

and even my own wrapper function:

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

But even wrapping booleans inside other functional constructs doesn't seem to be allowed by Oracle SQL (at least not in Oracle 10g).

There's also the option of writing a sub-select inside the in_stock column, but that could get excessively complicated in extreme examples too, and would also be case-specific.

As I say, I hope there's an ingenious solution out there somewhere (or at least a very simple one which I happen to have overlooked).

Thanks for your time.

Answer 1

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