how to find the table name on which integrity constraint not found

[zulu99]

Hi All

How to acheive this

I have a lot of tables with lot of primary key - foreign key
relationship.
In plsql
when any inserts happen in the child table & the corresponding row is not present in the parent table, we get an exception

ORA-02291: integrity constraint (user1.ppk) violated - parent key not found

On this exception , in the exception block i want to trap teh name of the parent table on which the primary key for the particular child table was not there

Is it possible to retrieve the parent table in this way. I am looking for a generic plsql code block which can help to acheive this


Regards

[rigatoni]

Here's an example. You may want to clean up the string parsing a bit. You shoul put the code in a stored procedure and then call it in any exception handler that requires it.

scott@AMSAA>
1 begin
2 insert into test2 values(-999, 'test');
3 end;
4*
scott@AMSAA> /
begin
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.TEST2_FK) violated - parent key not found
ORA-06512: at line 2



declare

l_err_tbl user_constraints.table_name%type;
l_cons user_constraints.constraint_name%type;
l_start number;
l_stop number;

-- user defined exception to handle error we are hitting
e_ConsViolation exception;
pragma exception_init(e_ConsViolation, -02291);

begin

insert into test2 values(-999, 'test');
exception

when e_ConsViolation then

l_start := instr( sqlerrm, '(' ) + 1;
l_stop := instr( sqlerrm, ')' );
l_cons := substr(sqlerrm, l_start, l_stop-l_start);
l_start := instr( l_cons, '.' ) + 1;
l_cons := substr(l_cons, l_start, l_stop-l_start);

select table_name
into l_err_tbl
from user_constraints
where constraint_name= (select r_constraint_name
from user_constraints
where constraint_name = l_cons);

dbms_output.put_line('Parent table is ' || l_err_tbl);

when others then
dbms_output.put_line('other section');

end;
/

Parent table is TEST1

PL/SQL procedure successfully completed.

[anupamasuresh]

CREATE OR REPLACE
PROCEDURE show_fkeys(
p_table_name IN user_constraints.table_name%TYPE)
IS
-- constants
-- identify fkeys on pkey for given table
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
-- record variables
rec_id_fkeys id_fkeys%ROWTYPE;
-- variables
l_table_name user_constraints.table_name%TYPE;
l_pkey_name user_constraints.constraint_name%TYPE;
l_status NUMERIC;
BEGIN
l_table_name := UPPER(p_table_name);
-- a primary key for the given table must exist
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
'show_fkeys: foreign key constraints on table [' || l_table_name || ']');
DBMS_OUTPUT.put_line(
' whose primary key is [' || l_pkey_name || ']');
OPEN id_fkeys(l_table_name);
LOOP -- display foreign keys
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name || ']',40) ||
RPAD('FK Name: [' || rec_id_fkeys.fkey || ']',42) ||
'Status: [' || rec_id_fkeys.status || ']');
END LOOP; -- display foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found
DBMS_OUTPUT.put_line(
'show_fkeys: No foreign keys found against table ' || l_table_name);

END IF; -- no rows found
CLOSE id_fkeys;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- primary key lookup failed
DBMS_OUTPUT.put_line(
'show_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
l_status := SQLCODE;
DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END show_fkeys;
/