Hi all,
I am writing a stored procedure to collect response data (stored in a data queue) into a "RESPONSES" document... Because I need to pass back as many responses as I can at one time, I'm trying to build my output as CLOB(2G)... However, I'm not catching on to how to append my message entries into the output CLOB... Here's my first (or second) stab at this... Following that is the error message I get when I run...
I guess I need to read up on "LOB LOCATOR"...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE TESTED.GETMQOUT
(OUT OUT_MESSAGES CLOB( 2G ) )
LANGUAGE SQL
SET OPTION DBGVIEW = *SOURCE
BEGIN
DECLARE DQNAME CHAR(10);
DECLARE DQLIB CHAR(10);
DECLARE DQRCDLN DECIMAL(5);
DECLARE DQDATA CLOB(64K);
DECLARE DQWAIT DECIMAL(5);
DECLARE NODATA INT;
DECLARE OUTSIZE BIGINT;
SET DQNAME = 'BTQUE_OUT';
SET DQLIB = 'BTSGDEV';
SET DQWAIT = 1;
SET NODATA = 0;
SET OUT_MESSAGES = '<RESPONSES>';
SET OUTSIZE = 11;
WHILE NODATA = 0 AND OUTSIZE < 2000000000 DO
SET DQDATA = '';
SET DQRCDLN = 0;
CALL QSYS.QRCVDTAQ (DQNAME, DQLIB, DQRCDLN, DQDATA, DQWAIT);
IF DQRCDLN > 0
THEN SET OUT_MESSAGES = OUT_MESSAGES concat
'<RESPONSE>' concat left(DQDATA, int(DQRCDLN)) concat '</RESPONSE>';
SET OUTSIZE = OUTSIZE + DQRCDLN + 21;
ELSE SET NODATA = 1;
END IF;
END WHILE;
SET OUT_MESSAGES = OUT_MESSAGES concat '</RESPONSES>';
END;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
call TESTED.GETMQOUT ( ? )
SQL State: 0F001
Vendor Code: -423
Message: [SQL0423] Locator *N not valid. Cause . . . . . : The value of locator *N is not currently valid. A LOB or XML locator may have been freed by a previous FREE LOCATOR statement or a COMMIT or ROLLBACK. A result set locator may have been freed by a CLOSE statement or a COMMIT or ROLLBACK. An ALLOCATE CURSOR statement cannot be run for a result set locator value that has already had a cursor allocated. Recovery . . . : Ensure that the locator value refers to an active locator that has not been freed because of a FREE LOCATOR, CLOSE, COMMIT, or ROLLBACK statement. A LOB or XML value can be assigned to a locator variable using a SELECT INTO statement, a VALUES INTO or SET statement, a GET DESCRIPTOR statement, or a FETCH statement. A value can be assigned to a result set locator variable using an ASSOCIATE LOCATORS statement or a DESCRIBE PROCEDURE statement.
Thanks in advance for any ideas...
-Eric DeLong
As an Amazon Associate we earn from qualifying purchases.