000028199 - KB-1143 - Collection fails with - Invalid number ORA-01722

The error noted below was seen with ACM V3.6.x, but may also be seen in newer versions using the Jboss Server. The server logs noted here are for Tomcat. Check the approprate Jboss server logs for newer ACM versions.

An IDC Collection fails with Oracle error- Invalid number ORA-01722

The entire stack trace from the /usr/bin/tomcat/logs/catalina.out is:
com.aveksa.server.db.PersistenceException: java.sql.SQLException: ORA-20002:

Failed to load user and group data for this IDC.

(idc=3,run=45,userfile=453UserData.data, groupfile=453GroupDefData.data,

groupmemfile=453GroupData.data) ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-01722: invalid number

Phase 1/2, Step 0: Main procedure for loading user and group data. Stored

Procedure:Load_User_Group_Data execution aborted.

Stack:

ORA-06512: at "SYS.ORACLE_LOADER", line 52

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 5344

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 332

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 4216

ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 979

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 4273

ORA-06512: at line 1

at

com.aveksa.server.db.PersistenceManager.runStoredProcedure(PersistenceManager.java:1169)

at com.aveksa.server.xfw.SAXUserDataFilter.loadData(SAXUserDataFilter.java:218)

at com.aveksa.server.xfw.SAXUserDataFilter.process(SAXUserDataFilter.java:175)

at com.aveksa.server.xfw.SAXUserDataFilter.process(SAXUserDataFilter.java:405)

at com.aveksa.server.xfw.DataProcessor.process(DataProcessor.java:85)

at com.aveksa.server.xfw.DataProcessor.process(DataProcessor.java:64)

at

com.aveksa.server.xfw.DataProcessingExecutor.executeTask(DataProcessingExecutor.java:124)

at com.aveksa.server.xfw.TaskExecutor.execute(TaskExecutor.java:86)

at

com.aveksa.server.xfw.ExecutionTaskQueue$Worker.run(ExecutionTaskQueue.java:117)

at java.lang.Thread.run(Thread.java:595)

Caused by: java.sql.SQLException: ORA-20002: Failed to load user and group data

for this IDC.

(idc=3,run=45,userfile=453UserData.data, groupfile=453GroupDefData.data,

groupmemfile=453GroupData.data) ORA-29913: error in executing ODCIEXTTABLEFETCH

callout

ORA-01722: invalid number

Phase 1/2, Step 0: Main procedure for loading user and group data. Stored

Procedure:Load_User_Group_Data execution aborted.

Stack:

ORA-06512: at "SYS.ORACLE_LOADER", line 52

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 5344

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 332

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 4216

ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 979

ORA-06512: at "AVUSER.LOAD_IDENTITY_DATA_PKG", line 4273

ORA-06512: at line 1

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

at

oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)

at

oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)

at

oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

at

oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)

at

oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)

at

oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)

at

org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)

at

com.aveksa.server.db.PersistenceManager.runStoredProcedure(PersistenceManager.java:1140)

... 9 more 

Reason for this error:

The appliance Database uses UTF-8 charset by default. In UTF-8, the char xAA (decimal=170) is represented as two bytes which is xC2AA or ª. That is why we see ª in .data files. For char x01, it is represented as is x01 (single byte) in UTF-8.
 

When we update the T_SYSTEM_SETTINGS, we need to update the Hex Code of the UTF-8 Representation of the delimiter. For example, for xAA, we do this:
UPDATE T_SYSTEM_SETTINGS SET VALUE='0x''c2aa''' WHERE LOWER(PARAMETER)='data-file-field-separator';

Please note: Values and parameters in sql queries are typically bounded by single quotes. The value within the boundaries are ALSO single quotes, not double quotes. This is VERY important for the update query shown above. The acutal data shown for the value statement is:

VALUE=<single-quote>0x<single-quote><single-quote>c2aa<single-quote><single-quote><single-quote>

After issuing the update one, the Aveksa Server (Tomcat for older ACM versions, Jboss for newer), must be restarted using the appropriate Aveksa server commands. For example:

V3.x - Tomcat: sudo service aveksa_server stop, sudo service aveksa_server start or sudo service aveksa_server restart