Convert Boolean

[Ian Ledzion]

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.

[Daniel A. Morgan]

Try decode.

SELECT DECODE(booleanvar, True, 1, 0)
FROM ....;

Daniel A. Morgan

[Ian Ledzion]

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" <dmo...@exesolutions.com> wrote in message
news:3ADD2460...@exesolutions.com...

[Daniel A. Morgan]

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.

[Ian Ledzion]

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" <dmo...@exesolutions.com> wrote in message

news:3ADE77F0...@exesolutions.com...

[fumi]

"Daniel A. Morgan" <dmo...@exesolutions.com> ????? news:3ADD2460...@exesolutions.com...

> Try decode.
>
> SELECT DECODE(booleanvar, True, 1, 0)
> FROM ....;
>
> Daniel A. Morgan

Obviously, it does NOT work.
The datatype BOOLEAN is not suppored in SQL.

>
> > 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.

If your functions use paremeters or return value of boolean datatype,
they can be used only in PL/SQL, not in SQL.