oracle数据库中,用SQL Select直接讀取openoffice ods數據

最新推荐文章于 2021-04-05 03:13:31 发布

mikean

最新推荐文章于 2021-04-05 03:13:31 发布

阅读量703

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

54 篇文章 0 订阅

订阅专栏

来至http://blog.chinaunix.net/uid-173640-id-4187684.html

之前有用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 ,
col0 celltype,
col1 celltype,
col2 celltype,
col3 celltype,
col4 celltype,
col5 celltype,
col6 celltype,
col7 celltype,
col8 celltype,
col9 celltype,
col10 celltype,
col11 celltype,
col12 celltype,
col13 celltype,
col14 celltype,
col15 celltype,
col16 celltype,
col17 celltype,
col18 celltype,
col19 celltype
);
type xls_table is table of xls_row ;
function get_xml (p_blob in blob, p_file_name in varchar2) return xmltype ;
function get_ods_table(p_data in blob) return xls_table pipelined ;
function get_ods_table2(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;
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"
';
-- for openoffice ods
/*
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_xml(p_blob in blob, p_file_name varchar2) return xmltype 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_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 xmltype (l_clob);
end get_xml;
function get_table(p_ods xmltype , idx pls_integer) return xmltype is
xpath_tab constant varchar2(200) :=
'/office:document-content/office:body/office:spreadsheet/table:table['|| idx ||']' ;
begin
if p_ods.existsNode(xpath_tab, ns_ods) > 0 then
return p_ods.extract(xpath_tab, ns_ods);
else
return null;
end if;
end ;
function getStringval(p_node xmltype, p_xpath varchar2, p_ns varchar2 default ns_ods ) return varchar2 is
t xmltype;
begin
t := p_node.extract(p_xpath, p_ns) ;
return case when t is null then null else t.getStringval() end ;
end ;
function getNumberval(p_node xmltype, p_xpath varchar2, p_ns varchar2 default ns_ods ) return Number is
t xmltype;
begin
t := p_node.extract(p_xpath, p_ns) ;
return case when t is null then null else t.getNumberval() end ;
end ;
function get_Cellvalue(p_cell xmltype) return varchar2 is
vdata_type varchar2(20) ;
vdata celltype ;
begin
vdata_type := getStringval(p_cell, '//@office:value-type') ;
if vdata_type = 'string' then
vdata := getStringval(p_cell, '//text:p/node()');
if vdata like '<text:s xmlns:text%' then
vdata := getStringval(xmltype(vdata), '/text:s/text()') ;
end if;
elsif vdata_type = 'float' then
vdata := getStringval(p_cell, '//text:p/node()');
else
vdata := null;
end if;
return vdata ;
end ;
procedure fill_cell(p_row in out xls_row, v_cellval celltype, c pls_integer) is
begin
case
when c=0 then p_row.col0 := v_cellval ;
when c=1 then p_row.col1 := v_cellval ;
when c=2 then p_row.col2 := v_cellval ;
when c=3 then p_row.col3 := v_cellval ;
when c=4 then p_row.col4 := v_cellval ;
when c=5 then p_row.col5 := v_cellval ;
when c=6 then p_row.col6 := v_cellval ;
when c=7 then p_row.col7 := v_cellval ;
when c=8 then p_row.col8 := v_cellval ;
when c=9 then p_row.col9 := v_cellval ;
when c=10 then p_row.col10 := v_cellval ;
when c=11 then p_row.col11 := v_cellval ;
when c=12 then p_row.col12 := v_cellval ;
when c=13 then p_row.col13 := v_cellval ;
when c=14 then p_row.col14 := v_cellval ;
when c=15 then p_row.col15 := v_cellval ;
when c=16 then p_row.col16 := v_cellval ;
when c=17 then p_row.col17 := v_cellval ;
when c=18 then p_row.col18 := v_cellval ;
when c=19 then p_row.col19 := v_cellval ;
end case;
end;
function get_ods_row_value(p_xmlrow xmltype) return xls_row is
c_cell_path constant varchar2(200) :='/table:table-row/table:covered-table-cell |
/table:table-row/table:table-cell';
cursor cur_cell is
select rownum,value(p) data
from table(xmlsequence( extract(p_xmlrow, c_cell_path, ns_ods))) p;
v_row xls_row ;
vrepeated pls_integer ;
v_current_col pls_integer := 1 ;
begin
for r in cur_cell loop
vrepeated := getNumberval(r.data, '//@table:number-columns-repeated') ;
fill_cell(v_row, get_Cellvalue(r.data), v_current_col -1) ;
v_current_col := v_current_col + nvl(vrepeated,1) ;
exit when v_current_col > 20 ; --limit for 20 column
end loop;
return v_row;
end get_ods_row_value;
function get_ods_table(p_data in blob) return xls_table pipelined is
xpath_row varchar2(2000) := '/table:table/table:table-row' ;
v_row xls_row ;
cursor c_2(p_table xmltype)
is
select value(p) row_data
from table(xmlsequence(extract(p_table, xpath_row, ns_ods))) p;
vx_tab ods_table := null;
vx_ods xmltype;
v_rows_repeated pls_integer ;
v_current_row pls_integer ;
begin
vx_ods := get_xml(p_data, 'content.xml') ;
for i in 1..100 loop
vx_tab := get_table(vx_ods, i) ;
exit when vx_tab is null;
v_current_row :=1;
for r2 in c_2(vx_tab) loop
v_rows_repeated := getNumberval(r2.row_data, '/table:table-row/@table:number-rows-repeated');
v_row := get_ods_row_value(r2.row_data) ;
v_row.sheet := i;
v_row.row_idx := v_current_row ;
v_current_row := v_current_row + nvl(v_rows_repeated, 1);
pipe row( v_row) ;
end loop;
end loop;
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, 可站內告知我,十分感謝.

阿飛
2014/03/29