In a COTS system, I am able to enter a SQL WHERE clause.
- I'm aware that a user-defined SQL WHERE clause is a questionable design choice from a security perspective. With that said, I have zero control over the design of this software. This isn't a security question.
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 (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)?
CodePudding user response:
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.