I have to write a script(SQL statement only, Not using PL/SQL block(s)) which generates the
1.) ALTER .. ADD CONSTRAINT script for all the tables that REFERENCES any particular table.
2.) ALTER.. ADD CONSTRAINT script for FOREIGN KEY CONSTRAINT for a particular table.

Note: The idea is to generate script for both. i.e. COMPOSITE FOREIGN KEY, as well as SINGLE FOREIGN KEY.

Eg.

SELECT 'ALTER TABLE ' || LOWER(a.table_name)
||' ADD CONSTRAINT ' || LOWER(a.constraint_name)
||' FOREIGN KEY (' || LOWER(column_name) || ') REFERENCES '
|| (SELECT LOWER(table_name) ||'('|| LOWER(column_name) ||')' FROM all_cons_columns
WHERE constraint_name = a.r_constraint_name)
||' INITIALLY DEFERRED DEFERRABLE;'
FROM all_constraints a,
all_cons_columns b
WHERE a.constraint_type ='R'
AND a.table_name = b.table_name
AND a.owner =b.owner
AND a.constraint_name =b.constraint_name
AND a.table_name ='EMP'
AND a.owner= (select user from dual)

Output:

ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) INITIALLY DEFERRED DEFERRABLE;

Problem: The above written query runs fine with SINGLE FOREIGN KEY CONSTRAINT. But this given me an error "ORA-01427: single-row subquery returns more than one row" for COMPOSITE FOREIGN KEY. HOW TO SOLVE THIS?