SQL & PL/SQL

Hi,

I have the following problem.

I am attempting to extract the referential constraints from my schema using DBMS_METADATA.GET_DEPENDENT_DDL and inserting into a table named my_ddl.

DBMS_METADATA.GET_DEPENDENT_DDL does a fine job of doing this but here is the rub for me ... I only want to extract one record into the table my_ddl because almost in every case each record that the DBMS_METADATA.GET_DEPENDENT_DDL inserts, results in identical multiple records. This means that when I want to print this out to a text file using UTL_FILE I end up with multiple REF CONSTRAINTS for each object.

I have attempted to cull the number of records using the following with a subquery. But get the resulting error, which I sort of understand, but is there any way to narrow the results of the tables down so that it only enters one record per table for the referential constraints.

Error starting at line 1 in command:
INSERT INTO my_ddl
(owner, object_name , ddl)
SELECT a.owner
, (SELECT DISTINCT(c.table_name)
FROM dba_constraints c
WHERE c.owner = USER
AND c.table_name LIKE 'FB%') b
, DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', a.table_name, a.owner) ddl
FROM dba_constraints a
WHERE a.owner = USER
AND a.constraint_type = 'R'
AND (a.constraint_name LIKE 'FB%'OR a.constraint_name LIKE 'SC%')
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:


Of course if I new which dba_table that the code for the constraints was in I could use that with some sort of relationship, but have not been able to find it, I cant see the actual code in the dba_constraints table.

Cheers
Ben