Using user defined function as boolean expression

Question

How to use user defined function as boolean expression ?

For example function REGEXP_LIKE() used as boolean expression, right?

... WHERE REGEXP_LIKE() ...

But how to make such for user defined function? When my function returns BOOLEAN type and then, when I call this function, I got error because pure SQL can't use BOOLEAN type.

P.S.

So, I do this: my function returns VARCHAR2 type: that is 'TRUE' or 'FALSE' and then in SQL I use: ... WHERE my_function() = 'TRUE' ...

This is right way for simulate boolean type? or exists more better solutions ?

Frank Schmitt · Accepted Answer · 2013-12-02 11:51:19Z

1

No. This can be confusing in Oracle but SQL does NOT know boolean datatype, although it deals with boolean expressions. Unlike procedural PL/SQL where boolean datatype can be used.

In Oracle SQL and PL/SQL are evaluated by different engines. These engines use similar datatypes, but not the same.

edited Dec 2, 2013 at 11:51

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

answered Dec 2, 2013 at 11:10

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Oto Shavadze Over a year ago

So, in SQL, simulating boolean type using varchar, that is: WHERE my_function() = 'TRUE' is a normal way ?

ibre5041 Over a year ago

Yes. You can even create your own "operator" in Oracle. But even operator work the same way.

ibre5041 Over a year ago

Maybe when you return a NUMBER instead of VARCHAR2 from your function it will be a little bit faster.

Frank Schmitt Over a year ago

@Ivan I'd go for return type NUMBER instead of VARCHAR2 - simpler and cleaner IMO.

Collectives™ on Stack Overflow

Using user defined function as boolean expression

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related