DECLARE 
 p_max_size NUMBER := dbms_lob.lobmaxsize; 
 src_offset NUMBER := 1; 
 dst_offset NUMBER := 1; 
 lang_ctx   NUMBER := nls_charset_id('UTF8'); 
 default_csid CONSTANT INTEGER := nls_charset_id('ZHS16GBK'); 
 warning     NUMBER; 
 l_file_number   PLS_INTEGER := 0; 
 l_count     NUMBER; 
 l_bfile     BFILE; 
 l_clob      CLOB; 
 l_commitelement xmldom.domelement; 
 l_parser    dbms_xmlparser.parser; 
 l_doc       dbms_xmldom.domdocument; 
 l_nl      dbms_xmldom.domnodelist; 
 l_n       dbms_xmldom.domnode; 
 rootnode    dbms_xmldom.domnode; 
 parent_rootnode dbms_xmldom.domnode; 
 file_length   NUMBER; 
 block_size    BINARY_INTEGER; 
 l_rootnode_name VARCHAR2(200); 
 l_status    VARCHAR2(1000); 
 l_recerrcode  VARCHAR2(1000); 
 l_FailCount   VARCHAR2(200); 
 l_RecCount    VARCHAR2(200); 
 l_name      VARCHAR2(1000); 
 l_comments    VARCHAR2(2000); 
 l_exists    BOOLEAN; 
  
 FUNCTION convertclobtoxmlelement(p_document IN CLOB) 
   RETURN xmldom.domelement IS 
   x_commitelement xmldom.domelement; 
   l_parser    xmlparser.parser; 
 BEGIN 
   l_parser := xmlparser.newparser; 
   xmlparser.parseclob(l_parser, p_document); 
   x_commitelement := xmldom.getdocumentelement(xmlparser.getdocument(l_parser)); 
   RETURN x_commitelement; 
 END convertclobtoxmlelement; 
 BEGIN 
 -- 检查XML是否在路径FTP_XXX下是否存在 
 utl_file.fgetattr('FTP_XXX', 
           'simanhe_test.xml', 
           l_exists, 
           file_length, 
           block_size); 
  
 IF NOT l_exists THEN 
   dbms_output.put_line('XML文件不存在'); 
   RETURN; 
 END IF; 
  
 l_bfile := bfilename('FTP_XXX', 'simanhe_test.xml'); 
 -- 创建一个Clob 
 dbms_lob.createtemporary(l_clob, TRUE); 
 dbms_lob.OPEN(l_bfile, dbms_lob.lob_readonly); 
 -- 将XML文件上载并转换为Clob类型 
 dbms_lob.loadclobfromfile(l_clob, 
               l_bfile, 
               p_max_size, 
               dst_offset, 
               src_offset,  
               default_csid, -- UTF8 
               lang_ctx,   -- GBK 
               warning); 
 l_file_number := dbms_lob.fileexists(l_bfile); 
 IF l_file_number = 0 THEN 
   dbms_output.put_line('XML文件未被转换成功'); 
   RETURN; 
 END IF; 
 dbms_lob.CLOSE(l_bfile); 
 -- Create a parser. 
 l_parser := dbms_xmlparser.newparser; 
 BEGIN 
   -- Parse the document and create a new DOM document. 
   dbms_xmlparser.parseclob(l_parser, l_clob); 
 EXCEPTION 
   WHEN OTHERS THEN 
   dbms_output.put_line('XML文件不完整'); 
   RETURN; 
 END; 
 l_doc := dbms_xmlparser.getdocument(l_parser); 
 -- Free resources associated with the CLOB and Parser now they are no longer needed. 
 dbms_lob.freetemporary(