DBA Hub

📋Steps in this guide1/6

APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns.

The APEX_DATA_PARSER package allows you to easily convert simple CSV, JSON, XML and XLSX data into rows and columns from SQL.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Introduction

The package makes it easy to display CSV, JSON, XML and XLSX formatted data as rows and columns from SQL. It's focused on basic tabular data, not complex document structures, so don't expect this to be a generic parser you will use for all documents. The package contains a number of routines, but you will mostly use the pipelined table function, which returns rows of type . There is a column, then columns named to , so there is a maximum of 300 columns in the data.
2

Setup

The examples in this article use files placed in a directory on the database server. The files are accessed using an Oracle directory object called . We create a directory object and grant access to is from our test user. The following files are present in the "/tmp" directory on the database server. Their contents are dumps of the data in the table, in the specified format. - emp.csv - emp.json - emp.xml - emp.xlsx The examples below will load the data from these files into a BLOB using the following function. The "emp.csv" file contains the following data. The "emp.json" file contains the following data. The "emp.xml" file contains the following data. The "emp.xslx" file contains the CSV data from the "emp.scv" loaded into Excel and saved as an XSLX file. There is only a single worksheet, and it's called "emp sheet". All the queries below use the following column formats in SQLcl/SQL*Plus unless otherwise stated.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE OR REPLACE DIRECTORY TEMP_DIR AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO testuser1;

CREATE OR REPLACE FUNCTION file_to_blob (p_dir       IN  VARCHAR2,
                                         p_filename  IN  VARCHAR2)
  RETURN BLOB
AS
  l_bfile  BFILE;
  l_blob   BLOB;

  l_dest_offset INTEGER := 1;
  l_src_offset  INTEGER := 1;
BEGIN
  l_bfile := BFILENAME(p_dir, p_filename);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.createtemporary(l_blob, FALSE);
  IF DBMS_LOB.getlength(l_bfile) > 0 THEN
    DBMS_LOB.loadblobfromfile (
      dest_lob    => l_blob,
      src_bfile   => l_bfile,
      amount      => DBMS_LOB.lobmaxsize,
      dest_offset => l_dest_offset,
      src_offset  => l_src_offset);
  END IF;
  DBMS_LOB.fileclose(l_bfile);
  RETURN l_blob;
END file_to_blob;
/

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

{"results":[{"columns":[{"name":"EMPNO","type":"NUMBER"},{"name":"ENAME","type":"VARCHAR2"},{"name":"JOB","type":"VARCHAR2"},{"name":"MGR","type":"NUMBER"},{"name":"HIREDATE","type":"DATE"},{"name":"SAL","type":"NUMBER"},{"name":"COMM","type":"NUMBER"},{"name":"DEPTNO","type":"NUMBER"}],
"items": [
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"17-DEC-80","sal":800,"deptno":20}
,{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"20-FEB-81","sal":1600,"comm":300,"deptno":30}
,{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"22-FEB-81","sal":1250,"comm":500,"deptno":30}
,{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"02-APR-81","sal":2975,"deptno":20}
,{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"28-SEP-81","sal":1250,"comm":1400,"deptno":30}
,{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"01-MAY-81","sal":2850,"deptno":30}
,{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"09-JUN-81","sal":2450,"deptno":10}
,{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"19-APR-87","sal":3000,"deptno":20}
,{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"17-NOV-81","sal":5000,"deptno":10}
,{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"08-SEP-81","sal":1500,"comm":0,"deptno":30}
,{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"23-MAY-87","sal":1100,"deptno":20}
,{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"03-DEC-81","sal":950,"deptno":30}
,{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"03-DEC-81","sal":3000,"deptno":20}
,{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"23-JAN-82","sal":1300,"deptno":10}
]}]}

<?xml version='1.0'  encoding='UTF-8' ?>
<RESULTS>
  <ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17-DEC-80</HIREDATE><SAL>800</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW>
  <ROW><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>20-FEB-81</HIREDATE><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>22-FEB-81</HIREDATE><SAL>1250</SAL><COMM>500</COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-81</HIREDATE><SAL>2975</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW>
  <ROW><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>28-SEP-81</HIREDATE><SAL>1250</SAL><COMM>1400</COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>01-MAY-81</HIREDATE><SAL>2850</SAL><COMM></COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>09-JUN-81</HIREDATE><SAL>2450</SAL><COMM></COMM><DEPTNO>10</DEPTNO></ROW>
  <ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-87</HIREDATE><SAL>3000</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW>
  <ROW><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><MGR></MGR><HIREDATE>17-NOV-81</HIREDATE><SAL>5000</SAL><COMM></COMM><DEPTNO>10</DEPTNO></ROW>
  <ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>08-SEP-81</HIREDATE><SAL>1500</SAL><COMM>0</COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-87</HIREDATE><SAL>1100</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW>
  <ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><MGR>7698</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>950</SAL><COMM></COMM><DEPTNO>30</DEPTNO></ROW>
  <ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>3000</SAL><COMM></COMM><DEPTNO>20</DEPTNO></ROW>
  <ROW><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>23-JAN-82</HIREDATE><SAL>1300</SAL><COMM></COMM><DEPTNO>10</DEPTNO></ROW>
