Bulk Collect From Oracle

Question

In my project I need to get over 6,000,000 records from Oracle database, for improving efficiency. I write a SP and use BULK COLLECT but it is taking longer time and session is getting expired and throws error in UI page

Here is my procedure

PROCEDURE SP_GET_RAW_DATA_EXTRACT(
                                P_I_GPLTO IN VARCHAR2,
                                P_I_YEARS IN VARCHAR2,
                                P_I_PROJ_OWNERS IN VARCHAR2,
                                P_I_IMPLEMENTATIN_LEVELS IN VARCHAR2,
                                P_I_BU_SITES IN t_BU_SITE_IDS,
                                P_O_REPORT OUT REF_CURSOR
                                ) IS
V_COUNT_CNTR NUMBER;
V_SQL VARCHAR2(4000);
V_WHERE VARCHAR2(4000);

TYPE R_ID IS RECORD(
      ID VARCHAR2(4000));
--create a table where each subscript holds a record type
TYPE T_IDS IS TABLE OF R_ID;
V_ALLOC_COLS T_IDS;
V_LVL1           VARCHAR2(1000);
V_LVL2           VARCHAR2(1000);
V_LVL3           VARCHAR2(1000);
V_LVL4           VARCHAR2(1000);
V_LVL5           VARCHAR2(1000);
V_SITE           VARCHAR2(1000);
V_CNTR           VARCHAR2(1000);
BEGIN


 V_SQL:='SELECT 
 "PROGRAMMEID","PROGRAMMENAME","PROJECTID","PROJECTNAME","PROJECTDESCRIPTION","SYNERGYPROJECT","FUTUREREADYPROJECT",
 "PROJECTSTATUS","CANCELREASON","LASTUPDATEDBY","LASTUPDATEDBYNAME","LASTUPDATEDDATE","GPLT0","GPLT1","PROJECTOWNER",
 "PROJECTOWNERNAME","PROJECTDOCUMENTATION","BASELINEVALUECREATION","FINANCEAPPROVAL","VALUELINEID",
 "VALUELINENAME","BULEVEL1","BULEVEL2","BULEVEL3","BULEVEL4","BULEVEL5","COUNTRY","SITE","IMPLEMENTATIONLEVEL","EXPECTEDIL3DATE","GPLT2",
 "COMMODITY_CODE","COMMCODEDESC","VALUEOWNERID","VALUEOWNERNAME","SITELOCALALIGNED","SITELOCAPPROVEDBY","VALUECREATION",
 "BASELINECALCULATION","FINANCEVIEW","WORKINGCAPITALTYPE","INVENTORYLEVER","PRIMPROCLEVER","SECPROCLEVER",
 "PIRMSPENDPUTGBP","SECDSPENDPUTGBP","DIRECTPLACCOUNT","FINANCELEAD","FINANCELEADNAME",
 "CATEGORYFRANCHISE","FISHBONE","IPW","SYNERGYTYPE","RAGSTATUS","SPENDSAVINGSOWNER","TECHNICALSUPPORTNEEDED","SUPPLIERNAME",
 "PIPELINECOMMENTS","DELIVERYSTARTMONTHYEAR","YEAROFDELIVERY","PIPELINECURRENCY","DELIVERYMONTH",YEAR,"PIPELINEAMOUNTLOCAL",
 "PIPELINEAMOUNTGBP","SORTORDER","GPLT0ID","PROJECTOWNERMUDID","ILCODE","LEVEL1ID","LEVEL2ID","LEVEL3ID","LEVEL4ID",
 "LEVEL5ID","COUNTRYID","SITEID","CREATEDBY","CREATEDBYNAME","CREATEDON","TECHNICALCOMPLIANCE","PROJECTSIZE","ESTIMATEDSPENDLOCAL","ESTIMATEDSPENDGBP",
 CASE WHEN FUTUREORGANIZATION = 1 THEN ''New GSK''
WHEN FUTUREORGANIZATION = 2 THEN ''Cx''
ELSE ''NA''
END  "FUTUREORGANIZATION","ARCHIVE_CC" FROM VW_GLXY3_RAWDATA';
 V_WHERE:=' WHERE PROJECTSTATUS IN (''OPEN'',''ARCHIVED'')';

