APEX

Hello Team,

Can anyone please help on the below. I have a table temp_emp and need to update the existing records using the below.

Step 1 - below works fine by loading excel to Apex collection. (Click button - Processing >> Excecute code >> PL/SQL)

Step 2 - I am able to view the content of the uploaded excel in the interactive report using step 2 below.

Step 3 - Unable to update the data in collection to the table. No error pops up and no update happening to the table.

************************************************************************

STEP 1 : To load data from excel to collection

************************************************************************

begin
IF APEX_COLLECTION.COLLECTION_EXISTS('loaddata') THEN
APEX_COLLECTION.TRUNCATE_COLLECTION('loaddata');
END IF;

IF NOT APEX_COLLECTION.COLLECTION_EXISTS('loaddata') THEN
APEX_COLLECTION.CREATE_COLLECTION('loaddata');
END IF;

for r1 in (select * from
apex_application_temp_files f, table( apex_data_parser.parse(
p_content => f.blob_content,
p_add_headers_row => 'Y',
--p_store_profile_to_collection => 'loaddata',
p_file_name => f.filename,
p_skip_rows => 1 ) ) p
where f.name = :P4_UPLOAD_FILE
)

   loop  
   APEX\_COLLECTION.ADD\_MEMBER(P\_COLLECTION\_NAME => 'loaddata',  
                                       p\_c001            => nvl(REPLACE(r1.col001,'-',''),0),  
                                       p\_c002            => nvl(REPLACE(r1.col002,'-',''),0),  
                                       P\_c003            => nvl(REPLACE(r1.col003,'-',''),0),  
                                       p\_c004            => nvl(REPLACE(r1.col004,'-',''),0)                                             
                                       );        
   END LOOP;   
   end;

******************************************************************************

STEP 2 : VIEW THE LOADED DATA IN INTERACTIVE REPORT

select line_number, col001, col002, col003, col004
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 => 'loaddata',
p_file_name => f.filename ) ) p
where f.name = :P4_UPLOAD_FILE
and line_number > 1

******************************************************************************

STEP 3 : TO UPDATE TABLE

MERGE INTO temp_emp target
USING (select c001, c002, c003, c004 from apex_collections where collection_name='loaddata') source
ON (target.empid = source.c001)
WHEN MATCHED THEN UPDATE
SET target.empname = source.c002,
target.empsal = source.c003,
target.hiredt = source.c004;

apex_collection.delete_collection( p_collection_name => 'loaddata' );
commit;

******************************************************************************

Table structure : TEMP_EMP

Column name | Data type | Nullable |

- - - - - - - - - - - - - - - - - - - -

ID | Number | N
EMPID | Number | Y
EMPNAME | Varchar | Y
EMPSAL | Number | Y
HIREDT | Date | Y

Thanks in advance