ORA-01722 : Invalid Number

  Hi

One of the queries a user runs on a regular basis has started failing with an invalid number error after a periodic archival of records from one table.  This did not happen when the data was archived in a similar fashion 6 months ago.

Following is the archival process followed to avoid excessive redo log generation:

Table to be archived : EVENTS

All the records eligible for archival are inserted with nologging into EVENTS_HIST.

All the rows that don't have to be archived are inserted from EVENTS into a temporary table.

EVENTS table is truncated.

All the rows that should be in EVENTS as current records are inserted back from the temporary table to EVENTS table.