Return as XML for BLOB in Oracle

[former_member223432]

Hi Guys,

I have a requirement to insert the payload as an xml in the database, So that  all records in one go.

I am using "return as XML" functionlity and passing this to one of the field of a table.

At the target side, i have created a table in Oracle  with 3 columns--INTERFACE.,XML(type BLOB), DATE. and i need to pass the payload to XML field of a table.

but when i run this interface i am getting wierd errors.

"JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'XML_LOAD' (structure 'StatementName'): java.sql.SQLException: ORA-00936: missing expression "

here are the details i am passing:

Action: INSERT

Table: XML_LOAD

Is there something which i am missing?

Regards

[Former Member · Accepted solution]

Hi Smith,

Please check -

http://scn.sap.com/thread/1090551

Regards,

Amol Bharti

[former_member223432 · Accepted solution]

Thanks Amol

[Former Member]

Have you tried to perform the same action, with an empty value in blog field?

Some times, due to blob image field dimension, jdbc operations have some issues (specially in abap application).

The workaround for this issue, is to use a DB Cursor with Fetch command, or an oracle StoredProcedure.

However, the "missing expression" error, indicate that a required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

[former_member223432]

Thanks for your reply simone...

where can i see this select statement? i am just INSERTing the payload to database and i have not used any select statement here.

IF i need to write a stored procedue,..how do i write? could you please help me with the code if you can.

your help is really appreciate.

thanks

[baskar_gopalakrishnan2]

>where can i see this select statement? i am just INSERTing the payload to database and i have not used any select statement here.

You r inserting here. Your target structure converted to statement during runtime by JDBC adapter. Use logSQLStatement = true in the advaced tab of the jdbc reciever channel. THis will provide log traces during runtime.

The problem is the insert statement generated is wrong here. It does not specify table name properly or statement is not valid one.

[former_member223432]

Hi Bhaskar,

I have set up a logSQLstament = TRUE in advanced table and also..please find the document attached, where i am passing XML_LOAD = table name and INSERT = ACTION

Am i missing something here?

Regards

[Former Member]

Try with "PACKAGE.TabName" (eg. MYDB.XML_LOAD ) to setup constant Table's Name

[former_member223432]

Hi Simone,

I tried even that option, but its stil failing.

here is log from AE.

"INSERT into XML_LOAD(PAYLOAD,INTERFACE_ID) VALUES(<?XML VERSION="1.0" encoding ="UTF-8"?

could not execute the statement for table/strored proc."XML_LOAD"(structure"statementName") due to javaSQLException ORA-00936:missing expressions.

can some one please give valuable suggestions.

thanks

[Former Member]

first all use replace string function and try to remove xml declaration "<?XML VERSION="1.0" encoding ="UTF-8"?" from xml string and then chk....

i have implemented the same req and used SP (few years back) for the same where SP parsed the input xml string and performed the necessary operations on the database......i am not a database expert but i guess SP option seems to be more useful one...

[former_member223432]

Hi Amit,

What does SP stands for? do you really think XML version..... is creating this problem here..because, i could see another interface which was developed by others have direct mapping without any other logic.

thanks

[baskar_gopalakrishnan2]

Amit is right. Yes I see the problem.  In the insert statement   .... into Values ("  ");  The value should be only string. Here you see that escape characters and inside " "quotes for xml causing this trouble. You can use some UDF to remove the first line. After filtering test the same.

[Former Member]

Hi Smith,

>>What does SP stands for

Stored procedure(SP)....

i remember that i have done the similar thing...like replaced the xml declaration and passed the string xml to the Stoted procedures...please give a try and let us know the outcome

[former_member223432]

Hi,

Any valuable suggestion please.

thanks

[former_member223432]

Hello,

DO you have any sample code for this in SP please?

your help would really be appreciated.,

thanks

[Former Member]

Hi Smith,

Please take help form ur database team..i am sure they will be knowing abt this...

(as alread stated)i am not a DB expert but i can tell u that u have to use a combination of xml_preparedocument along with OPENXML to create a SP which which suits ur design. Chk below links this will help u in creating SP which will parse the input xml string:

http://msdn.microsoft.com/en-us/library/ms187367.aspx

http://msdn.microsoft.com/en-us/library/ms186918.aspx

[Former Member]

Hi Smith,

Baskar is right, Take a look to string value that you put in PAYLOAD field.

The result SQL command, need to be something like this:

INSERT into XML_LOAD(PAYLOAD,INTERFACE_ID) VALUES('<field1></field1>...','X')