Let's say that I have a "Rules" table that has a column that contains oracle queries in a varchar2 column:

If this were never going to change, I'd just make a view with a union of these queries. Our requirement is that we be able to add to or to modify these rules on-the-fly at the whims of leadership.

So, what I need is either:

• a very smart view (I think impossible) that executes and unions all of these stored query strings

or

• a table function that returns the results of the union of these stored query strings. (I think this is the more likely solution)

It will only ever be those two columns: The hardcoded name of the table and the ID of the record.

Can someone help get me started on this?

Thanks

CodePudding user response：

You can use a PIPELINED function.

First create the types:

CREATE TYPE request_data IS OBJECT (tablename VARCHAR2(30), request_id NUMBER);

CREATE TYPE request_list IS TABLE OF request_data;

Then the function:

CREATE FUNCTION get_requests RETURN request_list PIPELINED
IS
BEGIN
  FOR r IN (SELECT "QUERY" FROM table_name ORDER BY "ROW")
  LOOP
    DECLARE
      c_cursor     SYS_REFCURSOR;
      v_tablename  VARCHAR2(30);
      v_request_id NUMBER;
    BEGIN
      OPEN c_cursor FOR r."QUERY";
      
      LOOP
        FETCH c_cursor INTO v_tablename, v_request_id;
        EXIT WHEN c_cursor%NOTFOUND;
        PIPE ROW (request_data(v_tablename, v_request_id));
      END LOOP;
      
      CLOSE c_cursor;
    EXCEPTION
      WHEN NO_DATA_NEEDED THEN
        CLOSE c_cursor;
        RETURN;
    END;
  END LOOP;
END;
/

Then, if you have the sample data:

CREATE TABLE table_name ("ROW", "QUERY") AS
SELECT 1, q'[select 'Hardcoded_Tablename_1' tablename, request_id from table_1 where status >= 4 and resolve_date < SYSDATE]' FROM DUAL UNION ALL
SELECT 2, q'[select 'Table_2' tablename, t2.request_id from table_2 t2 join table_1 t1 on t1.request_id = t2.parent_id where t1.status >= 4 and t1.resolve_date < SYSDATE]' FROM DUAL

CREATE TABLE table_1 (request_id, status, resolve_date) AS
SELECT 42, 4, SYSDATE - 1 FROM DUAL;

CREATE TABLE table_2 (request_id, parent_id) AS
SELECT 57, 42 FROM DUAL;

Then you can use the function in a table collection expression:

SELECT *
FROM   TABLE(get_requests());

Which outputs:

TABLENAME	REQUEST_ID
Hardcoded_Tablename_1	42
Table_2	57

db<>fiddle here

CodePudding user response：

One option might be a function that returns refcursor.

SQL> select * from rules;

      CROW QUERY
---------- ----------------------------------------------------------------------------------------------------
         1 select 'EMP' tablename, empno from emp where hiredate = (select max(hiredate) from emp)
         2 select 'DEPT' tablename, d.deptno from emp e join dept d on d.deptno = e.deptno where e.hiredate = (
           select min(hiredate) from emp)

Function creates union of all queries from the rules table and uses it as a source for the refcursor:

SQL> create or replace function f_test return sys_refcursor
  2  is
  3    l_rc   sys_refcursor;
  4    l_str  clob;
  5  begin
  6    for cur_r in (select query from rules order by crow) loop
  7      l_str := l_str || cur_r.query ||' union all ';
  8    end loop;
  9    l_str := rtrim(l_str, ' union all ');
 10
 11    open l_rc for l_str;
 12    return l_rc;
 13  end;
 14  /

Function created.

Testing:

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

TABL      EMPNO
---- ----------
EMP        7876
DEPT         20


SQL>