Passing table name dynamically each time

Question

In the below Query j.table_name is Parent table and it contains some dependent table

1 STORE_ORDER_JSON_DATA
2 ECOMM_SHIPDOCS_MQ_DATA

i am passing the parent table name dynamically and dependent table name has given directly.

But i want to subsite the dependent table names and its columns which is used in where condition (js.data_Load_id,js.status,js.date_loaded also need to pass dynamically) because the dependent tables will be change based on parent table each time. How can we achieve this? Now I gave the dependent tables and its columns directly in exits clause.

V_SQL := 'DELETE FROM ' || j.table_name || ' ord ' ||---------------main 1
                     ' WHERE ' || j.purge_condition_1 || ' = ''' || j.purge_indicator_1 || '''' ||  
                     ' AND trunc(' || j.purge_date || ') <= trunc(sysdate) - ' || j.threshold_days ||
                     ' AND EXISTS (SELECT 1 FROM STORE_ORDER_JSON_DATA js ' ||--
                     ' WHERE js.data_Load_id = ord.data_load_id ' ||
                     ' AND js.status = ''P'' ' ||
                     ' AND trunc(js.date_loaded) <= trunc(sysdate) - 10) ' ||
                     ' AND EXISTS (SELECT 1 FROM ECOMM_SHIPDOCS_MQ_DATA mq ' ||--
                     ' WHERE mq.data_Load_id = ord.data_load_id ' ||
                     ' AND mq.status = ''P'' ' ||
                     ' AND trunc(mq.date_loaded) <= trunc(sysdate) - 10)';

Answer 1

... because the dependent tables will be change based on parent table each time.

I hate saying that, but - dynamic SQL is evil. Do you really need it? Why? Couldn't you achieve the goal by creating e.g.

• a view (as UNION ALL of several queries, each of them using their own combination of tables), or
• a function that returns refcursor

or whatever else?

If not, then consider creating additional table which will contain pairs of master-detail tables and columns to be joined

id   master   detail   mcol1    dcol1    mcol2      dcol2
--   ------   ------   ------   ------   --------   ----------
1    dept     emp      deptno   deptno
2    emp      bonus    empno    empno    hiredate   bonus_date

and then compose dynamic SQL by querying that table, based on ID, so that you'd know which columns have to be joined. Expand it, if necessary, with another pairs of mcol (master column) and dcol (detail column). Or, if you think it is a better option, normalize it so that previous table contains only ID, master and detail table, while its detal table contains pairs of columns:

id   mcol        dcol
--   --------    ----------
1    deptno      deptno
2    empno       empno
2    hiredate    bonus_date

Someone else might have another, different, better ideas so - wait for their opinion.

Answer 2

As each parent table will have different dependant tables and potentially (or even presumably) different things to check each time, trying to build a dynamic statement that will always work will be painful and hard to maintain.

Unless you have a large number of possible parent tables, and maybe even if you do, I would consider getting rid of the dynamic statement completely and have a separate static statement for each table, something like:

if j.table_name = 'PARENT_TABLE_1' -- with real name, of course, and maybe case insensitive
  and j.purge_condition_1 = 'PARENT_COL_1' -- again, real column name
then
  DELETE FROM parent_table_1 ord
  WHERE parent_column_1 = j.purge_indicator_1
  AND trunc(j.purge_date) <= trunc(sysdate) - j.threshold_days
  AND EXISTS (
    SELECT 1 FROM STORE_ORDER_JSON_DATA js
    WHERE js.data_Load_id = ord.data_load_id
    AND js.status = 'P'
    AND trunc(js.date_loaded) <= trunc(sysdate) - 10)
 AND EXISTS (
   SELECT 1 FROM ECOMM_SHIPDOCS_MQ_DATA mq
   WHERE mq.data_Load_id = ord.data_load_id
   AND mq.status = 'P'
   AND trunc(mq.date_loaded) <= trunc(sysdate) - 10);
else if j.table_name = 'PARENT_TABLE_1'
  and j.purge_condition_1 = 'PARENT_COL_2'
then
  DELETE FROM parent_table_1 ord
  WHERE parent_column_2 = j.purge_indicator_1
  ...
-- etc.

That will be a lot of static code maybe, but because it's static it will be validated as compile time so you avoid run-time errors in the dynamic statement, and SQL injection (which you risk now from embedding values), and NLS issues (which you have from embedding dates converted to strings).

Easier to maintain in some ways, though admittedly harder in others, as a new parent table requires the code to be modified to handle it, not config as Littlefoot's approach allows - if all the structures are similar enough for that to work.