Hello,
Oracle 8.1.6 on Solaris 2.7
I am trying to write an exception handler that extracts the
constraint name from SQLERRM whenever a constraint violation
occurs. I am trying to do this by using the substr and instr
functions to extract just the constraint name; the reason is
that we want to try to handle certain constraint violations
programmatically, without human intervention; the first step is to
id the specific constraint violation.
Here is what I have so far:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE); -- first 2 lines test only
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF (SQLCODE = -1) -- UK violation
OR (SQLCODE = -2290) -- CK violation
OR (SQLCODE = -2291) THEN -- FK violation
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,(INSTR(SQLERRM,'.')+1),
(INSTR(SQLERRM,')')-INSTR(SQLERRM,'.'))-1 ));
ELSIF (SQLCODE = -1400) -- null insert violation
OR (SQLCODE = -1407) THEN -- null update violation
DBMS_OUTPUT.PUT_LINE('Table: '||SUBSTR(SQLERRM,(INSTR(SQLERRM,'"',1,3)+1),
(INSTR(SQLERRM,'"',1,4)-INSTR(SQLERRM,'"',1,3))-1 )
);
DBMS_OUTPUT.PUT_LINE('Field: '||SUBSTR(SQLERRM,(INSTR(SQLERRM,'"',1,5)+1),
(INSTR(SQLERRM,'"',1,6)-INSTR(SQLERRM,'"',1,5))-1 )
);
ELSE
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,(INSTR(SQLERRM,'.')+1),
(INSTR(SQLERRM,')')-INSTR(SQLERRM,'.'))-1 ));
END IF;
Apologies for the formatting, but as you can see, some of the
expressions are long and ugly, and it depends upon the presence
of periods, parentheses and double quotes in SQLERRM. Moreover,
for not null violations, SQLERRM does not contain the constraint name,
so the code above isolates the table and field names.
This actually works, but my question is:
does anyone know a better way (not parsing SQLERRM) to programmatically determine
which constraint name has been violated using a pl/sql exception handler?
Thanks to all responders.
P.S. Count me in for $20 US for list support.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Becker
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
