-
how to find the table name on which integrity constraint not found
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
-
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.
-
Just try out this
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;
/
anu
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|