公共程序包头
CREATE OR REPLACE PACKAGE ECS_WIP_PKG
IS
-- Public type declarations
/***********************************************************************
*Owner :EXCEL导入
*Parameters：
*Return Value：
*Creation Date： 2022-1-14 09:02:54
*Created By： created by iven.lin
*Version: 1.0
*Last Updated By：*/
G_DEBUG_LEVEL NUMBER := 0;
G_DEBUG_TYPE VARCHAR2(100) := 'DBMS_OUTPUT';
FUNCTION get_upload_url RETURN VARCHAR2;
PROCEDURE LOG(P_MSG IN VARCHAR2);
PROCEDURE OUTPUT(P_MSG IN VARCHAR2);
FUNCTION DEBUG_ENABLE(P_DEBUG_LEVEL IN NUMBER) RETURN BOOLEAN;
PROCEDURE DEBUGLOG(P_MSG IN VARCHAR2);
FUNCTION CONVER_EXCEL_TO_TAB(P_DOCUMENT BLOB,
P_SHEETS IN VARCHAR2 DEFAULT NULL,
P_RAISE IN NUMBER DEFAULT 1)
RETURN ECS_UPLOAD_OBJ_TAB
PIPELINED;
PROCEDURE PRO_DATA_INSERT_INTABLE(P_FILE_ID NUMBER);
END;
View Code
公共程序包体
CREATE OR REPLACE PACKAGE BODY ECS_WIP_PKG
IS
FUNCTION get_upload_url RETURN VARCHAR2
IS
v_url VARCHAR2(100);
BEGIN
v_url := fnd_profile.value_specific('APPS_SERVLET_AGENT');
v_url := SUBSTR(v_url,1,INSTR(v_url,'/',1,3))||'html//bocupload.html';
RETURN v_url;
END get_upload_url;
/* FUNCTION GET_APERTURE_COST (P_PNL NUMBER,
P_ORGANIZATION_ID NUMBER,
P_INVENTORY_ITEM_ID NUMBER
) RETURN NUMBER
is
p_total_cost number;
p_c number; --total
p_c1 number;
p_c2 number;
p_c3 number;
p_c4 number;
p_c5 number;
p_c6 number;
p_c7 number;
p_c8 number;
p_c9 number;
p_c11 number;
p_c12 number;
p_c13 number;
p_c14 number;
p_c15 number;
p_c16 number;
p_c17 number;
p_c18 number;
p_c19 number;
p_c20 number;
p_c21 number;
p_c21a number;
p_c22 number;
p_c23 number;
p_c24 number;
p_c25 number;
p_c26 number;
p_c27 number;
p_c28 number;
x_pcs_number number;
P_PNL_SF NUMBER;
cursor c1 is
select
SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) D_APERTURE,
NVL(pdd.APERTURE_NUM,0) APERTURE_NUM,
pdc.MAX_COUNT grinding_count,
round(pdc.HIT_NUMBER,5) hit_number ,
pdc.pcs_number,
pdm.APERTURE_HOUR,
pdd.notation
from pcb_drilling_data pdd,
(select distinct organization_id,FM_D_APERTURE,to_D_APERTURE,notation,pcs_number,hit_number,MAX_COUNT from apps.pcb_drilling_count) pdc,
apps.pcb_drilling_MOR pdm
where SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between pdc.FM_D_APERTURE and pdc.to_D_APERTURE
and NVL(decode(pdd.notation,'BGA','BGA','CPU','CPU','SLOT','SLOT',''),'*')=nvl(PDC.NOTATION,'*')
AND PDC.PCS_NUMBER=(select BRAND_PCS from apps.PCB_DRILLING_BRAND_PCS where organization_id=p_organization_id
and (select FG_HIGH from ECS_BOM_ENGINEERING WHERE ORGANIZATION_ID=p_organization_id AND ASSEMBLY_ITEM_ID=PDD.INVENTORY_ITEM_ID) between FM_BOARD_DEEP and TO_BOARD_DEEP
and SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between FM_D_APERTURE and TO_D_APERTURE )
AND PDD.inventory_item_id=p_inventory_item_id
and SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between pdm.FM_D_APERTURE and pdm.TO_D_APERTURE
and NVL(decode(pdd.notation,'CPU','CPU',''),'*')=nvl(pdm.type,'*')
and pdd.organization_id=pdm.organization_id
ORDER BY 1,2,3,4,5,6
;
begin
select
MIN(pdc.pcs_number)
into x_pcs_number
from pcb_drilling_data pdd,
(select distinct organization_id,FM_D_APERTURE,to_D_APERTURE,notation,pcs_number,hit_number,MAX_COUNT from apps.pcb_drilling_count) pdc,
apps.pcb_drilling_MOR pdm
where SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between pdc.FM_D_APERTURE and pdc.to_D_APERTURE
and NVL(decode(pdd.notation,'BGA','BGA','CPU','CPU','SLOT','SLOT',''),'*')=nvl(PDC.NOTATION,'*')
AND PDC.PCS_NUMBER=(select BRAND_PCS from apps.PCB_DRILLING_BRAND_PCS where organization_id=p_organization_id
and (select FG_HIGH from ECS_BOM_ENGINEERING WHERE ORGANIZATION_ID=p_organization_id AND ASSEMBLY_ITEM_ID=PDD.INVENTORY_ITEM_ID) between FM_BOARD_DEEP and TO_BOARD_DEEP
and SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between FM_D_APERTURE and TO_D_APERTURE )
AND PDD.inventory_item_id=p_inventory_item_id
and SUBSTR(PDD.D_APERTURE,1,DECODE(INSTR(PDD.D_APERTURE,'*',1,1),0,LENGTH(PDD.D_APERTURE),INSTR(PDD.D_APERTURE,'*',1,1)-1)) between pdm.FM_D_APERTURE and pdm.TO_D_APERTURE
and NVL(decode(pdd.notation,'CPU','CPU',''),'*')=nvl(pdm.type,'*')
and pdd.organization_id=pdm.organization_id;
for l_c1 in c1 loop
p_c11:=p_pnl*l_c1.APERTURE_NUM; --–pnl苝ふ计
p_c12:=1; --逼计
select NVL(SUM(RT.PO_UNIT_PRICE*RT.QUANTITY)/SUM(RT.QUANTITY),0)
INTO P_C13
FROM PO_LINES_ALL PLA ,RCV_TRANSACTIONS RT
WHERE 1=1
AND PLA.ORG_ID=2956
--and NVL(PLA.cancel_flag, 'N') = 'N'
AND RT.PO_LINE_ID=PLA.PO_LINE_ID
AND rt.TRANSACTION_TYPE in ('DELIVER')
and pla.item_id IN
(
select PDC.INVENTORY_ITEM_ID
from apps.pcb_drilling_count pdc
where 1=1
and l_c1.D_APERTURE between pdc.FM_D_APERTURE and pdc.to_D_APERTURE
and NVL(decode(l_c1.notation,'BGA','BGA','CPU','CPU','SLOT','SLOT',''),'*')=nvl(PDC.NOTATION,'*')
)
and TRUNC(RT.TRANSACTION_DATE) >= TRUNC(TRUNC(SYSDATE,'Q')-1,'Q')
and TRUNC(RT.TRANSACTION_DATE) <=TRUNC(SYSDATE,'Q')-1
;
if l_c1.D_APERTURE<=1.25 then
SELECT PRICE_SMALL_RATE into p_c14
from PCB_EXPEND_DETAIL WHERE ORGANIZATION_ID=p_organization_id;
else
SELECT PRICE_BIG_RATE into p_c14
from PCB_EXPEND_DETAIL WHERE ORGANIZATION_ID=p_organization_id;
end if;
p_c15:=l_c1.grinding_count;
p_c16:=l_c1.hit_number;
p_c17:=x_pcs_number;
p_c18:=l_c1.APERTURE_HOUR;
SELECT MONTH_MAKE,QUANTITY,RATE,MONTH_EMP,MONTH_DEP,MONTH_FIX,MONTH_EXPEND,MONTH_CLASS,MONTH_OTHER,MONTH_APPO,FLUID
into p_c19,p_c20,p_c21,p_c22,p_c23,p_c24,p_c25,p_c26,p_c27,p_c28,p_c21a
from PCB_EXPEND_DETAIL WHERE ORGANIZATION_ID=p_organization_id;
p_c1:=((p_C11*p_C12)*(p_C13+p_C14*p_C15))/((p_C16*(p_C15+1)*p_C17));
p_c2:=((P_C11*P_C12/P_C18)*(P_C19/P_C20/26/22/P_C21))/(P_C17*6) ; --せ禸
P_C3:=((P_C11*P_C12/P_C18)*(P_C21a/P_C20/26/22/P_C21))/(P_C17*6);
P_C4:=((P_C11*P_C12/P_C18)*(P_C22 /P_C20/26/22/P_C21))/(P_C17*6);
P_C5:=((P_C11*P_C12/P_C18)*(P_C23/P_C20/26/22/P_C21))/(P_C17*6);
P_C6:=((P_C11*P_C12/P_C18)*(P_C24/P_C20/26/22/P_C21))/(P_C17*6);
P_C7:=((P_C11*P_C12/P_C18)*(P_C25/P_C20/26/22/P_C21))/(P_C17*6);
P_C8:=((P_C11*P_C12/P_C18)*(P_C26/P_C20/26/22/P_C21))/(P_C17*6);
P_C9:=((P_C11*P_C12/P_C18)*(P_C27/P_C20/26/22/P_C21))/(P_C17*6);
P_C:=P_C1+P_C2+P_C3+P_C4+P_C5+P_C6+P_C7+P_C8+P_C9;
p_total_cost:=nvl(p_total_cost,0)+P_C ;
end loop;
select
(DIVIDE_PLN_LONG/1000)*(DIVIDE_PLN_WIDTH/1000)*10.76
INTO P_PNL_SF
from ECS_BOM_ENGINEERING
where assembly_item_id=p_inventory_item_id
and organization_id=p_organization_id;
p_total_cost:=p_total_cost/P_PNL_SF;
return round(p_total_cost,5);
end GET_APERTURE_COST;
*/
--===============================================================
-- Log
--===============================================================
PROCEDURE LOG(P_MSG IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG, P_MSG);
END LOG;
--===============================================================
-- Output
--===============================================================
PROCEDURE OUTPUT(P_MSG IN VARCHAR2) IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG);
END OUTPUT;
FUNCTION DEBUG_ENABLE(P_DEBUG_LEVEL IN NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN P_DEBUG_LEVEL <= G_DEBUG_LEVEL;
END DEBUG_ENABLE;
PROCEDURE DEBUGLOG(P_MSG IN VARCHAR2) IS
BEGIN
IF G_DEBUG_TYPE = 'DBMS_OUTPUT' THEN
DBMS_OUTPUT.PUT_LINE(P_MSG);
ELSIF G_DEBUG_TYPE = 'FILE_OUTPUT' THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG);
ELSIF G_DEBUG_TYPE = 'REQUEST_OUTPUT' THEN
LOG(P_MSG);
ELSIF G_DEBUG_TYPE = 'CONTEXT_OUTPUT' THEN
/*
IF LENGTHB(G_CONTEXT_OUTPUT||CHR(10)||P_MSG) <4000 THEN
G_CONTEXT_OUTPUT := CASE WHEN G_CONTEXT_OUTPUT IS NULL THEN P_MSG ELSE G_CONTEXT_OUTPUT||CHR(10)||P_MSG END;
XYG_FND_COMMON_PKG.SET_CONTEXT(G_DEBUG_TYPE,LTRIM(G_CONTEXT_OUTPUT,CHR(10)));
END IF;*/
NULL;
END IF;
END DEBUGLOG;
FUNCTION CONVER_EXCEL_TO_TAB(P_DOCUMENT BLOB,
P_SHEETS IN VARCHAR2 DEFAULT NULL,
P_RAISE IN NUMBER DEFAULT 1)
RETURN ECS_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 RAWTOHEX(UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(P_RK, 1, 1),'03'))
WHEN '02' THEN UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.BIT_AND(P_RK,'FCFFFFFF'),UTL_RAW.LITTLE_ENDIAN) / 4
WHEN '03' THEN UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.BIT_AND(P_RK,'FCFFFFFF'), UTL_RAW.LITTLE_ENDIAN) / 400
WHEN '00' THEN UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.CONCAT('00000000',P_RK),UTL_RAW.LITTLE_ENDIAN)
WHEN '01' THEN UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.CONCAT('00000000',UTL_RAW.BIT_AND(P_RK,'FCFFFFFF')),UTL_RAW.LITTLE_ENDIAN) / 100
END;
END;
FUNCTION NUM2DATE(P_NUM NUMBER) RETURN DATE IS
BEGIN
IF T_DATE1904 THEN
RETURN TO_DATE('01-01-1904', 'DD-MM-YYYY') + P_NUM;
END IF;
RETURN TO_DATE('01-03-1900', 'DD-MM-YYYY') +(P_NUM - 61);
END;
PROCEDURE READ_CFB(P_CF BLOB) IS
T_HEADER RAW(512);
T_BYTE_ORDER PLS_INTEGER;
T_ENCODING VARCHAR2(30);
T_SSZ PLS_INTEGER;
T_SSSZ PLS_INTEGER;
T_SECTID PLS_INTEGER;
T_TMP_SECTID T_SECTID%TYPE;
TYPE TP_SECIDS IS TABLE OF T_SECTID%TYPE INDEX BY PLS_INTEGER;
T_MSAT TP_SECIDS;
T_SAT TP_SECIDS;
T_SSAT TP_SECIDS;
T_SECTOR RAW(2048);
T_SHORT_CONTAINER BLOB;
T_STREAM BLOB;
T_LEN PLS_INTEGER;
T_NAME VARCHAR2(32 CHAR);
C_FREE_SECID CONSTANT PLS_INTEGER := -1;
C_END_OF_CHAIN_SECID CONSTANT PLS_INTEGER := -2;
C_SAT_SECID CONSTANT PLS_INTEGER := -3;
C_MSAT_SECID CONSTANT PLS_INTEGER := -4;
C_DIR_EMPTY CONSTANT RAW(1) := HEXTORAW('00');
C_DIR_STORAGE CONSTANT RAW(1) := HEXTORAW('01');
C_DIR_STREAM CONSTANT RAW(1) := HEXTORAW('02');
C_DIR_LOCK CONSTANT RAW(1) := HEXTORAW('03');
C_DIR_PROPERTY CONSTANT RAW(1) := HEXTORAW('04');
C_DIR_ROOT CONSTANT RAW(1) := HEXTORAW('05');
BEGIN
T_HEADER := DBMS_LOB.SUBSTR(P_CF, 512, 1);
IF (T_HEADER IS NULL OR UTL_RAW.LENGTH(T_HEADER) < 512 OR
UTL_RAW.SUBSTR(T_HEADER, 1, 8) != HEXTORAW('D0CF11E0A1B11AE1')) THEN
RETURN;
END IF;
T_BYTE_ORDER := CASE WHEN UTL_RAW.SUBSTR(T_HEADER, 29, 2) = HEXTORAW('FEFF') THEN UTL_RAW.LITTLE_ENDIAN ELSE UTL_RAW.BIG_ENDIAN END;
IF T_BYTE_ORDER = UTL_RAW.LITTLE_ENDIAN THEN
T_ENCODING := 'AL16UTF16LE';
ELSE
T_ENCODING := 'AL16UTF16';
END IF;
T_SSZ := POWER(2,
UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
31,
2),
T_BYTE_ORDER));
T_SSSZ := POWER(2,
UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
33,
2),
T_BYTE_ORDER));
FOR I IN 0 .. 109 - 1
LOOP
T_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
77 +
I * 4,
4),
T_BYTE_ORDER);
EXIT WHEN T_SECTID = C_FREE_SECID;
T_MSAT(I) := T_SECTID;
END LOOP;
T_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
69,
4),
T_BYTE_ORDER);
WHILE T_SECTID != C_END_OF_CHAIN_SECID
LOOP
T_SECTOR := DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 + T_SSZ * T_SECTID + 1);
FOR I IN 0 .. T_SSZ / 4 - 2
LOOP
T_MSAT(T_MSAT.COUNT()) := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 4 + 1,
4),
T_BYTE_ORDER);
END LOOP;
T_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
-4,
4),
T_BYTE_ORDER);
END LOOP;
FOR J IN 0 .. T_MSAT.COUNT() - 1
LOOP
T_SECTOR := DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 + T_SSZ * T_MSAT(J) + 1);
FOR I IN 0 .. T_SSZ / 4 - 1
LOOP
T_SAT(T_SAT.COUNT()) := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 4 + 1,
4),
T_BYTE_ORDER);
END LOOP;
END LOOP;
T_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
61,
4),
T_BYTE_ORDER);
WHILE T_SECTID != C_END_OF_CHAIN_SECID
LOOP
T_SECTOR := DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 + T_SSZ * T_SECTID + 1);
FOR I IN 0 .. T_SSZ / 4 - 1
LOOP
T_SSAT(T_SSAT.COUNT()) := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 4 + 1,
4),
T_BYTE_ORDER);
END LOOP;
T_SECTID := T_SAT(T_SECTID);
END LOOP;
T_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
49,
4),
T_BYTE_ORDER);
WHILE T_SECTID != C_END_OF_CHAIN_SECID
LOOP
T_SECTOR := DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 + T_SSZ * T_SECTID + 1);
FOR I IN 0 .. T_SSZ / 128 - 1
LOOP
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 128 + 65,
2),
T_BYTE_ORDER);
IF T_LEN > 2 THEN
T_NAME := UTL_I18N.RAW_TO_CHAR(UTL_RAW.SUBSTR(T_SECTOR,
I * 128 + 1,
T_LEN - 2),
T_ENCODING);
END IF;
CASE UTL_RAW.SUBSTR(T_SECTOR, I * 128 + 67, 1)
WHEN C_DIR_STREAM THEN
DBMS_LOB.CREATETEMPORARY(T_STREAM, TRUE);
T_TMP_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 128 + 117,
4),
T_BYTE_ORDER);
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 128 + 121,
4),
T_BYTE_ORDER);
IF T_LEN >=
UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_HEADER,
57,
4),
T_BYTE_ORDER) THEN
WHILE T_TMP_SECTID != C_END_OF_CHAIN_SECID
LOOP
DBMS_LOB.APPEND(T_STREAM,
DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 +
T_SSZ * T_TMP_SECTID + 1));
T_TMP_SECTID := T_SAT(T_TMP_SECTID);
END LOOP;
ELSE
WHILE T_TMP_SECTID != C_END_OF_CHAIN_SECID
LOOP
DBMS_LOB.APPEND(T_STREAM,
DBMS_LOB.SUBSTR(T_SHORT_CONTAINER,
T_SSSZ,
T_SSSZ * T_TMP_SECTID + 1));
T_TMP_SECTID := T_SSAT(T_TMP_SECTID);
END LOOP;
END IF;
DBMS_LOB.TRIM(T_STREAM, T_LEN);
IF T_NAME = 'Workbook' THEN
T_WORKBOOK := T_STREAM;
END IF;
IF T_NAME = 'Book' THEN
T_WORKBOOK := T_STREAM;
END IF;
WHEN C_DIR_ROOT THEN
DBMS_LOB.CREATETEMPORARY(T_SHORT_CONTAINER, TRUE);
T_TMP_SECTID := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_SECTOR,
I * 128 + 117,
4),
T_BYTE_ORDER);
WHILE T_TMP_SECTID != C_END_OF_CHAIN_SECID
LOOP
DBMS_LOB.APPEND(T_SHORT_CONTAINER,
DBMS_LOB.SUBSTR(P_CF,
T_SSZ,
512 + T_SSZ * T_TMP_SECTID + 1));
T_TMP_SECTID := T_SAT(T_TMP_SECTID);
END LOOP;
ELSE
NULL;
END CASE; END LOOP;
T_SECTID := T_SAT(T_SECTID);
END LOOP;
IF DBMS_LOB.ISTEMPORARY(T_SHORT_CONTAINER) = 1 THEN
DBMS_LOB.FREETEMPORARY(T_SHORT_CONTAINER);
END IF;
IF DBMS_LOB.ISTEMPORARY(T_STREAM) = 1 THEN
DBMS_LOB.FREETEMPORARY(T_STREAM);
END IF;
END;
BEGIN
--my_log('parsing XLS');
READ_CFB(P_DOCUMENT);
IF T_WORKBOOK IS NULL OR DBMS_LOB.GETLENGTH(T_WORKBOOK) = 0 THEN
--my_log('No workbook file found');
RAISE_APPLICATION_ERROR(-20003, 'Not a valid XLS-file', TRUE);
END IF;
T_IND := 1;
T_MAX_LEN := DBMS_LOB.GETLENGTH(T_WORKBOOK);
IF (DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0908') AND
DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 4) IN
(HEXTORAW('0005'), HEXTORAW('0006')) AND
DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 6) = HEXTORAW('0500')) THEN
T_BIFF5 := DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 4) =
HEXTORAW('0005');
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(T_WORKBOOK,
2,
T_IND + 2),
UTL_RAW.LITTLE_ENDIAN);
T_IND := T_IND + T_LEN + 4;
LOOP
EXIT WHEN T_IND >= T_MAX_LEN;
EXIT WHEN DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0A00');
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(T_WORKBOOK,
2,
T_IND + 2),
UTL_RAW.LITTLE_ENDIAN);
IF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('FC00') THEN
DECLARE
T_RUN INTEGER;
T_EXT INTEGER;
PROCEDURE ADD_CONT(P_LEN PLS_INTEGER) IS
BEGIN
IF (T_SIND + P_LEN > UTL_RAW.LENGTH(T_REC) + 1 AND
DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + T_LEN + 4) =
HEXTORAW('3C00')) THEN
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);
IF T_SIND <= UTL_RAW.LENGTH(T_REC) THEN
T_REC := UTL_RAW.CONCAT(UTL_RAW.SUBSTR(T_REC, T_SIND),
DBMS_LOB.SUBSTR(T_WORKBOOK,
T_LEN,
T_IND + 4));
ELSE
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
END IF;
T_SIND := 1;
END IF;
END;
BEGIN
T_SIND := 1;
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN - 8, T_IND + 12);
FOR J IN 1 .. UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(T_WORKBOOK,
4,
T_IND + 8),
UTL_RAW.LITTLE_ENDIAN)
LOOP
ADD_CONT(3);
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
T_SIND,
2),
UTL_RAW.LITTLE_ENDIAN);
T_SIND := T_SIND + 2;
T_GRBIT := UTL_RAW.SUBSTR(T_REC, T_SIND, 1);
T_SIND := T_SIND + 1;
IF UTL_RAW.BIT_AND(T_GRBIT, HEXTORAW('08')) =
HEXTORAW('08') THEN
ADD_CONT(2);
T_RUN := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
T_SIND,
2),
UTL_RAW.LITTLE_ENDIAN);
T_SIND := T_SIND + 2;
ELSE
T_RUN := 0;
END IF;
IF UTL_RAW.BIT_AND(T_GRBIT, HEXTORAW('04')) =
HEXTORAW('04') THEN
ADD_CONT(4);
T_EXT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
T_SIND,
4),
UTL_RAW.LITTLE_ENDIAN);
IF T_EXT < 0 THEN
T_EXT := T_EXT + 4294967296;
END IF;
T_SIND := T_SIND + 4;
ELSE
T_EXT := 0;
END IF;
READ_UNICODE_STRING;
T_SST(T_SST.COUNT()) := T_STR;
ADD_CONT(T_RUN * 4 + T_EXT);
T_SIND := T_SIND + T_RUN * 4 + T_EXT;
END LOOP;
END;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('8500') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_BIFF5 THEN
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
7,
1),
UTL_RAW.LITTLE_ENDIAN);
T_TMP := UTL_RAW.SUBSTR(T_REC, 8, T_CNT);
T_SHEET.NAME := UTL_I18N.RAW_TO_CHAR(T_TMP, T_CHAR_SET);
ELSE
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
7,
1));
T_GRBIT := UTL_RAW.SUBSTR(T_REC, 8, 1);
IF UTL_RAW.BIT_AND(T_GRBIT, HEXTORAW('01')) = HEXTORAW('01') THEN
T_STR := UTL_RAW.SUBSTR(T_REC, 9, T_CNT * 2);
ELSE
T_STR := NULL;
T_TMP := UTL_RAW.SUBSTR(T_REC, 9, T_CNT);
FOR I IN 1 .. UTL_RAW.LENGTH(T_TMP)
LOOP
T_STR := UTL_RAW.CONCAT(T_STR,
UTL_RAW.SUBSTR(T_TMP, I, 1),
HEXTORAW('00'));
END LOOP;
END IF;
T_SHEET.NAME := UTL_I18N.RAW_TO_CHAR(T_STR, 'AL16UTF16LE');
END IF;
T_SHEET.IND := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
1,
4),
UTL_RAW.LITTLE_ENDIAN);
T_SHEETS(T_SHEETS.COUNT()) := T_SHEET;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('2200') THEN
T_DATE1904 := DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 4) =
HEXTORAW('0100');
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('1E04') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_BIFF5 THEN
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
1),
UTL_RAW.LITTLE_ENDIAN);
T_TMP := UTL_RAW.SUBSTR(T_REC, 4, T_CNT);
T_FMT := UTL_I18N.RAW_TO_CHAR(T_TMP, T_CHAR_SET);
ELSE
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN);
T_GRBIT := UTL_RAW.SUBSTR(T_REC, 5, 1);
IF UTL_RAW.BIT_AND(T_GRBIT, HEXTORAW('01')) = HEXTORAW('01') THEN
T_STR := UTL_RAW.SUBSTR(T_REC, 6, T_CNT * 2);
ELSE
T_STR := NULL;
T_TMP := UTL_RAW.SUBSTR(T_REC, 6, T_CNT);
FOR I IN 1 .. UTL_RAW.LENGTH(T_TMP)
LOOP
T_STR := UTL_RAW.CONCAT(T_STR,
UTL_RAW.SUBSTR(T_TMP, I, 1),
HEXTORAW('00'));
END LOOP;
END IF;
T_FMT := UTL_I18N.RAW_TO_CHAR(T_STR, 'AL16UTF16LE');
END IF;
T_FMT_DATE(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN)) := (INSTR(T_FMT,
'dd') > 0 OR
INSTR(T_FMT,
'mm') > 0 OR
INSTR(T_FMT,
'yy') > 0);
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('E000') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
T_XF_FMT(T_XF_FMT.COUNT()) := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN);
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('4200') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF (RAWTOHEX(T_REC) IN ('1027', '0080') AND
NLS_CHARSET_ID('WE8MACROMAN8') IS NOT NULL) THEN
T_CHAR_SET := 'WE8MACROMAN8';
END IF;
END IF;
T_IND := T_IND + T_LEN + 4;
END LOOP;
T_FMT_DATE(14) := TRUE;
T_FMT_DATE(15) := TRUE;
T_FMT_DATE(16) := TRUE;
T_FMT_DATE(17) := TRUE;
T_FMT_DATE(22) := TRUE;
FOR I IN 0 .. T_XF_FMT.COUNT() - 1
LOOP
T_XF_DATE(I) := T_FMT_DATE.EXISTS(T_XF_FMT(I)) AND
T_FMT_DATE(T_XF_FMT(I));
END LOOP;
END IF;
FOR S IN 0 .. T_SHEETS.COUNT - 1
LOOP
T_IND := T_SHEETS(S).IND + 1;
IF (DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0908') AND
DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 4) = HEXTORAW('0006') AND
DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + 6) = HEXTORAW('1000') AND
(P_SHEETS IS NULL OR
INSTR(':' || P_SHEETS || ':', ':' || TO_CHAR(S + 1) || ':') > 0 OR
INSTR(':' || P_SHEETS || ':', ':' || T_SHEETS(S).NAME || ':') > 0)) THEN
T_MAX_C := 0;
T_ROWS.DELETE;
T_DATA(T_DATA.COUNT + 1).NAME := T_SHEETS(S).NAME;
--my_log('read ' || t_sheets(s).name);
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(T_WORKBOOK,
2,
T_IND + 2),
UTL_RAW.LITTLE_ENDIAN);
T_IND := T_IND + T_LEN + 4;
LOOP
EXIT WHEN T_IND >= T_MAX_LEN;
EXIT WHEN DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0A00');
T_CELL := NULL;
T_LEN := UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(T_WORKBOOK,
2,
T_IND + 2),
UTL_RAW.LITTLE_ENDIAN);
IF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('7E02') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_XF_DATE(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
5,
2),
UTL_RAW.LITTLE_ENDIAN)) THEN
T_CELL.DATA_TYPE := 'D';
T_CELL.DATE_VAL := NUM2DATE(RK2NUMBER(UTL_RAW.SUBSTR(T_REC,
7,
4)));
ELSE
T_CELL.DATA_TYPE := 'N';
T_CELL.NUMBER_VAL := RK2NUMBER(UTL_RAW.SUBSTR(T_REC, 7, 4));
END IF;
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0302') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_XF_DATE(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
5,
2),
UTL_RAW.LITTLE_ENDIAN)) THEN
T_CELL.DATA_TYPE := 'D';
T_CELL.DATE_VAL := NUM2DATE(UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.SUBSTR(T_REC,
7,
8),
UTL_RAW.LITTLE_ENDIAN));
ELSE
T_CELL.DATA_TYPE := 'N';
T_CELL.NUMBER_VAL := UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.SUBSTR(T_REC,
7,
8),
UTL_RAW.LITTLE_ENDIAN);
END IF;
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0600') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF (RAWTOHEX(UTL_RAW.SUBSTR(T_REC, 7, 1)) NOT IN
('00', '01', '02', '03') OR
UTL_RAW.SUBSTR(T_REC, 13, 2) != HEXTORAW('FFFF')) THEN
IF T_XF_DATE(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
5,
2),
UTL_RAW.LITTLE_ENDIAN)) THEN
T_CELL.DATA_TYPE := 'D';
T_CELL.DATE_VAL := NUM2DATE(UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.SUBSTR(T_REC,
7,
8),
UTL_RAW.LITTLE_ENDIAN));
ELSE
T_CELL.DATA_TYPE := 'N';
T_CELL.NUMBER_VAL := UTL_RAW.CAST_TO_BINARY_DOUBLE(UTL_RAW.SUBSTR(T_REC,
7,
8),
UTL_RAW.LITTLE_ENDIAN);
END IF;
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
ELSE
CASE RAWTOHEX(UTL_RAW.SUBSTR(T_REC, 7, 1))
WHEN '01' THEN
T_CELL.DATA_TYPE := 'S';
T_CELL.STRING_VAL := CASE
RAWTOHEX(UTL_RAW.SUBSTR(T_REC,
9,
1)) WHEN '00' THEN 'FALSE' WHEN '01' THEN 'TRUE' END;
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
WHEN '02' THEN
NULL;
WHEN '00' THEN
IF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND + T_LEN + 4) =
HEXTORAW('0702') THEN
DECLARE
T_ROW PLS_INTEGER := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
1,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_COL PLS_INTEGER := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
BEGIN
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_REC := DBMS_LOB.SUBSTR(T_WORKBOOK,
T_LEN,
T_IND + 4);
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
1,
2),
UTL_RAW.LITTLE_ENDIAN);
T_SIND := 4;
T_CELL.DATA_TYPE := 'S';
IF T_BIFF5 THEN
T_TMP := UTL_RAW.SUBSTR(T_REC,
3,
T_CNT);
T_CELL.STRING_VAL := UTL_I18N.RAW_TO_CHAR(T_TMP,
T_CHAR_SET);
ELSE
T_GRBIT := DBMS_LOB.SUBSTR(T_REC, 1, 3);
READ_UNICODE_STRING;
T_CELL.STRING_VAL := T_STR;
END IF;
T_MAX_C := GREATEST(T_MAX_C, T_COL);
T_ROWS(T_ROW)(T_COL) := T_CELL;
END;
END IF;
ELSE
T_CELL.DATA_TYPE := 'S';
T_CELL.STRING_VAL := '';
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
END CASE; END IF;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('0402') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_BIFF5 THEN
T_CELL.DATA_TYPE := 'S';
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
7,
2),
UTL_RAW.LITTLE_ENDIAN);
IF T_CNT = 0 THEN
T_CELL.STRING_VAL := NULL;
ELSE
T_TMP := UTL_RAW.SUBSTR(T_REC, 9, T_CNT);
T_CELL.STRING_VAL := UTL_I18N.RAW_TO_CHAR(T_TMP,
T_CHAR_SET);
END IF;
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
END IF;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('D600') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
IF T_BIFF5 THEN
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
7,
2),
UTL_RAW.LITTLE_ENDIAN);
T_TMP := UTL_RAW.SUBSTR(T_REC, 9, T_CNT);
T_CELL.DATA_TYPE := 'S';
T_CELL.STRING_VAL := UTL_I18N.RAW_TO_CHAR(T_TMP, T_CHAR_SET);
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
END IF;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('FD00') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
T_CELL.DATA_TYPE := 'S';
T_C := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) + 1;
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_CELL.STRING_VAL := T_SST(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
7,
4),
UTL_RAW.LITTLE_ENDIAN));
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(T_C) := T_CELL;
ELSIF DBMS_LOB.SUBSTR(T_WORKBOOK, 2, T_IND) = HEXTORAW('BD00') THEN
T_REC := DBMS_LOB.SUBSTR(T_WORKBOOK, T_LEN, T_IND + 4);
T_CNT := UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN);
FOR I IN UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
3,
2),
UTL_RAW.LITTLE_ENDIAN) .. UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC,
-2,
2),
UTL_RAW.LITTLE_ENDIAN)
LOOP
T_TMP := UTL_RAW.SUBSTR(T_REC, 5 + 6 * (I - T_CNT), 6);
IF T_XF_DATE(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_TMP,
1,
2),
UTL_RAW.LITTLE_ENDIAN)) THEN
T_CELL.DATA_TYPE := 'D';
T_CELL.DATE_VAL := NUM2DATE(RK2NUMBER(UTL_RAW.SUBSTR(T_TMP,
3,
4)));
ELSE
T_CELL.DATA_TYPE := 'N';
T_CELL.NUMBER_VAL := RK2NUMBER(UTL_RAW.SUBSTR(T_TMP, 3, 4));
END IF;
T_MAX_C := GREATEST(T_MAX_C, I + 1);
T_ROWS(UTL_RAW.CAST_TO_BINARY_INTEGER(UTL_RAW.SUBSTR(T_REC, 1, 2), UTL_RAW.LITTLE_ENDIAN) + 1)(I + 1) := T_CELL;
END LOOP;
END IF;
T_IND := T_IND + T_LEN + 4;
END LOOP;
IF T_ROWS.COUNT > 0 THEN
T_C := T_ROWS(T_ROWS.LAST).FIRST;
T_TYPE := T_ROWS(T_ROWS.LAST) (T_C).DATA_TYPE;
FOR R IN 1 .. T_ROWS.LAST - 1
LOOP
IF NOT T_ROWS.EXISTS(R) THEN
T_ROWS(R)(T_C).DATA_TYPE := T_TYPE;
END IF;
END LOOP;
IF T_ROWS.COUNT > 1 THEN
FOR C IN 1 .. T_MAX_C
LOOP
T_TYPE := NULL;
FOR R IN 2 .. T_ROWS.LAST
LOOP
IF T_ROWS(R).EXISTS(C) THEN
T_TYPE := T_ROWS(R) (C).DATA_TYPE;
EXIT;
END IF;
END LOOP;
IF T_TYPE IS NULL THEN
IF T_ROWS(1).EXISTS(C) THEN
T_TYPE := T_ROWS(1) (C).DATA_TYPE;
ELSE
T_TYPE := 'S';
END IF;
END IF;
FOR R IN 1 .. T_ROWS.LAST
LOOP
IF NOT T_ROWS(R).EXISTS(C) THEN
T_ROWS(R)(C).DATA_TYPE := T_TYPE;
END IF;
END LOOP;
END LOOP;
ELSE
FOR C IN 1 .. T_MAX_C
LOOP
IF NOT T_ROWS(1).EXISTS(C) THEN
T_ROWS(1)(C).DATA_TYPE := 'S';
END IF;
END LOOP;
END IF;
END IF;
T_DATA(T_DATA.COUNT).ROWS := T_ROWS;
END IF;
END LOOP;
RETURN T_DATA;
END;
FUNCTION G2(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 BLOB2NODE(P_BLOB BLOB) RETURN DBMS_XMLDOM.DOMNODE IS
BEGIN
IF P_BLOB IS NULL OR DBMS_LOB.GETLENGTH(P_BLOB) = 0 THEN
RETURN NULL;
END IF;
RETURN DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.GETDOCUMENTELEMENT(DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE(P_BLOB,
NLS_CHARSET_ID('AL32UTF8')))));
EXCEPTION
WHEN OTHERS THEN
DECLARE
T_ND DBMS_XMLDOM.DOMNODE;
T_CLOB CLOB;
T_DEST_OFFSET INTEGER;
T_SRC_OFFSET INTEGER;
T_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
T_WARNING INTEGER;
BEGIN
DBMS_LOB.CREATETEMPORARY(T_CLOB, TRUE);
T_DEST_OFFSET := 1;
T_SRC_OFFSET := 1;
DBMS_LOB.CONVERTTOCLOB(T_CLOB,
P_BLOB,
DBMS_LOB.LOBMAXSIZE,
T_DEST_OFFSET,
T_SRC_OFFSET,
NLS_CHARSET_ID('AL32UTF8'),
T_LANG_CONTEXT,
T_WARNING);
T_ND := DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.GETDOCUMENTELEMENT(DBMS_XMLDOM.NEWDOMDOCUMENT(T_CLOB)));
DBMS_LOB.FREETEMPORARY(T_CLOB);
RETURN T_ND;
END;
END;
FUNCTION BLOB2NUM(P_BLOB BLOB,
P_LEN INTEGER,
P_POS INTEGER) RETURN NUMBER IS
BEGIN
RETURN UTL_RAW.CAST_TO_BINARY_INTEGER(DBMS_LOB.SUBSTR(P_BLOB,
P_LEN,
P_POS),
UTL_RAW.LITTLE_ENDIAN);
END;
FUNCTION LITTLE_ENDIAN(P_BIG NUMBER,
P_BYTES PLS_INTEGER := 4) RETURN RAW IS
BEGIN
RETURN UTL_RAW.SUBSTR(UTL_RAW.CAST_FROM_BINARY_INTEGER(P_BIG,
UTL_RAW.LITTLE_ENDIAN),
1,
P_BYTES);
END;
FUNCTION COL_ALFAN(P_COL VARCHAR2) RETURN PLS_INTEGER IS
BEGIN
RETURN ASCII(SUBSTR(P_COL, -1)) - 64 + NVL((ASCII(SUBSTR(P_COL,
-2,
1)) - 64) * 26,
0) + NVL((ASCII(SUBSTR(P_COL,
-3,
1)) - 64) * 676,
0);
END;
FUNCTION GET_FILE(P_ZIPPED_BLOB BLOB,
P_FILE_NAME VARCHAR2) RETURN BLOB IS
T_TMP BLOB;
T_IND INTEGER;
T_HD_IND INTEGER;
T_FL_IND INTEGER;
T_ENCODING VARCHAR2(10);
T_LEN INTEGER;
BEGIN
T_IND := DBMS_LOB.GETLENGTH(P_ZIPPED_BLOB) - 21;
LOOP
EXIT WHEN T_IND < 1 OR DBMS_LOB.SUBSTR(P_ZIPPED_BLOB, 4, T_IND) = HEXTORAW('504B0506');
T_IND := T_IND - 1;
END LOOP;
IF T_IND <= 0 THEN
RETURN NULL;
END IF;
T_HD_IND := BLOB2NUM(P_ZIPPED_BLOB, 4, T_IND + 16) + 1;
FOR I IN 1 .. BLOB2NUM(P_ZIPPED_BLOB, 2, T_IND + 8)
LOOP
IF UTL_RAW.BIT_AND(DBMS_LOB.SUBSTR(P_ZIPPED_BLOB, 1, T_HD_IND + 9),
HEXTORAW('08')) = HEXTORAW('08') THEN
T_ENCODING := 'AL32UTF8';
ELSE
T_ENCODING := 'US8PC437';
END IF;
IF P_FILE_NAME = UTL_I18N.RAW_TO_CHAR(DBMS_LOB.SUBSTR(P_ZIPPED_BLOB,
BLOB2NUM(P_ZIPPED_BLOB,
2,
T_HD_IND + 28),
T_HD_IND + 46),
T_ENCODING) THEN
T_LEN := BLOB2NUM(P_ZIPPED_BLOB, 4, T_HD_IND + 24);
IF T_LEN = 0 THEN
IF SUBSTR(P_FILE_NAME, -1) IN ('/', '\') THEN
RETURN NULL;
ELSE
RETURN EMPTY_BLOB();
END IF;
END IF;
IF DBMS_LOB.SUBSTR(P_ZIPPED_BLOB, 2, T_HD_IND + 10) =
HEXTORAW('0800') THEN
T_FL_IND := BLOB2NUM(P_ZIPPED_BLOB, 4, T_HD_IND + 42);
T_TMP := HEXTORAW('1F8B0800000000000003');
DBMS_LOB.COPY(T_TMP,
P_ZIPPED_BLOB,
BLOB2NUM(P_ZIPPED_BLOB, 4, T_HD_IND + 20),
11,
T_FL_IND + 31 +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_FL_IND + 27) +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_FL_IND + 29));
DBMS_LOB.APPEND(T_TMP,
UTL_RAW.CONCAT(DBMS_LOB.SUBSTR(P_ZIPPED_BLOB,
4,
T_HD_IND + 16),
LITTLE_ENDIAN(T_LEN)));
RETURN UTL_COMPRESS.LZ_UNCOMPRESS(T_TMP);
END IF;
IF DBMS_LOB.SUBSTR(P_ZIPPED_BLOB, 2, T_HD_IND + 10) =
HEXTORAW('0000') THEN
T_FL_IND := BLOB2NUM(P_ZIPPED_BLOB, 4, T_HD_IND + 42);
DBMS_LOB.CREATETEMPORARY(T_TMP, TRUE);
DBMS_LOB.COPY(T_TMP,
P_ZIPPED_BLOB,
T_LEN,
1,
T_FL_IND + 31 +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_FL_IND + 27) +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_FL_IND + 29));
RETURN T_TMP;
END IF;
END IF;
T_HD_IND := T_HD_IND + 46 +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_HD_IND + 28) +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_HD_IND + 30) +
BLOB2NUM(P_ZIPPED_BLOB, 2, T_HD_IND + 32);
END LOOP;
RETURN NULL;
END;
FUNCTION PARSE_XLSX(P_DOC BLOB,
P_SHEETS VARCHAR2 := NULL,
P_EXTRA DBMSOUTPUT_LINESARRAY := NULL)
RETURN TP_DATA IS
T_ROWS TP_ROWS;
T_DATA TP_DATA;
T_DATE1904 BOOLEAN;
TYPE TP_DATE IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
T_XF_DATE TP_DATE;
T_NUMFMT_DATE TP_DATE;
T_STRINGS TP_STRINGS;
T_R VARCHAR2(32767);
T_S VARCHAR2(32767);
T_T VARCHAR2(32767);
T_VAL VARCHAR2(32767);
T_NR NUMBER;
T_X PLS_INTEGER;
T_XX PLS_INTEGER;
T_C PLS_INTEGER;
T_SC PLS_INTEGER;
T_RR PLS_INTEGER;
T_NS VARCHAR2(200) := 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
T_ND DBMS_XMLDOM.DOMNODE;
T_ND2 DBMS_XMLDOM.DOMNODE;
T_NL DBMS_XMLDOM.DOMNODELIST;
T_NL2 DBMS_XMLDOM.DOMNODELIST;
T_NL3 DBMS_XMLDOM.DOMNODELIST;
T_TYPE VARCHAR2(1);
T_MAX_C PLS_INTEGER;
BEGIN
--my_log('parsing XLSX');
T_ND := BLOB2NODE(GET_FILE(P_DOC, 'xl/workbook.xml'));
T_DATE1904 := LOWER(DBMS_XSLPROCESSOR.VALUEOF(T_ND,
'/workbook/workbookPr/@date1904',
T_NS)) IN ('true', '1');
T_NL := DBMS_XSLPROCESSOR.SELECTNODES(T_ND,
'/workbook/sheets/sheet',
T_NS);
FOR I IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL) - 1
LOOP
T_SHEET_IDS(I + 1) := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL,
I),
'@r:id',
'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"');
T_SHEET_NAMES(I + 1) := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL,
I),
'@name');
END LOOP;
DBMS_XMLDOM.FREENODE(T_ND);
T_ND := BLOB2NODE(GET_FILE(P_DOC, 'xl/styles.xml'));
T_NL := DBMS_XSLPROCESSOR.SELECTNODES(T_ND,
'/styleSheet/numFmts/numFmt',
T_NS);
FOR I IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL) - 1
LOOP
T_VAL := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL, I),
'@formatCode');
IF (INSTR(T_VAL, 'dd') > 0 OR INSTR(T_VAL, 'mm') > 0 OR
INSTR(T_VAL, 'yy') > 0) THEN
T_NUMFMT_DATE(DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL, I), '@numFmtId')) := TRUE;
END IF;
END LOOP;
T_NUMFMT_DATE(14) := TRUE;
T_NUMFMT_DATE(15) := TRUE;
T_NUMFMT_DATE(16) := TRUE;
T_NUMFMT_DATE(17) := TRUE;
T_NUMFMT_DATE(22) := TRUE;
T_NL := DBMS_XSLPROCESSOR.SELECTNODES(T_ND,
'/styleSheet/cellXfs/xf/@numFmtId',
T_NS);
FOR I IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL) - 1
LOOP
T_XF_DATE(I) := T_NUMFMT_DATE.EXISTS(DBMS_XMLDOM.GETNODEVALUE(DBMS_XMLDOM.ITEM(T_NL,
I)));
END LOOP;
DBMS_XMLDOM.FREENODE(T_ND);
T_ND := BLOB2NODE(GET_FILE(P_DOC, 'xl/sharedStrings.xml'));
IF NOT DBMS_XMLDOM.ISNULL(T_ND) THEN
T_X := 0;
T_XX := 10000;
LOOP
T_NL := DBMS_XSLPROCESSOR.SELECTNODES(T_ND,
'/sst/si[position()>="' ||
TO_CHAR(T_X * T_XX + 1) ||
'" and position()<="' ||
TO_CHAR((T_X + 1) * T_XX) || '"]',
T_NS);
EXIT WHEN DBMS_XMLDOM.GETLENGTH(T_NL) = 0;
T_X := T_X + 1;
FOR I IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL) - 1
LOOP
T_SC := T_STRINGS.COUNT;
T_STRINGS(T_SC) := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL,
I),
'.');
IF T_STRINGS(T_SC) IS NULL THEN
T_STRINGS(T_SC) := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL,
I),
'*/text()');
IF T_STRINGS(T_SC) IS NULL THEN
T_NL2 := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.ITEM(T_NL,
I),
'r/t/text()');
FOR J IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL2) - 1
LOOP
T_STRINGS(T_SC) := T_STRINGS(T_SC) ||
DBMS_XMLDOM.GETNODEVALUE(DBMS_XMLDOM.ITEM(T_NL2,
J));
END LOOP;
END IF;
END IF;
END LOOP;
END LOOP;
END IF;
T_ND2 := BLOB2NODE(GET_FILE(P_DOC, 'xl/_rels/workbook.xml.rels'));
FOR I IN 1 .. T_SHEET_IDS.COUNT
LOOP
IF (P_SHEETS IS NULL OR
INSTR(':' || P_SHEETS || ':', ':' || TO_CHAR(I) || ':') > 0 OR
INSTR(':' || P_SHEETS || ':', ':' || T_SHEET_NAMES(I) || ':') > 0) THEN
T_MAX_C := 0;
T_ROWS.DELETE;
T_DATA(T_DATA.COUNT + 1).NAME := T_SHEET_NAMES(I);
--my_log('read ' || t_sheet_names(i));
T_VAL := DBMS_XSLPROCESSOR.VALUEOF(T_ND2,
'/Relationships/Relationship[@Id="' ||
T_SHEET_IDS(I) || '"]/@Target',
'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"');
T_ND := BLOB2NODE(GET_FILE(P_DOC, 'xl/' || T_VAL));
T_X := 0;
T_XX := 10000;
LOOP
T_NL3 := DBMS_XSLPROCESSOR.SELECTNODES(T_ND,
'/worksheet/sheetData/row[position()>="' ||
TO_CHAR(T_X * T_XX + 1) ||
'" and position()<="' ||
TO_CHAR((T_X + 1) * T_XX) || '"]');
EXIT WHEN DBMS_XMLDOM.GETLENGTH(T_NL3) = 0;
T_X := T_X + 1;
FOR R IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL3) - 1
LOOP
T_NL2 := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.ITEM(T_NL3,
R),
'c[v]');
FOR J IN 0 .. DBMS_XMLDOM.GETLENGTH(T_NL2) - 1
LOOP
T_R := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL2,
J),
'@r',
T_NS);
T_RR := LTRIM(T_R, RTRIM(T_R, '0123456789'));
T_C := COL_ALFAN(RTRIM(T_R, '0123456789'));
T_MAX_C := GREATEST(T_MAX_C, T_C);
T_VAL := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL2,
J),
'v');
T_T := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL2,
J),
'@t');
IF T_T = 's' THEN
IF T_VAL IS NOT NULL THEN
T_ROWS(T_RR)(T_C).DATA_TYPE := 'S';
T_ROWS(T_RR)(T_C).STRING_VAL := T_STRINGS(TO_NUMBER(T_VAL));
END IF;
ELSIF T_T IN ('str', 'inlineStr', 'e') THEN
IF T_VAL IS NOT NULL THEN
T_ROWS(T_RR)(T_C).DATA_TYPE := 'S';
T_ROWS(T_RR)(T_C).STRING_VAL := T_VAL;
END IF;
ELSE
T_NR := TO_NUMBER(T_VAL, CASE WHEN INSTR(T_VAL, 'E') = 0 THEN TRANSLATE(T_VAL, '.012345678,-+', 'D999999999') ELSE TRANSLATE(SUBSTR(T_VAL, 1, INSTR(T_VAL, 'E') - 1), '.012345678,-+', 'D999999999') || 'EEEE' END, 'NLS_NUMERIC_CHARACTERS=.,');
T_S := DBMS_XSLPROCESSOR.VALUEOF(DBMS_XMLDOM.ITEM(T_NL2,
J),
'@s');
IF T_S IS NOT NULL AND T_XF_DATE.EXISTS(TO_NUMBER(T_S)) AND
T_XF_DATE(TO_NUMBER(T_S)) THEN
T_ROWS(T_RR)(T_C).DATA_TYPE := 'D';
IF T_DATE1904 THEN
T_ROWS(T_RR)(T_C).DATE_VAL := TO_DATE('01-01-1904',
'DD-MM-YYYY') + T_NR;
ELSE
T_ROWS(T_RR)(T_C).DATE_VAL := TO_DATE('01-03-1900',
'DD-MM-YYYY') +
(T_NR - 61);
END IF;
ELSE
T_ROWS(T_RR)(T_C).DATA_TYPE := 'N';
T_ROWS(T_RR)(T_C).NUMBER_VAL := T_NR;
END IF;
END IF;
END LOOP;
END LOOP;
END LOOP;
DBMS_XMLDOM.FREENODE(T_ND);
IF T_ROWS.COUNT > 0 THEN
T_C := T_ROWS(T_ROWS.LAST).FIRST;
T_TYPE := T_ROWS(T_ROWS.LAST) (T_C).DATA_TYPE;
FOR R IN 1 .. T_ROWS.LAST - 1
LOOP
IF NOT T_ROWS.EXISTS(R) THEN
T_ROWS(R)(T_C).DATA_TYPE := T_TYPE;
END IF;
END LOOP;
IF T_ROWS.COUNT > 1 THEN
FOR C IN 1 .. T_MAX_C
LOOP
T_TYPE := NULL;
FOR R IN 2 .. T_ROWS.LAST
LOOP
IF T_ROWS(R).EXISTS(C) THEN
T_TYPE := T_ROWS(R) (C).DATA_TYPE;
EXIT;
END IF;
END LOOP;
IF T_TYPE IS NULL THEN
IF T_ROWS(1).EXISTS(C) THEN
T_TYPE := T_ROWS(1) (C).DATA_TYPE;
ELSE
T_TYPE := 'S';
END IF;
END IF;
FOR R IN 1 .. T_ROWS.LAST
LOOP
IF NOT T_ROWS(R).EXISTS(C) THEN
T_ROWS(R)(C).DATA_TYPE := T_TYPE;
END IF;
END LOOP;
END LOOP;
ELSE
FOR C IN 1 .. T_MAX_C
LOOP
IF NOT T_ROWS(1).EXISTS(C) THEN
T_ROWS(1)(C).DATA_TYPE := 'S';
END IF;
END LOOP;
END IF;
END IF;
T_DATA(T_DATA.COUNT).ROWS := T_ROWS;
END IF;
END LOOP;
DBMS_XMLDOM.FREENODE(T_ND2);
RETURN T_DATA;
END;
BEGIN
IF DBMS_LOB.SUBSTR(P_DOCUMENT, 8, 1) = HEXTORAW('D0CF11E0A1B11AE1') THEN
--dbms_output.put_line( 'parsing XLS' );
--t_what := 'XLS-file';
--t_collection_base := :col_name;
L_PROCESS_PHASE := 0;
T_DATA := PARSE_XLS(P_DOCUMENT, P_SHEETS);
L_PROCESS_PHASE := 10;
--DBMS_OUTPUT.PUT_LINE('parsed,' || t_data.count || ' sheets found');
--my_log('moving to Collection(s)');
--apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');
FOR I IN 1 .. T_DATA.COUNT
LOOP
--t_collection_name := t_collection_base || to_char(nullif(i, 1));
--my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);
/*
apex_collection.add_member(t_collection_base || '_$MAP',
p_c001 => t_data(i).name,
p_c002 => t_collection_name,
p_n001 => i);
apex_collection.create_or_truncate_collection(t_collection_name);*/
IF T_DATA(I).ROWS.COUNT() > 0 THEN
--t2.delete;
--DBMS_OUTPUT.PUT_LINE('t_data(i).rows.count():' || t_data(i).rows.count());
--DBMS_OUTPUT.PUT_LINE('t_sheets(i).name:' || t_sheets(i-1).name);
FOR R IN 1 .. T_DATA(I).ROWS.LAST
LOOP
IF T_DATA(I).ROWS.EXISTS(R) THEN
PIPE ROW(ECS_UPLOAD_OBJ('EXCEL-XLS',
T_SHEETS(I - 1).NAME,
NULL,
R,
G1(I, R, 1),
G1(I, R, 2),
G1(I, R, 3),
G1(I, R, 4),
G1(I, R, 5),
G1(I, R, 6),
G1(I, R, 7),
G1(I, R, 8),
G1(I, R, 9),
G1(I, R, 10),
G1(I, R, 11),
G1(I, R, 12),
G1(I, R, 13),
G1(I, R, 14),
G1(I, R, 15),
G1(I, R, 16),
G1(I, R, 17),
G1(I, R, 18),
G1(I, R, 19),
G1(I, R, 20),
G1(I, R, 21),
G1(I, R, 22),
G1(I, R, 23),
G1(I, R, 24),
G1(I, R, 25),
G1(I, R, 26),
G1(I, R, 27),
G1(I, R, 28),
G1(I, R, 29),
G1(I, R, 30),
0,
NULL));
ELSE
--t2(1)(r) := ''
NULL;
END IF;
END LOOP;
END IF;
END LOOP;
ELSIF DBMS_LOB.SUBSTR(P_DOCUMENT, 4, 1) = HEXTORAW('504B0304') THEN
--log( 'parsing XLSX' );
--t_what := 'XLSX-file';
--t_collection_base := :col_name;
T_DATA := PARSE_XLSX(P_DOCUMENT, P_SHEETS);
--my_log('parsed,' || t_data.count || ' sheets found');
--my_log('moving to Collection(s)');
--apex_collection.create_or_truncate_collection(t_collection_base ||'_$MAP');
FOR I IN 1 .. T_DATA.COUNT
LOOP
/*
t_collection_name := t_collection_base || to_char(nullif(i, 1));
my_log('moving sheet ' || i || ': ' || t_data(i).name || ' to ' ||t_collection_name);
apex_collection.add_member(t_collection_base || '_$MAP',
p_c001 => t_data(i).name,
p_c002 => t_collection_name,
p_n001 => i);
apex_collection.create_or_truncate_collection(t_collection_name);*/
IF T_DATA(I).ROWS.COUNT() > 0 THEN
--t2.delete;
FOR R IN 1 .. T_DATA(I).ROWS.LAST
LOOP
IF T_DATA(I).ROWS.EXISTS(R) THEN
PIPE ROW(ECS_UPLOAD_OBJ('EXCEL-XLSX' --P_SOURCE_TYPE
,
T_SHEET_NAMES(I) --P_BATCH_CODE
,
NULL --P_BATCH_NAME
,
R,
G2(I, R, 1),
G2(I, R, 2),
G2(I, R, 3),
G2(I, R, 4),
G2(I, R, 5),
G2(I, R, 6),
G2(I, R, 7),
G2(I, R, 8),
G2(I, R, 9),
G2(I, R, 10),
G2(I, R, 11),
G2(I, R, 12),
G2(I, R, 13),
G2(I, R, 14),
G2(I, R, 15),
G2(I, R, 16),
G2(I, R, 17),
G2(I, R, 18),
G2(I, R, 19),
G2(I, R, 20),
G2(I, R, 21),
G2(I, R, 22),
G2(I, R, 23),
G2(I, R, 24),
G2(I, R, 25),
G2(I, R, 26),
G2(I, R, 27),
G2(I, R, 28),
G2(I, R, 29),
G2(I, R, 30),
0,
NULL));
ELSE
--t2(1)(r) := '';
NULL;
END IF;
END LOOP;
END IF;
END LOOP;
--RETURN CONVER_XLSX_TO_TAB(P_DOCUMENT,P_SHEETS,P_RAISE);
ELSE
IF P_RAISE = 1 THEN
L_PROCESS_PHASE := 97;
RAISE NO_DATA_FOUND;
ELSE
L_PROCESS_PHASE := 98;
NULL;
END IF;
END IF;
L_PROCESS_PHASE := 99;
RETURN;
EXCEPTION
WHEN OTHERS THEN
IF P_RAISE = 1 THEN
RAISE;
--DBMS_OUTPUT.PUT_LINE (R_LINE || '-' || V_PROCESS_MESSAGE);
ELSE
RETURN;
--return -1
END IF;
END CONVER_EXCEL_TO_TAB;
PROCEDURE PRO_DATA_INSERT_INTABLE(P_FILE_ID NUMBER) IS
BEGIN
INSERT INTO ECS_DATA_UPLOAD
SELECT A.*,P_FILE_ID
FROM TABLE(ECS_WIP_PKG.CONVER_EXCEL_TO_TAB((SELECT FILE_DATA
FROM FND_LOBS
WHERE FILE_ID =
P_FILE_ID),
NULL,
1)) A;
INSERT INTO CUX_ECS_DATA_UPLOAD_T ( SELECT x.* from ECS_DATA_UPLOAD x);
commit;
END PRO_DATA_INSERT_INTABLE;
END;
View Code
涉及到的表
-- Create table
create table ECS_DATA_UPLOAD
(
source_type VARCHAR2(240),
batch_code VARCHAR2(480),
batch_name VARCHAR2(4000),
row_num NUMBER,
attribute1 VARCHAR2(4000),
attribute2 VARCHAR2(4000),
attribute3 VARCHAR2(4000),
attribute4 VARCHAR2(4000),
attribute5 VARCHAR2(4000),
attribute6 VARCHAR2(4000),
attribute7 VARCHAR2(4000),
attribute8 VARCHAR2(4000),
attribute9 VARCHAR2(4000),
attribute10 VARCHAR2(4000),
attribute11 VARCHAR2(4000),
attribute12 VARCHAR2(4000),
attribute13 VARCHAR2(4000),
attribute14 VARCHAR2(4000),
attribute15 VARCHAR2(4000),
attribute16 VARCHAR2(4000),
attribute17 VARCHAR2(4000),
attribute18 VARCHAR2(4000),
attribute19 VARCHAR2(4000),
attribute20 VARCHAR2(4000),
attribute21 VARCHAR2(4000),
attribute22 VARCHAR2(4000),
attribute23 VARCHAR2(4000),
attribute24 VARCHAR2(4000),
attribute25 VARCHAR2(4000),
attribute26 VARCHAR2(4000),
attribute27 VARCHAR2(4000),
attribute28 VARCHAR2(4000),
attribute29 VARCHAR2(4000),
attribute30 VARCHAR2(4000),
process_flag NUMBER,
process_message VARCHAR2(4000),
file_id NUMBER
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
View Code
-- Create table
create table CUX_ECS_DATA_UPLOAD_T
(
source_type VARCHAR2(240),
batch_code VARCHAR2(480),
batch_name VARCHAR2(4000),
row_num NUMBER,
attribute1 VARCHAR2(4000),
attribute2 VARCHAR2(4000),
attribute3 VARCHAR2(4000),
attribute4 VARCHAR2(4000),
attribute5 VARCHAR2(4000),
attribute6 VARCHAR2(4000),
attribute7 VARCHAR2(4000),
attribute8 VARCHAR2(4000),
attribute9 VARCHAR2(4000),
attribute10 VARCHAR2(4000),
attribute11 VARCHAR2(4000),
attribute12 VARCHAR2(4000),
attribute13 VARCHAR2(4000),
attribute14 VARCHAR2(4000),
attribute15 VARCHAR2(4000),
attribute16 VARCHAR2(4000),
attribute17 VARCHAR2(4000),
attribute18 VARCHAR2(4000),
attribute19 VARCHAR2(4000),
attribute20 VARCHAR2(4000),
attribute21 VARCHAR2(4000),
attribute22 VARCHAR2(4000),
attribute23 VARCHAR2(4000),
attribute24 VARCHAR2(4000),
attribute25 VARCHAR2(4000),
attribute26 VARCHAR2(4000),
attribute27 VARCHAR2(4000),
attribute28 VARCHAR2(4000),
attribute29 VARCHAR2(4000),
attribute30 VARCHAR2(4000),
process_flag NUMBER,
process_message VARCHAR2(4000),
file_id NUMBER
)
tablespace APPS_TS_TX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
View Code
效果
标签：DBMS,END,Form,SUBSTR,EXCEL,UTL,RAW,EBS,INTEGER From： https://www.cnblogs.com/ivenlin/p/18121804