Geek Answers Handbook

Bool Oracle SQL Support

I am constantly disappointed that Oracle PL / SQL supports the bool data type, while Oracle SQL does not. This is a big pain in the notorious when you want to process the logical PL / SQL return value back into your everyday SQL (example below).

Even the ask-Tom site does not agree with this discrepancy, saying that you should encode logical columns as columns with fixed 'Y'/'N' CHAR values, which is such a bad answer to all sorts of levels, Knowing where to start criticizing it. In fact, the only redeeming quality of this answer is the fact that (as far as I recently discovered), many other databases also do not support the logical data type.

In any case, the question is ...

I have work on the following problem (albeit messy and verbose), so I ask this question out of curiosity, and not out of necessity. But one of the few things that surprises me more is the ingenuity of smart programmers, so hopefully one of you can come up with a solution to the following.

In the following example, the stock_pkg.is_in_stock() function (which is an integral part of my application) returns BOOL, which makes SQL invalid (remember, SQL does not support BOOL):

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

I need to find a way to use the above function call to generate a valid string output (varchar) in the format:

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

(You can replace "yes / no" with "true / false", "green / red", "tory / labor" or even numeric 1/0 with everything I need - only as long as the output falls into one two different categories.)

Unfortunately, I do not have the right to rewrite the original function in order to return a different data type. And besides, thousands of features like this are dotted around a larger application, making it impossible to rewrite them all.

So, in this sense, the solution should be “general” (ie, not specific to this function call). For example, it’s not enough to rewrite the function as stock_pkg.is_in_stock_chr() , because that would mean overwriting all other similar functions in my application.

I have 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 Boolean elements inside other functional constructs, apparently, is not allowed by Oracle SQL (at least not in Oracle 10g).

It is also possible to write subsamples inside the in_stock column, but this can be too complicated in extreme examples, as well as for specific cases.

As I said, I hope that somewhere there is a brilliant solution (or at least a very simple one, which I may not have noticed).

Thank you for your time.

+8
sql oracle boolean
source share
1 answer

You can write your own wrapper as follows:

 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 receives varchar as input, and not a boolean that the SQL engine does not recognize

+6
source share

More articles:


All Articles