Save Large XML Data to ORACLE database (CLOB)

[former_member192851]

We have stored procedure with parameters, that fill CLOB

something like this:

(
ids number, input raw, append number 
)
as
vlob_loc CLOB;
charcount binary_integer;
position integer := 1;
v_charclob varchar2(32000);
len integer;
begin

/* for test
for i in 1..32000 loop

v_charclob := v_charclob || 'x';

end loop;
*/

select XML_FILE into vlob_loc from MYTABLE where id=ids;
charcount := LENGTH(input);

/*if (append = 0) then
DBMS_LOB.append(vlob_loc,input);
dbms_output.put_line('CLOB Row Appended');
end if;*/

if (append = 1) then
DBMS_LOB.WRITE(vlob_loc,charcount,position,input);
dbms_output.put_line('CLOB Row Write');
end if;

len :=  dbms_lob.getlength(vlob_loc);

if (append = 2) then
DBMS_LOB.erase(vlob_loc,len,1);
dbms_output.put_line('CLOB Row Erased');
end if;

end;

Before this code we create record in table with empty CLOB from Java Mapping with some id. Then we pass id and string in Stored Procedure - but there is limitations on string length.

So - question.

How to send long string to Stored Procedure as Parameter?

And if it is impossible - how to load big XML in CBLOB from SAP XI/PI?

We can split message and send by packets, but this solution - last solution, that we want to use

Format of input.. I think no difference - in any case the limitation will cause exception.

[Former Member · Accepted solution]

We have similar scenario where in we pass whole XML payload as one string as CLOB parameter to Stored procedure.

Solution we opted:-

1. Created one intermediate DT with the fields DB is expecting and we created first level mapping which will fill only DB related entries.

2. Second level mapping - Which convert DB intermediate XML structure to single XML as CLOB using return as XML feature u can search on SDN.

3. Define tyep as CLOB and send it to JDBC using stored procedure.

Thanks

chirag

[former_member192851]

can be helpful

http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/03/28/sap-xipi-storing-binaries-images-...

[former_member192851]

I found another way to load LOB - by using prepare statement.

ps = conn.prepareStatement(sql);
oracle.sql.BLOB newBlob = oracle.sql.BLOB.createTemporary(conn,false, oracle.sql.BLOB.DURATION_SESSION);

newBlob.putBytes(1,docout.toString().getBytes());
ps.setBlob(1,newBlob);
ps.execute();

link - http://www.go4expert.com/forums/showthread.php?t=866

But.. Is this the right way to insert blob data through JDBC or is there is any other better way? What is standart way?

I found another thread -

It is not answered too, but useful.

So, question is - How to load Large XML in Oracle from SAP XI/PI?

Edited by: ChizzT on Aug 23, 2011 12:02 PM

[former_member854360]

The procedure should happily accept strings up to 32k in size (the maximum size of a VARCHAR2 in PL/SQL). If it were a function that was being called from SQL rather than PL/SQL, the limit would be 4k because the maximum size of a VARCHAR2 in SQL is only 4k.

see this

http://stackoverflow.com/questions/4675417/what-is-the-default-size-of-a-varchar2-input-to-oracle-st...

[former_member192851]

So.

And if it is impossible - how to load big XML in CLOB from SAP XI/PI?

Edited by: ChizzT on Aug 22, 2011 12:08 PM

[former_member854360]

Hi,

For CLOB

CLOB Character Large Object 4Gigabytes 8 TB

8 TB to 128 TB

(4 Gigabytes - 1) * (database block size)

http://ss64.com/ora/syntax-datatypes.html

[former_member192851]

It is wonderful and very useful.

But i still have no clue how to load CLOB from SAP XI/PI.