Introduction:
To read and import XLSX file in Oracle APEX 4.2
Scenario:
Customer wants to see their investor details in APEX (in the form of IR). Though investor details are in Oracle Directory (XLSX file format), But what we have done is, we read the XLSX file from Oracle directory and import data in APEX (Interactive Report).
Solution:
Step 1: Create the process in APEX to Call the PL/SQL procedure to generate excel file in oracle directory
Step 2: Install the AS_READ_XLSX Package as_read_xlsx
Step 3: Create the Interactive Report in APEX
Step 4: Paste below query in IR source
<<SQL>>
<< Begins>>
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’)
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( as_read_xlsx.file2blob( ‘BROKERREPORTS’,
‘Unitholderdetails_report.xls’ ) ) )
)
)
SELECT
ad.*
FROM
xlsx PIVOT ( MAX(CELL_VAL) FOR COL_NR IN (1 AS UNITHOLDERID,2 AS AUTH_STAT,3
AS REFERENCENUMBER,4 AS
ACCOUNTOPENINGDATE,5 AS AMCID)) ad
WHERE
row_nr >1;
<< Ends>>
Output:
Conclusion :
An XLSX file is imported in directory and read the details using AS_READ_XLSX package and displaying the details in the interactive report.
