Hi Friends,
I have the below scenerio. The XML file is stored in a table some_table1 as blob datatype.
The error is coming from the - in <EXT_COMMENT>OCTG2016 Stephen Cooker (SA) A 3620MS - SO16-1289
COST CENTER – xxxxxxxx
TO CREDIT YOUR ACCOUNT FOR PRICE ADJUSTMENT ON INVOICE#xxxxx</EXT_COMMENT>
This red dash(-) is chr(14844051) and not the normal chr(45)
select chr(45) t45, chr(14844051) not45 from dual;
The statement in the loop is where things are failing xmltype(rpt)
This is not the complete XML file, the XML file is used to generate oracle report. The package is failing because of this - special character and there could be other special characters. How can this be problem be remedied. We want a permarnent solution to the problem. Please help.
Create table some_table1 (xml_file_id Number
rpt BLOB);
DECLARE
l_xml SYS.XMLTYPE;
BEGIN
FOR i IN ( SELECT *
FROM some_table1
WHERE 1=1
ORDER BY 1 ASC)
LOOP
DBMS_OUTPUT.put_line ('i.some_table1_id ' || i.XML_FILE_id);
SELECT
XMLType ( (rpt),1)
INTO l_xml
FROM some_table1
WHERE xml_file_id = i.XML_FILE_id;
--XMLType(RTRIM(rpt, CHR(0)))
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('other problm');
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (SQLCODE);
END;
/
<INVC05>
<LIST_G_INVC>
<G_INVC>
<INVC_NO>xxxxx</INVC_NO>
<INVC_ID>1111111</INVC_ID>
<STATUS></STATUS>
<BILL_TO_BIZENT_NAME>1776 ENERGY OPERATORS LLC</BILL_TO_BIZENT_NAME>
<EXT_COMMENT>OCTG2016 Stephen Cooker (SA) A 3620MS - SO16-1289
COST CENTER – xxxxxxxx
TO CREDIT YOUR ACCOUNT FOR PRICE ADJUSTMENT ON INVOICE#xxxxx</EXT_COMMENT>
<LIST_G_HDR>
<G_HDR>
<HDR_ID>4395061</HDR_ID>
<LIST_G_HDR_GRP>
<G_HDR_GRP>
<WORK_ORDER_NO></WORK_ORDER_NO>
<PROJECT_NO></PROJECT_NO>
<PLANT_NO></PLANT_NO>
<COMPANY_CODE_NO></COMPANY_CODE_NO>
<LIST_G_LINE_GRP_STTL>
<G_LINE_GRP_STTL>
<SORT_1>0</SORT_1>
<SORT_2>1</SORT_2>
<LINE_GRP_STTL_DESC>Line Sub-Total</LINE_GRP_STTL_DESC>
<LINE_GRP_STTL_FLAG1>1</LINE_GRP_STTL_FLAG1>
<LIST_G_LINE_GRP>
<G_LINE_GRP>
</G_HDR>
</LIST_G_HDR>
</LIST_G_INVC>
<LIST_G_FILENAME>
<G_FILENAME>
<FILENAME></FILENAME>
</G_FILENAME>
</LIST_G_FILENAME>
<CF_CONN_STAMP>xxxxxxxxxxxxxx </CF_CONN_STAMP>
<CF_LOCAL_DTM>15-NOV-2016 01:14 PM</CF_LOCAL_DTM>
<CF_ERROR_MSG></CF_ERROR_MSG>
<CF_SELF_BIZENT>PTPGROUP</CF_SELF_BIZENT>
<CF_RPT_TITLE_STAMP>xxxxxxxxxxxxxxxxxx</CF_RPT_TITLE_STAMP>
<CS_FILENAME>0</CS_FILENAME>
</INVC05>