Hello Gurus,
I have been using Oracle for quite sometime now and know how to generate a csv file in Oracle. This would be first time, I am trying to generate a XML file in Oracle. I have pulled out some information from internet to use DBMS_XMLGEN package. I have tried using this package and trying to use GETXMLTYPE as below
SET SERVEROUTPUT ON
DECLARE
RESULT XMLTYPE;
QRYCTX DBMS_XMLGEN.CTXHANDLE;
v_sql VARCHAR2(1000);
BEGIN
v_sql := ' SELECT * FROM XXYY';
QRYCTX := DBMS_XMLGEN.NEWCONTEXT(v_sql);
DBMS_XMLGEN.SETNULLHANDLING(QRYCTX, DBMS_XMLGEN.EMPTY_TAG);
RESULT := DBMS_XMLGEN.GETXMLTYPE(QRYCTX);
DBMS_OUTPUT.PUT_LINE(RESULT.GETCLOBVAL);
END;
Now the above piece of anonymous block is giving me error as below :
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 33
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
But if I set DBMS_XMLGEN.SETMAXROWS(QRYCTX,100); then it doesn't throw the exception, but there is a <ROWSET> tag after every 100 records. How do I avoid this situation ?
Next I have to generate a hierarchical structure in XML as below
<ROW>
<IDENTIFIER> AAA</IDENTIFIER>
<PRODUCT>BBB</PRODUCT>
<CLASSIFICATION>CCC</CLASSIFICATION>
<MA>
<TITLE>DDD</TITLE>
<INCOME>EEE</INCOME>
<MA_CAD>
<PO_BOX>FFF</PO_BOX>
<TIMEATADDRESS>GGG</TIMEATADDRESS>
</MA_CAD>
<MA_PAD>
<PO_BOX>FFF</PO_BOX>
<TIMEATADDRESS>GGG</TIMEATADDRESS>
</MA_PAD>
<MA_TEL>
<PHONE_NUMBER>HHH</PHONE_NUMBER>
<PHONE_TYPE>III</PHONE_TYPE>
</MA_TEL>
<MA_EMA>
<EMAIL_ADDRESS>JJJ</EMAIL_ADDRESS>
</MA_EMA>
<MA_BNK>
<ACCOUNT_NUMBER>KKK</ACCOUNT_NUMBER>
<SORT_CODE>LLL</SORT_CODE>
</MA_BNK>
</MA>
<JA>
<TITLE>DDD</TITLE>
<INCOME>EEE</INCOME>
<JA_CAD>
<PO_BOX>FFF</PO_BOX>
<TIMEATADDRESS>GGG</TIMEATADDRESS>
</JA_CAD>
<JA_PAD>
<PO_BOX>FFF</PO_BOX>
<TIMEATADDRESS>GGG</TIMEATADDRESS>
</JA_PAD>
<JA_TEL>
<PHONE_NUMBER>HHH</PHONE_NUMBER>
<PHONE_TYPE>III</PHONE_TYPE>
</JA_TEL>
<JA_EMA>
<EMAIL_ADDRESS>JJJ</EMAIL_ADDRESS>
</JA_EMA>
<JA_BNK>
<ACCOUNT_NUMBER>KKK</ACCOUNT_NUMBER>
<SORT_CODE>LLL</SORT_CODE>
</JA_BNK>
</JA>
</ROW>
Please help in how to build XML file of above structure.
Thanks & Regards