sqloracleplsqloracle10gora-00904

How to use BOOLEAN type in SELECT statement


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?


Solution

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