How to use BOOLEAN type in SELECT statement

Question

I have a PL/SQL function with BOOLEAN in parameter:

function get_something(name in varchar2,
                       ignore_notfound in boolean)

This function is a part of 3rd party tool, I cannot change this.

I would like to use this function inside a SELECT statement like this:

select get_something('NAME', TRUE) from dual

This does not work, I get this exception:

ORA-00904: "TRUE": invalid identifier

As I understand it, keyword TRUE is not recognized.

How can I make this work?

Answer 1

PL/SQL is complaining that TRUE is not a valid identifier, or variable. Set up a local variable, set it to TRUE, and pass it into the get_something function.

Answer 2

You can build a wrapper function like this:

function get_something(name in varchar2,
                   ignore_notfound in varchar2) return varchar2
is
begin
    return get_something (name, (upper(ignore_notfound) = 'TRUE') );
end;

then call:

select get_something('NAME', 'TRUE') from dual;

It's up to you what the valid values of ignore_notfound are in your version, I have assumed 'TRUE' means TRUE and anything else means FALSE.

Answer 3

From documentation:

You cannot insert the values TRUE and FALSE into a database column. You cannot select or fetch column values into a BOOLEAN variable. Functions called from a SQL query cannot take any BOOLEAN parameters. Neither can built-in SQL functions such as TO_CHAR; to represent BOOLEAN values in output, you must use IF-THEN or CASE constructs to translate BOOLEANvalues into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

You will need to make a wrapper function that takes an SQL datatype and use it instead.

Answer 4

The BOOLEAN data type is a PL/SQL data type. Oracle does not provide an equivalent SQL data type (...) you can create a wrapper function which maps a SQL type to the BOOLEAN type.

Check this: http://forums.datadirect.com/ddforums/thread.jspa?threadID=1771&tstart=0&messageID=5284

Answer 5

How about using an expression which evaluates to TRUE (or FALSE)?

select get_something('NAME', 1 = 1) from dual