Hi, I have created a Store Procedure to genertae a xml file of Table Data
But In database some table have "extended ASCII codes (character code 128-255)",
when I generate xml file then it shows error "ORA-31061: XDB error: special char to escaped char conversion failed."
so I replace these Char to space but I need that all ASCII codes (character code 128-255) in XML files.
Please help
my Store Procedure has below
create or replace
PROCEDURE Export_project6
(
V_TABLE_NAME1 IN varchar2,
v_FLAG OUT NUMBER
)
AS
BEGIN
----- Export table data
DECLARE
v_file UTL_FILE.file_type;
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
v_FILENAME varchar2(50);
V_TABLE_NAME varchar2(50);
xt_data xmltype;
v_ctx dbms_xmlgen.ctxHandle;
rc_data sys_refcursor;
BEGIN
V_TABLE_NAME := UPPER(V_TABLE_NAME1) ;
v_file := UTL_FILE.fopen('MYXML',V_TABLE_NAME||'.xml', 'W');
OPEN rc_data FOR
'select * FROM '||V_TABLE_NAME||' ORDER BY 1' ;
v_ctx := dbms_xmlgen.newContext (rc_data);
DBMS_XMLGEN.USEITEMTAGSFORCOLL (v_ctx);
DBMS_XMLGEN.SETNULLHANDLING(v_ctx, 1);
DBMS_XMLGEN.setrowsettag(v_ctx,'root');
DBMS_XMLGEN.setrowtag(v_ctx,V_TABLE_NAME );
result:= DBMS_XMLGEN.getXML(v_ctx);
result := REPLACE( result, '<?xml version="1.0"?>','<?xml version="1.0" encoding="UTF-8" standalone ="yes"?>');
-- DBMS_XMLGEN.RESTARTQUERY (v_ctx);
-- xt_data := dbms_xmlgen.getXMLType (v_ctx);
dbms_xslprocessor.clob2file( result, 'MYXML', ''||V_TABLE_NAME||'.xml',1);
dbms_xmlgen.closeContext (v_ctx);
v_FLAG := 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_XMLGEN.closeContext (v_ctx);
v_FLAG := 0;
END ;
END Export_project6;