ORA-01704: string literal too long

[Former Member]

Hi, I'm trying to write large IDOC XMLs into oracle DB. Whole IDOC XML I'm writing into one field which is of type CLOB.

In some cases I can not to do it because of above message. What can be done in this case to avoid this problem. Oracle says:

ORA-01704:

string literal too long

Cause: The string literal is longer than 4000 characters.

Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

So this means that I have to use PreparedStatement and binded variables, how secure is it and is there any SAP std. solution for this problem?

thx

mario

[Former Member]

Hi Mario,

ORA-01704: string literal too long

With PL/SQL, you can populate a CLOB with max value of 32,767. But since you are executing a SQL statement, the max value of varchar2 is 4,000.

On your DB side, you will have to change your code to make it a PL/SQL program and declare the CLOB accordingly. Then it will work fine.

@ Sunil Singh

Solution is Simple either you reduce the length of that field in source side or increase the length of data type in the target. 

I don't think the solution is that simple If the Business requirement is to get the contents of the CLOB, then you can't truncate it and you will have to find a way around for that.

Also, it has nothing to do with the length of the DT in the target.

Regards,

Neetesh

[Former Member]

Yes, I think there is no simple solution, there is one blog on SDN about PreparedStatement, but this is never mentioned by SAP, so this is a problem form me. Blog is [ PreparedStatement|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/16870] [original link is broken] [original link is broken] [original link is broken];

[Former Member]

ORACLE website can give you some hint about how to handle this :

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/HowToLoadLargeXML.html

The question is : are those classes available (or useable) within XI/PI in a mapping object ... Otherwise, a custome module may be needed to bypass this limitation

Rgds

Chris

[Former Member]

I used parameter on Advanced Tab of com.chanel sqlBindMode and settled it to true. It works but is nowhere in SAP documentation, question is why?!

[sunil_singh13]

Hi Mario,

Solution is Simple either you reduce the length of that field in source side or increase the length of data type in the target.

Thanks,

Sunil