PL/SQL : My procedure works fine but not my exceptions?

Question

I've some issues with the package I'm working on. I'm using sqldeveloper and I write in PL/SQL.

The procedure "Ajouter" is supposed to add a swimmer, you have to specify the name, the surname, etc. When I try it and give the right informations, it works well.

But when I try to test my exceptions, it doesn't work. None of my exceptions work. For example, if I try to write "Z" in sexe, it doesn't tell me "Sex has to be F or M". It only tells me "Procédure PL/SQL terminée.", something like "PL / SQL procedure completed.".

Where is the problem ? How can I fix this and make my exceptions work ?

Here is the "Ajouter" (Add) code :

PROCEDURE Ajouter(Nageur Nageurs%ROWTYPE) AS
    ExcNull EXCEPTION;
    PRAGMA EXCEPTION_INIT(ExcNull, -1400);
    ExcPK EXCEPTION;
    PRAGMA EXCEPTION_INIT(ExcPK, -00001);
    ExcFK EXCEPTION;
    PRAGMA EXCEPTION_INIT(ExcFK, -2291);
    ExcCheck EXCEPTION;
    PRAGMA EXCEPTION_INIT(ExcCheck, -2290);
BEGIN
    INSERT INTO Nageurs values (Nageur.NRLIGUE, Nageur.NOM, Nageur.PRENOM,
    Nageur.ANNEENAISS, Nageur.SEXE, Nageur.CATEGORIE, Nageur.CLUB, Nageur.ADRESSE,
    Nageur.CODEPOSTAL, Nageur.LOCALITE, Nageur.NRTELEPHONE, Nageur.EMAIL, Nageur.GSM, Nageur.COTISATION);

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Nageur ajouté');
EXCEPTION
    WHEN ExcNull THEN
    IF(INSTR(SQLERRM, 'EveNomNotNull') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le nom ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EvePrenomNotNull') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le prenom ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EveNaissanceNotNull') <> 0) THEN DBMS_OUTPUT.PUT_LINE('L''annee ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EveSexeNotNull') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le sexe ne peut pas être null.');
    END IF;

    WHEN ExcPK THEN 
    IF(INSTR(SQLERRM, 'EveCpNageurs') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le nageur existe deja.');
    ELSIF (INSTR(SQLERRM, 'EveUnicite') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le prénom et le nom doivent etre uniques.');
    END IF;

    WHEN ExcFK THEN
    IF(INSTR(SQLERRM, 'EveRefNageursCat') <> 0) THEN DBMS_OUTPUT.PUT_LINE('La categorie n''existe pas');
    ELSIF (INSTR(SQLERRM, 'EveRefNageursClubs') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le club n''existe pas');
    ELSIF (INSTR(SQLERRM, 'EveRefNageursCP') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le code postal n''existe pas.');
    END IF;

    WHEN ExcCheck THEN
    IF(INSTR(SQLERRM, 'EveSexe') <> 0) THEN DBMS_OUTPUT.PUT_LINE('Le sexe doit être F ou M');
    ELSIF (INSTR(SQLERRM, 'EveCotisation') <> 0) THEN DBMS_OUTPUT.PUT_LINE('La cotisation doit être O ou N');
    END IF;

    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Exception : Trop de données.');
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Pas de données.');
    WHEN OTHERS THEN RAISE;
END;

Here is the table Nageur (Swimmer) :

CREATE TABLE Nageurs
(NrLigue CHAR(14)
     CONSTRAINT EveCpNageurs PRIMARY KEY,
Nom VARCHAR2(20) 
     CONSTRAINT EveNomNotNull NOT NULL,
Prenom VARCHAR2(20) 
   CONSTRAINT EvePrenomNotNull NOT NULL,
AnneeNaiss NUMBER(4) 
    CONSTRAINT EveNaissanceNotNull NOT NULL,
Sexe CHAR(1) 
  CONSTRAINT EveSexeNotNull NOT NULL
  CONSTRAINT EveSexe CHECK (Sexe in ('F','M')),
Categorie CHAR(2)
  CONSTRAINT EveRefNageursCat  REFERENCES Categories(Categorie),Club CHAR(5)
  CONSTRAINT EveRefNageursClubs  REFERENCES Clubs (Club),
 Adresse VARCHAR2(50),
 CodePostal CHAR(5)
        CONSTRAINT EveRefNageursCP REFERENCES CodePostaux (CodePostal),
 Localite VARCHAR2(20),
 NrTelephone CHAR(15),
 EMAIL VARCHAR2(50),
 GSM CHAR(15),
 Cotisation CHAR(1)
    CONSTRAINT EveCotisation CHECK (Cotisation in ('O','N')),
CONSTRAINT EveUnicite UNIQUE (Nom, Prenom)
);

Here an example of test that doesn't work :

DECLARE
  nag nageurs%rowtype;
BEGIN
  nag.nrligue := '01/000325/CCM';
  nag.nom := 'DISNEY';
  nag.prenom := 'WALTER';
  nag.anneenaiss := '1901';
  nag.sexe := 'Z';
  nag.club := 'CCM';
  EveGestionNageursMasters.Ajouter(nag);
END ;

Have a look at [`raise_application_error`](https://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS00705). Also, [don't use `char`](https://stackoverflow.com/a/42165653/230471). — William Robertson, Aug 14 '17 at 07:37
Most likely, you don't have the output turned on - e.g. "set serveroutput on" (or, if you're in a GUI like Toad or PL/SQL Developer, you're not looking in the correct place). However, this is a \*terrible\* way of handling exceptions - don't output messages to something that won't be seen; instead use raise or raise_application_error to specifically halt the procedure's execution and pass the error message back to the calling procedure. Far safer. — Boneist, Aug 14 '17 at 11:00
Thanks for your comments, I've already put "SET serveroutput ON size unlimited;" in the beginning of my package, sorry I should have mentionned it. I'm going to have a look at raise_application_error. :) — Shony, Aug 14 '17 at 20:07

Kirby · Answer 1 · 2017-08-13T22:30:41.393

I would suggest to add dbms_output.put_line statements in each exception handler before the IF statement to see precisely the text of SQLERRM.

Your exception handlers print messages but do not propagate the exception. Maybe you want to add a RAISE; at the end of each exception handler to propagate the exception back to the caller, as shown below.

EXCEPTION
  WHEN ExcNull THEN
    dbms_output.put_line(SQLERRM);
    IF(INSTR(SQLERRM, 'EveNomNotNull') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le nom ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EvePrenomNotNull') <> 0) THEN
      DBMS_OUTPUT.PUT_LINE('Le prenom ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EveNaissanceNotNull') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('L''annee ne peut pas être null.');
    ELSIF (INSTR(SQLERRM, 'EveSexeNotNull') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le sexe ne peut pas être null.');
    END IF;
    RAISE;
  WHEN ExcPK THEN 
    dbms_output.put_line(SQLERRM);
    IF(INSTR(SQLERRM, 'EveCpNageurs') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le nageur existe deja.');
    ELSIF (INSTR(SQLERRM, 'EveUnicite') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le prénom et le nom doivent etre uniques.');
    END IF;
    RAISE;
  WHEN ExcFK THEN
    dbms_output.put_line(SQLERRM);
    IF(INSTR(SQLERRM, 'EveRefNageursCat') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('La categorie n''existe pas');
    ELSIF (INSTR(SQLERRM, 'EveRefNageursClubs') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le club n''existe pas');
    ELSIF (INSTR(SQLERRM, 'EveRefNageursCP') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le code postal n''existe pas.');
    END IF;
    RAISE;
  WHEN ExcCheck THEN
    dbms_output.put_line(SQLERRM);
    IF(INSTR(SQLERRM, 'EveSexe') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('Le sexe doit être F ou M');
    ELSIF (INSTR(SQLERRM, 'EveCotisation') <> 0) THEN 
      DBMS_OUTPUT.PUT_LINE('La cotisation doit être O ou N');
    END IF;
    RAISE;
  WHEN TOO_MANY_ROWS THEN 
    DBMS_OUTPUT.PUT_LINE('Exception : Trop de données.');
    RAISE;
  WHEN NO_DATA_FOUND THEN 
    DBMS_OUTPUT.PUT_LINE('Pas de données.');
    RAISE;
  WHEN OTHERS THEN 
    RAISE;
END;

I tried your first proposition, that's what I get : "ORA-02290: check constraint (EVEPACKAGES.EVESEXE) violated" It seems like my exceptions are working but the text doesn't show. I don't get where I should put my RAISE; exactly, could you please give me an example ? (I tried to put it before the ENDIF, but it doesn't seem to be the right place.) I'm still a beginner, thank you for your patience. :) — Shony, Aug 13 '17 at 21:41
No shame in being a beginner; we were all beginners once. :-) I suggest to put the RAISE; statement after each of each IF statement. I'll update my answer with code. — Kirby, Aug 13 '17 at 22:24
To ensure you see the text, within SQLDeveloper go to the view menu and select DBMS Output. this will give you a window for this, you will then need to Click the green + button and select the connection you will be running this as. — Shaun Peterson, Aug 14 '17 at 01:53
@Kirby : Thanks for your help ! I put the RAISE; at the right place and here's what I get : Rapport d'erreur - ORA-02290: check constraint (EVEPACKAGES.EVESEXE) violated ORA-06512: at "EVEPACKAGES.EVEGESTIONNAGEURSMASTERS", line 61 ORA-06512: at line 10 02290. 00000 - "check constraint (%s.%s) violated" *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint. It works well but the text still doesn't show and I still don't get why... T_T Shaun Peterson : Thanks for the advice. :) — Shony, Aug 14 '17 at 09:08

score 0 · Accepted Answer · answered Aug 15 '17 at 06:26

When you name objects in Oracle like this, with no delimiters:

CONSTRAINT EveNomNotNull NOT NULL

The actual constraint name is non-case-sensitive, so the contraint name in the error message will be reported as:

EVENOMNOTNULL

You can check this by running a query like this:

select constraint_name from all_constraints where table_name = 'NAGEURS';

That's why the following condition using INSTR will not detect the constraint name:

IF(INSTR(SQLERRM, 'EveNomNotNull') <> 0) THEN

Since INSTR does a case-sensitive search, you must make sure it matches the constraint name as created in the database, e.g.:

IF(INSTR(SQLERRM, 'EVENOMNOTNULL') <> 0) THEN

Thank you very much ! I never thought my problem came from there. It works much better now ! — Shony, Aug 18 '17 at 13:21

PL/SQL : My procedure works fine but not my exceptions?

2 Answers2