Oracle pl/sql解析openoffice ods文檔

之前有用java封裝一個讀取excel 2000的 oracle package
這個是用來讀取 openoffice ods格式的 package
原理是:
1. 用as_zip解壓得到xml
2. 用oracle xmltype讀取每一行數據, pipelined方式返回.

測試代碼如下

select b.*
from table( ods_utl_pkg.get_ods_table(
     utl_lob.url2blob('ftp://172.17.2.43/pub/test/work.ods'))) b

效果圖如下


oracle package: ods_utl_pkg封裝如下

点击(此处)折叠或打开

create or replace package ods_utl_pkg
as
/* ********************************************************************
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2014-3-29   gangjh           1. 用sql select 讀取open office ods數據

********************************************************************/
subtype celltype is varchar2(4000 byte) ; -- limit for oracle 10g
  type xls_row is record (
    sheet   int ,
    row_idx int ,
    col_a  celltype,
    col_b  celltype,
    col_c  celltype,
    col_d  celltype,
    col_e  celltype,
    col_f  celltype,
    col_g  celltype,
    col_h  celltype,
    col_i  celltype,
    col_j  celltype,
    col_k  celltype,
    col_l  celltype,
    col_m  celltype,
    col_n  celltype,
    col_o  celltype,
    col_p  celltype,
    col_q  celltype,
    col_r  celltype,
    col_s  celltype,
    col_t  celltype
  );

type xls_table is table of xls_row ;


/**********************************
 @get_xmldoc   --抓取ods 壓縮檔中的xml內容
*******************************/
function get_xmldoc (p_zipped_blob in blob, p_file_name in varchar2) return clob ;

/***********************************************
@get_ods_table   -- Table函數, 讀取open office ods中的內容, 以xmldom的方式實現
 ex:
  select * from table(
         ods_utl_pkg.get_ods_table(utl_lob.get_blob('MAILTEMP','pcn2.ods')))
*********************************************************/
function get_ods_table(p_data in blob) return xls_table  pipelined;

end ods_utl_pkg;


/
create or replace package body ods_utl_pkg
as

subtype  ods_table is xmltype;
type t_hash_str is table of varchar2(2000) index by varchar2(256);

type t_cell is record (
  col_idx       pls_integer,
  value_type varchar2(256), -- @office:value-type
  office_value  celltype, -- @office:value | @office:date-value | node()
  spanned       pls_integer, -- @table:number-columns-spanned
  repeated      pls_integer, -- @table:number-columns-repeated
  raw_data varchar2(32767)
);


  ns_ods constant varchar2(32767) := '
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"
';

 crlf constant varchar2(1) := chr(10) ;


procedure print(m varchar2) is
begin
  dbms_output.put_line(m);
end;


procedure print_xml(node XMLDOM.DOMNode) is
  m clob ;
begin
  dbms_lob.createtemporary(m, true, dbms_lob.call);
  XMLDOM.writeToClob(node, m);
  dbms_output.put_line(dbms_lob.substr(m,32767, 1));
end;

function get_ns(p_name varchar2) return varchar2 is
begin
   return case p_name
           when 'xmlns:all' then ns_ods
           when 'xmlns:office' then 'xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" '
           when 'xmlns:text' then 'xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" '
           when 'xmlns:table' then 'xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" '
           when 'xmlns:number' then 'xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" '
           else ns_ods
      end;

end;

-- for openoffice ods
/*
 get_xml 取得ods中的xml文件
 大文件xml可能會使xmltype報ora 24030錯誤
ex:
 select ods_utl_pkg.get_xml(utl_lob.url2blob('ftp://172.17.2.43/pub/test/work.ods'),'content.xml') aa
from dual;
*/

function get_xmldoc(p_zipped_blob in blob, p_file_name varchar2) return clob is
  l_clob clob;
  l_blob blob ;
  dest_offset integer := 1;
  src_offset integer := 1;
  lang_context integer := 0;
  warning integer :=0;
