oracle怎么执行xml文件

发布网友发布时间：2022-04-23 16:08

共1个回答

热心网友时间：2022-04-08 10:56

create table inputfile (
filename varchar2(30),
xmlcontent  xmltype,
sqlcontent  varchar2(3000)
);

create or replace directory TESTFILE as 'D:\work\oralce\';

create or replace procere alter_table (pi_file_name in varchar2)
is
  filehandle utl_file.file_type;
  filebuffer varchar2(32767);
  l_clob     clob;
  doc        DBMS_XMLDOM.DOMDocument;
  CDATA      VARCHAR2(3000);
begin
  filehandle := utl_file.fopen('TESTFILE',pi_file_name,'R');
  IF utl_file.is_open(filehandle) THEN
    dbms_output.put_line('file is open!');
  END IF;
  loop
    begin
      utl_file.get_line(filehandle,filebuffer);
      dbms_output.put_line(filebuffer);
      l_clob := l_clob||filebuffer;
    EXCEPTION
      WHEN no_data_found THEN
        --dbms_output.put_line('EXCEPTION0:'||SUBSTR(SQLERRM, 1, 100));
        exit;
      WHEN OTHERS THEN
        dbms_output.put_line('EXCEPTION1:'||SUBSTR(SQLERRM, 1, 100));
    end;
  end loop;
   --doc := DBMS_XMLDOM.newDOMDocument(xmltype.extract(XMLType(l_clob),'/sqls/sql'));
  cdata:=xmltype.extract(XMLType(l_clob),'/sqls/sql').getClobVal();
  insert into inputfile(FILENAME, XMLCONTENT,sqlcontent )
  values(pi_file_name, XMLType(l_clob),replace(replace(replace(CDATA,'<sql><![CDATA[--',''),']]></sql>',''),'/',chr(10)||'/'||chr(10)));
  utl_file.fclose(filehandle);
  IF utl_file.is_open(filehandle) THEN
    dbms_output.put_line('file is open!');
  else
    dbms_output.put_line('file is close!');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('EXCEPTION2:'||SUBSTR(SQLERRM, 1, 100)) ;
end;
/

exec alter_table('myTable.xml');

sql语句会去inputfile.sqlcontent.
循环取出值，然后execute immediate(sql);
稍微做些微调即可。