For istance, if I have a string like
my_string := ' ''a'' = ''a'' and 1 > 0 '
I can get it evaluated doing something like this in a procedure/function
execute immediate 'select CASE WHEN(' || my_string || ') THEN 1 ELSE 0 END from dual'
But is there a way to do that without using execute immediate? Is there a way to evaluate a string like it was written in a query?
I want to this because I have generic conditions in a table like "COD1 like '%x%' OR COD2 = 'Z'". So I do some replace with this strings but then I would like to have them evaluated with the costraint to not use a user definied function, so no "execute immediate"
CodePudding user response：
No way, as far as I can tell. That's what dynamic SQL (i.e. execute immediate) is used for.
For example, if you put just one condition (for simplicity) into a table:
SQL> select * from test;
MY_STRING
---------------------
'a' = 'a' and 1 > 0
and cross-join it to another table (because, I'd expect everything to be returned as that condition is always met), you get an error:
SQL> select *
2 from dept d cross join test t
3 where t.mystring;
where t.mystring
*
ERROR at line 3:
ORA-00920: invalid relational operator
while - if condition is literally put into the where clause, it works:
SQL> select *
2 from dept d cross join test t
3 where 'a' = 'a' and 1 > 0;
DEPTNO DNAME LOC MY_STRING
---------- -------------- ------------- ---------------------
10 ACCOUNTING NEW YORK 'a' = 'a' and 1 > 0
20 RESEARCH DALLAS 'a' = 'a' and 1 > 0
30 SALES CHICAGO 'a' = 'a' and 1 > 0
40 OPERATIONS BOSTON 'a' = 'a' and 1 > 0
SQL>
So, dynamic SQL it is, I'm afraid.
CodePudding user response：
is there a way to do that without using execute immediate
You can use a substitution variable as an alternative method such as
SQL> SELECT CASE WHEN(&str) THEN 1 ELSE 0 END
2 FROM dual;
CASEWHEN('A'='A'AND1>0)THEN1EL
------------------------------
1
where 'a' = 'a' and 1 > 0 entered for &str whenever prompted
CodePudding user response：
Yes, but ... you effectively have to write your own expression parser:
If you have the tables:
CREATE TABLE table_name (a, b, c, d) AS
SELECT 'x', 'x', 'x', 'x' FROM DUAL UNION ALL
SELECT 'w', 'x', 'y', 'z' FROM DUAL;
CREATE TABLE filters (filter) AS
SELECT 'a = b AND c <= d' FROM DUAL UNION ALL
SELECT 'a < b AND b < c AND c < d' FROM DUAL UNION ALL
SELECT 'a < ''y''' FROM DUAL UNION ALL
SELECT 'c LIKE ''%y%''' FROM DUAL;
and you want to apply the filters to table_name then, from Oracle 12, you can use:
WITH split_filters ( id, filter, left_operand, operator, right_operand, expr, num_expr ) AS (
SELECT ROWID,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
4
),
1,
REGEXP_COUNT(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
'i'
)
FROM filters
UNION ALL
SELECT id,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
4
),
expr 1,
num_expr
FROM split_filters
WHERE expr < num_expr
)
SELECT *
FROM table_name t
CROSS JOIN LATERAL (
SELECT MAX(filter) AS filter
FROM (
SELECT id,
filter,
CASE
WHEN UPPER(left_operand) = 'A' THEN t.a
WHEN UPPER(left_operand) = 'B' THEN t.b
WHEN UPPER(left_operand) = 'C' THEN t.c
WHEN UPPER(left_operand) = 'D' THEN t.d
WHEN left_operand LIKE '''%''' THEN REPLACE(SUBSTR(left_operand, 2, LENGTH(left_operand) - 2), '''''', '''')
END AS l_op,
operator AS op,
CASE
WHEN UPPER(right_operand) = 'A' THEN t.a
WHEN UPPER(right_operand) = 'B' THEN t.b
WHEN UPPER(right_operand) = 'C' THEN t.c
WHEN UPPER(right_operand) = 'D' THEN t.d
WHEN right_operand LIKE '''%''' THEN REPLACE(SUBSTR(right_operand, 2, LENGTH(right_operand) - 2), '''''', '''')
END AS r_op,
num_expr
FROM split_filters
)
WHERE CASE
WHEN op = '=' AND l_op = r_op THEN 1
WHEN op = '!=' AND l_op != r_op THEN 1
WHEN op = '<' AND l_op < r_op THEN 1
WHEN op = '>' AND l_op > r_op THEN 1
WHEN op = '<=' AND l_op <= r_op THEN 1
WHEN op = '>=' AND l_op >= r_op THEN 1
WHEN op = 'LIKE' AND l_op LIKE r_op THEN 1
END = 1
GROUP BY id
HAVING COUNT(*) = MAX(num_expr)
);
Which outputs:
A B C D FILTER x x x x a = b AND c <= d x x x x a < 'y' w x y z a < b AND b < c AND c < d w x y z a < 'y' w x y z c LIKE '%y%'
db<>fiddle here
In Oracle 11g, you could re-write it as:
WITH split_filters ( id, filter, left_operand, operator, right_operand, expr, num_expr ) AS (
SELECT ROWID,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
1,
'i',
4
),
1,
REGEXP_COUNT(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
'i'
)
FROM filters
UNION ALL
SELECT id,
filter,
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
1
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
3
),
REGEXP_SUBSTR(
filter,
'(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- left_operand
|| '\s*([<>!]?=|[<>]|LIKE)' -- operator
|| '\s*(''([^'']|'''')*''|[A-Z][A-Z0-9_]*)' -- right_operand
|| '\s*($|\sAND\s )', -- expression concatenator
1,
expr 1,
'i',
4
),
expr 1,
num_expr
FROM split_filters
WHERE expr < num_expr
),
operand_substitutions (t_id, f_id, a, b, c, d, filter, l_op, op, r_op, num_expr) AS (
SELECT t.ROWID,
f.id,
t.a,
t.b,
t.c,
t.d,
filter,
CASE
WHEN UPPER(left_operand) = 'A' THEN t.a
WHEN UPPER(left_operand) = 'B' THEN t.b
WHEN UPPER(left_operand) = 'C' THEN t.c
WHEN UPPER(left_operand) = 'D' THEN t.d
WHEN left_operand LIKE '''%''' THEN REPLACE(SUBSTR(left_operand, 2, LENGTH(left_operand) - 2), '''''', '''')
END,
operator,
CASE
WHEN UPPER(right_operand) = 'A' THEN t.a
WHEN UPPER(right_operand) = 'B' THEN t.b
WHEN UPPER(right_operand) = 'C' THEN t.c
WHEN UPPER(right_operand) = 'D' THEN t.d
WHEN right_operand LIKE '''%''' THEN REPLACE(SUBSTR(right_operand, 2, LENGTH(right_operand) - 2), '''''', '''')
END,
num_expr
FROM split_filters f
CROSS JOIN table_name t
)
SELECT MAX(a) AS a,
MAX(b) AS b,
MAX(c) AS c,
MAX(d) AS d,
MAX(filter) AS filter
FROM operand_substitutions
WHERE CASE
WHEN op = '=' AND l_op = r_op THEN 1
WHEN op = '!=' AND l_op != r_op THEN 1
WHEN op = '<' AND l_op < r_op THEN 1
WHEN op = '>' AND l_op > r_op THEN 1
WHEN op = '<=' AND l_op <= r_op THEN 1
WHEN op = '>=' AND l_op >= r_op THEN 1
WHEN op = 'LIKE' AND l_op LIKE r_op THEN 1
END = 1
GROUP BY t_id, f_id
HAVING COUNT(*) = MAX(num_expr);
db<>fiddle here