Oracle读取excel数据_oracle_

 --解析excel，转换成table，可供查询，支持xls、xlsx --首先修改这个Type，长度改为4000. CREATE OR REPLACE TYPE XYG_PUB_DATA_UPLOAD_Obj AS OBJECT( SOURCE_TYPE VARCHAR2(240)--EXCEL/TXT ,BATCH_CODE VARCHAR2 (480 BYTE)--批的Code，Excel用，因为一个Excel可能有多个 ,BATCH_NAME VARCHAR2 (4000 BYTE)--批的名称 ,ROW_NUM NUMBER ,ATTRIBUTE1 VARCHAR2 (4000 BYTE) ,ATTRIBUTE2 VARCHAR2 (4000 BYTE) ,ATTRIBUTE3 VARCHAR2 (4000 BYTE) ,ATTRIBUTE4 VARCHAR2 (4000 BYTE) ,ATTRIBUTE5 VARCHAR2 (4000 BYTE) ,ATTRIBUTE6 VARCHAR2 (4000 BYTE) ,ATTRIBUTE7 VARCHAR2 (4000 BYTE) ,ATTRIBUTE8 VARCHAR2 (4000 BYTE) ,ATTRIBUTE9 VARCHAR2 (4000 BYTE) ,ATTRIBUTE10 VARCHAR2 (4000 BYTE) ,ATTRIBUTE11 VARCHAR2 (4000 BYTE) ,ATTRIBUTE12 VARCHAR2 (4000 BYTE) ,ATTRIBUTE13 VARCHAR2 (4000 BYTE) ,ATTRIBUTE14 VARCHAR2 (4000 BYTE) ,ATTRIBUTE15 VARCHAR2 (4000 BYTE) ,ATTRIBUTE16 VARCHAR2 (4000 BYTE) ,ATTRIBUTE17 VARCHAR2 (4000 BYTE) ,ATTRIBUTE18 VARCHAR2 (4000 BYTE) ,ATTRIBUTE19 VARCHAR2 (4000 BYTE) ,ATTRIBUTE20 VARCHAR2 (4000 BYTE) ,ATTRIBUTE21 VARCHAR2 (4000 BYTE) ,ATTRIBUTE22 VARCHAR2 (4000 BYTE) ,ATTRIBUTE23 VARCHAR2 (4000 BYTE) ,ATTRIBUTE24 VARCHAR2 (4000 BYTE) ,ATTRIBUTE25 VARCHAR2 (4000 BYTE) ,ATTRIBUTE26 VARCHAR2 (4000 BYTE) ,ATTRIBUTE27 VARCHAR2 (4000 BYTE) ,ATTRIBUTE28 VARCHAR2 (4000 BYTE) ,ATTRIBUTE29 VARCHAR2 (4000 BYTE) ,ATTRIBUTE30 VARCHAR2 (4000 BYTE) ,PROCESS_FLAG NUMBER ---0:INIT,3:SUCESSFUL ,PROCESS_MESSAGE VARCHAR2(4000)); 

 create or replace package xyg_pub_data_upload_pkg as /*TYPE XYG_PUB_DATA_UPLOAD_Obj is record( SOURCE_TYPE VARCHAR2(240)--EXCEL/TXT ,BATCH_CODE VARCHAR2 (480 BYTE)--批的Code，Excel用，因为一个Excel可能有多个 ,BATCH_NAME VARCHAR2 (4000 BYTE)--批的名称 ,ROW_NUM NUMBER ,ATTRIBUTE1 VARCHAR2 (4000 BYTE) ,ATTRIBUTE2 VARCHAR2 (4000 BYTE) ,ATTRIBUTE3 VARCHAR2 (4000 BYTE) ,ATTRIBUTE4 VARCHAR2 (4000 BYTE) ,ATTRIBUTE5 VARCHAR2 (4000 BYTE) ,ATTRIBUTE6 VARCHAR2 (4000 BYTE) ,ATTRIBUTE7 VARCHAR2 (4000 BYTE) ,ATTRIBUTE8 VARCHAR2 (4000 BYTE) ,ATTRIBUTE9 VARCHAR2 (4000 BYTE) ,ATTRIBUTE10 VARCHAR2 (4000 BYTE) ,ATTRIBUTE11 VARCHAR2 (4000 BYTE) ,ATTRIBUTE12 VARCHAR2 (4000 BYTE) ,ATTRIBUTE13 VARCHAR2 (4000 BYTE) ,ATTRIBUTE14 VARCHAR2 (4000 BYTE) ,ATTRIBUTE15 VARCHAR2 (4000 BYTE) ,ATTRIBUTE16 VARCHAR2 (4000 BYTE) ,ATTRIBUTE17 VARCHAR2 (4000 BYTE) ,ATTRIBUTE18 VARCHAR2 (4000 BYTE) ,ATTRIBUTE19 VARCHAR2 (4000 BYTE) ,ATTRIBUTE20 VARCHAR2 (4000 BYTE) ,ATTRIBUTE21 VARCHAR2 (4000 BYTE) ,ATTRIBUTE22 VARCHAR2 (4000 BYTE) ,ATTRIBUTE23 VARCHAR2 (4000 BYTE) ,ATTRIBUTE24 VARCHAR2 (4000 BYTE) ,ATTRIBUTE25 VARCHAR2 (4000 BYTE) ,ATTRIBUTE26 VARCHAR2 (4000 BYTE) ,ATTRIBUTE27 VARCHAR2 (4000 BYTE) ,ATTRIBUTE28 VARCHAR2 (4000 BYTE) ,ATTRIBUTE29 VARCHAR2 (4000 BYTE) ,ATTRIBUTE30 VARCHAR2 (4000 BYTE) ,PROCESS_FLAG NUMBER ---0:INIT,3:SUCESSFUL ,PROCESS_MESSAGE VARCHAR2(4000)); */ type xyg_pub_data_upload_obj_tab is table of xyg_pub_data_upload_obj; c_item_return_num constant number := 0; c_item_return_char constant varchar2(1) := null; c_item_err_code constant number := -20120; c_return_num constant number := -1; c_return_char constant varchar2(1) := null; c_true constant number := 1; c_false constant number := 0; function convert_file_blob(p_filedir in varchar2 ---文件路径 /usr/usr/glmr/customer , p_filename in varchar2 ---文件名称 DHS.csv , p_raise in number default /*xyg_pub_const_pkg.*/ c_true) return blob; ---------------------- ------程序主体部分---- ---------------------- function conver_excel_to_tab(p_document blob, p_sheets in varchar2 default null, p_raise in number default /*xyg_pub_const_pkg.*/ c_true) return xyg_pub_data_upload_obj_tab pipelined; end xyg_pub_data_upload_pkg; 包体： create or replace package body xyg_pub_data_upload_pkg as function convert_file_blob(p_filedir in varchar2 ---文件路径 /usr/usr/glmr/customer , p_filename in varchar2 ---文件名称 DHS.csv , p_raise in number default /*xyg_pub_const_pkg.*/ c_true) return blob is l_result blob := empty_blob(); l_filedir varchar2(240); l_files bfile; l_dest_offset binary_integer; l_src_offset binary_integer; l_process_phase number; begin l_process_phase := 0; select directory_name into l_filedir from all_directories where 1 = 1 and (upper(directory_path) = case when substr(p_filedir, -1) = '/' then upper(substr(p_filedir, 1, length(p_filedir) - 1)) else upper(p_filedir) end or upper(directory_path) = upper(p_filedir) or directory_name = p_filedir) and rownum <= 1; --DBMS_OUTPUT.PUT_LINE('L_FILEDIR:'||L_FILEDIR); l_process_phase := 1; l_files := bfilename(l_filedir, p_filename); dbms_lob.createtemporary(lob_loc => l_result, cache => true, dur => dbms_lob.call); l_dest_offset := 1; l_src_offset := 1; dbms_lob.open(l_files, dbms_lob.lob_readonly); dbms_lob.loadblobfromfile(l_result --dest_lob IN OUT NOCOPY BLOB, , l_files --src_lob IN BFILE, , dbms_lob.lobmaxsize --amount IN INTEGER, , l_dest_offset --dest_offset IN INTEGER := 1, , l_src_offset --src_offset IN INTEGER := 1 ); l_process_phase := 2; dbms_lob.close(l_files); l_process_phase := 99; return l_result; exception when others then if p_raise = /*xyg_pub_const_pkg.*/ c_true then dbms_output.put_line('转换文件有异常错误!进度:' || l_process_phase); raise; /*xyg_pub_common_pkg.raise_error('-20001' --'ERR_DEFAULT_CODE' , sqlerrm, '转换文件有异常错误!进度:' || l_process_phase);*/ --DBMS_OUTPUT.PUT_LINE ('THERE ARE SOME ERROR, PLEASE CONTACT WITH MIS'); else --UTL_FILE.FCLOSE (L_FILEHANDLE); return empty_blob(); end if; end; ---------------------- ------程序主体部分---- ---------------------- function conver_excel_to_tab(p_document blob, p_sheets in varchar2 default null, p_raise in number default /*xyg_pub_const_pkg.*/ c_true) return xyg_pub_data_upload_obj_tab pipelined is type tp_cell is record( data_type varchar2(1), string_val varchar2(32767), number_val number, date_val date, blob_val blob); type tp_row is table of tp_cell index by pls_integer; type tp_rows is table of tp_row index by pls_integer; type tp_sheet is record( name varchar2(2000), rows tp_rows); type tp_data is table of tp_sheet index by pls_integer; t_data tp_data; t_collection_base varchar2(32767); t_collection_name varchar2(32767); --type tp_2col is table of vc_arr2 index by pls_integer; --t2 tp_2col; l_round char(1) := 'Y'; l_process_phase number; ---xls: type tp_sheet_rec is record( name varchar2(32767), ind integer); type tp_sheets is table of tp_sheet_rec index by pls_integer; t_sheets tp_sheets; t_sheet tp_sheet_rec; --xlsx: type tp_strings is table of varchar2(32767) index by pls_integer; t_sheet_ids tp_strings; t_sheet_names tp_strings; ----------------- ----XLS解析器---- ----------------- function g1(i pls_integer, r pls_integer, c pls_integer) return varchar2 is l_return varchar2(4000); begin if l_round = 'Y' then l_return := case when t_data(i).rows(r).exists(c) then coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000), to_char(round(t_data(i).rows(r)(c).number_val, 14 - substr(to_char(t_data(i).rows(r)(c) .number_val, 'TME'), -3)), 'TM9'), to_char(t_data(i).rows(r)(c).date_val, 'yyyy-mm-dd hh24:mi:ss')) end; else l_return := case when t_data(i).rows(r).exists(c) then coalesce(substr(t_data(i).rows(r)(c).string_val, 1, 4000), to_char(t_data(i).rows(r)(c).number_val, 'TM9'), to_char(t_data(i).rows(r)(c).date_val, 'yyyy-mm-dd hh24:mi:ss')) end; end if; return l_return; --return case when t_data(i)(r).exists(c) then substr( t_data(i)(r)(c), 1, 4000 ) end; end; function parse_xls(p_document blob, p_sheets varchar2 := null, p_extra dbmsoutput_linesarray := null) return tp_data is t_cell tp_cell; t_rows tp_rows; t_data tp_data; t_workbook blob; t_ind integer; t_sind integer; t_len integer; t_max_len integer; t_cnt integer; t_grbit raw(1); t_biff5 boolean; t_str varchar2(32767); t_tmp raw(32767); t_rec raw(32767); t_date1904 boolean; type tp_sst is table of varchar2(32767) index by pls_integer; t_sst tp_sst; type tp_date is table of boolean index by pls_integer; t_xf_date tp_date; t_fmt_date tp_date; type tp_xf_fmt is table of pls_integer index by pls_integer; t_xf_fmt tp_xf_fmt; t_fmt varchar2(32767); t_char_set varchar2(100) := 'WE8MSWIN1252'; t_c pls_integer; t_type varchar2(1); t_max_c pls_integer; procedure read_unicode_string is t_uni raw(32767); begin t_str := null; while t_cnt > 0 loop if utl_raw.bit_and(t_grbit, hextoraw('01')) = hextoraw('01') then if (t_sind + t_cnt * 2 > utl_raw.length(t_rec) + 1 and dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) = hextoraw('3C00')) then t_str := t_str || utl_i18n.raw_to_char(utl_raw.substr(t_rec, t_sind), 'AL16UTF16LE'); t_cnt := t_cnt - utl_raw.length(utl_raw.substr(t_rec, t_sind)) / 2; t_ind := t_ind + t_len + 4; t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook, 2, t_ind + 2), utl_raw.little_endian); t_grbit := dbms_lob.substr(t_workbook, 1, t_ind + 4); t_rec := dbms_lob.substr(t_workbook, t_len - 1, t_ind + 4 + 1); t_sind := 1; else t_str := t_str || utl_i18n.raw_to_char(utl_raw.substr(t_rec, t_sind, t_cnt * 2), 'AL16UTF16LE'); t_sind := t_sind + t_cnt * 2; t_cnt := 0; end if; else if (t_sind + t_cnt > utl_raw.length(t_rec) + 1 and dbms_lob.substr(t_workbook, 2, t_ind + t_len + 4) = hextoraw('3C00')) then t_tmp := utl_raw.substr(t_rec, t_sind); t_cnt := t_cnt - utl_raw.length(utl_raw.substr(t_rec, t_sind)); t_ind := t_ind + t_len + 4; t_len := utl_raw.cast_to_binary_integer(dbms_lob.substr(t_workbook, 2, t_ind + 2), utl_raw.little_endian); t_grbit := dbms_lob.substr(t_workbook, 1, t_ind + 4); t_rec := dbms_lob.substr(t_workbook, t_len - 1, t_ind + 4 + 1); t_sind := 1; else t_tmp := utl_raw.substr(t_rec, t_sind, t_cnt); t_sind := t_sind + t_cnt; t_cnt := 0; end if; t_uni := null; for i in 1 .. utl_raw.length(t_tmp) loop t_uni := utl_raw.concat(t_uni, utl_raw.substr(t_tmp, i, 1), hextoraw('00')); end loop; t_str := t_str || utl_i18n.raw_to_char(t_uni, 'AL16UTF16LE'); end if; end loop; end; function rk2number(p_rk raw) return number is begin return case rawt
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：Windows下编写批处理脚本来启动和重置Oracle数据库_oracle_
                                                                
下一篇：ORA-00392ORA-00312日志正在清除故障_oracle_