SQL & PL/SQL

I am trying to find out foreign key constraints on a table.
For e.g main table is table "A" and the child tables are table "B", table "C" and table "D". Table B, C and D have referential integrity constraint on table "A" and table "A" has a primary key.

Now i want to delete some records in table "A" and insert it back again to correct the chanined rows. I have to disable the referential intergrity constraints on table "B", "C" and "D" to achieve that.

So i am using all_constraints table as in the query below to identify the referential integrity constriaints and disable them

CURSOR c_constraint(c_schemaname IN VARCHAR2, c_tabname IN VARCHAR2) IS
SELECT owner, table_name, constraint_name, r_constraint_name
FROM all_constraints
WHERE owner = c_schemaname
AND constraint_type = 'R'
AND r_constraint_name IN (
SELECT constraint_name
FROM all_constraints
WHERE owner = c_schemaname
AND table_name = c_tabname
AND constraint_type = 'P')

C_tabname is the table "A". All the tables "A","B","C","D" are in the same schema.


When i execute this as a anonymous PL/SQL block this query returns me the correct number of rows (3 rows - constraints on table "B","C" and "D").

But when i execute this as a procedure i am getting only 1 row.

I am not sure why this is happening.

User in which i am executing this procedure has "Dicitionary view" access

I am using oracle 9.2.0.6

can some one help me on this