Skip to Content
0
Aug 22, 2011 at 09:53 AM

Save Large XML Data to ORACLE database (CLOB)

976 Views

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.