Insert XML content from BLS to oracle table

[Former Member]

Hi,

Is there any way to send XML content from BLS to Oracle table. I tried with FixedQueryWithOuput. It works, but it has limitation, it cannot take more than 4000 character , even though the param is configured as clob in oracle database side?

[Former Member]

Sajjad

This is not issue from xMII-side itself, instead this is an Oracle issue itself. This has some limitation in manipulating data in Database.

Managing CLOBs and BLOBs in JDBC is not very simple when compared to managing small amount of data. The way the CLOB and the BLOB objects are created and managed is different than a VARCHAR(String) column. In fact, Oracle extension classes are provided to support these types objects in JDBC like oracle.sql.CLOB, oracle.sql.BLOB etc.

The new enhancements in Oracle JDBC 10g promises to simplify the insertion and retrieval techniques for the CLOBs using the standard APIs. This document enumerates how easily the CLOB data can be handled in JDBC, by harnessing the new features of the Oracle JDBC 10g driver

Anyway, to more get info on Oracle problems u may read <a href="http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html">this</a>

Regards

Som

[Former Member]

Thanks for the information. Currently i am using 11.5 XMII and the database i use is 10g. As i understand by reading the URL you sent me, seems like oracle resloved this issue with their new driver. How can in corporate this into my current enviornment.

[salvatore_castro]

There are plenty of links you can search for to load a new JDBC driver into xMII. Remember "Google is your friend" and will help you find your way. Don't forget that once the driver is loaded to change the classpath in the Data Server to use the class.

-Sam

[Former Member]

Hi SAM,

Instead of googling, I think SAP should provide the guideline to reslove this driver issue. Even before 10g it was possible to manupulate CLOB data over jdbc using differnet approach. I guess XMII api did not adopt that approach.

It will be greatly appreicate if SAP can provide the Guildeline

[salvatore_castro]

Sajjad,

Funny you should mention that it's an xMII issue, just goes to show how much you actually understand what's going on. It's a JDBC Driver issue.

-Sam

[Former Member]

Maybe i don't anything. You know everything. You are master of JDBC and XMII.

Thats is why i am asking help from SAP, to reslove this issue. For me this is a another XMII issue out of many issues. Please read the paragraph carefully.

"Prior to Oracle JDBC 10g, to manipulate the CLOB data in JDBC, Oracle extension class oracle.sql.CLOB was used. But now, Oracle JDBC 10g has a few enhancements that simplifies the CLOB manipulation in JDBC applications. This enables handling of large data using some of the available standard APIs, instead of using the Oracle extension classes. These could be thought as shortcuts for inserting and retrieving CLOB data from the database.

"

As a user, i don't need to know what is going on underneath the JDBC. All i want to insert data which is more than 4000 byte long and it does not work in XMII.

Instead of arguing and stating offending statement, you should say this is a limitation of XMII.

[salvatore_castro]

Sajjad,

Once again this is a JDBC issue and the resolution can be found via Google. It is not an xMII issue so please stop saying this it is "USER ERROR" and the same probably goes for the "other" issues you are claiming. Do not take your inexperience out on the product.

As for large data inserts you can find out how to do this via Google. I will not help someone who can't help themselves. "Give a man a fish he'll eat for a day, teach a man to fish and he'll eat forever".

-Sam

[Former Member]

Sajjad

One simple suggestion i can provide.

Suppose ur XML Content contains Columns : <b>C1, C2, C3</b>

In ur current Oracle DB, create a table with three Fields defining those columns C1, C2 & C3 with DataType nVarChar or nText or something like textual object.

Then in BLS,

1. use Query to insert the contents (Data Values from XML) instead of inserting whole XML

2. use Repeater to loop the XML Content coming from ur transaction or watever any source, and get the {Rowsets/Rowset/Row/Item} in XPath target to be used as Parameters in that Query for insertion

Hope this will suit ur requirement

Regards

Som

[Former Member]

Your statement eplains your atitude. And please. do not replay back of my question. And if i offended you, than i am sorry. That was not my intention.

[Former Member]

Shafi,

xMII <u>utilizes</u> the Oracle JDBC driver. I repeat: <i>xMII <u>utilizes</u> the Oracle JDBC driver</i> just to make sure it comes across clear.

Hence it is not an xMII limitation but the driver limitation. I believe Sam suggested Google as there are others who have found workarounds for the driver limitation.

Just to make it easier i have even gone the distance of searching Google for you; simply click here for what the web has to offer: <a href="http://www.google.com/search?hl=en&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=oracle4000character+blob&spell=1">Google Results</a>

No one is here to offend anyone else. But we are also not here to spoon feed people and provide mundane answers for simple questions.

[salvatore_castro]

Alin,

Do you think he'll mind if I reply back to you??

Anyway, I like your post especially since the answer to his question can be found in the search results.

-Sam