</RESULTS>

COLUMN COL001 FORMAT A10
COLUMN COL002 FORMAT A10
COLUMN COL003 FORMAT A10
COLUMN COL004 FORMAT A10
COLUMN COL005 FORMAT A10
COLUMN COL006 FORMAT A10
COLUMN COL007 FORMAT A10
COLUMN COL008 FORMAT A10
SET LINESIZE 110
3

CSV Data

In the following example we use the function to load the contents of the "emp.csv" file into a BLOB, so we can pass it into the function as the parameter. We also pass in the file name as the parameter. The type of data to load can be inferred from the file extension. Alternatively we could use the or parameters to indicate the type of data, but using the file name is simple. The select list includes the line number and the first eight columns. For CSV data, by default the first row contains the column headers, then we get the 14 rows of data we would expect. We could remove the column headers row by adding the parameter into the function call.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content   => file_to_blob('TEMP_DIR', 'emp.csv'),
           p_file_name => 'emp.csv'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       17-DEC-80  800                   20
          3 7499       ALLEN      SALESMAN   7698       20-FEB-81  1600       300        30
          4 7521       WARD       SALESMAN   7698       22-FEB-81  1250       500        30
          5 7566       JONES      MANAGER    7839       02-APR-81  2975                  20
          6 7654       MARTIN     SALESMAN   7698       28-SEP-81  1250       1400       30
          7 7698       BLAKE      MANAGER    7839       01-MAY-81  2850                  30
          8 7782       CLARK      MANAGER    7839       09-JUN-81  2450                  10
          9 7788       SCOTT      ANALYST    7566       19-APR-87  3000                  20
         10 7839       KING       PRESIDENT             17-NOV-81  5000                  10
         11 7844       TURNER     SALESMAN   7698       08-SEP-81  1500       0          30
         12 7876       ADAMS      CLERK      7788       23-MAY-87  1100                  20
         13 7900       JAMES      CLERK      7698       03-DEC-81  950                   30
         14 7902       FORD       ANALYST    7566       03-DEC-81  3000                  20
         15 7934       MILLER     CLERK      7782       23-JAN-82  1300                  10

15 rows selected.

SQL>
4

JSON Data

In the following example we use the "emp.json" file. It's similar to the CSV example, but we need to use the parameter to show where the array of row data can be found, which in this case is the "results.items" item. For JSON data, the column names aren't included in the output by default, so we use the parameter to include them. Notice the column order is not what we might have expected.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.json'),
           p_file_name       => 'emp.json',
           p_row_selector    => 'results.items',
           p_add_headers_row => 'Y'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            JOB        MGR        SAL        COMM       EMPNO      ENAME      DEPTNO     HIREDATE
          1 CLERK      7902       800                   7369       SMITH      20         17-DEC-80
          2 SALESMAN   7698       1600       300        7499       ALLEN      30         20-FEB-81
          3 SALESMAN   7698       1250       500        7521       WARD       30         22-FEB-81
          4 MANAGER    7839       2975                  7566       JONES      20         02-APR-81
          5 SALESMAN   7698       1250       1400       7654       MARTIN     30         28-SEP-81
          6 MANAGER    7839       2850                  7698       BLAKE      30         01-MAY-81
          7 MANAGER    7839       2450                  7782       CLARK      10         09-JUN-81
          8 ANALYST    7566       3000                  7788       SCOTT      20         19-APR-87
          9 PRESIDENT             5000                  7839       KING       10         17-NOV-81
         10 SALESMAN   7698       1500       0          7844       TURNER     30         08-SEP-81
         11 CLERK      7788       1100                  7876       ADAMS      20         23-MAY-87
         12 CLERK      7698       950                   7900       JAMES      30         03-DEC-81
         13 ANALYST    7566       3000                  7902       FORD       20         03-DEC-81
         14 CLERK      7782       1300                  7934       MILLER     10         23-JAN-82

15 rows selected.

SQL>
5

XML Data

