Detecting Duplicates and Displaying Error Messages in Oracle PL/SQL

Managing duplicate data is a critical aspect of database maintenance. In this blog post, I’ll demonstrate how to create a validation process in Oracle PL/SQL to check if specific values already exist in the database. For this example, we’ll use the standard EMP table.

The Problem

The objective is to prevent duplicate entries of an employee with the name "Clark" in the database. If an entry with this name already exists, the system should display an error message.

The Solution

To tackle this issue, we’ll use Oracle’s PL/SQL procedural language to write a script that checks for duplicates in the EMP table. If the name "Clark" is already present, the script outputs an appropriate error message.

PL/SQL Code to Detect Duplicates

Here’s the PL/SQL code to detect and handle duplicates:

 

SQL

xxxxxxxxxx

31

 

1

plsql

2

Code kopieren

3

-- Declare the variables used in the PL/SQL block

4

DECLARE

5

    v_check_duplicate       NUMBER;           -- Variable to store the count of duplicate names

6

    v_emp_name              VARCHAR2(4000) := 'Clark';  -- Variable to store the employee name

7

-- Begin the execution block

8

BEGIN

9

    -- Check for duplicates in the EMP table where the employee name matches v_emp_name (case insensitive)

10

    SELECT COUNT(*)

11

    INTO v_check_duplicate

12

    FROM dual

13

    WHERE EXISTS (SELECT EMPNO

14

                  FROM EMP

15

                  WHERE LOWER(ENAME) = LOWER(v_emp_name));

16

    -- If exactly one duplicate is found, output an error message

17

    IF v_check_duplicate = 1 THEN

18

        -- The following block is commented out but is intended for use in an Oracle APEX environment

19

        -- to display an error message directly on a page item.

20

        /*

21

        APEX_ERROR.ADD_ERROR (

22

            p_message            => v_emp_name || ' exists already!',

23

            p_display_location   => apex_error.c_inline_with_field_and_notif,

24

            p_page_item_name     => 'APEX_PAGE_ITEM_NAME'

25

        );

26

        */

27

        -- For environments other than APEX, output the error to the console

28

        DBMS_OUTPUT.PUT_LINE(v_emp_name || ' exists already!');

29

    END IF;

30

-- End the execution block

31

END;

 

---

Explanation of the Code

• Variable Declaration:
• v_check_duplicate: Used to store the count of duplicate entries.
• v_emp_name: Stores the name of the employee to check, in this case, "Clark".
• Checking for Duplicates:
• The EXISTS clause checks whether the employee name "Clark" exists in the EMP table.
• The LOWER function ensures the check is case-insensitive.
• Outputting the Error:
• If a duplicate is found (v_check_duplicate = 1), an error message is displayed.
• The code provides options for different environments:
• Oracle APEX: Use the APEX_ERROR.ADD_ERROR procedure to display the message inline.
• Other Environments: Use DBMS_OUTPUT.PUT_LINE to log the error message to the console.

Enhancing the User Experience

Using this script, you can ensure:

• Data Integrity: Prevent duplicate entries in your database.
• User Feedback: Provide clear and actionable error messages to users.
• Flexibility: Adapt the script for use in Oracle APEX or other Oracle environments.

Best Practices

• Case Insensitivity: Always use functions like LOWER or UPPER to handle case differences.
• Error Handling: Implement robust error handling to manage edge cases and improve user feedback.
• Commenting: Document the code to ensure it’s easy to understand and maintain.

Conclusion

Detecting duplicates and displaying error messages in Oracle PL/SQL is a fundamental skill for maintaining clean and reliable data. By implementing this script, you can enhance data validation processes and improve user experience in your applications. Start using this approach today to prevent duplicates and ensure data quality in your Oracle database!

 {fullWidth}