begin

  l_blob := as_zip.get_file (p_zipped_blob, p_file_name);
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call) ;
  dbms_lob.convertToclob( l_clob
                          , l_blob
                          , dbms_lob.lobmaxsize
                          , dest_offset
                          , src_offset
                          , nls_charset_id( 'AL32UTF8' )
                          , lang_context
                          , warning
                          );

  return (l_clob);
end get_xmldoc;


function all_attribute(p_node in XMLDOM.DOMNode) return t_hash_str is
  v_ret t_hash_str ;
  l_attr XMLDOM.domnamednodemap;
  l_node XMLDOM.domnode;
BEGIN
  l_attr := XMLDOM.getAttributes(p_node);

  for i in 0.. XMLDOM.getLength(l_attr) -1 loop
     l_node := XMLDOM.item(l_attr, i);
     v_ret(XMLDOM.getNodeName(l_node)) := XMLDOM.getNodeValue(l_node);
--     print( XMLDOM.getNodeName(l_node)||':'||XMLDOM.getNodeValue(l_node));
  end loop;
  return v_ret;
end ;



function get_xmlnode_value(l_node XMLDOM.DOMNode) return varchar2 is
  vret celltype ;

/****************************************
get_xmldom_text  --遞歸調用,取xmlnode的文字內容
VALUE_ERROR  Exception when long varchar2
**************************************/
  procedure  get_xmldom_text(p_node XMLDOM.DOMNode, p_dest in out varchar2) is
    p1 XMLDOM.DOMNode;
  begin
    if XMLDOM.getNodeType(p_node) = XMLDOM.ELEMENT_NODE then
      p1 := XMLDOM.getFirstChild(p_node) ;
      while not XMLDOM.isNull(p1) loop
         get_xmldom_text(p1, p_dest) ;
         p1 := XMLDOM.getNextSibling(p1) ;
      end loop;
    elsif   XMLDOM.getNodeType(p_node) = XMLDOM.TEXT_NODE then

      p_dest := p_dest || XMLDOM.getNodeValue(p_node) ;
      if XMLDOM.getNodeName(p_node) = '#text' then
        p_dest := p_dest || crlf ;
      end if;
    end if;

    exception
      when VALUE_ERROR then --可能超過4000 byte
        null;
  end get_xmldom_text;
begin
  get_xmldom_text(l_node, vret);
  return vret;
end;

/**********************************
@get_ods_table -- 以xmldom的方式實現
**********************************/

function get_ods_table(p_data in blob) return xls_table  PIPELINED is
  xpath_table  constant varchar2(2000) := '/office:document-content/office:body/office:spreadsheet/table:table';

  v_row     xls_row ;
  doc       XMLDOM.domdocument;
  l_tables  XMLDOM.DOMNodeList;
  l_table   XMLDOM.DOMNode;
  l_rows    XMLDOM.DOMNodeList;
  l_row     XMLDOM.DOMNode;


function xsl_valueOf(n xmldom.DOMNode, pattern varchar2,
                     namespace IN VARCHAR2 default ns_ods) return VARCHAR2 IS
begin
  return xslprocessor.valueOf(n, pattern, namespace);
end;

