SAP PI java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

[Former Member]

Hi everybody,

We experienced the following error while sending Idoc over JDBC link to Oracle Tables. The error message seen on Xi-Communication Monitor is here-

"Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification : Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification"

Pl Note - 1) In the above,Oracle Table Name is-'TERP_EMP_ADDRESS_MAS'. I am using SAP PI 7.01 version for Idoc Transfer

Pl provide solution for the above

Hanumanth S

[Former Member]

Dear Hanumanth

Enable DB logs on Receiver CC to get the place where you are making a mistake. then the CC audit logs will give you the query being formed.

All the column names in the table along with case should be same in the Receiver DT.

Regards

Monika

[Former Member]

Dear Monika

Pl speecify How to access DB logs/Audit Logs in Receiver Communication Channel to Identify SQL Query being formulated for JDBC Call to Oracle Tables

Thank You

Hanumanth S

[deepak_shah]

Hi,

check this link below to enable logs in receiver JDBC channel

http://www.saptechnical.com/Tips/XI/SQL/Log.htm

-Deepak

[Former Member]

Hi,

I have eanbled SQL Logging in Comm Channel but the Audit Log is not showing SQL String for JDBC call.Pl see the Audit Log below & advise

29.03.2012 12:07:51

Error

Unable to execute statement for table or stored procedure. 'TERP_EMP_ADDRESS_MAS' (Structure 'Statement') due to java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

[Former Member]

after applying the above setting in receiver JDBC comm channel save it and activate it, then rerun the scneario and see if the audit log of the new msg in comm channel monitoring shows the SQL statement.

[former_member184681]

Hi,

My two cents, in addition to what RAJEEV GUPTA has already mentioned: you will not be able to see the SQL statement for the messages that were already processed. After switching on the logging, you have to execute a test again and check the Audit Log of this new test message. Additionally, you might need to perform a cache refresh in sxi_cache after changing the channel's properties.

Hope this helps,
Greg

[Former Member]

Hi,,

I have chosen default 'Message Protocol':XML SQL for JDBC & performed suggested steps. I am not able to find SQL String formed in Audit Log. Pl see the error log-

Unable to execute statement for table or stored procedure. 'TERP_EMP_ADDRESS_MAS' (Structure 'Statement') due to java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification.

MP: Exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification : Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification : Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification.

[Former Member]

Dear Hanumanth

I hope you have enabled logging in Receiver CC now. Please paste the extract here, so that we can revert.

Regards

Monika

[Former Member]

Hi Monika,

I have chosen default 'Message Protocol':XML SQL for JDBC & performed suggested steps. I am not able to find SQL String formed in Audit Log. Pl see the error log-

Unable to execute statement for table or stored procedure. 'TERP_EMP_ADDRESS_MAS' (Structure 'Statement') due to java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification.

MP: Exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification : Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification : Error processing request in sax parser: Error when executing statement for table/stored proc. 'TERP_EMP_ADDRESS_MAS' (structure 'Statement'): java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

[baskar_gopalakrishnan2]

JDBC Adapter converts your structure elements into the appropriate  query during runtime to invoke database call.  Check your Access and Key tag elements and see those field elements are available in the table.

[Former Member]

in reciever JDBC msg structure, check if the column names are the columns in the table. also check the tablename getting sent in target msg to database.

[Former Member]

ORA-01747 exception usually occurs when you specify wrong cloumn name...cross chk the filed name (created in PI for JDBC receiver) with the DB table column name

[former_member184681]

Hi Hanumanth,

Most probably you are using an incorrect field (column) name for the database table TERP_EMP_ADDRESS_MAS. Review the target structure values against typo mistakes and compare it to the table structure in the database.

Hope this helps,
Greg

[Former Member]

Hi Greg,

I have checked Table Fields/Structures in oracle.It is Okay.& also the 'Message Mapping Test' is successful in Designer.But still the problem persists.Pl advise

Hanumanth S

[former_member184681]

I wouldn't expect any mapping error, since your problem is located in the receiver communication channel (so it's later in message processing by PI). Definitely focus on the target message payload, versus database table structure. You could try changing the message payload to an SQL statement manually and executing it directly in the DBMS server - maybe you will get a more precise error description.

Regards,
Greg

[baskar_gopalakrishnan2]

Provide your target database structure if that is feasible to you. We will see whether any syntax issues in the target structure.

One tip:

Though oracle side the column names are not case sensitive. I always name  target structure key elements, access tag elements and table names uppercase.