Hi,
I need to parse a .csv file and select the records that are present in a collection (this is the SQL code that initializes an Interactive Grid):
with tmp1 as (
select
line_number, col001, col002, col003, col004, col005,
col006, col007, col008, col009, col010, col011, col012
-- more columns (up to col300) can be selected here.
from apex_collections c,
table( apex_data_parser.parse(
p_content => c.blob001,
p_skip_rows => 1,
p_add_headers_row => 'Y',
p_max_rows => 500,
p_file_name => c.c001 ) ) p
where c.collection_name = 'DROPZONE_UPLOAD'),
tmp2 as (
select n001
from apex_collections
where collection_name = 'REGISTRATION_LIST')
select t1.line_number, t1.col001, t1.col002, t1.col003, t1.col004, t1.col005,
t1.col006, t1.col007, t1.col008, t1.col009, t1.col010, t1.col011, t1.col012
from tmp1 t1, tmp2 t2
where t1.line_number = t2.n001;
The 1st collection (DROPZONE_UPLOAD) is created by the Dropzone 2 plugin and contains the blob of the uploaded file
The 2nd collection (REGISTRATION_LIST) contains only the set of rows that must be shown inside an Interactive Grid
The problem is that the query takes a long long time to finish; by looking at the APEX debug window I see that the file parsing is executed many times, as if it was executed once for each record of the REGISTRATION_LIST collection.
Please help me understand what I'm doing wrong .
Thank you.