My select statement fails with Error :
ORA-19011 Character string buffer too small
The select statement looks like :
SELECT TO_CLOB(
XMLELEMENT("Accounts",
XMLELEMENT("Account",
XMLATTRIBUTES(
rownum AS "recordId",
TO_DATE('20130520','YYYYMMDD') AS "datestarted",
123456 AS "previousBatchId",
56789 AS "previousRecordId"
),
....
.... .
.....
XMLFOREST(
SIG_ROLE AS "SignatoryRole",
TO_CHAR(TRANSFER_DATE,'YYYY-MM-DD') AS "TransferDate",
NVL(REASON,0) AS "Reason"
) AS "Transfer"
)
) )AS CRDTRPT
FROM ANY_TABLE a;
- seems like i can select only 4000 characters using SELECT statement(please correct me if I am wrong)
I would have used XMLGEN package. But the environment team says no mounted drives from now on with onset of EXADATA.
NO MOUNTED DRIVES , NO ACCESS TO DATABASE DIRECTORIES
no UTL_FILE
I Need to use SPOOL to spool the resultant XML data from the SELECT Query.
SQL is standard in my org but I can do with PL/SQL solution also to load data into a table(can't use SPOOL with PL/SQL)
What I am doing is :
- from the above SELECT query loading a CLOB type column of a table xml_report
- Then using SELECT * FROM xml_report to SPOOL the data to a file report.xml
No need of XMLTYPE indented data. Stream of xml data is fine for me.
Further I need to Validate the XML File also using XSD.
Problem is the resultant rows from the select query are expected to be 15000 to 20000 bytes long.
Oracle Database Version is : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Any Suggestion or workaround with this problem would be appreciated.
(Sorry for using BOLD, just to make it more readable and highlight the imp points)
Cheers !!
Rahul