Use inline function in subquery (in a WHERE clause)

user_5576771 0
Viewed 183

In a COTS system, I am able to enter a SQL WHERE clause.

Example of WHERE clauses that work:

worktype = 'Corrective Maintenance'

or

1 = (
    with 
        cte as (select 1 as calc from dual)
    select 
        calc 
    from 
        cte
    )

Similarly, I'm wondering if it's possible to use an inline function in a subquery in a WHERE clause.

This inline function works on it's own in an SQL client:

with 
    function inline_f(p_num number) return number is 
    begin
        return p_num + 0;
    end;
select 
    inline_f(1) as calc 
from 
    dual

Calc
----
1

But if I were to wrap it in a subquery in a WHERE clause, then I'd get an error (in the COTS system and even in an SQL client):

--select * from a_tbl where

1 = (
    with 
        function inline_f(p_num number) return number is 
        begin
            return p_num + 0;
        end;
    select 
        inline_f(1) as calc 
    from 
        dual
    )

ORA-00921: unexpected end of SQL command
Invalid statement
ORA-00933: SQL command not properly ended

Question:

Is there a way to use an inline function in subquery (in a WHERE clause)?

oracle19c subquery with-statement inline-functions oracle
1 Answers

Is there a way to use an inline function in subquery (in a WHERE clause)?

There is, but I am not sure if this will help you. I don't know what a COTS system is, and how it works. I understand that users can only enter a WHERE clause; but do you (as a developer) - or whoever the developer is, if it's not you - have access to the entire SELECT statement?

If the query in which you define a function in the WITH clause is not the top-level SELECT statement (in your case it isn't), then the top-level SELECT must include the with_plsql hint. (Oracle calls it a "hint", anyway; it uses the hint syntax, but it's not a hint, it is really a compiler directive.) Alas, this must be in the top-level SELECT (meaning the SELECT clause of your query, which is not accessible by your end user).

This is why I asked... if whoever develops these queries for your users doesn't mind adding the with_plsql hint to the top-level SELECT, then you can do what you asked. The hint won't hurt anything if a user types a WHERE clause with no PL/SQL code in a WITH clause, so there should be no reason not to want to do this; but, like I said, I don't know what COTS means or how it works.

Admin1
Related