My apologies if I wasn't clear in my explanation. In the production
environment we have a large number of functions returning Boolean, all of
which are called by other PL/SQL modules. This works fine, but the problem
occurs when you try to call the function from within a SELECT statement,
i.e. when you are on the interface between PL/SQL and SQL.
The function return_boolean was just a stripped down example so that you can
replicate the error without having to waste time on superfluous code.
Obviously the this kind of function is not in use in the production
environment!
The real problem is that the moment there is a Boolean datatype within a
SELECT, the whole operation fails. This is true even where the Boolean is a
parameter for another function, as in my 1st & 2nd posts.
The way I see it, I have three options,
1. Modify all functions returning Boolean to a datatype which is supported
in SQL.
2. Create a function which encapsulates my calls to Boolean functions within
a string. E.g. SELECT encap_bool ('return_boolean (1)') FROM...
3. Keep on looking...
"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message
news:3ADE77F0.45CDF9E2_at_exesolutions.com...
> 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 - 05:01:11 CDT
