Hi,
DB Version: 11.2.0.2.0
Apps Version: 12.1.3
We are trying to generate an xml file in a directory in the server through pl/sql program. Below is the code we are using. But this is giving an error like "failed due to ORA-01422: exact fetch returns more than requested number of rows". When we put the select query in a cursor and try to write the data one by one in the loop, it is writing only one record. Please advise if we are making any mistake.
---Code giving error
CREATE OR REPLACE PROCEDURE TEST_XML_CREATE(errbuf OUT VARCHAR2
,errcode OUT NUMBER)
IS
l_file_name VARCHAR2 (30);
l_file_path VARCHAR2 (200);
l_xmldoc CLOB;
BEGIN
l_file_path := 'FTPPOST_OUT_GTI_AUDIT';
l_file_name := 'TEST_XREF4.xml';
SELECT
XMLElement("financialorganization:LegalEntityList",
xmlattributes('http://schema.bppost1.be/entities/financial/financialorganization/v001' "xmlns:financialorganization",
'http://schema.bppost1.be/entities/base/basetypes/v001' "xmlns:basetype",
'http://www.w3.org/2001/XMLSchema-instance' "xmlns:xsi"),
xmlelement("financialorganization:LegalEntity",
xmlelement("financialorganization:LegalEntityCode", fv.flex_value),
xmlelement("financialorganization:Description", fv.description),
xmlelement("basetype:ValidityPeriod",
xmlelement("basetype:OpenEndedPeriod",
xmlelement("basetype:StartDate", TO_CHAR(start_date_active,'YYYY-MM-DD')),
xmlelement("basetype:EndDate", TO_CHAR(end_date_active, 'YYYY-MM-DD'))
)
)
)
).getClobVal()
INTO l_xmldoc
FROM
fnd_flex_value_sets fvs,
fnd_flex_values_vl fv
WHERE 1 = 1
-- to be replaced by value set name parameter
AND fvs.flex_value_set_name = 'iPROMIS_POST_GL_AFK01_ENTITEIT'
AND fvs.flex_value_set_id = fv.flex_value_set_id
AND fv.summary_flag = 'N';
dbms_xslprocessor.clob2file(l_xmldoc, l_file_path, l_file_name, nls_charset_id('UTF8'));
END;
/
The XML format we want to generate is below
============================================
<financialorganization:LegalEntityList
xmlns:financialorganization="http://schema.bppost1.be/entities/financial/financialorganization/v001"
xmlns:basetype="http://schema.bppost1.be/entities/base/basetypes/v001"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<financialorganization:LegalEntity>
<financialorganization:LegalEntityCode>12345</financialorganization:LegalEntityCode>
<financialorganization:Description>Test Description</financialorganization:Description>
<basetype:ValidityPeriod>
<basetype:OpenEndedPeriod>
<basetype:StartDate>1900-01-01</basetype:StartDate>
</basetype:OpenEndedPeriod>
</basetype:ValidityPeriod>
</financialorganization:LegalEntity>
<financialorganization:LegalEntity>
<financialorganization:LegalEntityCode>54321</financialorganization:LegalEntityCode>
<financialorganization:Description>Test Description1</financialorganization:Description>
<basetype:ValidityPeriod>
<basetype:OpenEndedPeriod>
<basetype:StartDate>1900-01-01</basetype:StartDate>
</basetype:OpenEndedPeriod>
</basetype:ValidityPeriod>
</financialorganization:LegalEntity>
</financialorganization:LegalEntityList>
Regards,
Krushna