Load Data Using Pl_SQL APEX_DATA_PARSER

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Load Data Using Pl_SQL APEX_DATA_PARSER

Post by admin »

Code: Select all

-- The APEX_DATA_PARSER.PARSE function allows to "select" up to 300 columns (COL001 to COL300) from the
-- parsed file. This example uses the following parameters.
--
--     P_CONTENT             the file content to be parsed as a BLOB
--     P_FILE_NAME           the name of the file; only used to derive the file type. 
--     P_ADD_HEADERS_ROW     add the detected attribute names for XML or JSON files as the first row
--     P_XLSX_SHEET_NAME     For XLSX workbooks. The name of the worksheet to parse. If omitted or NULL, the function will
--                           use the first worksheet found.
--      
select line_number, col001, col002, col003, col004, col005, col006, col007, col008, col009, col010
       -- more columns (col011 to col300) can be selected here.
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_add_headers_row             => 'Y',
                  p_xlsx_sheet_name             => :P31_XLSX_WORKSHEET,
                  p_max_rows                    => 500,
                  p_store_profile_to_collection => 'FILE_PARSER_COLLECTION',
                  p_file_name                   => f.filename ) ) p
 where f.name = :P31_FILE
-- Discovered Columns

Code: Select all

select column_position, column_name, data_type, format_mask
  from apex_collections c, 
       table( apex_data_parser.get_columns( p_profile => c.clob001 ) )
 where c.collection_name = 'FILE_PARSER_COLLECTION' 
   and c.seq_id = 1

Code: Select all

lower( :P31_FILE ) like '%.xlsx' or
lower( :P31_FILE ) like '%.xml' or
lower( :P31_FILE ) like '%.csv' or
lower( :P31_FILE ) like '%.txt' or
lower( :P31_FILE ) like '%.js' or
lower( :P31_FILE ) like '%.json' or
lower( :P31_FILE ) like '%.geojson'
-- Load into table

Code: Select all

INSERT INTO EBA_DEMO_LOAD_EMP (empno,
                               ename,
                               job,
                               mgr,
                               hiredate,
                               sal,
                               comm,
                               deptno)
    (SELECT col001,
            col002,
            col003,
            col004,
            TO_DATE (col005, 'mm/dd/yyyy'),
            col006,
            col007,
            col008
       FROM apex_application_temp_files f, apex_data_parser.parse      (
p_content => f.blob_content,
p_skip_rows => 1,
p_file_type => apex_data_parser.c_file_type_csv )
where f.name = :P31_FILE
)

Code: Select all

select sheet_display_name, sheet_file_name
  from apex_application_temp_files f,
       table( apex_data_parser.get_xlsx_worksheets( p_content => blob_content ) ) p
 where f.name = :P31_FILE
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests