Inserting Large XML(Can be any huge size) into a Oracle Table Field

, ,
1

Hi All,

I need to insert a huge XML string into a Oracle Table Field.
What would be the best solution for this. At Oracle side what would be the data type and how it will be handle at webMethods.

Thanks in advance…

2

The best solution will depend on the actual size of the “huge” XML document and whether you need to use SQL to operate on the document while it is stored in the table. The decision may also depend on the version of Oracle you are using as the XML features have changed significantly since 9i.

The decision on whether to use a column with a datatype of CLOB vs XMLType is mostly related to whether you want to use Oracle SQL statements to operate on the document while it is stored rather than to retrieve it all into memory and chug through it in your application (e.g. IntegrationServer). Use XMLType if you want to use SQL to retrieve a portion of the larger document from a record or if you want to modify a portion of the document inside the DB.

In Oracle 10g, the default storage mechanism for the xmltype column type was CLOB. In Oracle 11g the default has changed to binary.

This blog post discusses how to create and populate a table using the XMLType and use SQL to query it.

This post on a different blog discusses when you might want to use CLOB vs XMLType column types.

3

Thanks for you reply Mark…

At Oracle, they have decided on datatype. now it is CLOB data type the field which XML(can be any size) needs to be inserted.
and they are using Oracle 10g.

At webMethods, what should be the webMethods fields data type(Input Field Type)… I tried with , Sting, Object and SqlClob types. But something is missing here…not geeting inserted, each time giving some diff errors.
I’m not getting into that point.

At final, my intention is to insert a XML into a CLOB data type field at Oracle.

Please suggest me from webMethods perspective… thank you

Thanks in Advance

4

Please indicate exactly how the JDBC adapter service is set up and how you are passing the XML data to it. What version of IS? What version of the JDBC driver?

5

HI Reamon,

We are Using IS version 7.1.3
JDBC Adapter Version 6.5
Oracle 10g

—> Need to insert huge XML into a Oracle Table field(CLOB), from webMethods.
I could able to insert small amount of data like 2kb size… but while triying to insert a HUGE xml, it is giving error “Data size bigger than max size for this type”

Thanks in advance…

6

Can anyone suggest for the above problem…

Oracle Field DataType = CLOB
webMethods needs to insert Huge(can be any size since CLOB capable of holding 4GB) into this field.

We have a Procedure now which will take input CLOB and intern will insert this CLOB into a Table.

Please Advice, in webMethods perspective…

( One of the solution I came to know, construct a STRUCT type then map it to that CLOB field ---- If so, please advice)

Thanks in advance …

7

Check this link on Oracle Forums.

https://forums.oracle.com/forums/thread.jspa?threadID=2219142

From the above statement looks like you should not be using the setAsString option for the clob field. If needed convert your huge xml to clob object using java service and then map it to the adapter service.

Cheers,
Akshith

8

Hi Akki, Thanks for your reply…

We have created a Java service to insert the data into field… not sure on this java program but still it seems to be not working.

If you can… please provide the code for the java service to do this job. It would really helpful.

When creating Insert Adapter service… In the InputDataTypeField, it is showing 4 datatypes…

  1. String 2) sql.CLOB 3) IO.Stream 4) Object ------------- which one should i select

Please provide me with some more inputs…

Thanks

9

use sql.clob in the adapter service input data type.

Create a Java service to convert a string (XML) to a clob object and then map this clob object to the Adapter service (clob) input.

Share your code to convert string to a clob here and i can try to fix it.

Cheers,
Akshith

10

I do not have an Oracle database to test this code with but no harm in giving it a shot on your end.

If it does not work try to remove the comments tags next to the setString method.

//inputClob.setString(0, inString);
   
 // pipeline
pipelineCursor = pipeline.getCursor();
String    inString = IDataUtil.getString( pipelineCursor, "inString" );
pipelineCursor.destroy();
                
try {
            Connection gConn;
            gConn = DriverManager.getConnection("jdbc:oracle:thin:@hostname:port", "properties","");
                      
            oracle.sql.CLOB inputClob =  new oracle.sql.CLOB((OracleConnection) gConn,inString.getBytes());  
            //inputClob.setString(0, inString);
        
            //String insertSql="Insert xml in to the databse SQL query";
            
            IDataUtil.put( pipelineCursor, "clobObject", inputClob );
            //IDataUtil.put(pipelineCursor, "message", inputClob.toString());            
            
        } catch (SQLException e) {
            e.printStackTrace();
            IDataUtil.put(pipelineCursor, "message", e);
        }

Hope this helps!

11

Is there any way possible to not load the “huge” data as a string? Basically, you’re loading the entire string into memory, which will undoubtedly be an issue at some point in production. If possible, change your integration to reference the data as stream from a disk file of some sort (do not load the data into a string and then “convert” to a stream–that doesn’t resolve the “all in memory” problem). Then pass that stream reference to the JDBC service.

You should not need to use a Java service for this.

12

webMethods IS: 7.1.2, JDBC Adapter:6.5 (with no fixes/patches/upgrades)
Class File : ojdbc14.jar
adapter connection class: oracle.jdbc.pool.OracleDataSource

test flow service :
pub.string.padRight

instring :A, padString:A, length: <varies between 32kb(321024) to 10MB (101024*1024)>
call the insert adapter service for the table created at oracle. Only modification which I did is,
‘input field type’ - from java. sql.clob to java.lang.string.

Oracle:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
Table
created a table with two columns
CREATE TABLE “TEST”.“CLBTST”
( “IKEY” VARCHAR2(4000 BYTE),
“IKEYDATA” CLOB
)

I can insert a simple string from 32Kb to 10MB without any problem into the database table!
Am I missing anything?

13

Thank you PRP, ROB, MARK and AKKI for your’s valuable suggestions.

Now, it got resolved :)… I could able to insert HUGE data into CLOB Oracle Table field from webMethods.

14

Using latest ojdbc14.jar file would solve this issue. This is placed in webMethods7\IntegrationServer\lib\jars folder for establishing connection to Oracle database.
The jar file size is 1533 KB

15

Could you please share the code .

16

Could you please share the code ?