ORA-06502: PL/SQL: numeric or value error: character to number conversion error

This is a very common Oracle issue when handling mobile numbers stored in a numeric field.

Let’s go step by step on why it happens and how to avoid ORA-06502 safely 👇

---

⚠️ Why the Error Happens

If a mobile number is stored or entered as a character string (like '98765A4321' or '0987654321'),
and your PL/SQL variable or table column is defined as NUMBER, Oracle will try to convert it to a number automatically.

That causes:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

---

✅ Best Practices to Avoid the Error

1️⃣ Store Mobile Numbers as VARCHAR2, Not NUMBER

📌 Always define mobile numbers as VARCHAR2(15) (or similar).

Example:

CREATE TABLE customers (
    mobile_no VARCHAR2(15)
);

💡 Because:

• Mobile numbers are identifiers, not quantities.
• They can start with 0 or +91.
• Numeric fields drop leading zeros and cause conversion issues.

---

2️⃣ If You Already Have It as NUMBER

If your table or variable is already defined as NUMBER and you can’t change it:

Use explicit conversion with validation:

BEGIN
   IF REGEXP_LIKE(:mobile_input, '^[0-9]{10}$') THEN
      v_mobile := TO_NUMBER(:mobile_input);
   ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Invalid mobile number format');
   END IF;
END;

This ensures only pure 10-digit numbers are converted.

---

3️⃣ Use TO_CHAR When Comparing or Concatenating

If you must join or compare with another VARCHAR field:

WHERE TO_CHAR(mobile_no) = other_table.mobile_no

---

4️⃣ Use Exception Handling for Safety

BEGIN
   v_mobile_num := TO_NUMBER(:mobile_input);
EXCEPTION
   WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Invalid mobile number entered');
END;

---

5️⃣ Add Input Validation at Application Level

If this comes from a form or web app, validate mobile input before hitting the database, e.g.:

• Allow only digits [0-9]
• Length = 10
• No spaces, symbols, or alphabets

---

🧠 Best Option (Recommended)

✅ Change data type to VARCHAR2(15)
✅ Validate input with REGEXP_LIKE()
✅ Never treat mobile numbers as numeric values.

---

Example of a trigger that automatically checks mobile numbers before INSERT or UPDATE, so you’ll never hit ORA-06502 (character-to-number conversion error) again.

---

✅ Example: Automatic Mobile Number Validation Trigger

CREATE OR REPLACE TRIGGER trg_validate_mobile
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
DECLARE
BEGIN
   --   Check for NULL
   IF :NEW.mobile_no IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001, 'Mobile number cannot be null.');
   END IF;

   --   Validate format: exactly 10 digits, all numeric
   IF NOT REGEXP_LIKE(:NEW.mobile_no, '^[0-9]{10}$') THEN
      RAISE_APPLICATION_ERROR(
         -20002,
         'Invalid mobile number. Please enter a 10-digit number only.'
      );
   END IF;
END;
/

🧱 Assumed Table

CREATE TABLE customers (
    cust_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cust_name   VARCHAR2(100),
    mobile_no   VARCHAR2(15)  --  keep as VARCHAR2, not NUMBER
);

---

🧩 How It Works

• Runs before every insert or update on the customers table.
• Checks that mobile_no is not null and only 10 digits.
• If invalid, raises a descriptive error (ORA-20002) before data goes into the table.
• Prevents ORA-06502 because no implicit character-to-number conversion occurs.

---

💡 Optional: Allow Country Code (+91)

If you want to allow formats like +919876543210:

IF NOT REGEXP_LIKE(:NEW.mobile_no, '^(?:\+91)?[0-9]{10}$') THEN
   RAISE_APPLICATION_ERROR(-20002, 'Invalid mobile number. Must be 10 digits or +91 followed by 10 digits.');
END IF;

---

🧠 Bonus – Prevent Error at Application Level (Procedure Example)

If you want to validate inside a procedure instead of a trigger:

