Really interesting question. There are two aspects to this.
First, is this a good idea. The problem is that the text in the rule is invisible to the database. It will not be displayed when checking dependencies, so analyzing the effect becomes complicated. Obviously (or perhaps not obvious) the syntax of a rule can only be verified by running it. This can create problems with adding rules. So it can also be a maintenance problem. And, as we will see, when you go beyond simplified queries, it will be difficult to program.
The second aspect - is this possible? It. We need to use dynamic SQL; combining dynamic SQL with static SQL is doable, but boring.
create table rules (project_name varchar2(30) , rule_name varchar2(30) , rule_text varchar2(4000) ) / insert into rules values ('SO', 'ACC_SALES' , 'select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > 500000 ') / create table customers (accountnumber number(7,0) , name varchar2(20) , accumulated_sales number , sales_region varchar2(3)) / insert into customers values (111, 'ACME Industries', 450000, 'AA') / insert into customers values (222, 'Tyrell Corporation', 550000, 'BB') / insert into customers values (333, 'Lorax Textiles Co', 500000, 'BB') /
This function receives the rule, executes it, and returns a numerical array.
create or replace type rule_numbers as table of number / create or replace function exec_numeric_rule ( p_pname in rules.project_name%type , p_rname in rules.rule_name%type ) return rule_numbers is return_value rule_numbers; stmt rules.rule_text%type; begin select rule_text into stmt from rules where project_name = p_pname and rule_name = p_rname; execute immediate stmt bulk collect into return_value; return return_value; end exec_numeric_rule; /
Let's check it out.
SQL> select * from customers 2 where accountnumber in 3 ( select * from table (exec_numeric_rule('SO', 'ACC_SALES'))) 4 / ACCOUNTNUMBER NAME ACCUMULATED_SALES SAL ------------- -------------------- ----------------- --- 222 Tyrell Corporation 550000 BB 1 row selected. SQL>
What is the only and correct answer.
But now we come to your additional question:
"Can this be done if the stored request uses its own variables"
Yes, it can, but things are getting a little more fragile. New rule:
insert into rules values ('SO', 'ACC_SALES_VAR' , 'select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > :LMT ') /
We will modify the function to apply it:
create or replace function exec_numeric_rule ( p_pname in rules.project_name%type , p_rname in rules.rule_name%type , p_variable in number := null) return rule_numbers is return_value rule_numbers; stmt rules.rule_text%type; begin select rule_text into stmt from rules where project_name = p_pname and rule_name = p_rname; if p_variable is null then execute immediate stmt bulk collect into return_value; else execute immediate stmt bulk collect into return_value using p_variable; end if; return return_value; end exec_numeric_rule; /
Crossed fingers!
SQL> select * from customers 2 where accountnumber in 3 ( select * from table (exec_numeric_rule('SO', 'ACC_SALES_VAR', 480000))) 4 / ACCOUNTNUMBER NAME ACCUMULATED_SALES SAL ------------- -------------------- ----------------- --- 222 Tyrell Corporation 550000 BB 333 Lorax Textiles Co 500000 BB 2 rows selected. SQL>
Ok, that still works. But you can see that the permutations are not friendly. If you want to pass more than one argument to a rule, then you need more functions or cooler internal logic. If you want to return sets of dates or strings, you need more functions. If you want to pass the P_VARIABLE parameters of various data types, you may need more functions. You certainly need the prerequisites for type checking.
Which brings me back to the first point: yes, it can be done, but is it worth the hassle?