wasp256 wasp256 - 6 months ago 25
SQL Question

Oracle sql execute sql from varchar field

I have a column in a table were a sql-where clause is stored:

Table_A
ID | entry
--------------
1 | "(select some_id FROM table_one)" -- This is stored a VARCHAR(4000)
2 | "(select some_id FROM table_one WHERE column_one like 'test')"


Is it possible to use this where statement in the entry column as a where in a select?

Something like:

SELECT *
FROM table_B, table_A
WHERE table_B.id = table_A.id
AND table_B.value IN --and here should be the entry column from table_A

Answer

You can't dynamicly add SQL within SQL.

Alternatively you can use encapsulate the query logic in a function and use Dynamic PL/SQL.

For this you would need to create a function (my_function in the SQL below) that returns a collection of string and accepts a SQL statement as a parameter and write your query this way

 SELECT *
   FROM table_B, table_A 
   WHERE table_B.id = table_A.id
     AND table_B.value IN (select column_value from Table(MY_FUNCTION(Table_A.SQL_Statement))

Performance is not to be ignored with this approach. I suggest you to evaluate the consequence of context switching before going with this solution

Below is a Test script with a very simple implementation You'll have to analyze if SQL Injection is a possibility and make sure that no malicious SQL is passed as a parameter to the function

CREATE TYPE varchar_tab_t AS TABLE OF VARCHAR2(30);
/


CREATE OR REPLACE function MY_FUNCTION (sqlstring in varchar2) return varchar_tab_t IS
 v_values_tab varchar_tab_t;
BEGIN

  EXECUTE IMMEDIATE sqlstring bulk collect into v_values_tab;
  return v_values_tab;  
END MY_FUNCTION;
/


with table_a (id, SQL_STATEMENT) as 
  (select 1, 'Select 1 from dual union select 2 from dual union select 3 from dual' from dual)
, table_b (id, value) as 
  (            select 1, 1 from dual 
    union  all select 1, 2 from dual 
    union  all select 1, 5 from dual )  
 SELECT *
   FROM table_B, table_A 
   WHERE table_B.id = table_A.id
     AND table_B.value IN (select column_value from Table(MY_FUNCTION(Table_A.SQL_Statement)))

Result

1   1   1   Select 1 from dual union select 2 from dual union select 3 from dual
1   2   1   Select 1 from dual union select 2 from dual union select 3 from dual
Comments