Re: Convert Boolean

Booleans are perfectly valid in PL/SQL. I use them all the time. What bothers me is your first line of code:

CREATE OR REPLACE FUNCTION return_boolean (p_binary_number IN NUMBER) RETURN BOOLEAN By doing it the way you have the function is totally useless. Once you have it in your p_binary_number variable which is a NUMBER you already have forced the failure you are trying to catch. The function is rendered worse than useless. You either pass in the value as a VARCHAR2 or forget the whole thing and do your trapping in whatever you had calling the function.

Daniel A. Morgan

Ian Ledzion wrote:

> 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 Thu Apr 19 2001 - 00:30:24 CDT