declare
M_TABLE_NAME VARCHAR2(50) := 'MSV2C02_WRNTYCLAIM';
CURSOR C1(C_TAB_NAME VARCHAR2) IS
SELECT table_name,r_pk,listagg(column_name,',')within group(order by column_name ASC) CHILD_COLS
FROM
(
SELECT a.table_name, a.column_name, a.constraint_name, c.owner, c.r_owner referenced_pk , c_pk.table_name r_table_name, c_pk.constraint_name r_pk
FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c_pk.table_name = C_TAB_NAME
AND C.CONSTRAINT_TYPE = 'R')
group by table_name,r_pk;
CURSOR C2(C_CONSTRAINT VARCHAR2)IS
SELECT listagg(COLUMN_NAME,',')within group (order by table_name asc)
FROM all_cons_columns
WHERE constraint_name = C_CONSTRAINT;
CURSOR C3 IS
select A.TABLE_NAME ,listagg(B.column_name,',')within group(order by B.column_name ASC) uniq_cols
from user_constraints a,all_cons_columns b
where a.constraint_name = b.CONSTRAINT_NAME
AND A.TABLE_NAME = M_TABLE_NAME
AND A.CONSTRAINT_TYPE ='U'
group by A.TABLE_NAME;
CURSOR C4 IS
Select Trigger_Name,Trigger_Type,Triggering_Event
From User_Triggers
Where Table_Name = M_TABLE_NAME;
CURSOR C5 IS
SELECT UNIQUE CONSTRAINT_NAME FORGN_KEY FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
AND TABLE_NAME = M_TABLE_NAME;
CURSOR C6A(C_FORGN_KEY VARCHAR2)IS
SELECT A.TABLE_NAME ,listagg(B.COLUMN_NAME,',')within group(order by column_name ASC) CHILD_COLS
FROM USER_CONSTRAINTS A,all_cons_columns B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_NAME = C_FORGN_KEY
GROUP BY A.TABLE_NAME;
CURSOR C6(C_FORGN_KEY VARCHAR2) IS
SELECT A.TABLE_NAME ,listagg(B.COLUMN_NAME,',')within group(order by column_name ASC) CHILD_COLS
FROM USER_CONSTRAINTS A,all_cons_columns B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_NAME = (SELECT UNIQUE c_pk.constraint_name r_pk
FROM all_cons_columns a JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN all_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
WHERE a.constraint_name = C_FORGN_KEY)
GROUP BY A.TABLE_NAME;

M_Parent_Col varchar2(200);
m_cnt number:= 1;
M_CHLD_TABLE VARCHAR2(100);
M_CHLD_COLS VARCHAR2(500);
M_PRNT_TABLE VARCHAR2(100);
M_PRNT_COLS VARCHAR2(500);
begin
DBMS_OUTPUT.PUT_LINE('*******Given Table : '||M_TABLE_NAME||' Parents?????????????*******');
FOR A IN C5 LOOP
If C6%ISOPEN then
CLOSE C6;
END IF;
OPEN C6(A.FORGN_KEY);
FETCH C6 INTO M_PRNT_TABLE,M_PRNT_COLS;
CLOSE C6;
If C6A%ISOPEN then
CLOSE C6A;
END IF;
OPEN C6A(A.FORGN_KEY);
FETCH C6A INTO M_CHLD_TABLE,M_CHLD_COLS;
CLOSE C6A;

DBMS_OUTPUT.PUT_LINE(' Given Table :'||M_CHLD_TABLE||'('||M_CHLD_COLS||') is a Child of *****<<<'||M_PRNT_TABLE||'('||M_PRNT_COLS||')>>>****');
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(9));

DBMS_OUTPUT.PUT_LINE('*******Given Table : '||M_TABLE_NAME||' Childs?????????????*******');
FOR I IN C1(M_TABLE_NAME) LOOP
if C2%ISOPEN then
CLOSE C2;
END IF;

OPEN C2(I.r_pk);
FETCH C2 INTO M_Parent_Col;
CLOSE C2;
IF m_cnt = 1 then
DBMS_OUTPUT.PUT_LINE('Parent Table *****<<<'||M_TABLE_NAME||'('||M_Parent_Col||')>>>****');
end if;
DBMS_OUTPUT.PUT_LINE(CHR(9)||'Child Table('||m_cnt||') :='||I.table_name||'('||I.CHILD_COLS||')');
m_cnt := m_cnt + 1;
END LOOP;

for j in c3 loop
DBMS_OUTPUT.PUT_LINE('**************************(Unique Constraint of Given Table)**********************************');
DBMS_OUTPUT.PUT_LINE('Given Table Unique Contraint Columns *****<<<'||M_TABLE_NAME||'('||j.uniq_cols||')>>>****');
end loop;
m_cnt := 1;
for j in c4 loop
IF m_cnt = 1 then
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('**************************(Triggers Activated for Given Table)**********************************');
end if;
DBMS_OUTPUT.PUT_LINE('Triggers Activated for Given Table('||M_TABLE_NAME||')['||m_cnt||'] Trigger name :{'||j.TRIGGER_NAME||'}, Triggering type :{'||J.TRIGGER_TYPE||'} and Triggering Event :{'||J.TRIGGERING_EVENT||'}***');
m_cnt := m_cnt +1;
end loop;
end;

DBMS_OUTPUT.PUT_LINE(m_var.count||'-'||m_var.first||'-'||m_var.last);
for i in m_var.first.. m_var.last LOOP
BEGIN
--UTL_FILE.GET_LINE(tab_col_files,m_sql);
--- DBMS_OUTPUT.PUT_LINE(m_var(I)||''||M_VALUE );
EXECUTE IMMEDIATE m_var(I)||''||M_VALUE INTO M_CNT;
--- DBMS_OUTPUT.PUT_LINE(m_sql);
IF NVL(M_CNT,'0') != 0 THEN
DBMS_OUTPUT.PUT_LINE(REPLACE(m_var(I)||''||M_VALUE,'count(*)','*'));
END IF;
EXCEPTION
WHEN OTHERS
THEN
-- DBMS_OUTPUT.PUT_LINE(m_sql);
NULL;
END;
END LOOP;
---UTL_FILE.FCLOSE(tab_col_files);
dbms_output.put_line('END'||'-'||TO_CHAR(SYSDATE,'HH:MI'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm||'-'||dbms_utility.format_error_backtrace());

end;

DECLARE
M_DATA_TYPE VARCHAR2(24) := 'VARCHAR2'; ------- DATA_TYPE MAY BE VARCHAR2,NUMBER
M_VALUE VARCHAR2(100) :='''FLXEW1''';
m_table varchar2(100);
m_sql varchar2(300);
tab_col_files utl_file.file_type;
M_CNT NUMBER;
type abc is table of varchar2(400);
m_var abc;
m_count number;
begin
m_var :=abc();
dbms_output.put_line(TO_CHAR(SYSDATE,'HH:MI'));
IF M_DATA_TYPE = 'VARCHAR2' THEN