UPDATE_INSERT Error

[Former Member]

Hi Experts,

Need urgent help.

Right now i am using UPDATE_INSERT for my Receiver JDBC Adapter. But i am getting error in communication channel monitoring that : *ORA-00933: SQL command not properly ended *

and

in oracle if i am trying to run the below command :

*update_insert ol_account

set user_login_id = 'hetal'

where user_unique_id = '750124783';*

Then i am getting error that invalid comman. Can anyone tell me that y it is happening.

Its bit urgent so please reply soon.

The scema is something like this :

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

- <ns0:Modify_OL_Account xmlns:ns0="http://reliant.com/xi/EmailSolicitation">

- <statement>

- <OL_ACCOUNT action="UPDATE_INSERT">

<table>OL_ACCOUNT</table>

- <access>

<USER_UNIQUE_ID>0000294551</USER_UNIQUE_ID>

<CPDB_UPDATE_DATE hasQuot="No">04/01/2008 16:18:24</CPDB_UPDATE_DATE>

</access>

- <key>

<USER_UNIQUE_ID>0000294551</USER_UNIQUE_ID>

</key>

</OL_ACCOUNT>

</statement>

</ns0:Modify_OL_Account>

Thanks in advance.

Regards,

Hetal

[moorthy]

Hi,

Go thru this SAP help to understand the Query syntax and data structure used for the same.

http://help.sap.com/saphelp_nw70/helpdata/EN/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

Moorthy

[Former Member]

Hi,

this error is due to

ORA-00933: SQL command not properly ended

Cause:

You tried to execute an SQL statement with an inappropriate clause.

Refer this link:

http://www.techonthenet.com/oracle/errors/ora00933.php

http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm

Regards,

Nithiyanandam

[aashish_sinha]

Hi Beena,

UPDATE_INSERT

The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

The response document has the following format; one of the two values is always 0 because either an UPDATE or an INSERT action is always executed:

<update_count>count</update_count>

<insert_count>count</insert_count>

You can use it like this :

<root>

<StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName1>

Hope this will help you.

Regards

Aashish Sinha

PS : reward points if helpful

[Former Member]

Hi,

1st thing that you can't use the below statement directly in Oracle

update_insert ol_account set user_login_id = 'hetal'

where user_unique_id = '750124783';

The Meaning of update_insert is it will check as per the Key elements in the given table. If the record is available then it will UPDATE that record and will execute UPDATE record else it will INSERT new record.

So in ORACLE you need to try either Insert / Update......(further query as it is)

Now ORA-00933: SQL command not properly ended

Cause: The SQL statement ends with an inappropriate clause.

Are you sure that the in JDBC structure you have provided, you will be passing all the mandatory fields if in case the record is to be Inserted.

Action: Correct the syntax by removing the inappropriate clauses. Try to execute the query as below in Oracle and see if there is any syntax error.

insert into table <field Name1, Field2 ....> VALUES ('XYZ', value1, value2....). -- Thus while inserting you need to pass all fields

update ol_account set user_login_id = 'hetal' where user_unique_id = '750124783'

refer

http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm

Thanks

Swarup