Oracle XMLAGG doesn't work when characters are more than 32K

Question

Getting following error while assigning value to declared clob variable in stored procedure. Same thing works when I run query on SQL Developer.

This is the query which currently I'm using in my stored procedure:

SELECT 
    RTRIM(XMLAGG(XMLELEMENT(E, col1, chr(10)).EXTRACT('//text()')).GetClobVal(),',') 
        INTO CLOB_VAR 
FROM Table1**

I don't understand why it causes an error like numeric or value error.

Error report -

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "XXXXXX.TMP_STORED_PROC", line 39
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Stored procedure:

create or replace PROCEDURE TMP_STORE_PROC  () IS
    CLOB_TEXT CLOB;
    start_timestamp TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
    ERR_CODE VARCHAR2(20);
    ERR_MSG VARCHAR2(500);
    BEGIN
        SAVEPOINT startTransaction;

        SELECT RTRIM(XMLAGG(XMLELEMENT(E, col1, chr(10)).EXTRACT('//text()')).GetClobVal(),',') INTO CLOB_TEXT FROM Table1

        DBMS_OUTPUT.PUT_LINE('################ String length -- '||TO_CHAR(CLOB_TEXT));

        COMMIT;
      NULL;
     EXCEPTION 
        WHEN OTHERS THEN
            ERR_CODE := SQLCODE;
            ERR_MSG := SQLERRM;
            DBMS_OUTPUT.PUT_LINE('Stored procedure failed in execution. Error Message : '||ERR_CODE||' -- MSG --  '||ERR_MSG);
            ROLLBACK TO startTransaction;
            RAISE;
    END;

I can't reproduce with some dummy data. Can you include an MCVE demonstrating the issue? Or at least your procedure code. — Alex Poole, Aug 02 '18 at 08:38
Well, I was hoping that would help identify which line 39 actually is, but as that seems to be a small subset of your actual code it doesn't What is `APP_IDS`? If that is the real CLOB variable then that `dbms_output` line could be the real culprit; the message text implies you wanted the length, not the whole string, whatever it is. — Alex Poole, Aug 02 '18 at 09:25
APP_IDS -- typo error. It's CLOB_TEXT Even if I removed the DBMS_OUTPUT, the error remains same. — Ashish Mishra, Aug 02 '18 at 09:55
@AshishMishra I am able to reproduce the same error in 11g database. As mentioned by Alex, removing `DBMS_OUTPUT`, the error has disappeared. — Jacob, Aug 02 '18 at 10:24

Jacob · Accepted Answer · 2018-08-03T08:11:41.607

Replace

DBMS_OUTPUT.PUT_LINE('################ String length -- '||TO_CHAR(CLOB_TEXT));

with the following

DBMS_OUTPUT.put_line ( '################ String length -- ' || length(clob_text));

Procedure

CREATE OR REPLACE PROCEDURE tmp_store_proc
IS
   clob_text         CLOB;
   start_timestamp   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
   err_code          VARCHAR2 (20);
   err_msg           VARCHAR2 (500);
BEGIN
   SAVEPOINT starttransaction;

   SELECT RTRIM (
             XMLAGG (XMLELEMENT (e, col1, CHR (10)).EXTRACT ('//text()')).getclobval (),
             ',')
     INTO clob_text
     FROM table1;


   DBMS_OUTPUT.put_line (
      '################ String length -- ' || LENGTH (clob_text));

   COMMIT;
   NULL;
EXCEPTION
   WHEN OTHERS
   THEN
      err_code := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.put_line (
            'Stored procedure failed in execution. Error Message : '
         || err_code
         || ' -- MSG --  '
         || err_msg);
      ROLLBACK TO starttransaction;
      RAISE;
END;

Execute the procedure by

exec tmp_store_proc

Output

########## String length -- 18224744

If you would like to print CLOB, then have a look at this

Oracle XMLAGG doesn't work when characters are more than 32K

1 Answers1