views:

88

answers:

3

In our Oracle datbase we have a table called RULES, with a field called SQLQUERY. This field is a varchar with an SQL statement stored. The PK is DM_PROJECT.

A typical statement that is stored could be

select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > 500000

I want to do something like this:

select 
  * 
from 
  customers 
where
     accountnumber like 'A%'
  or salesregion = 999
  or accountnumber in
     (
       <run the query SQLQUERY from RULES where DM_PROJECT=:DM_PROJECT>
     )

Can this be done?

(Secondary concern: Can it be done if the stored query uses its own variables, like

select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATEDSALES > :LIMIT 

)

A: 

You can use dynamic SQL(Execute Immediate) ..please refer Execute Immediate for more details.

+7  A: 

Interesting question indeed. There are two aspects to this.

The first is whether it's a good idea. The problem is, the text in the RULE is invisible to the database. It won't show up in dependency checks, so impact analysis becomes hard. Obviously (or maybe not obviously) the syntax of the 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 shall see, once you move beyond simplistic queries it is tough to program with.

The second aspect is whether it's possible. It is. We need to use dynamic SQL; combining dynamic SQL with static SQL is doable but gnarly.

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 gets a rule, executes it and returns a numeric 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;
/

Lets test it.

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>

Which is the one and only correct answer.

But now we come to your suplementary question:

"Can it be done if the stored query uses its own variables"

Yes it can, but things start to get a bit more brittle. New rule:

insert into rules 
values ('SO', 'ACC_SALES_VAR'
        , 'select ACCOUNTNUMBER from CUSTOMERS where ACCUMULATED_SALES > :LMT ')
/

We amend 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;
/

Fingers crossed!

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>

Okay, so it still works. But you can see that the permutations are not friendly. If you want to pass more than one argument to the RULE then you need more functions or a twistier internal logic. If you want to return sets of dates or strings you need more functions. If you want to pass P_VARIABLE parameters of different data_types you may need more functions. You certainly need some type checking pre-conditions.

Which comes back to my first point: yes it can be done, but is it the hassle?

APC
Fantastic answer. People like you is what makes stackoverflow awesome. Good work, sire!
Jamie
I'll just have to agree with Jamie here, that was really interesting and a great answer! +1 for sure.
wasatz
+1: Very well stated answer. Pipeline functions might perform better than nested tables for this kind of procedure.
Allan
@Allan - I doubt that a pipelined function would do anything other than slow things down. The outcome is the same, a nested collection which has to be transformed using a TABLE() function. So why bother looping through the variable piping individual rows?
APC
@APC - I thought that too. In fact, I believed that strongly enough that I [suggested using a nested table over a pipelined function](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:12718072439781#209880500346848707) (admittedly for a simpler case) on AskTom and was told I was wrong... Honestly, I've never been able to find an adequate explanation of how pipelined functions work behind the scenes, but limited testing has shown that they tend to be faster than the nested table solution.
Allan
Thank you for a very thorough and good answer. Accepted! As for wether it's a good idea... Well, I need to do it, so yes it is :-) As part of our application, our users need to store rules for making selections among their customers. Mostly the do this by other means, like checkboxes and other input fields. Sometimes, however, they need to make more complex selections, and we want to solve this by letting them (the power-users at least) write SQL queries. I hope that explains why we needed this functionality :-)
Svein Bringsli
@Allan - that is a different situation. Pipelined functions are useful when the consumer can start work with first rows. In a sub-query we need the *whole* set. And the memory issues don't apply either.
APC
A: 

You should check out Expression Filter: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14288/exprn_expconcepts.htm

jonearles