Create big xml on pl/sql

Question

I tried to create my xml-text:

SELECT XMLELEMENT(
    "firms",
    XMLAGG(
        XMLELEMENT(
            "firm",c
            XMLELEMENT("FirmID", wo.id),
            XMLELEMENT("Name", substr(wo.namerus, 1, 120)),
            XMLELEMENT("ShortName", wo.code)           
        )
    )
)
INTO my_var
FROM organs wo
WHERE type_id = 5;

dbms_output.put_line(my_var.GetClobVal());

But then I got an error :

ORA-06502: PL/SQL: numeric or value error

I know that my data is correct because if I doubled the first column three or more times I got this error again. How can i create a big xml?

my_var XMLType; Clob - Do you mean to create a long string into a clob variable? — Darya, Dec 15 '18 at 14:09
CLOB *should* be large enough. Maybe it is DBMS_OUTPUT.PUT_LINE that is throwing an error, not SELECT itself. If that's so - so what? Don't *output* it, but do whatever you meant to do with it. — Littlefoot, Dec 15 '18 at 15:24

score 0 · Answer 1 · answered Dec 15 '18 at 19:35

0

It is very likely that the error arises not when you create the XML, but when you try to print it :

dbms_output.put_line(my_var.GetClobVal());

There are limitations to dbms_output.put_line that cause issues when processing large data : namely, clobs larger than 32k do fail to be printed this way, with error ORA-06502.

If you don’t actually need to print the clob, just comment out that line and you should be fine.

Else, have a look at this StackOverflow post, that provides a solution based on a procedure that iterates through the clob and prints out one smaller chunk at a time.

answered Dec 15 '18 at 19:35

GMB

216,147
25
84
135

No... a number of error string shows to my select, not to output. – Darya Dec 16 '18 at 11:10
@Darya ok then can you pls post your whole script, including variable declaration, and the full error message, including line number ? – GMB Dec 16 '18 at 11:19
Also did you actually try to comment the line that prints the variable ? – GMB Dec 16 '18 at 11:20

Create big xml on pl/sql

1 Answers1