What if you could get your queries without PL/SQL - just plain SQL?
Lets say that your two tables look like below:
CREATE TABLE
A_TBL_1 (ID, TXT, SOME_COL, COL_1_T1, DATE_T1) AS
(
SELECT 1, 'TEXT for ID 1', 'Something else 1 in tbl_1', 'X', To_Date('20221231', 'yyyymmdd') From Dual Union All
SELECT 2, 'TEXT for ID 2', 'Something else 2 in tbl_1', 'Y', To_Date('20221231', 'yyyymmdd') From Dual Union All
SELECT 3, 'TEXT for ID 3', 'Something else 3 in tbl_1', 'Z', To_Date('20221231', 'yyyymmdd') From Dual
);
CREATE TABLE
A_TBL_2 (ID, TXT, SOME_COL, COL_1_T2) AS
(
SELECT 11, 'TEXT for ID 11', 'Something else 11 in tbl_2', 'X' From Dual Union All
SELECT 12, 'TEXT for ID 12', 'Something else 12 in tbl_2', 'Y' From Dual Union All
SELECT 13, 'TEXT for ID 13', 'Something else 13 in tbl_2', 'X' From Dual
);
... and that your rules are set up like here
CREATE TABLE
A_RULE_TBL (RULE_ID, PAR_ID, PAR_EXPL, PAR_VAL) AS
(
SELECT 1, 1, 'A_TBL_1', 'a' FROM DUAL UNION ALL
SELECT 1, 2, 'A_TBL2', 'b' FROM DUAL UNION ALL
SELECT 1, 3, 'COL_1_T1', 'X' FROM DUAL UNION ALL
SELECT 1, 4, 'COL_1_T2', 'X' FROM DUAL UNION ALL
SELECT 1, 5, 'DATE_T1', '20221231' FROM DUAL UNION ALL
SELECT 2, 1, 'A_TBL_1', 'a' FROM DUAL UNION ALL
SELECT 2, 2, 'A_TBL_2', 'b' FROM DUAL UNION ALL
SELECT 2, 3, 'COL_1_T1', 'Y' FROM DUAL UNION ALL
SELECT 2, 4, 'COL_1_T2', 'Y' FROM DUAL UNION ALL
SELECT 2, 5, 'DATE_T1', '20221231' FROM DUAL
);
If we Pivot And Unpivot the rules using a CTE (named params)_
WITH
params AS
( Select *
From A_RULE_TBL
PIVOT (
Max(CASE WHEN PAR_ID = 1 THEN PAR_EXPL END) "SRC_TBL",
Max(CASE WHEN PAR_ID = 2 THEN PAR_EXPL END) "LKP_TBL",
Max(CASE WHEN PAR_ID = 3 THEN PAR_EXPL END) "SRC_COL",
Max(CASE WHEN PAR_ID = 4 THEN PAR_EXPL END) "LKP_COL",
Max(CASE WHEN PAR_ID = 5 THEN PAR_EXPL END) "DATE",
--
Max(CASE WHEN PAR_ID = 1 THEN PAR_VAL END) "SRC_TBL_VAL",
Max(CASE WHEN PAR_ID = 2 THEN PAR_VAL END) "LKP_TBL_VAL",
Max(CASE WHEN PAR_ID = 3 THEN PAR_VAL END) "SRC_COL_VAL",
Max(CASE WHEN PAR_ID = 4 THEN PAR_VAL END) "LKP_COL_VAL",
Max(CASE WHEN PAR_ID = 5 THEN PAR_VAL END) "DATE_VAL"
FOR RULE_ID IN(1 "ID1", 2 "ID2") )
UNPIVOT( (SRC_TBL, SRC_COL, LKP_TBL, LKP_COL, A_DATE, SRC_TBL_VAL, SRC_COL_VAL, LKP_TBL_VAL, LKP_COL_VAL, DATE_VAL)
FOR RULE_ID
IN (
(ID1_SRC_TBL, ID1_SRC_COL, ID1_LKP_TBL, ID1_LKP_COL, ID1_DATE, ID1_SRC_TBL_VAL, ID1_SRC_COL_VAL, ID1_LKP_TBL_VAL, ID1_LKP_COL_VAL, ID1_DATE_VAL ) as 1,
(ID2_SRC_TBL, ID2_SRC_COL, ID2_LKP_TBL, ID2_LKP_COL, ID2_DATE, ID2_SRC_TBL_VAL, ID2_SRC_COL_VAL, ID2_LKP_TBL_VAL, ID2_LKP_COL_VAL, ID2_DATE_VAL ) as 2 )
)
ORDER BY RULE_ID
)
--
-- R e s u l t
-- RULE_ID SRC_TBL SRC_COL LKP_TBL LKP_COL A_DATE SRC_TBL_VAL SRC_COL_VAL LKP_TBL_VAL LKP_COL_VAL DATE_VAL
-- ---------- -------- -------- -------- -------- -------- ----------- ----------- ----------- ----------- --------
-- 1 A_TBL_1 COL_1_T1 A_TBL_2 COL_1_T2 DATE_T1 a X b X 20221231
-- 2 A_TBL_1 COL_1_T1 A_TBL_2 COL_1_T2 DATE_T1 a Y b Y 20221231
Resulting dataset has everything you need to construct different sql commands. Here for RULE_ID = 1 there will be SQL for left joining tables and selecting rows that doesn't match. For RULE_ID = 2 rows that does match.
SELECT
'Select ' || SRC_TBL_VAL || '.' || SRC_COL || ', Count(*) "CNT" ' || Chr(10) ||
'From ' || SRC_TBL || ' ' || SRC_TBL_VAL || ' ' || Chr(10) ||
'Left Join ' || LKP_TBL || ' ' || LKP_TBL_VAL || ' ON(' || LKP_TBL_VAL || '.' || LKP_COL || ' = ' || SRC_TBL_VAL || '.' || SRC_COL || ')' || Chr(10) ||
'Where ' || LKP_TBL_VAL || '.' || LKP_COL || ' Is ' || CASE RULE_ID WHEN 2 THEN 'Not' ELSE '' END || ' Null ' || Chr(10) ||
'Group By ' || SRC_TBL_VAL || '.' || SRC_COL || ' ' || Chr(10) ||
'Order By Count(*) DESC' "SQL_COMMANDS"
FROM params
ORDER BY RULE_ID
/* R e s u l t :
SQL_COMMANDS
--------------------------------------------------
Select a.COL_1_T1, Count(*) "CNT"
From A_TBL_1 a
Left Join A_TBL_2 b ON(b.COL_1_T2 = a.COL_1_T1)
Where b.COL_1_T2 Is Null
Group By a.COL_1_T1
Order By Count(*) DESC
Select a.COL_1_T1, Count(*) "CNT"
From A_TBL_1 a
Left Join A_TBL_2 b ON(b.COL_1_T2 = a.COL_1_T1)
Where b.COL_1_T2 Is Not Null
Group By a.COL_1_T1
Order By Count(*) DESC
*/
The first query, if run against above sample data, results as:
-- COL_1_T1 CNT
-- -------- ----------
-- Z 1
... while second results as:
-- COL_1_T1 CNT
-- -------- ----------
-- X 2
-- Y 1
You can select some or all of other columns and you can construct the sql commands with different joins and where conditions, groupings, orderings etc...
EXECUTE IMMEDIATEis generally used for DDL commands which have no return data, or for queries that include dynamic structures that specifically return data into bind variables for further processing. What is your expected output or result from running this procedure? Also this structure (using concatenation instead of bind variables) may be vulnerable to SQL injection.EXECUTE IMMEDIATE v_query;codedbms_output.put_linr(v_query);. And you will know what you've done wrong. Make sure your dbms out is ON