I wrote a scalar function which uses systools.base64decode and transforms a base64 string into a xml field.
My function is doing the job, but after x calls, I get one of these messages "Space offset is outside current limit for object MYJOBNAME JOBUSER JOBNUMBER" or "SQ20398 XML parsing failed" (I'm sure that my input field allways contain valid xml data).
I could analyze the problem a little bit and I saw that when the first call a little base64 string sends (length 60748)
and the second one is much bigger (length 3603584), it (mostly) occurs.
Here is my source:
Code: Select all
Create OR REPLACE Function b64toxml (b64 clob (20m) ccsid 1208)
Returns xml
Language SQL
Specific b64toxml
Deterministic
modifies SQL Data
Called on NULL Input
Disallow Parallel
Set Option ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE, COMMIT = *NONE, DBGVIEW = *SOURCE
begin
declare xmlot xml;
declare xLEN dec(9);
declare xPOS integer;
declare blob1 BLOB(2k);
declare blob2 BLOB(20M) ;
declare x varchar(4096) ccsid 1208;
SET xPOS = 1;
SET xLEN = LENGTH(b64);
MYLOOP: LOOP
set x = SUBSTR(b64, xPos, 1024);
SET blob1 = cast(SYSTOOLS.BASE64DECODE(x) as blob (2k));
IF xPOS = 1 THEN
SET blob2 = blob1;
ELSE
SET blob2 = blob2 CONCAT blob1;
END IF;
SET xPOS = xPOS + 1024;
IF xPOS > xLEN THEN
LEAVE MYLOOP;
END IF;
END LOOP;
VALUES(XMLPARSE(DOCUMENT blob2)) INTO XMLOT;
return xmlot;
end;