XMLTYPE column larger THAN 4000 bytes => ORA-19011

When you try to convert an xml file, which has a tag that contains for example one or more pdf files, into an XMLTYPE object, you will get the following error:
ORA-19011: CHARACTER string BUFFER too small

This error will be thrown because the content of a tag of an XMLTYPE is limited to 4000 bytes(this problem should be solved in 11g).
I have developed a solution/workaround for this.

In the code bellow we will handle an xml file which contains some <file>pdf data</file>tags.
I will get the content of these pdf files out of the xml file and put it in the temporary t_file table.
I will also replace the content in the corresponding tag by a number that corresponds with the counter of the table where I put the value of the pdf in.
So, for example <file>this is pdf content></file>  will become <file>1</file>
Later on, when I would like to handle this xml file and his content, I can get the corresponding pdf file out of my table using
v_clob := t_table(i);–where i would be 1 in this example

While creating this code I also found some (in my opinion) weird things in oracle:
watch out if you use dbms_lob.substr, because this doesn’t work the same way as the substr we normally use!

The position and length are switched(see bellow)!
I believe its even better not to use the dbms_lob.substr at all because it cannot handle as large information as the normal substr….
SUBSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS  PLS_INTEGER,                — starting position
LEN  PLS_INTEGER := 2147483647)  — number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

dbms_lob.substr(
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount  IN INTEGER := 32767,
offset  IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

This is a part of the package that I used to get my information of an xml file into  the database with the  content of a pdf file

create or replace
package body handle_xml
IS
TYPE r_file IS RECORD(ID   NUMBER,
FILE CLOB);
TYPE t_file IS TABLE OF r_file INDEX BY  BINARY_INTEGER;
t_files      t_file;

PROCEDURE split_clob(p_clob IN OUT clob)
IS

v_delim varchar2(10) := ‘File>';–end of the tag where the clob is in
v_count number  := 0;–counter for file id
v_count2 number := 0;

v_pos1 number;
v_pos2 number;
v_pos3 number;

BEGIN
loop
v_count2 := v_count2+1;
select dbms_lob.instr(p_clob,v_delim,1,v_count+1)+5
into v_pos1
from dual;
select dbms_lob.instr(p_clob,v_delim,1,v_count+2)-2
into v_pos2
FROM dual;
v_pos3 :=v_pos2-v_pos1;
dbms_output.put_line(v_pos1||’-‘||v_pos2||’-‘||v_pos3);

EXIT WHEN v_pos3 <= 0;
EXIT WHEN v_pos3 IS NULL;

SELECT trim(substr(p_clob,v_pos1,v_pos3)
INTO t_files(v_count2).file
FROM dual;
–dbms_output.put_line(‘length of the file=’||dbms_lob.getlength(t_files(v_count2).file));

t_files(v_count2).id := v_count2;
p_clob := substr(p_clob,1,v_pos1-1)||v_count2||substr(p_clob,v_pos2);
v_count := v_count+2;
END LOOP;
END;

PROCEDURE handle_xml(p_clob IN OUT CLOB)
IS
v_xml                 XMLTYPE;
BEGIN

split_clob(p_clob);

—-        — FOR i IN t_files.first..t_files.last LOOP
—-        —   dbms_output.put_line(‘length of document’||i||’= ‘||dbms_lob.getlength(t_files(i).file));
—-        — END LOOP;
—-        –dbms_output.put_line(‘length2’||dbms_lob.getlength(p_clob));

v_xml := XMLTYPE.createXML(p_clob);
….

now you have the xml data and the file data seperately, and you can handle the xml file and the document file as you wish
you could for example put the pdf files in a table and link it to the xml data file

…..
END;

END;

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s