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

Document created by RSA Customer Support Employee on Jun 15, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000028199
Applies ToAffected Versions: All Versions
Issue

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 

Cause

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>

Resolution

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



V4.x+ - Jboss: service aveksa_server stop, service aveksa_server start, or service aveksa_server restart

or acm stop, acm start or acm restart

 


Essentially the resolution of this form of the error is to change the data-file-field-separator to a different value - for example 0x01 (START OF HEADING)

Attachments

    Outcomes