Here is something that I have .... feel free to modify ...
Although it is designed to be used in Forms application, it will give you
some idea.
-------------- start here ------------------------------
PROCEDURE LP_CIVILIZED_ERROR
(PI_SQLERRM IN VARCHAR2) IS
--
/*
** Please note that this procedure will handle only 40735 errors ...
** Normal Forms errors, will not be processed.
*/
--
CURSOR CUR_CONS (c_owner varchar2, c_conname varchar2) is
SELECT CC.CONSTRAINT_NAME
,CC.CONSTRAINT_TYPE
,CC.TABLE_NAME
,CC.SEARCH_CONDITION
,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS CC
WHERE CC.OWNER = C_OWNER
AND CC.CONSTRAINT_NAME = C_CONNAME;
--
CURSOR CUR_COLCONS (c_owner varchar2, c_conname varchar2) is
SELECT ACC.COLUMN_NAME
,ACC.TABLE_NAME
FROM ALL_CONS_COLUMNS ACC
WHERE ACC.OWNER = C_OWNER
AND ACC.CONSTRAINT_NAME = C_CONNAME;
--
szConName ALL_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
szConType ALL_CONSTRAINTS.CONSTRAINT_TYPE%TYPE;
szTabName ALL_CONSTRAINTS.TABLE_NAME%TYPE;
szSrchCon ALL_CONSTRAINTS.SEARCH_CONDITION%TYPE;
szRConName ALL_CONSTRAINTS.R_CONSTRAINT_NAME%TYPE;
--
szTitle varchar2(100);
szMessage varchar2(1000);
szColNames varchar2(1000);
szConText varchar2(1000);
szSchCon varchar2(65);
szSchema varchar2(32);
szCons varchar2(32);
--
nConCount pls_integer;
nDotPos pls_integer;
nStartPos pls_integer;
nEndPos pls_integer;
--
BEGIN
-- Begin here ...
nStartPos := instr(pi_sqlerrm, '(');
nEndPos := instr(pi_sqlerrm, ')');
if nStartPos = 0 and nEndPos = 0 then
-- we have nothing to process here ...
return;
end if;
-- proceed only if we have something to work with ...
szSchCon := substr(pi_sqlerrm, nStartPos+1,
nEndPos-nStartPos-1);
nDotPos := instr(szSchCon, '.');
szSchema := substr(szSchCon, 1, nDotPos-1);
szCons := substr(szSchCon, nDotPos+1);
--
-- Get constraint information ...
open cur_cons (szSchema, szCons);
fetch cur_cons
into szConName, szConType, szTabName, szSrchCon, szRConName;
close cur_cons;
--
if szConType in ('P', 'R', 'U') then
-- Primary Key Constraint
for recCols in cur_colcons (szSchema, szCons)
loop
exit when cur_colcons%notfound;
--
szColNames := szcolNames ||
rtrim(recCols.column_name) || ',';
end loop;
szColNames := upper(rtrim(szColNames, ','));
--
if szConType = 'P' then
szMessage := 'PRIMARY KEY "' || szCons || '" (' ||
szColNames || ') violated, Contact MIS.';
elsif szConType = 'R' THEN
szMessage := 'REFERENTIAL INTEGRITY "' || szCons ||
'" (' || szColNames || ')' ||
' violated, contact MIS';
elsif szConType = 'U' THEN
szMessage := 'UNIQUE KEY CONSTRAINT "' || szCons ||
'" (' || szColNames || ')' ||
' violated, contact MIS';
end if;
elsif szConType = 'C' then
szMessage := 'CHECK CONSTRAINT "' || szCons || '" (' ||
upper(szSrchCon) || ')' ||
' violated, contact MIS.';
end if;
--
-- now we should display the error ...
-- reuse nDotPos ...
nDotPos := LF_DISPLAY_ERROR ( 'Error processing table "' ||
upper(szTabName) || '"'
,szMessage);
RAISE FORM_TRIGGER_FAILURE;
--
END LP_CIVILIZED_ERROR;
-------------- end here ------------------------------
HTH
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art !
*********************************************************************1
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*********************************************************************1
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jamadagni, Rajendra
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).
