ORA-01722: invalid number ORA-06512: at "BWSTAGE.LOAD_CONTACT_PROCEDURE", line 8 ORA-06512: at line 2

Question

CREATE OR REPLACE PROCEDURE LOAD_CONTACT_PROCEDURE AS

CURSOR C1 IS SELECT FIRST_NAME, MIDDLE_INITIAL, LAST_NAME, TITLE, DATE_OF_BIRTH, PHONE_MOBILE, PREFERRED_PHONE, EMAIL_ADDRESS, PREFERRED_EMAIL, DECEASED, 
preferred_name, student_id, last_website_login, north_america_email, international_email, afloat_email, lifelong_quarterly, fundraising_email, id_first_name, 
id_middle_name, id_last_name, id_number, passport_first_name, passport_middle_name, passport_last_nm, passport_number, passport_issuing_auth, passport_date_issued, 
passport_expiration_dt, state_of_birth, special_needs_descr, whitelist_descr FROM STAGE_CONTACT;

BEGIN

FOR i IN C1 LOOP

INSERT INTO LOAD_CONTACT(FIRSTNAME, MIDDLENAME, LASTNAME, TITLE, BIRTHDATE, PHONE, NPE01__PREFERREDPHONE__C, NPE01__HOMEEMAIL__C, NPE01__PREFERRED_EMAIL__C,
NPSP__DECEASED__C, PREFERRED_NAME__C, STUDENT_ID__C, LAST_WEBSITE_LOGIN__C, NORTH_AMERICA_EMAIL__C, INTERNATIONAL_EMAIL__C, AFLOAT_EMAIL__C, LIFELONG_QUARTERLY__C,
FUNDRAISING_EMAIL__C, ID_FIRST_NAME__C, ID_MIDDLE_NAME__C, ID_LAST_NAME__C, ID_NUMBER__C, PASSPORT_FIRST_NAME__C, PASSPORT_MIDDLE_NAME__C, PASSPORT_LAST_NAME__C, 
PASSPORT_NUMBER__C, PASSPORT_ISSUING_AUTHORITY__C, PASSPORT_DATE_ISSUED__C, PASSPORT_EXPIRATION_DATE__C, STATE_OF_BIRTH__C, SPECIAL_NEEDS_DESCRIPTION__C, 
WHITELIST_DESCRIPTION__C) 

VALUES 

(i.FIRST_NAME, i.MIDDLE_INITIAL, i.LAST_NAME, i.TITLE, i.DATE_OF_BIRTH, i.PHONE_MOBILE, 'Home', i.EMAIL_ADDRESS, 'Personal',
DECODE (i.DECEASED, 'Y', '1', 'N', '0'), i.preferred_name, i.student_id, i.last_website_login, DECODE (i.north_america_email, 'Y', '1', 'N', '0'), 
DECODE (i.international_email, 'Y', '1', 'N', '0'), DECODE (i.afloat_email, 'Y', '1', 'N', '0'), DECODE (i.lifelong_quarterly, 'Y', '1', 'N', '0'),
DECODE (i.fundraising_email, 'Y', '1', 'N', '0'), i.id_first_name, i.id_middle_name, i.id_last_name, i.id_number, i.passport_first_name, i.passport_middle_name, 
i.passport_last_nm, i.passport_number, i.passport_issuing_auth, i.passport_date_issued, i.passport_expiration_dt, i.state_of_birth, i.special_needs_descr,
i.whitelist_descr);

END LOOP;

COMMIT;

END LOAD_CONTACT_PROCEDURE;

I am writing this procedure where I am getting the following error:

ORA-01722: invalid number ORA-06512: at "BWSTAGE.LOAD_CONTACT_PROCEDURE", line 8 ORA-06512: at line 2

I am trying to solve it since very long now but I am unable to find a solution. Please help.

Add exception when others inside the loop of insert and debug the variable I.first_name for the error record — psaraj12
I think that INSERT .. SELECT .. (with subquery) is better. Also is possible to use BULK if you are working with millions of rows... — michaos
the data type of student_id is varchar(15) in stage_contact and it's Number(18,0) in load_contact. When I excluded this column from the current script, it got executed. — Abid Majgaonkar
Please check and match the column datatype of your Staging table and the other table. There might be some datatype mismatch issue. The conversion of a character string to a number generates this issue. For example in the stagging table Amount might be in Varchar with the value as -100.90 and u are trying to insert into the current table with the field as "Amount" which is in Number datatype. This would result in -1722 . Please check the datatypes of all the fields getting inserted. I would request you to add bulk collect option and try running the code. — Vimal Bhaskar

APC APC · Accepted Answer · 2017-11-22T08:24:56

ORA-01722: invalid number means you are trying to put a non-numeric value in a numeric column. So you need to check all the columns in LOAD_CONTACT which you reference in the INSERT projection and compare them to the columns in the VALUES clause.

There are two possibilities:

The two projections are out of synch and you need to re-order one to match the other.
The STAGE_CONTACT uses string columns for numeric attributes. This is not unusual with staging tables. But it does leave open the possibility that the staging table contains strings which cannot be cast to a number. This is harder to catch.

But you can put in some crude error handling:

FOR i IN C1 LOOP
   begin
     INSERT INTO LOAD_CONTACT(FIRSTNAME, MIDDLENAME, LASTNAME, TITLE, BIRTHDATE, PHONE, NPE01__PREFERREDPHONE__C, NPE01__HOMEEMAIL__C, NPE01__PREFERRED_EMAIL__C,
NPSP__DECEASED__C, PREFERRED_NAME__C, STUDENT_ID__C, LAST_WEBSITE_LOGIN__C, NORTH_AMERICA_EMAIL__C, INTERNATIONAL_EMAIL__C, AFLOAT_EMAIL__C, LIFELONG_QUARTERLY__C,
FUNDRAISING_EMAIL__C, ID_FIRST_NAME__C, ID_MIDDLE_NAME__C, ID_LAST_NAME__C, ID_NUMBER__C, PASSPORT_FIRST_NAME__C, PASSPORT_MIDDLE_NAME__C, PASSPORT_LAST_NAME__C, 
PASSPORT_NUMBER__C, PASSPORT_ISSUING_AUTHORITY__C, PASSPORT_DATE_ISSUED__C, PASSPORT_EXPIRATION_DATE__C, STATE_OF_BIRTH__C, SPECIAL_NEEDS_DESCRIPTION__C, 
WHITELIST_DESCRIPTION__C) 

    VALUES 

    (i.FIRST_NAME, i.MIDDLE_INITIAL, i.LAST_NAME, i.TITLE, i.DATE_OF_BIRTH, i.PHONE_MOBILE, 'Home', i.EMAIL_ADDRESS, 'Personal',
DECODE (i.DECEASED, 'Y', '1', 'N', '0'), i.preferred_name, i.student_id, i.last_website_login, DECODE (i.north_america_email, 'Y', '1', 'N', '0'), 
DECODE (i.international_email, 'Y', '1', 'N', '0'), DECODE (i.afloat_email, 'Y', '1', 'N', '0'), DECODE (i.lifelong_quarterly, 'Y', '1', 'N', '0'),
DECODE (i.fundraising_email, 'Y', '1', 'N', '0'), i.id_first_name, i.id_middle_name, i.id_last_name, i.id_number, i.passport_first_name, i.passport_middle_name, 
i.passport_last_nm, i.passport_number, i.passport_issuing_auth, i.passport_date_issued, i.passport_expiration_dt, i.state_of_birth, i.special_needs_descr,
i.whitelist_descr);

 exception
    when invalid_number then
        raise_application_error(-20000, 
             'record for ' ||i.FIRST_NAME||' '||i.MIDDLE_INITIAL||' '||i.LAST_NAME 
       || ' contains invalid number',
            true);
  end;
END LOOP;

This will give you some information which you can use to query STAGE_CONTACT and look for rogue values.

If you're doing this for a real system (as opposed to homework) you should investigate Oracle's bulk DML error logging. Find out more.

ORA-01722: invalid number ORA-06512: at "BWSTAGE.LOAD_CONTACT_PROCEDURE", line 8 ORA-06512: at line 2

2 Answers