String Literal Limitation On Passing XML Doc In Query

[former_member1170711]

I am trying to pass a large XML document to an Oracle PLSQL procedure as a CLOB via an MII SQL Fixed Query.  It works if the file size is relatively small but not for large files.  Beyond a certain point I get a "string literal too long" error.

Here is what my fixed query looks like:

DECLARE

xmlData CLOB;

BEGIN

   xmlData := '[Param.1]';

  -- line below is calling my PLSQL procedure

  insert_from_xml_doc( xmlData, 'tableNameHere');

END

I believe the "string literal too long" error is because of this statement above:

xmlData := '[Param.1]';

It makes sense to me that it would break if that string literal in the single quotes is too long but I don't know of another way to get the data to the CLOB variable.

Does anyone know of a workaround for this limitation?  I am getting the XML data from a machine's log file in the factory and posting it to an MII transaction via the PCo File Monitor capability.  From the transaction I am calling the SQL query.

Thank you,

Mike

[former_member1170711 · Accepted solution]

I eventually found that this thread from 2012:

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

pretty much had the answer.  I was using Oracle 11g and MII V15.  I did not need to make any driver changes.

All I had to do to get this to work was change this line:

xmlData := '[Param.1]';

to this:

xmlData := [Param.1];  -- no single quotes

and, on the Parameters page of my Query Template, I had to check the Use Typed Parameters checkbox and choose the String type for parameter 1.

After making the above changes I was able to pass a string that was over 1 megabyte without a problem.

[former_member185280]

I don't think MII supports true CLOB and BLOB data types so you are hitting the size limitation of the data type its actually using. 4000 characters I think.  Google 'sap xmii clob' or similar and you should be able to find a lot a threads discussing this. I know others have sent xml via queries in the past so if you dig around you may be able to find a solution unless somebody chimes in.  I have been able to read clob data by using functions to pull it out in chunks. You may be able to insert it in chunks. I think this issue is typically solved with a custom action.

Regards,
Christian