Overview
The APEX_DATA_PARSER package allows you to easily convert simple CSV and XML formatted data into rows and columns from SQL. You must have APEX 19.1 or later installed in the database for this package to be available, but you can use the package independently of APEX.
Technologies and Tools Used
The following technology has been used to achieve this in oracle apex.
- Oracle Apex.
Use Case
The APEX_DATA_PARSER package makes it easy to display CSV and XML 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..
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 TEMP_DIR. We create a directory object and grant access to is from our test user.
CREATE OR REPLACE DIRECTORY TEMP_DIR AS ‘/tmp/’;
GRANT READ, WRITE ON DIRECTORY TEMP_DIR TO testuser1;
The following files are present in the “/tmp” directory on the database server. Their contents are dumps of the data in the EMP table, in the specified format.
PLSQL Function:
The examples below will load the data from these files into a BLOB using the following function.
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;
/
CSV Data:
The “emp.csv” file contains the following data.
“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
XML Data:
The “emp.xml” file contains the following data.
<?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>
</RESULTS>
To Get CSV Data
Use the below query To get CSV data. we use the FILE_TO_BLOB function to load the contents of the “emp.csv” file into a BLOB, so we can pass it into the PARSE function as the P_CONTENT parameter. We also pass in the file name as the P_FILE_NAME parameter.
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
TO Get XML Data
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 7666 17-NOV-81 5000 10
