constraint names

From: Bill Becker <beckerb_at_mfldclin.edu>
Date: Thu, 30 Aug 2001 11:26:52 -0700
Message-ID: <F001.0037DAF4.20010830105901@fatcity.com>

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: beckerb_at_mfldclin.edu

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: ListGuru_at_fatcity.com (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).

Received on Thu Aug 30 2001 - 13:26:52 CDT