How to write a function only used in the where clause that evaluates to true without a comparison operator?

Question

My question is: In Oracle regexp_like works alone in the where clause without having to compare to 1 or 0 or a string. The function can only be called when evaluating something in a case statement or the where clause. Since it can't be described (tried searching the data dictionary for it) I'm wondering how to write a function that works the same way.

For example:

function is_prod
returns boolean
is 
  l_var boolean := false;
begin
  if sys_context('userenv','db_unique_name') = '"PROD_SERVER"' then
    l_var := true;
  end if;
return l_var;
end;

That function compiles, but cannot be used in a SQL statement like the following:

select *
from table t
where is_prod

Because I get the following error: ORA-00920: invalid relational operator.

Comparing it to a number or true doesn't work either.

Where can I find the code base for regexp_like or what do I need to do to make this work like regexp_like?

Note: I've looked around for several hours and found that Oracle's regexp functions are actually java calls, but that means they still need a pl/sql wrapper.

Answer 1

Basically, oracle has a boolean datatype only for PLSQL. So, as long as you stay in plsql you can use them but not in SQL.

From documentation:

Because SQL has no data type equivalent to BOOLEAN, you cannot:

• Assign a BOOLEAN value to a database table column

• Select or fetch the value of a database table column into a BOOLEAN variable

• Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement

If you want to find metadata about built-in functions, then maybe this post can help.