0

How can I provide a user defined error message in PL/SQL when a check constraint is violated?

      CREATE TABLE "AP"."SOURCE" 
      (
        ,"RATING" CHAR(30 BYTE)
        ,"SUBMISSION_STATUS" CHAR(12 BYTE)
        ,"UOANAME" CHAR(32 BYTE)
        ,"W_INSERT_DT" TIMESTAMP (6)
        ,"W_UPDATE_DT" TIMESTAMP (6)
        ,"SCIVAL_CIT_CATEGORY" NUMBER(5,0)
        ,"TOTAL_AUTHORS" BINARY_DOUBLE
        ,"REF2014" CHAR(3 BYTE)
      CONSTRAINT "Submission_Rating_not_valid" 
      CHECK ( Submission_status ='To be scored' 
            and Rating is null  
            or Submission_status ='NO' 
            and Rating is null
            or Submission_status = 'Potential'
            and Rating is not null 
            and Rating != 'Not REF Eligible'              
            or  Submission_status ='Yes'
            and Rating is not null
            and Rating != 'Not REF Eligible'
            or  Submission_status ='No'
            and Rating is not null 
            and Rating != 'Not REF Eligible'
            or  Submission_status ='No'
            and Rating = 'Not REF Eligible'*) ENABLE
       )
Andrew
  • 26,629
  • 5
  • 63
  • 86
Praveen
  • 5
  • 3
  • Be careful when mixing AND and OR... Perhaps some parentheses are needed. – jarlh Oct 03 '18 at 10:05
  • After formatting it to be easier to read you can see the AND / OR combinations there do need parens really. – Andrew Oct 03 '18 at 10:13
  • Apart from the above mentioned aspects, please have a look at Oracle reserved names and keywords. And if you are specifying double quotes for table name and columns, you have to use double quotes when referencing in future. – Jacob Oct 03 '18 at 10:14
  • Possible duplicate of [Is there way to give user-friendly error message on constraint violation](https://stackoverflow.com/questions/6068792/is-there-way-to-give-user-friendly-error-message-on-constraint-violation) – Andrew Oct 03 '18 at 10:16
  • Thanks everyone for the useful suggestion and tips. i have changed it. – Praveen Oct 03 '18 at 11:53

2 Answers2

2

In PL/SQL there is a EXCEPTION keyword to handle the excpetions.

EXCEPTION

Then you can set inside the PL/SQL block what to do if the execution of PL/SQL code raise specific exception.

WHEN <name of the exception> THEN <statements to handle it>;

When you need a fallback for all nonmentioned exceptions, use OTHERS:

WHEN OTHERS THEN <statements to handle it>;

To raise a custom exception for the user with custom errortext, use RAISE_APPLICATION_ERROR.

Example:

CREATE OR REPLACE PROCEDURE "APEXDEV01"."ADD_OMT_SOURCE" (
  rating VARCHAR2, 
  submission_status VARCHAR2, 
  uoaname VARCHAR2, 
  scival_cit_category NUMBER,
  total_authors BINARY_DOUBLE,
  selfselected VARCHAR2,
  ref2014 VARCHAR2)
AS
BEGIN
  INSERT INTO "APEXDEV01"."OMT_SOURCE" VALUES (
    rating, submission_status, uoaname, SYSDATE, SYSDATE, 
    scival_cit_category, total_authors, selfselected, ref2014);
  EXCEPTION
    WHEN "Submission_Rating_not_valid" THEN
      RAISE_APPLICATION_ERROR(-20081, "Submission rating not valid");
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20086,sqlerrm);
END;
/

It is also worth mentioning that the condition in your DDL statement combines ANDs and ORs and those should be managed by some parentheses to aviod mess and confusion in its logic.

Miroslav Duník
  • 580
  • 2
  • 9
  • HI Miroslav, I tried your above script and i am getting an error- Error(66,2): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache accessible The symbol "is" was substituted for "BEGIN" to continue. – Praveen Oct 03 '18 at 11:52
  • Praveen, you are right, my bad. I didn't test the code in actual DB. I missed the "AS" statement. The important here are the EXCEPTION parts. – Miroslav Duník Oct 03 '18 at 12:27
0

you need one parenthesis and use separate parenthesis for and or condition 

    CREATE TABLE "AP"."SOURCE"     
 (

"RATING" CHAR(30 BYTE), 

 "SUBMISSION_STATUS" CHAR(12 BYTE), 

"UOANAME" CHAR(32 BYTE), 

 "W_INSERT_DT" TIMESTAMP (6), 

"W_UPDATE_DT" TIMESTAMP (6), 

"SCIVAL_CIT_CATEGORY" NUMBER(5,0), 

"TOTAL_AUTHORS" BINARY_DOUBLE, 

"REF2014" CHAR(3 BYTE), 

CONSTRAINT "Submission_Rating_not_valid" 

CHECK ( (Submission_status ='To be scored' and Rating is null)  or 

(Submission_status ='NO' and Rating is null )
     or ( Submission_status = 'Potential' and
       Rating is not null and Rating != 'Not REF Eligible')              

or ( Submission_status ='Yes' and Rating  is not null 
     and Rating != 'Not REF Eligible' )
  or ( Submission_status ='No'
  and Rating  is  not null and Rating != 'Not REF Eligible')
 or  (Submission_status ='No' and Rating = 'Not REF Eligible'
      ) ENABLE

   )
 )
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63