Users upload spreadsheets, CSV exports, and JSON or XML feeds, and your code has to turn them into rows. APEX_DATA_PARSER is the parser behind Oracle APEX's Data Loading and Data Upload features, and you can call it yourself: give it a file as a BLOB, usually from apex_application_temp_files after a File Upload item, and it returns rows with the values in columns COL001 to COL300, ready for a single INSERT ... SELECT. APEX_ZIP complements it by creating ZIP archives and reading their contents.
This guide covers both packages with tested examples and their real output, including file profiles, worksheet names, and a CSV backslash setting that breaks Windows paths.
Quick Reference
| Task | Subprogram |
|---|---|
| Turn a CSV, XLSX, JSON, XML, or ICS file into rows | APEX_DATA_PARSER.PARSE |
| List an Excel workbook's worksheets | APEX_DATA_PARSER.GET_XLSX_WORKSHEETS |
| Describe a file's columns and settings | GET_FILE_PROFILE, DISCOVER, GET_COLUMNS, JSON_TO_PROFILE |
| Check a file's type | GET_FILE_TYPE, ASSERT_FILE_TYPE |
| Change CSV backslash handling | SET_PARSER_FLAGS |
| Create a ZIP archive | APEX_ZIP.ADD_FILE, FINISH |
| Read a ZIP archive | APEX_ZIP.GET_DIR_ENTRIES, GET_FILE_CONTENT |
How to Run These Examples
The examples ran in Oracle APEX 26.1. To keep them self-contained, they build their files in memory with apex_util.clob_to_blob instead of reading uploads; in an application you would pass the BLOB of an uploaded file instead. Run them as your workspace schema in SQL Developer, SQLcl, SQL*Plus, or SQL Workshop with server output switched on. The output under each example is exactly what the database printed.
Two examples read the orb_products and orb_categories tables of the Orbit Outfitters sample schema, which you can install from the orb_tables repository on GitHub. The Excel example also needs an APEX session of application 200, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL.
Parsing Files: APEX_DATA_PARSER
PARSE
A pipelined table function. The file type comes from p_file_type, one of apex_data_parser.c_file_type_xlsx, c_file_type_csv, c_file_type_xml, c_file_type_json, or c_file_type_ics, or from the extension of p_file_name. It detects the columns and their data types, and whether a CSV or XLSX file has a header row.
Syntax:
apex_data_parser.parse(p_content in blob, p_file_name in varchar2 default null, p_file_type in t_file_type default null,
p_file_profile in clob default null, p_detect_data_types in varchar2 default 'Y', p_decimal_char in varchar2 default null,
p_xlsx_sheet_name in varchar2 default null, p_row_selector in varchar2 default null,
p_csv_row_delimiter in varchar2 default LF, p_csv_col_delimiter in varchar2 default null, p_csv_enclosed in varchar2 default '"',
p_skip_rows in pls_integer default null, p_add_headers_row in varchar2 default 'N', p_nullif in varchar2 default null,
p_force_trim_whitespace in varchar2 default 'N', p_file_charset in varchar2 default 'AL32UTF8',
p_max_rows in number default null, p_return_rows in number default null, p_store_profile_to_collection in varchar2 default null,
p_xml_namespaces in varchar2 default null, p_fix_excel_precision in varchar2 default 'N')
return apex_t_parser_table pipelined| Parameter | Description |
|---|---|
| p_file_profile | A profile from an earlier parse or from DISCOVER. The file is parsed with those columns and settings instead of detecting them. |
| p_decimal_char | The decimal character, used when detecting numbers. |
| p_xlsx_sheet_name | The worksheet's file name, such as sheet1.xml, from GET_XLSX_WORKSHEETS. Defaults to the first sheet. |
| p_row_selector | For JSON, the path of the array holding the rows; for XML, an XPath to the row elements. |
| p_csv_col_delimiter, p_csv_enclosed | The column delimiter, detected by default, and the quote character. |
| p_skip_rows, p_add_headers_row | Rows to skip at the start; 'Y' returns the header row as the first row. |
| p_nullif, p_force_trim_whitespace | A value to return as null; trim whitespace even inside quotes. |
| p_max_rows, p_return_rows | Rows to parse, and rows to return. Parsing further refines the detected data types. |
| p_store_profile_to_collection | The name of a collection to store the profile in, for a later page. |
| p_fix_excel_precision | 'Y' rounds XLSX numbers to Excel's 15 significant digits, removing floating-point artifacts. |
Each row has a LINE_NUMBER, the columns COL001 to COL300 as strings, CLOB01 to CLOB10 for long JSON and XML values, and ROW_INFO. Values come back exactly as they are in the file, so 249,00 stays 249,00; convert them with the format the profile detected.
A semicolon-separated CSV with a decimal comma:
Example:
-- the .csv extension picks the CSV parser; p_skip_rows => 1 skips the header line
with f as (
select apex_util.clob_to_blob(
'SKU;Product;Price;Launched' || chr(10) ||
'TNT-2P;Trailblazer 2-Person Tent;249,00;14.03.2025' || chr(10) ||
'BAG-0F;"Nightfall 0° Down Bag";389,50;01.10.2024' || chr(10) ||
'LMP-HD;Headlamp "Beam 400";39,95;') as content
from dual )
select p.line_number, p.col001, p.col002, p.col003, p.col004
from f,
table(apex_data_parser.parse(
p_content => f.content,
p_file_name => 'prices.csv',
p_decimal_char => ',',
p_skip_rows => 1)) p;Output:
LINE_NUMBER COL001 COL002 COL003 COL004
----------- ------ ------------------------- ------ ----------
2 TNT-2P Trailblazer 2-Person Tent 249,00 14.03.2025
3 BAG-0F Nightfall 0° Down Bag 389,50 01.10.2024
4 LMP-HD Headlamp "Beam 400" 39,95 (null)The parser detected the semicolon itself, handled the quoted values, and returned the empty date as null.
For JSON, the columns are ordered as the parser found the members, not as they appear in the file, so read the column list from the profile rather than relying on positions:
Example:
-- p_row_selector names the array that holds the rows
with f as (
select apex_util.clob_to_blob('{"order":"ORD-10042","items":['
|| '{"sku":"TNT-2P","qty":2,"price":249},'
|| '{"sku":"BAG-0F","qty":1,"price":389.5}]}') as content
from dual )
select p.line_number, p.col001, p.col002, p.col003
from f,
table(apex_data_parser.parse(p_content => f.content,
p_file_name => 'order.json',
p_row_selector => 'items')) p;
-- which member landed in which column
select column_position, column_name, data_type
from table(apex_data_parser.get_columns(apex_data_parser.get_file_profile));Output:
LINE_NUMBER COL001 COL002 COL003
----------- ------ ------ ------
1 2 TNT-2P 249
2 1 BAG-0F 389.5
COLUMN_POSITION COLUMN_NAME DATA_TYPE
--------------- ----------- ------------
1 QTY NUMBER
2 SKU VARCHAR2(50)
3 PRICE NUMBERqty landed in COL001 even though sku comes first in the file.
For XML, p_row_selector is an XPath to the row elements:
Example:
select p.line_number, p.col001, p.col002
from table(apex_data_parser.parse(
p_content => apex_util.clob_to_blob(
'<stores><store><code>DEN</code><city>Denver</city></store>'
|| '<store><code>SEA</code><city>Seattle</city></store></stores>'),
p_file_name => 'stores.xml',
p_row_selector => '/stores/store')) p;Output:
LINE_NUMBER COL001 COL002
----------- ------ -------
1 DEN Denver
2 SEA SeattleGET_XLSX_WORKSHEETS
Returns an XLSX file's worksheets with SHEET_SEQUENCE, SHEET_DISPLAY_NAME (the tab name), SHEET_FILE_NAME (what p_xlsx_sheet_name takes), and SHEET_PATH.
Syntax:
apex_data_parser.get_xlsx_worksheets(p_content in blob) return apex_t_parser_worksheets
This example needs a session of application 200, page 1. It creates a real workbook from the orb_products table with APEX_DATA_EXPORT and reads it back.
Example:
declare
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
begin
-- create a real workbook from ORBIT data
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select sku, product_name, unit_price
from orb_products order by product_id
fetch first 3 rows only');
l_export := apex_data_export.export(p_context => l_context,
p_format => apex_data_export.c_format_xlsx);
apex_exec.close(l_context);
for s in (select * from table(apex_data_parser.get_xlsx_worksheets(l_export.content_blob))) loop
dbms_output.put_line('sheet ' || s.sheet_sequence || ': ' || s.sheet_display_name
|| ' (' || s.sheet_file_name || ')');
end loop;
for r in (select line_number, col001, col002, col003
from table(apex_data_parser.parse(
p_content => l_export.content_blob,
p_file_name => 'products.xlsx',
p_xlsx_sheet_name => 'sheet1.xml'))) loop
dbms_output.put_line(r.line_number || ' | ' || r.col001 || ' | ' || r.col002 || ' | ' || r.col003);
end loop;
end;
/Output:
sheet 1: Sheet1 (sheet1.xml) 1 | SKU | PRODUCT_NAME | UNIT_PRICE 2 | TNT-1001 | Trailblazer 1-Person Tent | 239.99 3 | TNT-1002 | Trailblazer 2-Person Tent | 274.99 4 | TNT-1003 | Basecamp 4-Person Tent | 204.99
Pass the sheet's file name, not its display name, to PARSE. Offering the user a select list of worksheets built from this function is the usual pattern for multi-sheet uploads.
GET_FILE_PROFILE, DISCOVER, and GET_COLUMNS
A file profile is a JSON document that describes a file: its type, delimiters, and header row, and its columns with names, data types, and format masks. GET_FILE_PROFILE returns the profile of the last PARSE call in the session. DISCOVER computes a profile without returning rows, taking the same file parameters as PARSE. GET_COLUMNS turns a profile into rows, with COLUMN_POSITION, COLUMN_NAME, DATA_TYPE, FORMAT_MASK, and more, to show the user or to generate a table.
Syntax:
apex_data_parser.get_file_profile return clob apex_data_parser.discover(p_content in blob, p_file_name in varchar2, ... ) return clob apex_data_parser.get_columns(p_profile in clob) return apex_t_parser_columns
Example:
declare
l_csv blob := apex_util.clob_to_blob('SKU,Price' || chr(10) ||
'TNT-2P,249.00' || chr(10) ||
'BAG-0F,389.50');
l_rows pls_integer := 0;
begin
for r in (select * from table(apex_data_parser.parse(l_csv, 'p.csv'))) loop
l_rows := l_rows + 1;
end loop;
dbms_output.put_line(l_rows || ' rows parsed');
-- the profile of the last parse() call, printed compactly
dbms_output.put_line(json_object_t.parse(apex_data_parser.get_file_profile).to_clob);
end;
/Output:
3 rows parsed
{"file-type":2,"file-encoding":"AL32UTF8","headings-in-first-row":true,"csv-delimiter":",","csv-enclosed":"\"","force-trim-whitespace":false,"columns":[{"name":"SKU","data-type":1,"data-type-len":50,"selector":"SKU","is-json":false},{"name":"PRICE","data-type":2,"decimal-char":".","selector":"Price","is-json":false}],"parsed-rows":3}Example:
select c.column_position, c.column_name, c.data_type, c.format_mask
from table(apex_data_parser.get_columns(
apex_data_parser.discover(
p_content => apex_util.clob_to_blob(
'SKU,Product,Price,Launched,In Stock' || chr(10) ||
'TNT-2P,Trailblazer Tent,249.00,14.03.2025,Y' || chr(10) ||
'BAG-0F,Nightfall Bag,389.50,01.10.2024,N'),
p_file_name => 'products.csv'))) c;Output:
COLUMN_POSITION COLUMN_NAME DATA_TYPE FORMAT_MASK
--------------- ----------- ------------ ------------
1 SKU VARCHAR2(50) (null)
2 PRODUCT VARCHAR2(50) (null)
3 PRICE NUMBER (null)
4 LAUNCHED DATE DD"."MM"."RR
5 IN_STOCK BOOLEAN (null)DISCOVER recognized the dates with their format mask and even the Y and N column as a Boolean, which is a good starting point for generating a staging table.
JSON_TO_PROFILE
Converts a profile's JSON into the record type apex_data_parser.t_file_profile, with fields such as file_type, csv_delimiter, first_row_headings, and the columns in file_columns, each with name, data_type (1 VARCHAR2, 2 NUMBER, 3 DATE, as in APEX_EXEC), and format_mask.
Syntax:
apex_data_parser.json_to_profile(p_json in clob) return t_file_profile
Example:
declare
l_profile apex_data_parser.t_file_profile;
begin
l_profile := apex_data_parser.json_to_profile(apex_data_parser.discover(
p_content => apex_util.clob_to_blob('A|B|C' || chr(10) || '1|x|2026-01-31'),
p_file_name => 'pipes.csv'));
dbms_output.put_line('file type: ' || l_profile.file_type);
dbms_output.put_line('delimiter: ' || l_profile.csv_delimiter);
dbms_output.put_line('headings: ' || case when l_profile.first_row_headings then 'yes' else 'no' end);
dbms_output.put_line('columns: ' || l_profile.file_columns.count);
for i in 1 .. l_profile.file_columns.count loop
dbms_output.put_line(' ' || l_profile.file_columns(i).name || ' data type ' || l_profile.file_columns(i).data_type
|| ' ' || l_profile.file_columns(i).format_mask);
end loop;
end;
/Output:
file type: 2 delimiter: | headings: yes columns: 3 A data type 2 B data type 1 C data type 3 YYYY"-"MM"-"DD
GET_FILE_TYPE and ASSERT_FILE_TYPE
GET_FILE_TYPE returns the file type for a file name's extension, and ASSERT_FILE_TYPE whether a name is of a given type, which is handy for validating an upload. Extensions it does not know count as CSV.
Syntax:
apex_data_parser.get_file_type(p_file_name in varchar2) return t_file_type apex_data_parser.assert_file_type(p_file_name in varchar2, p_file_type in t_file_type) return boolean
Example:
begin
for f in (select column_value as name
from table(apex_t_varchar2('orders.xlsx', 'ORDERS.CSV', 'feed.json',
'stores.xml', 'events.ics', 'notes.txt'))) loop
dbms_output.put_line(rpad(f.name, 12) || ' type ' ||
nvl(to_char(apex_data_parser.get_file_type(f.name)), '(null)') || ', is CSV: ' ||
case when apex_data_parser.assert_file_type(f.name, apex_data_parser.c_file_type_csv)
then 'yes' else 'no' end);
end loop;
end;
/Output:
orders.xlsx type 1, is CSV: no ORDERS.CSV type 2, is CSV: yes feed.json type 4, is CSV: no stores.xml type 3, is CSV: no events.ics type 5, is CSV: no notes.txt type 2, is CSV: yes
Because unknown extensions count as CSV, notes.txt passed as a CSV file. When validating uploads, check the extension against an explicit list as well.
SET_PARSER_FLAGS
Sets a parser flag for the session. The one flag is CSV_BACKSLASH_ESCAPING: with Y, the default, a backslash escapes the quote character, which breaks Windows paths that end in a backslash.
Syntax:
apex_data_parser.set_parser_flags(p_name in varchar2, p_value in varchar2)
Example:
declare
l_csv blob := apex_util.clob_to_blob('Path,Size' || chr(10) || '"C:\Temp\",12');
begin
for flag in (select column_value as v from table(apex_t_varchar2('Y', 'N'))) loop
apex_data_parser.set_parser_flags(p_name => 'CSV_BACKSLASH_ESCAPING', p_value => flag.v);
for r in (select col001, col002
from table(apex_data_parser.parse(p_content => l_csv, p_file_name => 'x.csv',
p_skip_rows => 1))) loop
dbms_output.put_line(flag.v || ': col001=[' || r.col001 || '] col002=[' || r.col002 || ']');
end loop;
end loop;
end;
/Output:
Y: col001=[C:\Temp\",12] col002=[] N: col001=[C:\Temp\] col002=[12]
With the default, the backslash before the closing quote swallowed the quote and merged two columns into one. Setting the flag to N parsed the path correctly. For the declarative side of file loading, see data sources, data loads, and duality views, and for uploading files in the first place, uploading files in Oracle APEX.
ZIP Files: APEX_ZIP
ADD_FILE and FINISH
ADD_FILE adds a file to a ZIP archive held in a BLOB, creating the BLOB on the first call; a path in the file name creates folders. FINISH writes the archive's directory, so call it once, after the last file and before the BLOB is used.
Syntax:
apex_zip.add_file(p_zipped_blob in out nocopy blob, p_file_name in varchar2, p_content in blob) apex_zip.finish(p_zipped_blob in out nocopy blob)
This example reads the sample schema's orb_categories table.
Example:
declare
l_zip blob;
begin
-- one CSV per top-level category, plus a README in the root
for c in (select category_id, category_name
from orb_categories
where parent_category_id is null and category_id <= 3
order by category_id) loop
apex_zip.add_file(
p_zipped_blob => l_zip,
p_file_name => 'categories/' || lower(c.category_name) || '.csv',
p_content => apex_util.clob_to_blob(
'category_id,category_name' || chr(10) ||
c.category_id || ',' || c.category_name));
end loop;
apex_zip.add_file(l_zip, 'README.txt', apex_util.clob_to_blob('Exported from ORBIT'));
apex_zip.finish(p_zipped_blob => l_zip); -- writes the central directory
dbms_output.put_line('zip size: ' || dbms_lob.getlength(l_zip) || ' bytes');
dbms_output.put_line('signature: ' || utl_raw.cast_to_varchar2(dbms_lob.substr(l_zip, 2, 1)));
end;
/Output:
zip size: 587 bytes signature: PK
The archive starts with the PK signature every ZIP file has. To send it to the browser, download the BLOB with apex_http.download. There are more examples in the APEX_ZIP example and in how to zip a file in PL/SQL.
GET_DIR_ENTRIES and GET_FILE_CONTENT
GET_DIR_ENTRIES reads an archive's directory into apex_zip.t_dir_entries, a table indexed by file name whose t_dir_entry records have file_name, uncompressed_length, and is_directory; p_only_files set to false includes folders. GET_FILE_CONTENT with a t_dir_entry returns a file's content without searching the archive again. p_encoding is the character set of the file names, for archives not made with UTF-8 names.
Syntax:
apex_zip.get_dir_entries(p_zipped_blob in blob, p_only_files in boolean default true,
p_encoding in varchar2 default null) return t_dir_entries
apex_zip.get_file_content(p_zipped_blob in blob, p_dir_entry in t_dir_entry) return blobExample:
declare
l_zip blob;
l_dir apex_zip.t_dir_entries;
l_name varchar2(32767);
l_file blob;
begin
apex_zip.add_file(l_zip, 'orders/2026-03.csv', apex_util.clob_to_blob('ORD-10042,1047.30'));
apex_zip.add_file(l_zip, 'orders/2026-04.csv', apex_util.clob_to_blob('ORD-10077,249.00'));
apex_zip.add_file(l_zip, 'README.txt', apex_util.clob_to_blob('Monthly order files'));
apex_zip.finish(l_zip);
l_dir := apex_zip.get_dir_entries(p_zipped_blob => l_zip); -- indexed by file name
l_name := l_dir.first;
while l_name is not null loop
l_file := apex_zip.get_file_content(p_zipped_blob => l_zip,
p_dir_entry => l_dir(l_name));
dbms_output.put_line(rpad(l_name, 20) || lpad(l_dir(l_name).uncompressed_length, 3)
|| ' bytes: ' || utl_raw.cast_to_varchar2(dbms_lob.substr(l_file, 100, 1)));
l_name := l_dir.next(l_name);
end loop;
-- deprecated signature 1: look a file up by name
dbms_output.put_line('by name: ' || utl_raw.cast_to_varchar2(dbms_lob.substr(
apex_zip.get_file_content(p_zipped_blob => l_zip, p_file_name => 'README.txt'), 100, 1)));
end;
/Output:
README.txt 19 bytes: Monthly order files orders/2026-03.csv 17 bytes: ORD-10042,1047.30 orders/2026-04.csv 16 bytes: ORD-10077,249.00 by name: Monthly order files
Because the directory is indexed by file name, the loop visits the files in alphabetical order. For unpacking uploaded archives, see also how to unzip a file in PL/SQL.
Deprecated: GET_FILES and GET_FILE_CONTENT by Name
GET_FILES returns the file names as apex_zip.t_files, a list, and GET_FILE_CONTENT with p_file_name finds a file by name, reading the directory each time. Both are deprecated; use GET_DIR_ENTRIES and GET_FILE_CONTENT with a directory entry, which read the directory only once. The previous example's last line uses the by-name form; this one shows GET_FILES:
Example:
declare
l_zip blob;
l_files apex_zip.t_files;
begin
apex_zip.add_file(l_zip, 'a/one.txt', apex_util.clob_to_blob('1'));
apex_zip.add_file(l_zip, 'b/two.txt', apex_util.clob_to_blob('2'));
apex_zip.finish(l_zip);
l_files := apex_zip.get_files(p_zipped_blob => l_zip); -- deprecated: use get_dir_entries
for i in 1 .. l_files.count loop
dbms_output.put_line(i || ': ' || l_files(i));
end loop;
end;
/Output:
1: a/one.txt 2: b/two.txt
Conclusion
APEX_DATA_PARSER.PARSE turns CSV, XLSX, JSON, XML, and ICS files into rows with string columns COL001 to COL300, detecting delimiters, headers, and data types along the way. GET_XLSX_WORKSHEETS lists a workbook's sheets, and the file profile from GET_FILE_PROFILE or DISCOVER describes the columns, which GET_COLUMNS and JSON_TO_PROFILE make easy to use. Convert values yourself using the detected format masks, read JSON column positions from the profile, and switch off backslash escaping for CSV files with Windows paths. APEX_ZIP builds archives with ADD_FILE and FINISH, and reads them efficiently through GET_DIR_ENTRIES.
