之前有用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
阅读(910) | 评论(0) | 转发(0) |