Forum Stats

  • 3,872,018 Users
  • 2,266,364 Discussions
  • 7,911,026 Comments

Discussions

New Built-In PL/SQL Exception

FatMartinR
FatMartinR Member Posts: 190 Blue Ribbon
edited Aug 17, 2016 6:22AM in Database Ideas - Ideas

Having recently done some checking for violated check constraints, I realised there is no built-in method to help with this.

For example,

CREATE PACKAGE BODY QPR AS

   FUNCTION CONSTRAINT_NAME RETURN VARCHAR2 IS

   Constraint_Start CONSTANT INTEGER := INSTR(SQLERRM, '.') + 1;

   Constraint_End   CONSTANT INTEGER := INSTR(SQLERRM, ')', Constraint_Start + 1);

   --

   BEGIN

     RETURN(SUBSTR(SQLERRM, Constraint_Start, Constraint_End - Constraint_Start));

   END CONSTRAINT_NAME;

   -------------------------------------------------------------------------------

   PROCEDURE XYZ(p_REPORT_DATE DATE) IS

   BEGIN

     DECLARE  -- INSERT Header

       CHECK_CONSTRAINT_VIOLATED EXCEPTION;

       PRAGMA EXCEPTION_INIT (CHECK_CONSTRAINT_VIOLATED, -2290);

       --

     BEGIN

       INSERT INTO ABC(REPORT_DATE) VALUES (p_REPORT_DATE);

       --

     EXCEPTION

       WHEN DUP_VAL_ON_INDEX THEN

         RAISE_APPLICATION_ERROR(-20001, 'Figures for '|| TO_CHAR(p_REPORT_DATE, 'DD/MM/YYYY') ||' have already been generated.');

         --

       WHEN CHECK_CONSTRAINT_VIOLATED THEN

         IF CONSTRAINT_NAME = 'BAD_HEADER_DATE' THEN

           RAISE_APPLICATION_ERROR(-20000, 'Time element for date parameter '''|| TO_CHAR(p_REPORT_DATE, 'DD/MM/YYYY HH:MI:SS') ||''' should not be included.');

         ELSE

           RAISE;

         END IF;

     END;

   END XYZ;

END QPR;

As you can see, I found it necessary to first define an exception and then SUBSTR SQLERRM in order to get the name of the actual constraint violated.

Therefore, I'd like to propose the addition of a new built-in CHECK_CONSTRAINT_VIOLATED exception (to save me having to define it every time), along with an associated psuedo-column CONSTRAINT_NAME to pick up the actual constraint violated.

Should be simple to do.

FatMartinRLothar FlatzSven W.ctriebMike KutzApexBinegaverillMarwimulohmannJeffrey KempPeter HraškoBEDETony AndrewsDanilo PiazzalungaGregVWilliam Robertsonfac586mtefftsdstuberUser_G3UV3
21 votes

Active · Last Updated

Comments