Re: Convert Boolean

Thanks for the advice, I tried the following:

CREATE OR REPLACE FUNCTION return_boolean (p_binary_number IN NUMBER) RETURN BOOLEAN
IS
BEGIN
   RETURN p_binary_number = 1;
END;
/

Selecting the function:
SELECT return_boolean (1) FROM dual;
gets the error from my first post
ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type

Attempting to use DECODE:
SELECT DECODE(return_boolean (1), True, 1, 0) FROM dual; gets
ORA-00904: invalid column name

The nub of the problem seems to be that the Boolean datatype is is not supported in Oracle SQL (check the SQL Reference). In fact Booleans do not appear to be part of ANSI SQL Datatypes either.

Boolean may be supported for PL/SQL, but when you are interacting between the two, things go astray. I thought that there might be a function which could be used to convert a boolean result into something which Oracle won't choke on.

Of course, rewriting all the functions which return Booleans to return a Number would be a solution, but you don't necessarily have access to all the code...

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3ADD2460.54953F92_at_exesolutions.com...
> Try decode.
>
> SELECT DECODE(booleanvar, True, 1, 0)
> FROM ....;
>
> Daniel A. Morgan
>
>
> Ian Ledzion wrote:
>
> > Is there a package function or other method to convert BOOLEAN values to
> > something else, e.g. numeric within a query?
> >
> > I've checked the built-in packages with no luck (though maybe I missed
> > something), then tried my own function, which is below:
> >
> > CREATE OR REPLACE
> > FUNCTION test_conv (p_boolean IN BOOLEAN)
> > RETURN NUMBER
> > IS
> > v_value NUMBER DEFAULT 0;
> > BEGIN
> > IF p_boolean
> > THEN
> > v_value := 1;
> > END IF;
> >
> > RETURN v_value;
> > END;
> > /
> >
> > This works fine in an anonymous SQL block, but no luck in a query. The
> > error message I get is:
> >
> > ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is
 of
> > wrong type
> >
> > Any thoughts would be much appreciated.
>
Received on Wed Apr 18 2001 - 04:30:11 CDT