XLSX UPLOAD IN APEX USING PL/SQL

Introduction:

The process of uploading a .XLSX file in APEX ( 4,5 AND 18C)  using PL/SQL.

Procedures:

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. xlsx-pkg

 

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 :

  1. a) Create a page process to read the data from Uploaded excel and store it in a temporary table.
  2. b) Change the Process point to ‘On Submit – After Computations and Validations’
  3. c) Copy below code as Source :

DECLARE

CURSOR c1

IS

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;

/

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

Summary

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

Recent Posts