SQL & PL/SQL

Hi All,

I am experiencing a data corruption issue with PL/SQL procedures. Here is the procedure I used.

PROCEDURE XML_UPDATE (TABLENAME IN VARCHAR2, REC IN VARCHAR2, XMLREC IN CLOB, INSERTFLAG IN OUT NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'UPDATE '|| TABLENAME ||
' SET XMLRECORD = XMLTYPE(:bXMLREC, NULL, 1, 1) WHERE RECID = :bREC'
USING XMLREC, REC;
IF SQL%ROWCOUNT = 0 THEN
EXECUTE IMMEDIATE 'INSERT INTO '|| TABLENAME ||
' VALUES(:bREC, XMLTYPE(:bXMLREC, NULL, 1, 1))'
USING REC, XMLREC;
INSERTFLAG := 1;
ELSE
INSERTFLAG := 0;
END IF;
END;


The table always has two columns RECID - a primary key and XMLREC - a XMLTYPE column, obviously contains XML document.

The above procedure has four arguments, ie table name, RECID, XMLREC and insert flag. RECID is varchar2 type; XMLREC is CLOB type, but the binding variable could be varchar2 if the data length less than 4000 bytes, otherwise application will create a temp lob and write data into the lob, then bind the lob locator to XMLREC when calling the procedure; When the procedure is called at the first time, insertflag is 0 by default, it could be changed in return value;


When the data is written into the table, sometimes the XMLREC column gets corrupted, the example data is like this,

SQL>SELECT t.xmlrec.getclobval() from TAB t WHERE recid = 'TEST';
T.XMLREC.GETCLOBVAL()
--------------------------------------------------------------------------------
roX @??

SQL> select dump(to_char(t.xmlrecord.getclobval()), 1016) from TAB t where recid='TEST';

DUMP(TO_CHAR(T.XMLRECORD.GETCLOBVAL()),1016)
--------------------------------------------------------------------------------
Typ=1 Len=32 CharacterSet=WE8ISO8859P1: 0,0,0,1,10,72,f3,58,0,0,0,0,0,0,0,0,0,0,
0,1,10,40,88,f8,0,9,4,1,0,1f,0,0


I also turned on the SQL_TRACE to capture what exact data being passed into the procedure, I got the trace like ( this is not the corrupted record),

BEGIN JBASEXML.XML_UPDATE('TAB', :REC, :XMLRECORD, :INSERTFLAG); END;
END OF STMT
PARSE #3:c=0,e=227,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1187548989960206
BINDS #3:
kkscoacd
Bind#0
oacdty=01 mxl=2000(200) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=01 csi=873 siz=2000 off=0
kxsbbbfp=2a97391f80 bln=2000 avl=05 flg=05
value="test2"
Bind#1
oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=01 csi=873 siz=4000 off=0
kxsbbbfp=2a9738f398 bln=4000 avl=4000 flg=05
value="<row id='test2'><c1>test2</c1><c2></c2></row>"...
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a97392b50 bln=22 avl=01 flg=05
value=0
=====================
PARSING IN CURSOR #4 len=77 dep=1 uid=61 oct=6 lid=61 tim=1187548989963182 hv=1810678167 ad='a673e3f0'
UPDATE CLT1 SET XMLRECORD = XMLTYPE(:bXMLREC, NULL, 1, 1) WHERE RECID = :bREC
END OF STMT
PARSE #4:c=1000,e=421,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1187548989963170
BINDS #4:
kkscoacd
Bind#0
oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=206001 frm=01 csi=873 siz=4000 off=0
kxsbbbfp=2a97403060 bln=4000 avl=40 flg=05
value=
Dump of memory from 0x0000002A97403060 to 0x0000002A97403088
2A97403060 01002600 03800802 00000200 01DD0015 [.&..............]
2A97403070 01000000 1A006903 01000000 00000000 [.....i..........]
2A97403080 3DB231EC 5B23F382 [.1.=..#[]
Bind#1
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=2a9724e0d8 bln=32 avl=05 flg=09
value="test2"
EXEC #4:c=6998,e=6916,p=0,cr=38,cu=0,mis=1,r=0,dep=1,og=1,tim=1187548989970233
=====================
=====================
PARSING IN CURSOR #8 len=61 dep=1 uid=61 oct=2 lid=61 tim=1187548989970772 hv=595299715 ad='a671cdc0'
INSERT INTO CLT1 VALUES(:bREC, XMLTYPE(:bXMLREC, NULL, 1, 1))
END OF STMT
PARSE #8:c=1000,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1187548989970765
BINDS #8:
kkscoacd
Bind#0
oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=873 siz=32 off=0
kxsbbbfp=2a9724e0d8 bln=32 avl=05 flg=09
value="test2"
Bind#1
oacdty=112 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=206001 frm=01 csi=873 siz=4000 off=0
kxsbbbfp=2a973fd7c0 bln=4000 avl=40 flg=05
value=
Dump of memory from 0x0000002A973FD7C0 to 0x0000002A973FD7E8
2A973FD7C0 01002600 03800802 00000200 01DD0015 [.&..............]
2A973FD7D0 01000000 1A006903 01000000 00000000 [.....i..........]
2A973FD7E0 3DB231EC 5B23F382 [.1.=..#[]

above trace we can see in BINDS #3: bind#1 the buffer address is 2a9738f398, and the data is correct; but in BINDS #4: bind#0 the buffer address is 2a97403060. I think they should be the same buffer. But as I said this record is ok in the table, not corrupted.

Can anybody have any idea why the data is corrupted? and why the bind value showing differently in trace?

Many thanks,
Charles