IF P_I_GPLTO IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND GPLT0ID IN(' || P_I_GPLTO ||')';
END IF;
IF P_I_YEARS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND YEAR IN(' || P_I_YEARS ||')';
END IF;
IF P_I_PROJ_OWNERS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND UPPER(PROJECTOWNERMUDID) IN(' || UPPER(P_I_PROJ_OWNERS) ||')';
END IF;
IF P_I_IMPLEMENTATIN_LEVELS IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND ILCODE IN(' || P_I_IMPLEMENTATIN_LEVELS ||')';
END IF;

---IF P_I_BU_SITES IS NOT NULL THEN
--SPLITTING ALL BU SITE DATA INTO BU SITE LEVELS
FOR I IN 1..P_I_BU_SITES.COUNT LOOP
  SELECT COLUMN_VALUE BULK COLLECT
         INTO V_ALLOC_COLS 
    FROM TABLE (SPLIT(P_I_BU_SITES(I), ';')) WHERE rownum <= 50000 ;
   exit when V_ALLOC_COLS.count =0;
IF(NVL(V_ALLOC_COLS(1).ID,0)>0) THEN
  IF V_LVL1 IS NULL THEN
  V_LVL1:=V_ALLOC_COLS(1).ID;
  ELSE
  V_LVL1:= V_LVL1||','||V_ALLOC_COLS(1).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(2).ID,0)>0) THEN
  IF V_LVL2 IS NULL THEN
  V_LVL2:=V_ALLOC_COLS(2).ID;
  ELSE
  V_LVL2:= V_LVL2||','||V_ALLOC_COLS(2).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(3).ID,0)>0) THEN
  IF V_LVL3 IS NULL THEN
  V_LVL3:=V_ALLOC_COLS(3).ID;
  ELSE
  V_LVL3:= V_LVL3||','||V_ALLOC_COLS(3).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(4).ID,0)>0) THEN
  IF V_LVL4 IS NULL THEN
  V_LVL4:=V_ALLOC_COLS(4).ID;
  ELSE
  V_LVL4:= V_LVL4||','||V_ALLOC_COLS(4).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(5).ID,0)>0) THEN
  IF V_LVL5 IS NULL THEN
  V_LVL5:=V_ALLOC_COLS(5).ID;
  ELSE
  V_LVL5:= V_LVL5||','||V_ALLOC_COLS(5).ID;
  END IF;
END IF;
IF(NVL(V_ALLOC_COLS(6).ID,0)>0) THEN
  IF V_SITE IS NULL THEN
  V_SITE:=V_ALLOC_COLS(6).ID;
  ELSE
  V_SITE:= V_SITE||','||V_ALLOC_COLS(6).ID;
  END IF;
END IF;
IF NVL(V_ALLOC_COLS(7).ID,0)>0 THEN
 SELECT  COUNT(*) INTO V_COUNT_CNTR FROM TBL_BU_SITE_MASTER WHERE PARENT_ID =V_ALLOC_COLS(7).ID AND BU_TYPE_CODE='CNTR' AND ACTIVE='Y';
   IF V_COUNT_CNTR>0 THEN
   IF V_CNTR IS NULL THEN
    V_CNTR:= V_ALLOC_COLS(7).ID;
    ELSE
    V_CNTR:= V_CNTR||','||V_ALLOC_COLS(7).ID;
   END IF;
   END IF;
END IF;
END LOOP;
--END IF;

--Applying filter on BU site values
IF V_LVL1 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL1ID IN(' || V_LVL1 ||')';
END IF;

IF V_LVL2 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL2ID IN(' || V_LVL2 ||')';
END IF;
IF V_LVL3 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL3ID IN(' || V_LVL3 ||')';
END IF;
IF V_LVL4 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL4ID IN(' || V_LVL4 ||')';
END IF;
IF V_LVL5 IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND LEVEL5ID IN(' || V_LVL5 ||')';
END IF;
IF V_SITE IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND SITEID IN(' || V_SITE ||')';
END IF;
IF V_CNTR IS NOT NULL THEN
V_WHERE:=V_WHERE ||' AND COUNTRYID IN(' || V_CNTR ||')';
END IF;
---dbms_output.put_line('WHERE:'||V_WHERE);
V_SQL:=V_SQL||V_WHERE ;
---DBMS_OUTPUT.PUT_LINE(V_SQL);
OPEN P_O_REPORT FOR V_SQL ;
 ---  FETCH P_O_REPORT  BULK COLLECT into V_ALLOC_COLS LIMIT 100;
  --- CLOSE P_O_REPORT;
END SP_GET_RAW_DATA_EXTRACT;

I have tried with adding where condition to my select statement like this SELECT A_COLUMN FROM MY_TABLE WHERE ROWNUM <= 100000 but of no use.