/********************************
@fill_cell -- 根據col_idx 將數據填入xls_row對應的欄位中
*************************/
 procedure fill_cell(p_row in out xls_row, l_val t_cell) is
 begin
   case  l_val.col_idx
      when 1 then p_row.col_a := l_val.office_value ;
      when 2 then p_row.col_b := l_val.office_value ;
      when 3 then p_row.col_c := l_val.office_value ;
      when 4 then p_row.col_d := l_val.office_value ;
      when 5 then p_row.col_e := l_val.office_value ;
      when 6 then p_row.col_f := l_val.office_value ;
      when 7 then p_row.col_g := l_val.office_value ;
      when 8 then p_row.col_h := l_val.office_value ;
      when 9 then p_row.col_i := l_val.office_value ;
      when 10 then p_row.col_j := l_val.office_value ;
      when 11 then p_row.col_k := l_val.office_value ;
      when 12 then p_row.col_l := l_val.office_value ;
      when 13 then p_row.col_m := l_val.office_value ;
      when 14 then p_row.col_n := l_val.office_value ;
      when 15 then p_row.col_o := l_val.office_value ;
      when 16 then p_row.col_p := l_val.office_value ;
      when 17 then p_row.col_q := l_val.office_value ;
      when 18 then p_row.col_r := l_val.office_value ;
      when 19 then p_row.col_s := l_val.office_value ;
      when 20 then p_row.col_t := l_val.office_value ;
      else
        null;
    end case;
 end;


/**********************************************************
@get_ods_row_value -- 根據xpath_table取得xml中的某一行數據
*********************************************************/

  function get_ods_row_value(p_xmlrow XMLDOM.DOMNode) return xls_row is
    c_cell_path constant varchar2(200) :='table:covered-table-cell | table:table-cell';
    v_row   xls_row ;

    v_cell   t_cell;
    l_cells  XMLDOM.DOMNodeList;
    l_cell   XMLDOM.DOMNode;

  begin
      l_cells := xslprocessor.selectNodes(p_xmlrow, c_cell_path, ns_ods);
      v_cell.col_idx := 1;

      for r in 0 .. XMLDOM.getlength( l_cells ) - 1 loop
          l_cell := XMLDOM.item(l_cells, r);

          v_cell.value_type := xsl_valueOf(l_cell, '@office:value-type');
          v_cell.repeated := xsl_valueOf(l_cell, '@table:number-columns-repeated');
          v_cell.spanned := xsl_valueOf(l_cell, '@table:number-columns-spanned');

          v_cell.office_value :=
          case  v_cell.value_type
             when 'string' then get_xmlnode_value(l_cell)
             when 'float' then xsl_valueOf(l_cell, '@office:value')
             when 'date' then xsl_valueOf(l_cell, '@office:date-value')
          end;

          fill_cell(v_row, v_cell ) ;
          v_cell.col_idx := v_cell.col_idx + nvl(v_cell.repeated , 1) ;
      end loop;
      return v_row;
  end get_ods_row_value;

begin
    if dbms_lob.substr( p_data, 4, 1 ) <> hextoraw( '504B0304' ) then -- 504B0304: zip file
       raise_application_error(-20013, 'blob is not zip file');
    end if;

    doc := XMLDOM.newDOMDocument(get_xmldoc(p_data, 'content.xml'));
    l_tables := xslprocessor.selectNodes( XMLDOM.makeNode( XMLDOM.getDocumentElement(doc) ) , xpath_table, ns_ods);

    for r1 in 0 .. XMLDOM.getlength( l_tables ) - 1 loop
        l_table := XMLDOM.item(l_tables, r1);

        l_rows := xslprocessor.selectNodes(l_table, 'table:table-row', ns_ods);

        for r2 in 0 .. XMLDOM.getlength( l_rows ) - 1  loop
            l_row := XMLDOM.item(l_rows, r2);

            v_row := get_ods_row_value( l_row ) ;
            v_row.sheet := r1 + 1;
            v_row.row_idx := r2 + 1;
            pipe row( v_row) ;
        end loop;

    end loop;

    XMLDOM.freeDocument(doc);
    return ;

end get_ods_table;


end ods_utl_pkg;
/

ods_utl_pkg需要as_zip 包來解壓blob數據.

as_zip可由網上取得http://technology.amis.nl/wp-content/uploads/images/as_zip.txt

讀取基本的數據是沒有問題的.
如使用中遇到bug, 可站內告知我,十分感謝.

2015/09/04 更新

                         阿飛
               2014/03/29