PL/SQL: numeric or value error%s in loop

Question

I have a function with a loop inside a loop and I'm getting 'numeric or value error'.

sendXML CLOB;

 FOR p IN (
      SELECT ID, NAME, GUID FROM products
      WHERE ID = IN_PROJECT_ID
      )
    LOOP

    if p.ID is not null and p.NAME is not null then

     sendXML := sendXML || '<product type="product" id="' || p.ID|| '" name="' || p.NAME || '">';

     FOR t IN (
      SELECT
        identifier ATTR_IDENTIFIER, 
        label ATTR_LABEL, 
      CASE type
        WHEN UPPER('STRING') THEN TRIM(string_value)
        WHEN UPPER('NUMBER') THEN TRIM(TO_CHAR(number_value))
      ELSE '' END ATTR_VALUE
      FROM products_data
      WHERE 
        product_id = p.ID AND
        identifier is not null
      ORDER BY identifier
      )

    LOOP

      sendXML := sendXML || '<attribute identifier="' || t.ATTR_IDENTIFIER || '" label="'|| t.ATTR_LABEL || '">' || t.ATTR_VALUE || '</attribute>';        

    END LOOP;

   END IF;

END LOOP;

The error

ORA-06502: PL/SQL:numeric or value error ORA-06512: at "ASM.XXXX", line 85 06502
06502. 00000 - "PL/SQL: numeric or value error%s"

throws for the line:

 sendXML := sendXML || '<product type="product" id="' || p.ID|| '" name="' || p.NAME || '">';

But I found out that if I delete the last

sendXML := sendXML || '<attribute identifier="' || t.ATTR_IDENTIFIER || '" label="'|| t.ATTR_LABEL || '">' || t.ATTR_VALUE || '</attribute>';

I'm not getting any error.

Wheres the problem?

SOLUTION:

p.ID is integer and must be char... TO_CHAR(p.ID) solved my problem!

sendXML := sendXML || '<product type="product" id="' || TO_CHAR(p.ID) ||

What is the specific error message? Ora-6502 has several different subtypes. — Klas Lindbäck
ORA-06502: PL/SQL: numerischer oder Wertefehler ORA-06512: in "ASM.XXXX", Zeile 85 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action: — frgtv10
Would have been nice to know which subtype the %s expands to... — Klas Lindbäck
Thank you for your help, I added the solution to my question. — frgtv10

Klas Lindbäck Klas Lindbäck · Accepted Answer · 2013-10-02T10:33:39

My guess is that the string becomes larger than the maximum for varchar2 in PL/SQL.

Try the following to append text to a clob:

 dbms_lob.append(sendXML, to_clob('<product type="product" id="' || p.ID|| '" name="' || p.NAME || '">'));

and the second one:

 dbms_lob.append(sendXML, to_clob('<attribute identifier="' || t.ATTR_IDENTIFIER || '" label="'|| t.ATTR_LABEL || '">' || t.ATTR_VALUE || '</attribute>'));

PL/SQL: numeric or value error%s in loop

2 Answers