Question

Write a PL/SQL program which accepts the customer_ID from the user. If the enters an invalid ID then the exception invalid_id is raised using exception handling.

Answer 1

Of course. Here is a complete, runnable PL/SQL program that accomplishes this task.

The solution includes:
1. Setup steps to create a sample Customers table.
2. The PL/SQL block that accepts user input and handles the exception.
3. A detailed explanation of how it works.
4. Instructions on how to run the code and what output to expect.

---

Step 1: Create a Sample Customers Table

First, we need a table to check the customer_ID against. Run the following SQL to create and populate a simple Customers table.

`sql
-- Create the table
CREATE TABLE Customers (

CustomerID   NUMBER(4) PRIMARY KEY,
CustomerName VARCHAR2(50)

);

-- Insert some sample data
INSERT INTO Customers VALUES (1001, 'Alice Smith');
INSERT INTO Customers VALUES (1002, 'Bob Johnson');
INSERT INTO Customers VALUES (1003, 'Charlie Brown');
COMMIT;
`

---

Step 2: The PL/SQL Program

This PL/SQL block will prompt the user for a customer_ID. It then attempts to find that customer. If the customer is not found, it raises a custom exception named invalid_id and handles it gracefully.

`sql
-- Enable output in tools like SQL*Plus or SQL Developer
SET SERVEROUTPUT ON;

-- Start of the PL/SQL block
DECLARE

-- Declare a custom exception. This gives our error a name.
invalid_id EXCEPTION;

-- Declare variables to hold the user's input and the customer's name.
-- Using %TYPE is good practice as it adapts if the table structure changes.
v_input_id      Customers.CustomerID%TYPE := &Enter_Customer_ID;
v_customer_name Customers.CustomerName%TYPE;
v_customer_count NUMBER;

BEGIN

-- Step 1: Check if the customer ID exists.
-- We use a COUNT(*) query which is safe and will not raise NO_DATA_FOUND.
SELECT COUNT(*)
INTO v_customer_count
FROM Customers
WHERE CustomerID = v_input_id;

-- Step 2: If the count is 0, the ID is invalid. Raise our custom exception.
IF v_customer_count = 0 THEN
    RAISE invalid_id; -- This immediately transfers control to the EXCEPTION block.
ELSE
    -- If the ID is valid, retrieve the customer's name and display it.
    SELECT CustomerName
    INTO v_customer_name
    FROM Customers
    WHERE CustomerID = v_input_id;
    
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Success: Customer Found.');
    DBMS_OUTPUT.PUT_LINE('Customer ID:   ' || v_input_id);
    DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
END IF;

EXCEPTION

-- Step 3: This block catches the exceptions raised in the BEGIN block.
WHEN invalid_id THEN
    -- This is our custom handler for the invalid_id exception.
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('ERROR: The Customer ID "' || v_input_id || '" is invalid.');
    DBMS_OUTPUT.PUT_LINE('Please enter a valid ID.');
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    
WHEN OTHERS THEN
    -- A general handler for any other unexpected errors.
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');

END;
/
`

Explanation of the Code

1. DECLARE Section:
   invalid_id EXCEPTION;: This line declares a new, user-defined exception named invalid_id. It doesn't do anything on its own; it just gives us a named error that we can RAISE later.
   v_input_id Customers.CustomerID%TYPE := &Enter_Customer_ID;: This declares a variable to hold the input. The & symbol creates a substitution variable, which will cause SQLPlus or SQL Developer to prompt the user to "Enter value for Enter_Customer_ID".
   v_customer_count NUMBER;: This variable will store the result of our check to see if the customer exists.

2. BEGIN Section (Main Logic):
   `SELECT COUNT()...: Instead of directly selecting the name (which would raise a NO_DATA_FOUND error for an invalid ID), we first count how many customers match the ID. This is a safer way to check for existence. * IF v_customer_count = 0 THEN: If the count is zero, it means no customer with that ID was found. * RAISE invalid_id;: This is the key part. We explicitly trigger our custom exception. The program's normal flow stops immediately, and control jumps to the EXCEPTION block, looking for a handler for invalid_id. * ELSE ...`: If the count is not zero, the ID is valid. The code proceeds to select the customer's name and print a success message.

3. EXCEPTION Section (Error Handling):
   WHEN invalid_id THEN: This is the "handler" for our custom exception. When RAISE invalid_id is executed, the code inside this WHEN block is run.
   DBMS_OUTPUT.PUT_LINE(...): We print a user-friendly error message, informing the user that the ID they entered was invalid.
   * WHEN OTHERS THEN: This is a catch-all handler for any other unexpected errors that might occur.

How to Run and Test

1. Copy and paste the table creation code (Step 1) into your SQL tool and run it.
2. Copy and paste the PL/SQL program (Step 2) into your SQL tool.
3. Run the block. Your tool will prompt you for input.

Test Case 1: Enter a Valid ID

When prompted, enter 1002.

Expected Output:
`

Enter value for enter_customer_id: 1002

Success: Customer Found.
Customer ID: 1002

Customer Name: Bob Johnson

PL/SQL procedure successfully completed.
`

Test Case 2: Enter an Invalid ID

Run the block again. When prompted, enter 9999.

Expected Output:
`

Enter value for enter_customer_id: 9999

ERROR: The Customer ID "9999" is invalid.

Please enter a valid ID.

PL/SQL procedure successfully completed.
`