xml文件导入oracle

针对xml文件导入oracle做了实验测试，数据库为oracle11g，步骤如下：

1、创建测试xml文件test.XML

<?xml version = '1.0'?>


100
aaa


101
bbb


102
ccc222

2、创建测试表

create table TEST
(
USER_ID NUMBER(10),
USER_NAME VARCHAR2(30)
);

3、创建路径 XML_DIR

create directory XML_DIR as 'F:\xmltest';

4、执行过程体

DECLARE
  QRYCTX DBMS_XMLGEN.CTXHANDLE;
  dst_loc CLOB;
  SAVCTX DBMS_XMLSTORE.CTXTYPE;
  N      NUMBER;
  src_loc    bfile := bfilename('XML_DIR', TEST.XML');
  amt        number := dbms_lob.lobmaxsize;
  src_offset number := 1;
  dst_offset number := 1;
  lang_ctx   number := dbms_lob.default_lang_ctx;
  warning    number;
  v_exists   PLS_INTEGER := 0;
BEGIN
  DBMS_LOB.CREATETEMPORARY(dst_loc, true);

  dbms_lob.fileopen(src_loc, dbms_lob.file_readonly);
  v_exists := DBMS_LOB.fileexists(src_loc);
----  用于测试文件名以及路径是否正确
  IF v_exists = 0 THEN
    dbms_output.put_line('FILE NOT EXIST');
  ELSE
    dbms_output.put_line('FILE AND DIR VALID');
  END IF;
----将数据从OS文件加载到CLOB变量-------
  dbms_lob.LOADCLOBFROMFILE(dst_loc,
                            src_loc,
                            amt,
                            dst_offset,
                            src_offset,
                            dbms_lob.default_csid,
                            lang_ctx,
                            warning);
  dbms_output.put_line(' Amount specified ' || amt);
  dbms_output.put_line(' Number of bytes read from source: ' ||
                       (src_offset - 1));
  dbms_output.put_line(' Number of characters written to destination: ' ||
                       (dst_offset - 1));
  if (warning = dbms_lob.warn_inconvertible_char) then
    dbms_output.put_line('Warning: Inconvertible character');
  end if;
  dbms_lob.filecloseall();
  DBMS_OUTPUT.PUT_LINE(dst_loc);
  --开始设置加载数据的列
  SAVCTX := DBMS_XMLSTORE.NEWCONTEXT('TEST');
  DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(SAVCTX);
  DBMS_XMLSTORE.SETUPDATECOLUMN(SAVCTX, 'USER_ID');
  DBMS_XMLSTORE.SETUPDATECOLUMN(SAVCTX, 'USER_NAME');
  N := DBMS_XMLSTORE.INSERTXML(SAVCTX, dst_loc);
  DBMS_XMLSTORE.CLOSECONTEXT(SAVCTX);
END;

/

commit;

5、查看结果

select * from TEST t

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7836777/viewspace-722839/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7836777/viewspace-722839/