XI cannot access oracle databse
A IDOC to JDBC scenario is created in the XI. The database is SQL Server 2000. The adapters are IDOC and JDBC. The scenario is working fine.
Next, the existing database (tables+data) in SQL Server 2000 is transferred to an Oracle Database. The JDBC communication channel is changed to access the corresponding table in the Oracle databse. i.e. driver, url, userid, password etc and classes12.jar is imported for the scenario.
The scenario can create connection to the database successfully but cannot recognize the table attributes. i.e. delete from tableName is working fine but insert into tablename(col1, col2 ..)values(val1, val2) is not working. The error generated is a given below,
Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. <tableName> (structure 'STATEMENTNAME'): java.sql.SQLException: FATAL ERROR: Column <columnName> does not exist in table <tableName>
The table attributes in the Oracle database are in CAPITAL letter while the corresponding dataType node is in SMALL letter. i.e. EMPNO in database and empno in dataType. Surprisingly, If I change the dataType node to CAPITAL letter then it works.
The SQL should not be case sensitive.
Some advice on why it is happening and the remedy is urgently needed.
thanks and regards,
Rana Brata De