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.