ORACLE MASTERMINDS: .XLSX UPLOAD IN APEX USING PL/SQL

Follow the steps below to achieve, the process of uploading a .XLSX file in APEX ( 4,5 AND 18C) using PL/SQL.

ü Step 1 : We need to create a Page browse item and provide storage type as Table WWV_FLOW_FILES.

ü Step 2 : Create a Page Button and Action when Button Clicked as Submit Page.

ü Step 3 : Execute the below package scripts to read the contents of the uploaded .XLSX file.

CREATE OR REPLACE PACKAGE as_read_xlsx

-- ********************************************************************************************

-- Program : AS_READ_XLSX

-- Purpose : Package for Reading data from Excel(.XLSX).

-- |------- -------- --------------- ---------------------------------------------------------------------------------------

TYPE tp_one_cell IS RECORD (

sheet_nr NUMBER (2),

sheet_name VARCHAR (4000),

row_nr NUMBER (10),

col_nr NUMBER (10),

cell VARCHAR2 (100),

cell_type VARCHAR2 (1),

string_val VARCHAR2 (4000),

number_val NUMBER,

date_val DATE,

formula VARCHAR2 (4000)

);

TYPE tp_all_cells IS TABLE OF tp_one_cell;

FUNCTION READ (

p_xlsx BLOB,

p_sheets VARCHAR2 := NULL,

p_cell VARCHAR2 := NULL

)

RETURN tp_all_cells PIPELINED;

FUNCTION file2blob (p_dir VARCHAR2, p_file_name VARCHAR2)

RETURN BLOB;

END as_read_xlsx;

CREATE OR REPLACE PACKAGE BODY as_read_xlsx

FUNCTION READ (

p_xlsx BLOB,

p_sheets VARCHAR2 := NULL,

p_cell VARCHAR2 := NULL

)

RETURN tp_all_cells PIPELINED

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_numfmt_text tp_date;

TYPE tp_strings IS TABLE OF VARCHAR2 (32767)

INDEX BY PLS_INTEGER;

t_strings tp_strings;

t_sheet_ids tp_strings;

t_sheet_names tp_strings;

t_r VARCHAR2 (32767);

t_s VARCHAR2 (32767);

t_val VARCHAR2 (32767);

t_t VARCHAR2 (400);

t_nr NUMBER;

t_c PLS_INTEGER;

t_x PLS_INTEGER;

t_xx PLS_INTEGER;

t_ns VARCHAR2 (200)

:= 'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';

t_nd DBMS_XMLDOM.domnode;

t_nl DBMS_XMLDOM.domnodelist;

t_nl2 DBMS_XMLDOM.domnodelist;

t_nl3 DBMS_XMLDOM.domnodelist;

t_one_cell tp_one_cell;

t_ndoc DBMS_XMLDOM.domdocument;

FUNCTION blob2node (p_blob BLOB)

RETURN DBMS_XMLDOM.domnode

BEGIN

IF p_blob IS NULL OR DBMS_LOB.getlength (p_blob) = 0

THEN

RETURN NULL;

END IF;

IF NOT DBMS_XMLDOM.isnull (t_ndoc)

THEN

DBMS_XMLDOM.freedocument (t_ndoc);

END IF;

t_ndoc :=

DBMS_XMLDOM.newdomdocument (XMLTYPE (p_blob,

NLS_CHARSET_ID ('AL32UTF8')

)

);

RETURN DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc));

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, DBMS_LOB.CALL);

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_ndoc := DBMS_XMLDOM.newdomdocument (t_clob);

t_nd :=

DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc)

);

DBMS_LOB.freetemporary (t_clob);

RETURN t_nd;

END;

FUNCTION blob2num (p_blob BLOB, p_len INTEGER, p_pos INTEGER)

RETURN NUMBER

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

BEGIN

RETURN UTL_RAW.SUBSTR

(UTL_RAW.cast_from_binary_integer (p_big,

UTL_RAW.little_endian

p_bytes

);

END;

FUNCTION col_alfan (p_col VARCHAR2)

RETURN PLS_INTEGER

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

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');

-- End of central directory signature

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,

t_hd_ind + 9

HEXTORAW ('08')

) = HEXTORAW ('08')

