PL/SQL存储过程重复值异常未触发问题咨询

Let’s walk through the most likely reasons your exception isn’t firing, and how to fix them:

1. Your CONTINENT table lacks a unique constraint on the nom column #

The DUP_VAL_ON_INDEX exception only kicks in when an insert violates a unique index (either a standalone unique constraint or a primary key). If your nom column doesn’t have this type of constraint, Oracle has no way to detect duplicate continent names—so it just lets the insert go through without throwing an error.

To fix this, add a unique constraint to the nom column (or make it the primary key if that fits your schema design):

-- Add a unique constraint to nom
ALTER TABLE CONTINENT ADD CONSTRAINT uq_continent_nom UNIQUE (nom);

-- OR, if nom should be the primary identifier for continents:
ALTER TABLE CONTINENT ADD CONSTRAINT pk_continent_nom PRIMARY KEY (nom);

2. Case sensitivity is causing a mismatch #

Oracle treats string values as case-sensitive by default. If the existing 'Asia' in your table is stored as 'ASIA' (all uppercase) or 'asia' (all lowercase), inserting 'Asia' won’t be recognized as a duplicate.

You can check this with a quick query:

SELECT nom FROM CONTINENT WHERE UPPER(nom) = 'ASIA';

If this returns a row, you have a case mismatch. You can either:

• Normalize the input in your insert (e.g., convert to uppercase):

  INSERT INTO CONTINENT VALUES (UPPER(pnom), psuperficie);
  

• Or create a case-insensitive unique index:

  CREATE UNIQUE INDEX uq_continent_nom_ci ON CONTINENT (UPPER(nom));
  

3. You might not have DBMS_OUTPUT enabled #

Even if the exception is triggering, you won’t see your custom error message if DBMS_OUTPUT isn’t turned on in your SQL client (like SQL*Plus or SQL Developer). Before running your procedure, run:

SET SERVEROUTPUT ON;

Then re-execute GEST_GEO.ADDCONTINENT('Asia',99380);—if the exception was firing all along, you’ll now see the "The continent already exists" message.

Start with checking the unique constraint first—it’s the most common culprit here. Once that’s in place, the DUP_VAL_ON_INDEX exception should work as expected.

内容的提问来源于stack exchange，提问作者user13339757