Getting ORA-01722 Invalid Number Running Allocation Against Character Column (Doc ID 367967.1)

Last updated on APRIL 18, 2014

Applies to:

Oracle Profitability Manager - Version 4.5 and later
Information in this document applies to any platform.
Oracle Financial Services Applications (OFSA)
Oracle Performance Analyzer - Version: 4.5
Checked for relevance on 18-Apr-2014.

Symptoms

When running an Allocation ID that moves a value in a varchar(2) column from one table to a varchar(2) column in another table, the following error appears in the ofspa.xxxx file:

Error(Oracle Financial Services Application error) (203105) Oracle drv_oci error:

OCI Function: [12] - ofetch(), ofen()
SQL Function: [4] - SELECT
Oracle Error: ORA-01722: invalid number

Both columns have identical definitions.
The varchar(2) column on the source table does contain characters (ex. 034GA).

Example of SQL Statement triggering the error:

SQL Statement: select a.rowid, a.IDENTITY_CODE, a.ID_NUMBER, NVL(ACCOUNT_OFFICER, 0) from DEPOSITS a where a.COMMON_COA_ID = :COMMON_COA_ID and a.as_of_date='01/31/2006';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms