Blog

The procedure below helps to find which record has text instead of number, so it produces “ORA-01722: invalid number” when you try to join with another number field.

DECLARE
EMPLID VARCHAR2(15);
LAST_EMPLID VARCHAR2(15);

CURSOR CUR IS
SELECT * FROM XXVV_INITIAL_NATIONAL_IDS WHERE EMPLOYEE_ID LIKE '154916%' ORDER BY EMPLID;

RESULTS_REC XXVV_INITIAL_NATIONAL_IDS%ROWTYPE;

BEGIN
FOR rec IN CUR LOOP
FETCH CUR INTO RESULTS_REC;
 LAST_EMPLID:=EMPLID;
 DBMS_OUTPUT.PUT_LINE('ok1: ' || RESULTS_REC.EMPLOYEE_ID);
 SELECT EMPLOYEE_ID INTO EMPLID FROM XXVV_INITIAL_NATIONAL_IDS WHERE EMPLID=RESULTS_REC.EMPLOYEE_ID;
 DBMS_OUTPUT.PUT_LINE('ok2: ' || EMPLID);
END LOOP;

EXCEPTION
WHEN INVALID_NUMBER THEN
 DBMS_OUTPUT.PUT_LINE('Problem: ' || LAST_EMPLID);
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Problem: ' || LAST_EMPLID);     
END;