JDBC Receiver adapter-java.sql.SQLException: ORA-00904: "BATCH_ID": invalid

[Former Member]

Hi

I am trying insert rows in Oracle database using JDBC receiver adapter setup in PI 7.1. However this receiver adapter is throwing following exception.

Delivering the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'PRADEEP_OTC_ORDERS' (structure 'statement'): java.sql.SQLException: ORA-00904: "BATCH_ID": invalid identifier .

<?xml version="1.0" encoding="UTF-8" ?>

<ns0:mt_sql_writer xmlns:ns0="http://rdbms_poc">

<statement>

<dbtablename action="INSERT">

<table>PRADEEP_OTC_ORDERS</table>

<access>

<order_id>19126</order_id>

<version>1</version>

<batch_id>132</batch_id>

</access>

</dbtablename>

</statement>

</ns0:mt_sql_writer>

PRADEEP_OTC_ORDERS table has just 3 columns as specified the access segment. However I stil get this ORA-00904 error message.

Any idea on what could be wrong.?

Thanks

-Pradeep

[dharamveer_gaur2 · Accepted solution]

Hi Pradeep,

Please check below points

1. Yours XML structure showing column name like batch_id but at database using field like mps_batch_id, check PI Column name must be same as database column name ...and create column name in Caps in PI

2. While mapping you are passing table name.....try <Database Name>.<TableName>

[Former Member]

Hi All,

I was successfully able to insert rows after converting columns names in XML payload to upper case as below. I don't know why this (column name in access segment must be all upper case) is not clearly mentioned in SAP documentation. Anyway thanks a lot to everyone who responding to this thread.

- <ns0:mt_sql_writer xmlns:ns0="http://rdbms_poc">

- <statement>

- <dbtablename action="INSERT">

<table>PRADEEP_OTC_ORDERS</table>

- <access>

<MPS_ORDER_ID>19126</MPS_ORDER_ID>

<MPS_VERSION>1</MPS_VERSION>

<MPS_BATCH_ID>132</MPS_BATCH_ID>

</access>

</dbtablename>

</statement>

</ns0:mt_sql_writer>

[Former Member]

Hi All,

Thanks a lot for responding...

I verified the table structure, table just contains those 3 coulms all varchar type.

I modified the column name as well, But was still getting ORA-00904. Then I went directly to the the database and executed below insert statement,

insert into PRADEEP_OTC_ORDERS (mps_order_id, mps_version,mps_batch_id) Values(1,2,3)

I got exact SQL Error: ORA-00904: "MPS_BATCH_ID": invalid identifier.

So ISAP JDBC adapter is not enclosing the string values in quotes.

SAP documentation says it puts quotes for values and treats everything as string(text).

Anybody has any idea, why JDBC receiver adapter not enclosing the values in quotes?

Thanks

-Pradeep

[Former Member]

As you are using VARACHAR type columns, you have to treat the numbers are string. Use the following command:

INSERT INTO PRADEEP_OTC_ORDERS (mps_order_id, mps_version,mps_batch_id) VALUES ('1', '2', '3');

regards,

Roshni[.|http://srinivasapipeindustries.weebly.com/]

[Former Member]

"Version" is a reserved word as defined by ORACLE.

Take a look at this link for the list of reserved words:

http://www.uwex.edu/infosys/oraprod/reserved/

Hence, when you try to insert value into "Version" column, this oracle DB error is thrown.

So, rename the column name and then try inserting the values. It will work. For renaming the column, you may use the following command:

alter table <tablename> rename column <old_column_name> to <new_column_name>;

[Former Member]

Hi Pradeep,

When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid. The "invalid identifier" most common happens when you are referencing an invalid alias in a select statement.

To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid:

• begin with a letter

• be less than or equal to thirty characters

• consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them

Regds,

Pinangshuk.

[vkaushik82]

Hi Pradeep,

if you see oracle point of view this error occurs when the column name entered is either missing or invalid.

Can you please check database col and yours is matching or not.

In SAP 7.1 now we have feature of pulling table structure try using it.

Regards,

Vikrant