In the following example we use the "emp.xml" file. It's similar to the CSV example, but we need to use the parameter show where the array of row data can be found, which in this case is the "/RESULTS/ROW" tag. For XML data, column names aren't included in the output by default, so we use the parameter to include them.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xml'),
           p_file_name       => 'emp.xml',
           p_row_selector    => '/RESULTS/ROW',
           p_add_headers_row => 'Y'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
            EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       DEPTNO
          1 7369       SMITH      CLERK      7902       17-DEC-80  800                   20
          2 7499       ALLEN      SALESMAN   7698       20-FEB-81  1600       300        30
          3 7521       WARD       SALESMAN   7698       22-FEB-81  1250       500        30
          4 7566       JONES      MANAGER    7839       02-APR-81  2975                  20
          5 7654       MARTIN     SALESMAN   7698       28-SEP-81  1250       1400       30
          6 7698       BLAKE      MANAGER    7839       01-MAY-81  2850                  30
          7 7782       CLARK      MANAGER    7839       09-JUN-81  2450                  10
          8 7788       SCOTT      ANALYST    7566       19-APR-87  3000                  20
          9 7839       KING       PRESIDENT             17-NOV-81  5000                  10
         10 7844       TURNER     SALESMAN   7698       08-SEP-81  1500       0          30
         11 7876       ADAMS      CLERK      7788       23-MAY-87  1100                  20
         12 7900       JAMES      CLERK      7698       03-DEC-81  950                   30
         13 7902       FORD       ANALYST    7566       03-DEC-81  3000                  20
         14 7934       MILLER     CLERK      7782       23-JAN-82  1300                  10

15 rows selected.

SQL>
6

XLSX Data

In the following example we use the "emp.xslx" file. If we are interested in the first worksheet, we can keep things really simple and use it in a similar way to the CSV example. If there are multiple worksheets, we use the parameter to decide which sheet we want to use. We list the worksheets in the document using the function. We then use the value in the parse call. For more information see: - APEX_DATA_PARSER - APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xlsx'),
           p_file_name       => 'emp.xlsx'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       1980-12-17 800                              20
          3 7499       ALLEN      SALESMAN   7698       1981-02-20 1600       300        300        30
          4 7521       WARD       SALESMAN   7698       1981-02-22 1250       500        500        30
          5 7566       JONES      MANAGER    7839       1981-04-02 2975                             20
          6 7654       MARTIN     SALESMAN   7698       1981-09-28 1250       1400       1400       30
          7 7698       BLAKE      MANAGER    7839       1981-05-01 2850                             30
          8 7782       CLARK      MANAGER    7839       1981-06-09 2450                             10
          9 7788       SCOTT      ANALYST    7566       1987-04-19 3000                             20
         10 7839       KING       PRESIDENT             1981-11-17 5000                             10
         11 7844       TURNER     SALESMAN   7698       1981-09-08 1500       0          0          30
         12 7876       ADAMS      CLERK      7788       1987-05-23 1100                             20
         13 7900       JAMES      CLERK      7698       1981-12-03 950                              30
         14 7902       FORD       ANALYST    7566       1981-12-03 3000                             20
         15 7934       MILLER     CLERK      7782       1982-01-23 1300                             10

15 rows selected.

SQL>

COLUMN sheet_display_name FORMAT A30
COLUMN sheet_file_name FORMAT A30

SELECT sheet_display_name, sheet_file_name
FROM   TABLE(
         APEX_DATA_PARSER.get_xlsx_worksheets(
           p_content => file_to_blob('TEMP_DIR', 'emp.xlsx')
         )
       );

SHEET_DISPLAY_NAME             SHEET_FILE_NAME
------------------------------ ------------------------------
emp sheet                      sheet1.xml

SQL>

SELECT line_number, col001, col002, col003, col004, col005, col006, col007, col008
FROM   TABLE(
         APEX_DATA_PARSER.parse(
           p_content         => file_to_blob('TEMP_DIR', 'emp.xlsx'),
           p_file_name       => 'emp.xlsx',
           p_xlsx_sheet_name => 'sheet1.xml'
         )
       );

LINE_NUMBER COL001     COL002     COL003     COL004     COL005     COL006     COL007     COL007     COL008
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          1 EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       COMM       DEPTNO
          2 7369       SMITH      CLERK      7902       1980-12-17 800                              20
          3 7499       ALLEN      SALESMAN   7698       1981-02-20 1600       300        300        30
          4 7521       WARD       SALESMAN   7698       1981-02-22 1250       500        500        30
          5 7566       JONES      MANAGER    7839       1981-04-02 2975                             20
          6 7654       MARTIN     SALESMAN   7698       1981-09-28 1250       1400       1400       30
          7 7698       BLAKE      MANAGER    7839       1981-05-01 2850                             30
          8 7782       CLARK      MANAGER    7839       1981-06-09 2450                             10
          9 7788       SCOTT      ANALYST    7566       1987-04-19 3000                             20
         10 7839       KING       PRESIDENT             1981-11-17 5000                             10
         11 7844       TURNER     SALESMAN   7698       1981-09-08 1500       0          0          30
         12 7876       ADAMS      CLERK      7788       1987-05-23 1100                             20
         13 7900       JAMES      CLERK      7698       1981-12-03 950                              30
         14 7902       FORD       ANALYST    7566       1981-12-03 3000                             20
         15 7934       MILLER     CLERK      7782       1982-01-23 1300                             10

15 rows selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!