THEN

t_encoding := 'AL32UTF8'; -- utf8

ELSE

t_encoding := 'US8PC437'; -- IBM codepage 437

END IF;

IF p_file_name =

UTL_I18N.raw_to_char

(DBMS_LOB.SUBSTR (p_zipped_blob,

blob2num (p_zipped_blob,

t_hd_ind + 28

t_hd_ind + 46

t_encoding

)

THEN

t_len := blob2num (p_zipped_blob, 4, t_hd_ind + 24);

-- uncompressed length

IF t_len = 0

THEN

IF SUBSTR (p_file_name, -1) IN ('/', '\')

THEN -- directory/folder

RETURN NULL;

ELSE -- empty file

RETURN EMPTY_BLOB ();

END IF;

IF DBMS_LOB.SUBSTR (p_zipped_blob, 2, t_hd_ind + 10) =

HEXTORAW ('0800')

-- deflate

THEN

t_fl_ind := blob2num (p_zipped_blob, 4, t_hd_ind + 42);

t_tmp := HEXTORAW ('1F8B0800000000000003'); -- gzip header

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)

-- File name length

+ blob2num

(p_zipped_blob, 2, t_fl_ind + 29)

-- Extra field length

);

DBMS_LOB.append

(t_tmp,

UTL_RAW.CONCAT

(DBMS_LOB.SUBSTR (p_zipped_blob,

t_hd_ind + 16

) -- CRC32

little_endian (t_len)

-- uncompressed length

)

);

RETURN UTL_COMPRESS.lz_uncompress (t_tmp);

END IF;

IF DBMS_LOB.SUBSTR (p_zipped_blob, 2, t_hd_ind + 10) =

HEXTORAW ('0000')

-- The file is stored (no compression)

THEN

t_fl_ind := blob2num (p_zipped_blob, 4, t_hd_ind + 42);

DBMS_LOB.createtemporary (t_tmp, TRUE, DBMS_LOB.CALL);

DBMS_LOB.COPY

(t_tmp,

p_zipped_blob,

t_len,

t_fl_ind

+ 31

+ blob2num (p_zipped_blob, 2, t_fl_ind + 27)

-- File name length

+ blob2num

(p_zipped_blob, 2, t_fl_ind + 29)

-- Extra field length

);

RETURN t_tmp;

END IF;

t_hd_ind :=

t_hd_ind

+ 46

+ blob2num (p_zipped_blob, 2, t_hd_ind + 28)

-- File name length

+ blob2num (p_zipped_blob, 2, t_hd_ind + 30)

-- Extra field length

+ blob2num (p_zipped_blob, 2, t_hd_ind + 32);

-- File comment length

END LOOP;

RETURN NULL;

END;

BEGIN

t_one_cell.cell_type := 'S';

t_one_cell.sheet_name :=

'This doesn''t look like an Excel (xlsx) file to me!';

t_one_cell.string_val := t_one_cell.sheet_name;

IF DBMS_LOB.SUBSTR (p_xlsx, 4, 1) != HEXTORAW ('504B0304')

THEN

PIPE ROW (t_one_cell);

RETURN;

END IF;

t_nd := blob2node (get_file (p_xlsx, 'xl/workbook.xml'));

IF DBMS_XMLDOM.isnull (t_nd)

THEN

PIPE ROW (t_one_cell);

RETURN;

END IF;

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.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

t_nd := blob2node (get_file (p_xlsx, '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_val := DBMS_XMLDOM.getnodevalue (DBMS_XMLDOM.item (t_nl, i));

t_xf_date (i) := t_numfmt_date.EXISTS (t_val);

t_numfmt_text (i) := t_val = '49';

END LOOP;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

t_nd := blob2node (get_file (p_xlsx, 'xl/sharedStrings.xml'));

IF NOT DBMS_XMLDOM.isnull (t_nd)

THEN

t_x := 0;

t_xx := 5000;

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_c := t_strings.COUNT;

t_strings (t_c) :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i), '.');

IF t_strings (t_c) IS NULL

THEN

t_strings (t_c) :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl, i),

'*/text()'

);

IF t_strings (t_c) 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_c) :=

t_strings (t_c)

|| DBMS_XMLDOM.getnodevalue

(DBMS_XMLDOM.item (t_nl2,

)

);

END LOOP;

END IF;

END LOOP;

END IF;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

t_nd := blob2node (get_file (p_xlsx, 'xl/_rels/workbook.xml.rels'));

FOR i IN 1 .. t_sheet_ids.COUNT

LOOP

t_sheet_ids (i) :=

DBMS_XSLPROCESSOR.valueof

(t_nd,

'/Relationships/Relationship[@Id="'

|| t_sheet_ids (i)

|| '"]/@Target',

'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"'

);

IF SUBSTR (t_sheet_ids (i), 1, 4) = '/xl/'

THEN -- thanks Lilian Arnaud

t_sheet_ids (i) := SUBSTR (t_sheet_ids (i), 5);

END IF;

END LOOP;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

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) || ':') >

)

THEN

t_one_cell.sheet_nr := i;

t_one_cell.sheet_name := t_sheet_names (i);

t_nd := blob2node (get_file (p_xlsx, 'xl/' || t_sheet_ids (i)));

t_nl3 :=

DBMS_XSLPROCESSOR.selectnodes (t_nd,

'/worksheet/sheetData/row'

);

FOR r IN 0 .. DBMS_XMLDOM.getlength (t_nl3) - 1

LOOP

t_nl2 :=

DBMS_XSLPROCESSOR.selectnodes (DBMS_XMLDOM.item (t_nl3, r),

'c'

);

FOR j IN 0 .. DBMS_XMLDOM.getlength (t_nl2) - 1

LOOP

t_one_cell.date_val := NULL;

t_one_cell.number_val := NULL;

t_one_cell.string_val := NULL;

t_r :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j),

'@r',

t_ns

);

t_val :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j),

'v'

);

-- see Changelog 2013-02-19 formula column

t_one_cell.formula :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j),

'f'

);

-- see Changelog 2013-02-18 type='str'

t_t :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j),

'@t'

);

IF t_t IN ('str', 'inlineStr', 'e')

THEN

t_one_cell.cell_type := 'S';

t_one_cell.string_val := t_val;

ELSIF t_t = 's'

THEN

t_one_cell.cell_type := 'S';

IF t_val IS NOT NULL

THEN

t_one_cell.string_val :=

t_strings (TO_NUMBER (t_val));

END IF;

ELSE

t_s :=

DBMS_XSLPROCESSOR.valueof (DBMS_XMLDOM.item (t_nl2, j),

'@s'

);

t_nr :=

TO_NUMBER

(t_val,

CASE

WHEN INSTR (t_val, 'E') = 0

THEN TRANSLATE (t_val,

'.012345678,-+',

'D999999999'

)

ELSE TRANSLATE (SUBSTR (t_val,

INSTR (t_val, 'E')

- 1

'.012345678,-+',

'D999999999'

)

|| 'EEEE'

END,

'NLS_NUMERIC_CHARACTERS=.,'

);

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_one_cell.cell_type := 'D';

IF t_date1904

THEN

t_one_cell.date_val :=

TO_DATE ('01-01-1904', 'DD-MM-YYYY')

+ t_nr;

ELSE

t_one_cell.date_val :=

TO_DATE ('01-03-1900', 'DD-MM-YYYY')

+ (t_nr - 61);

END IF;

ELSE

IF t_s IS NOT NULL

AND t_numfmt_text.EXISTS (TO_NUMBER (t_s))

AND t_numfmt_text (TO_NUMBER (t_s))

THEN

t_one_cell.cell_type := 'S';

t_one_cell.string_val := t_val;

ELSE

t_one_cell.cell_type := 'N';

t_nr :=

ROUND (t_nr,

14 - SUBSTR (TO_CHAR (t_nr, 'TME'), -3)

);

t_one_cell.number_val := t_nr;

END IF;

t_one_cell.row_nr := LTRIM (t_r, RTRIM (t_r, '0123456789'));

t_one_cell.col_nr := col_alfan (RTRIM (t_r, '0123456789'));

t_one_cell.cell := t_r;

IF p_cell IS NULL OR t_r = UPPER (p_cell)

THEN

PIPE ROW (t_one_cell);

END IF;

END LOOP;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

END IF;

END LOOP;

DBMS_XMLDOM.freedocument (t_ndoc);

t_xf_date.DELETE;

t_numfmt_date.DELETE;

t_strings.DELETE;

t_sheet_ids.DELETE;

t_sheet_names.DELETE;

RETURN;

END;

FUNCTION file2blob (p_dir VARCHAR2, p_file_name VARCHAR2)

RETURN BLOB

file_lob BFILE;

file_blob BLOB;

BEGIN

file_lob := BFILENAME (p_dir, p_file_name);

DBMS_LOB.OPEN (file_lob, DBMS_LOB.file_readonly);

DBMS_LOB.createtemporary (file_blob, TRUE, DBMS_LOB.CALL);

DBMS_LOB.loadfromfile (file_blob, file_lob, DBMS_LOB.lobmaxsize);

DBMS_LOB.CLOSE (file_lob);

RETURN file_blob;

EXCEPTION

WHEN OTHERS

THEN

IF DBMS_LOB.ISOPEN (file_lob) = 1

THEN

DBMS_LOB.CLOSE (file_lob);

END IF;

IF DBMS_LOB.istemporary (file_blob) = 1

THEN

DBMS_LOB.freetemporary (file_blob);

END IF;

RAISE;

END;

END as_read_xlsx;

ü Step 4 : Create an .XLSX excel sheet template. (Varies based on the requirement. In my case,Check below Template).

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1001	Doyen	MANAGER	7698	22-Jun-18	2500		62
1002	Doyen	MGR	7883		8000	54	63

ü Step 5 :

a) Create a page process to read the data from Uploaded excel and store it in a temporary table.

b) Change the Process point to ‘On Submit - After Computations and Validations’.

c) Copy below code as Source :

DECLARE

CURSOR c1

WITH xlsx AS

(SELECT row_nr, col_nr,

CASE cell_type

WHEN 'S'

THEN string_val

WHEN 'N'

THEN TO_CHAR (number_val)

WHEN 'D'

THEN TO_CHAR (date_val, 'DD-MON-YYYY')

-- We can change the Data format - In my Case 'DD-MON-YYYY'

ELSE formula

END cell_val

-- to make PIVOT works we have to have one data type for this column - in our case CHAR

FROM (SELECT *

FROM TABLE

(as_read_xlsx.READ

((SELECT blob_content

FROM wwv_flow_files

WHERE last_updated =

(SELECT MAX (last_updated)

FROM wwv_flow_files

WHERE updated_by =

:app_user)

AND ID =

(SELECT MAX (ID)

FROM wwv_flow_files

WHERE updated_by =

:app_user))

)

)))

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

FROM xlsx

PIVOT ( MAX ( cell_val )

FOR col_nr

IN ( 1 AS EMPNO,2 AS ENAME,3 AS JOB,4 AS MGR,5 AS HIREDATE,6 AS SAL,7 AS COMM

,8 AS DEPTNO)

) -- To notify AS_READ_XLS package as 1st Column is EMPNO and 2 Column is ENAME ....

WHERE row_nr > 1;

BEGIN

FOR i IN c1

LOOP

-- To insert the Uploaded Excel data into Our Table

INSERT INTO emp

(empno, ename, job, mgr, hiredate, sal,

comm, deptno

)

VALUES (i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal,

i.comm, i.deptno

);

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

raise_application_error (-20035, SQLERRM);

END;

d) Provide Process Condition as When Button Pressed to ‘P_BUTTON_NAME’.

Note : This Process works only for .XLSX format excel files. It does not work for other formats like .CSV OR .XLS

Thursday, 5 July 2018

.XLSX UPLOAD IN APEX USING PL/SQL

No comments:

Post a Comment