CREATE OR REPLACE PROCEDURE validate_mobile(p_mobile IN VARCHAR2) IS
BEGIN
   IF NOT REGEXP_LIKE(p_mobile, '^[0-9]{10}$') THEN
      RAISE_APPLICATION_ERROR(-20002, 'Invalid mobile number format.');
   END IF;
END;

Then call it before insert/update:

BEGIN
   validate_mobile(:mobile_input);
   INSERT INTO customers (cust_name, mobile_no)
   VALUES ('Ravi Kumar', :mobile_input);
END;
/

---

Here’s an enhanced and production-grade version of the mobile number validation trigger.
It not only validates the mobile number but also logs invalid attempts into a separate audit table for tracking — useful for debugging, misuse prevention, or compliance review.

---

✅ 1️⃣ Create an Audit Table

CREATE TABLE mobile_audit_log (
    log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    table_name    VARCHAR2(50),
    operation     VARCHAR2(10),
    cust_id       NUMBER,
    mobile_entered VARCHAR2(30),
    error_message VARCHAR2(200),
    username      VARCHAR2(50),
    log_date      DATE DEFAULT SYSDATE
);

---

✅ 2️⃣ Validation + Logging Trigger

CREATE OR REPLACE TRIGGER trg_validate_mobile
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
DECLARE
   v_error_message VARCHAR2(200);
BEGIN
   -- 1️⃣ Check for NULL
   IF :NEW.mobile_no IS NULL THEN
      v_error_message := 'Mobile number cannot be null.';
      INSERT INTO mobile_audit_log (table_name, operation, cust_id, mobile_entered, error_message, username)
      VALUES ('CUSTOMERS', NVL(ORA_SYSEVENT, 'INSERT/UPDATE'), :NEW.cust_id, :NEW.mobile_no, v_error_message, USER);
      RAISE_APPLICATION_ERROR(-20001, v_error_message);
   END IF;

   -- 2️⃣ Validate format (10 digits or +91 optional)
   IF NOT REGEXP_LIKE(:NEW.mobile_no, '^(?:\+91)?[0-9]{10}$') THEN
      v_error_message := 'Invalid mobile number format: must be 10 digits or +91 followed by 10 digits.';
      INSERT INTO mobile_audit_log (table_name, operation, cust_id, mobile_entered, error_message, username)
      VALUES ('CUSTOMERS', NVL(ORA_SYSEVENT, 'INSERT/UPDATE'), :NEW.cust_id, :NEW.mobile_no, v_error_message, USER);
      RAISE_APPLICATION_ERROR(-20002, v_error_message);
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      -- In case of any unexpected exception, log and rethrow
      INSERT INTO mobile_audit_log (table_name, operation, cust_id, mobile_entered, error_message, username)
      VALUES ('CUSTOMERS', NVL(ORA_SYSEVENT, 'INSERT/UPDATE'), :NEW.cust_id, :NEW.mobile_no, SQLERRM, USER);
      RAISE;
END;
/

---

✅ 3️⃣ How It Works

• Validates every INSERT/UPDATE on the CUSTOMERS table.

• If invalid data is detected:

• Logs details (table name, user, invalid value, timestamp) in MOBILE_AUDIT_LOG.
• Raises a friendly Oracle application error.

• Ensures invalid data never reaches the main table, yet you retain a full record of all attempts.

---

✅ 4️⃣ Example Output

After a failed insert:

INSERT INTO customers (cust_name, mobile_no)
VALUES ('Arun', '98765A4321');

Oracle returns:

ORA-20002: Invalid mobile number format: must be 10 digits or +91 followed by 10 digits.

And this entry is added to MOBILE_AUDIT_LOG:

LOG_ID	TABLE_NAME	OPERATION	CUST_ID	MOBILE_ENTERED	ERROR_MESSAGE	USERNAME	LOG_DATE
101	CUSTOMERS	INSERT	NULL	98765A4321	Invalid mobile number format...	SCOTT	13-OCT-25

---

✅ 5️⃣ Optional Enhancements

• Include IP address or terminal info:
• SYS_CONTEXT('USERENV', 'IP_ADDRESS')
• Add a status flag column for easier reporting (e.g., VALID / INVALID).
• Purge or archive logs periodically to manage size.

---