Hello everyone,
I have a problem and I am not even sure what I will be about to ask!
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
There is a code written many years ago and works fine but just got an error (ORA-22922: nonexistent LOB value) after 12c upgrade.
I simplify the code:
create table tmp_xml as
select xmlelement("a", level) as col1
from dual
connect by level < 1000;
declare
x_Result clob;
begin
SELECT xmltype.getclobval(xmlagg(col1))
into x_result
from tmp_xml;
x_Result := '<b> <id>10</id>' || x_Result || '<b>'; -- if you remove this line code will work
dbms_output.put_line('size is: ' || dbms_lob.getlength(x_Result));
end;
/
so there is a table which contains xmltype rows. at the end those rows are aggregated and a constant header is being added with || (concat) and then it is written to a file etc.
now when I run this code I got ORA-22922: nonexistent LOB value and if you remove the line starts with "x_result := '<b>..." it completed an shows output "size is: 9883"
so I thought there might be a problem using concatenation with clob and focused on it. I change the code to use dbms_lob.append but problem was still exists! so I run the select statement and try to see data:
in SqlDeveloper (also other tools):
SELECT xmltype.getclobval(xmlagg(col1))
from tmp_xml;
oracle.sql.CLOB@58a8ad2b
there is no data but (I assume) address of the clob is returned and I can not even get the data! as I said I tried it on other development tools but same result! Only sqlplus fetched some data but it brings only a few character of clob data not whole.
I thought again and decide this might related to development tool but I run this code in a procedure! so there is no development tool interference and even so I got "ORA-22922: nonexistent LOB value" error.
from that point I focused on XMLTYPE.GetClobVal() function. I know that it is deprecated but still documented in 12.2 and can be used! also it was working on 11.2.0.4 too (it was deprecated on that version too). Anyway, I checked about what should I do to get data as clob from xmltype and I saw XMLSerialize function and decided to use it but it raises another error on that structure:
SELECT XMLSerialize(DOCUMENT XMLAGG(COL1) AS CLOB)
from tmp_Xml;
ORA-19012: Cannot convert XML fragment to the required datatype
19012. 00000 - "Cannot convert XML fragment to the required datatype"
*Cause: A conversion to a datatype was requested which cannot be performed
*Action: The XMLType may contain fragments and other elements which cannot be converted to the appropriate datatype.
so XMLSerialize requires a main tag as far as I understand because this is working:
SELECT XMLSerialize(DOCUMENT XMLElement("B", XMLAGG(COL1)) AS CLOB)
from tmp_Xml;
"<B><a>1</a><a>2</a><a>3</a><a>4</a><a>5</a><a>6</a><a>7</a><a>8</a><a>9</a><a.........."
of course you say something about the design of data, why many xmltype data rows are aggregated and then converted to clob and concatenation after that but this is an old proc and there are many lines like this. I might not be able to change all for now.
so my questions:
1- why xmltype.getclobval returns something like oracle.sql.CLOB@58a8ad2b which is an address but not a valid clob!
2- even returning value of getclobval is not valid, when I try to get length of lob using DBMS_LOB.GetLength(X_result), returns correct data (9883). how? is this a valid or not clob
3- I want to change getclobval to XMLSerialize but it requires a main tag, why? by saying why of course you can say, this is the way to work but at least how can I achieve this?
4- is there a way that I can add this constant header (and closing tag to the end) using xmlserialize?
for now, I just add a loop to generate clob as a workaround:
Dbms_Lob.Createtemporary(X_Result, True);
For Rec In (Select Xmlserialize(Document Col1 As Clob) Ll --XMLTYPE.GETCLOBVAL(XMLAGG(XML_ROWS))
From Tmp_Xml)
Loop
Dbms_Lob.Append(X_Result, To_Clob(Rec.Ll));
End Loop;
Return X_Result;
thanks.