Step 1:
=====
Create 1st Region.
Name: Upload Data File
Create Button: UPLOAD, SAVE & CLEAR
Create Item: P13741_UPLOAD_FILE
Type: File Upload
---------------------------------
Create 2nd Region.
Name: Upload Data View
Type: Interactive Report
SQL Query:
SELECT
COL.c001 AS CUSTOMER_ID,
COL.c002 AS GROUP_CODE,
COL.c003 AS COUNTRY_ID,
TO_DATE(c004, 'DD-MM-YYYY') AS START_DATE_ACTIVE, -- String → DATE
COL.c005 as REMARKS
FROM APEX_COLLECTIONS COL
WHERE COL.collection_name = 'UPLOAD_CUST_DATA_COL'
Step 2:
=====
Create Process for show excel data
Name: INSERT_COLLECTION_PRC
PL/SQL:
/**********Insert Excel Data into Collection*******************/
declare
vXid varchar2(100);
vXnumgrp varchar2(100);
vErrorNum varchar2(100);
vErrorMsg varchar2(500);
vIpadr varchar2(500);
begin
--=== Initialization ====
IF APEX_COLLECTION.COLLECTION_EXISTS('UPLOAD_CUST_DATA_COL') THEN
APEX_COLLECTION.TRUNCATE_COLLECTION('UPLOAD_CUST_DATA_COL');
END IF;
IF NOT APEX_COLLECTION.COLLECTION_EXISTS('UPLOAD_CUST_DATA_COL') THEN
APEX_COLLECTION.CREATE_COLLECTION('UPLOAD_CUST_DATA_COL');
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 => 'FILE_PROV_CASH',
p_file_name => f.filename,
p_skip_rows => 1 ) ) p /* This line will skip excel the first row, as I contain heading only */
where f.name = :P13741_UPLOAD_FILE /* Page Item name */
)
loop
APEX_COLLECTION.ADD_MEMBER(P_COLLECTION_NAME => 'UPLOAD_CUST_DATA_COL' ,
--EXCEL_COLUMN COLLECTION_COLUMN
p_c001 => nvl(r1.col001,0), -- CUSTOMER_ID
p_c002 => nvl(r1.col002,0), -- GROUP_CODE
p_c003 => nvl(r1.col003,0), -- COUNTRY_ID
p_c004 => r1.col004, -- START_DATE_ACTIVE
------------------------------------------------------------
p_c005 => r1.col005 -- REMARKS
);
END LOOP;
exception when others then
vErrorNum :=sqlcode;
vErrorMsg :=sqlerrm;
generate_errorlog_prc(:g_zid,:g_xbid,'ERROR','Process:INSERT_COLLECTION_PRC',verrornum ,verrormsg ,:P22085_TXN_SOURCE,:APP_USER,:APP_ID,:APP_PAGE_ID,:SESSION,vipadr,null);
end;
When Button Pressed: UPLOAD
----------------------------------------------------
Create a Process for inserting Excel data into a database table
Name: INSERT_TABLE_PRC
PL/SQL:
/************Insert Data into Table*************/
declare
vXid number;
vXnumgrp varchar2(100);
vErrorNum varchar2(100);
vErrorMsg varchar2(500);
vIpadr varchar2(500);
begin
--=== Initialization ====
FOR R1 IN (
SELECT
COL.c001 AS CUSTOMER_ID,
COL.c002 AS GROUP_CODE,
COL.c003 AS COUNTRY_ID,
TO_DATE(c004, 'DD-MM-YYYY') AS START_DATE_ACTIVE,
COL.c005 AS REMARKS
FROM APEX_COLLECTIONS COL
WHERE COL.collection_name = 'UPLOAD_CUST_DATA_COL'
)
LOOP
INSERT INTO AR_CUSTTERRSRZMDM
(ZTIME, ZUTIME, CID, ZID, XLID, XBID, COMBINATION_ID, CUSTOMER_ID, GROUP_CODE, COUNTRY_ID, START_DATE_ACTIVE, REMARKS)
VALUES (SYSDATE, SYSDATE, :G_CID, :G_ZID, :G_XLID, :G_XBID, AR_CUSTTERRSRZMDM_ID_SEQ.NEXTVAL, R1.CUSTOMER_ID, R1.GROUP_CODE, R1.COUNTRY_ID, R1.START_DATE_ACTIVE, r1.REMARKS );
END LOOP;
COMMIT;
IF APEX_COLLECTION.COLLECTION_EXISTS('UPLOAD_CUST_DATA_COL') THEN
APEX_COLLECTION.TRUNCATE_COLLECTION('UPLOAD_CUST_DATA_COL');
END IF;
exception when others then
vErrorNum :=sqlcode;
vErrorMsg :=sqlerrm;
generate_errorlog_prc(:g_zid,:g_xbid,'ERROR','Process:INSERT_TABLE_PRC',verrornum ,verrormsg ,null,:APP_USER,:APP_ID,:APP_PAGE_ID,:SESSION,vipadr,null);
end;
When Button Pressed: SAVE
------------------------------------------
Step 3:
=====
Select "Clear" Button.
Create Dynamic Action.
Name: Clear_Collection_region
Action: Execute Server-side Code
PL/SQL Code:
IF APEX_COLLECTION.COLLECTION_EXISTS('UPLOAD_CUST_DATA_COL') THEN
APEX_COLLECTION.TRUNCATE_COLLECTION('UPLOAD_CUST_DATA_COL');
END IF;
Again True Action:
Action: Execute JavaScript Code
Code: $('#general_report_view').trigger('apexrefresh');
Select Collection Region (Upload Data View)
Set Static ID: general_report_view
------------------------------------------------------------------------------------------
This Is an Excel File:
