Thursday 5 July 2018

EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE

Follow the below steps to achieve, the process of uploading a .XLSX file with multiple tabs with Select List in APEX using PL/SQL. 


ü  Step 1 : Create an .XLSX file with multiple tabs.
a) Tab 1(Data) will have Data and Tab2(Select List) will have Select List Values.

a) Provide List of values in the Select List to be shown in the Data Tab.

Note : The List of values should be started in the column where there are no values in the columns in Data Tab. Refer Below Images.

ü  The columns in the above image I.e. in the Data tab are ended at Column ‘H’.

ü  The Columns in the above image (Select List Tab) are starting at Column ‘I’.


ü  Values may interchange if values are present in same columns in both tabs.

a) Create a select list for Job Column in Data Sheet.

1. Click on Job --> Go to data tab --> Click on Validation


2. Change validation Criteria to List --> Click on source

3. Go to Select List Tab --> Drag the values and Press Enter and Click OK.

4. You can see Drop down for Job Column in Data tab.



Sample Excel :


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1001 Doyen MANAGER 7698 22-Jun-18 2500 30
1002 Doyen SALESMAN 7566 23-Jun-18 2501 350 10
1003 Doyen CLERK 7782 24-Jun-18 2502 0 20

ü  Step 2 : Create a Page browse item and provide storage type as
Table APEX_APPLICATION_TEMP_FILES.

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

ü  Step 4: Execute the below package scripts to read the contents of the uploaded .XLSX file.
CREATE OR REPLACE PACKAGE as_read_xlsx
IS
-- ********************************************************************************************
-- Program  :  AS_READ_XLSX
-- Purpose  : Package for Reading data from Excel.

   -- |-------  -------- --------------- ---------------------------------------------------------------------------------------
   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
IS
--
   FUNCTION READ (
      p_xlsx     BLOB,
      p_sheets   VARCHAR2 := NULL,
      p_cell     VARCHAR2 := NULL
   )
      RETURN tp_all_cells PIPELINED
   IS
      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
      IS
      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;
      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');
                                         -- 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,
                                                 1,
                                                 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,
                                                              2,
                                                              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;
               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,
                                                     4,
                                                     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,
                                1,
                                  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;
            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,
                                                                        j
                                                                       )
                                                     );
                     END LOOP;
                  END IF;
               END IF;
            END LOOP;
         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) || ':') >
                                                                             0
            )
         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,
                                                          1,
                                                            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;
                     END IF;
                  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;
            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
   IS
      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 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 Submit’.

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 apex_application_temp_files
                                      WHERE ID =
                                               (SELECT MAX (ID)
                                                  FROM apex_application_temp_files))
                                   )
                              )))
      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
      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;

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

2 comments: