How to Handle Data Conversion Errors in Oracle 12c with VALIDATE_CONVERSION and CAST

When loading data into Oracle tables, conversion errors such as ORA‑01722 “invalid number” often arise, and it can be difficult to pinpoint the offending column.

Traditional approaches add extensive validation code or use the BADFILE clause for external files, but these methods are limited when data comes from ETL jobs or web forms.

New features in Oracle Database 12c Release 2

Oracle 12cR2 introduces two important enhancements:

The VALIDATE_CONVERSION(expr AS datatype) function, which returns 1 if the expression can be converted to the specified datatype and 0 otherwise.

Extended CAST (and related TO_*) functions that accept DEFAULT … ON CONVERSION ERROR clauses, allowing a user‑defined value to be returned instead of raising an error.

Example: staging table with bad data

CREATE TABLE STAGING_EMP (
  EMPNO   VARCHAR2(6),
  ENAME   VARCHAR2(10),
  JOB     VARCHAR2(9),
  MGR     VARCHAR2(4),
  HIREDATE VARCHAR2(10),
  SAL     VARCHAR2(7),
  COMM    VARCHAR2(9),
  DEPTNO  VARCHAR2(6)
);

-- Insert rows with intentional errors
INSERT INTO STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES ('GB9369','SMITH','CLERK','7902','17-DEC-80','800',null,'20');
INSERT INTO STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES ('9499','ALLEN','SALESMAN','7698','31-FEB-81','1600','300','30');  -- invalid date
INSERT INTO STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES ('9521','WARD','SALESMAN','7698','22-FEB-81','1250','500','SALES');  -- invalid number for DEPTNO
... (additional rows omitted for brevity) ...

Attempting a direct insert into the target EMP table produces the familiar ORA‑01722 error.

INSERT INTO scott.emp SELECT * FROM staging_emp;

Identifying problematic rows with VALIDATE_CONVERSION

SELECT
  VALIDATE_CONVERSION(empno   AS NUMBER) AS is_empno,
  VALIDATE_CONVERSION(mgr    AS NUMBER) AS is_mgr,
  VALIDATE_CONVERSION(hiredate AS DATE)   AS is_hiredate,
  VALIDATE_CONVERSION(sal    AS NUMBER) AS is_sal,
  VALIDATE_CONVERSION(comm   AS NUMBER) AS is_comm,
  VALIDATE_CONVERSION(deptno AS NUMBER) AS is_deptno
FROM staging_emp;

The query returns a result set where each column is 1 for convertible values and 0 for failures, allowing you to filter rows before insertion.

Loading clean data with CAST defaults

INSERT INTO emp
SELECT
  empno,
  ename,
  job,
  CAST(mgr      AS NUMBER   DEFAULT 9999 ON CONVERSION ERROR),
  CAST(hiredate AS DATE     DEFAULT SYSDATE ON CONVERSION ERROR),
  CAST(sal      AS NUMBER   DEFAULT 0 ON CONVERSION ERROR),
  CAST(comm     AS NUMBER   DEFAULT NULL ON CONVERSION ERROR),
  CAST(deptno   AS NUMBER   DEFAULT 99 ON CONVERSION ERROR)
FROM staging_emp
WHERE VALIDATE_CONVERSION(empno AS NUMBER) = 1;

This statement inserts only rows whose EMPNO can be converted, while substituting sensible defaults for other columns that fail conversion. The resulting data shows that invalid dates become the current date, invalid department numbers become 99, and invalid manager numbers become 9999.

Summary

The enhanced CAST (and TO_*) functions let you handle conversion errors without writing complex PL/SQL or application‑side validation, while VALIDATE_CONVERSION helps you locate and filter bad rows before they reach the target table.