APEX

I am facing an issue that the data are not exported fully when i use APEX_REGION.EXPORT_DATA built-in function.

Customer has a requirement to export one of the Apex Interactive Report (Saved Report) as CSV file and store it in a table.

I have used APEX_REGION.EXPORT_DATA function to export CSV as blob and store it in a table. This function is working without any error, but it does not export the data fully. It exports only partial data. Also, it does not give any error or exception. To cross check, I have downloaded same report as CSV from Apex interactive Report, that gives full data.

I tried all the possibilities. But this function does not export the full data. Is there any size limitation at apex level for this function that need to be modified or Am i doing any mistake? Please refer below my code and give me suggestions.

DECLARE

   -- The App, Page, Region Static ID and Alias ID are configured in this table for which CSV export to be done..
   
 CURSOR C_CONFIG IS SELECT APP_ID, PAGE_ID, RGN_STATIC_ID, REP_ALIAS_ID, FILE_FORMAT
   FROM T_EXP_IR_REPORT_PAGE_CONFIG
   ORDER BY CONFIG_ID;
   
 CURSOR C_RGN(P_APP_ID NUMBER
             ,P_PAGE_ID    NUMBER
             ,P_STATIC_ID  VARCHAR2
             )IS
   SELECT region_id
   FROM apex_application_page_regions
   WHERE application_id = P_APP_ID
   AND page_id = P_PAGE_ID
   AND static_id = P_STATIC_ID;
   
  CURSOR C_RPT(P_APP_ID NUMBER
             ,P_PAGE_ID    NUMBER
             ,P_ALIAS_ID  NUMBER
             ) IS
  SELECT REPORT_ID 
   FROM APEX_APPLICATION_PAGE_IR_RPT 
   WHERE APPLICATION_ID = P_APP_ID
   AND PAGE_ID = P_PAGE_ID
   AND REPORT_ALIAS = P_ALIAS_ID;
   
   L_EXPORT            apex_data_export.t_export;
   L_REGION_ID         NUMBER;
   L_REPORT_ID         NUMBER;
   L_RESPONSE          CLOB;
   L_USER_ID          CONSTANT apex_workspace_apex_users.user_name%TYPE   := 'SAMPLE_USER';
   L_APP_ID           CONSTANT apex_applications.application_id%TYPE      := 1200;
   L_PAGE_ID          CONSTANT apex_application_pages.page_id%TYPE        := 81;
   
BEGIN

   P_CREATE_APEX_SESSION(P_APEX_USER_ID        => L_USER_ID
                           , P_APP_ID          => L_APP_ID
                           , P_PAGE_ID         => L_PAGE_ID);
   DBMS_OUTPUT.PUT_LINE('SESSION CREATED');
   
   FOR RG IN C_CONFIG
   LOOP
       -- Get the report region
       OPEN C_RGN(RG.APP_ID, RG.PAGE_ID, RG.RGN_STATIC_ID);
       FETCH C_RGN INTO L_REGION_ID;
       CLOSE C_RGN;
       
       DBMS_OUTPUT.PUT_LINE('REGION SELECTED : '||L_REGION_ID);
       
       OPEN C_RPT(RG.APP_ID, RG.PAGE_ID, RG.REP_ALIAS_ID);
       FETCH C_RPT INTO L_REPORT_ID;
       CLOSE C_RPT;
       
       DBMS_OUTPUT.PUT_LINE('REPORT ID SELECTED : '||L_REPORT_ID);
       
       -- Use export_data function to return data from IR in CSV format
       L_EXPORT := apex_region.export_data(
                      p_format         => apex_data_export.c_format_csv,
                      p_page_id        => RG.PAGE_ID,
                      p_region_id      => L_REGION_ID,
                      p_component_id   => L_REPORT_ID,
                      p_as_clob        => FALSE);
                      
       DBMS_OUTPUT.PUT_LINE('REPORT EXPORTED');
       
       INSERT INTO T_EXP_IR_REPORT_FILE
           ( 
             APP_ID
           , PAGE_ID
           , RGN_STATIC_ID
           , REP_ALIAS_ID
           , FILE_NAME
           , MIME_TYPE
           , FILE_BLOB
           )
       VALUES
           ( 
             RG.APP_ID
           , RG.PAGE_ID
           , RG.RGN_STATIC_ID
           , RG.REP_ALIAS_ID
           , RG.FILE_NAME
           , F_GET_MIME_TYPE(RG.FILE_FORMAT)
           , L_EXPORT.content_blob
           );
       COMMIT;
   END LOOP;
END;