11:23:12 SQL> create table foo (a number, b varchar2(200));

Table created.

Elapsed: 00:00:00.03


1   insert into foo select * from (
  2   with data as (select level, rpad('x',200,'x') from dual connect by level < 10000)
  3   select data.* from data, data data2
  4*  where rownum <=10000000)
11:23:59 SQL> /

10000000 rows created.

Elapsed: 00:00:39.10

CREATE OR REPLACE PROCEDURE RAJEMITRA.DIG_CRT (
   p_from_dt       VARCHAR2,
   p_to_dt         VARCHAR2,     
   p_record    OUT sys_refcursor
)
AS
   V_P_FROM_DT   DATE;
   V_P_TO_DT     DATE;     
   CHK_TOKEN     NUMBER;
   CHK_TOKEN2    NUMBER;
   SL_TOKEN     NUMBER := 0;
   CNT_NO     NUMBER := 0;
BEGIN
   BEGIN
      SELECT   TO_DATE (P_FROM_DT, 'DD-MM-RRRR'),
               TO_DATE (P_TO_DT, 'DD-MM-RRRR')             
        INTO   V_P_FROM_DT, V_P_TO_DT
        FROM   DUAL;
   END;

   OPEN p_record FOR
     --   SELECT *  FROM   RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG WHERE UPDT_TIMESTAMP BETWEEN V_P_FROM_DT AND V_P_TO_DT;
     select * from dual;
       -- DBMS_OUTPUT.PUT_LINE('Select in Main table ');
        
        FOR t IN (SELECT 
         rcpt RCPT_NO,
         token TOKEN_NO, 
         MAX (audit_no) audit_no,
         scd srv_cd,
         dept_cd dept_cd,         
         dcd dist_cd,
         dc_cd dc_cd,
         stscd stscd,
         lcd lsp_cd, 
         kiosk,
         tcd teh_cd,
         MAX (updt) UPDT_TIMESTAMP,
         trans_dt_time trans_dt_time,
         para APPLICANT_NAME,
         father_name,
         pics PICS,
         eid,
         uid1 uaid,                 
         max(remrk) remarks
    FROM (  SELECT TO_CHAR (a.rcpt_no) rcpt,
                   TO_CHAR (a.token_no) token,
                   kiosk.kiosk_cd kiosk,
                   srv.dept_cd dept_cd,                                   
                   DIST.DIST_CD dcd,
                   A.DC_CD,
                   STST.STAT_CODE stscd,                  
                   CONVERT (para_text_val, 'UTF8', 'AL32UTF8') para,
                   srv.srv_cd scd,                  
                   a.audit_no audit_no,
                   TO_CHAR (a.updt_dt_tm, 'dd-mon-yyyy') updt,
                   a.rmrk remrk,                   
                   TEHI.TEH_CD tcd,
                   TO_CHAR (h.trans_dt_time, 'dd-mon-yyyy') trans_dt_time,                   
                   LSP1.LSP_CD lcd,
                   (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)   dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND (   UPPER (para_nm_e) =
                                                     'FATHER''S NAME'
                                               OR UPPER (para_nm_e) =
                                                     'FATHER''S/HUSBAND NAME')))
                      father_name,                                         
                   (SELECT aa.PARA_TEXT_VAL
                      FROM rajemitra.MST_SRV_TRN_DTL_WEB partition(jul_2013) aa,
                           rajemitra.MST_PARA_DTL_WEB bb
                     WHERE     aa.TOKEN_NO = a.token_no
                           AND bb.PARA_DATA_TP_CD = 18
                           AND aa.SRV_CD = bb.SRV_CD
                           AND aa.VERSION_ID = bb.VERSION_ID
                           AND aa.PARA_CD = bb.para_cd
                           AND LOWER (
                                  SUBSTR (aa.para_text_val,
                                          INSTR (aa.para_text_val, '.', -1) + 1,
                                          5)) IN
                                  ('jpg', 'png', 'bmp','gif')
                           AND aa.dc_Cd = a.dc_cd)
                         pics,
                          (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)  dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND  UPPER (para_nm_e) =
                                                     'AADHAR (UID Number 12 Digit)'
                                              ))
                      eid ,
                      (SELECT para_text_val
                      FROM RAJEMITRA.MST_SRV_TRN_DTL_WEB partition(jul_2013)  dtl1
                     WHERE     dtl1.TOKEN_NO = a.token_no
                           AND dtl1.para_cd =
                                  (SELECT para_cd
                                     FROM RAJEMITRA.MST_PARA_DTL_WEB para1
                                    WHERE     PARA1.SRV_CD = a.srv_cd
                                          AND PARA1.VERSION_ID = A.VERSION_ID
                                          AND  UPPER (para_nm_e) =
                                                     'AADHAR (EID Number 14 Digit)'
                                              ))
                           uid1
              FROM rajemitra.mst_srv_trn_dtl_audit_web partition(jul_2013)  a
                   INNER JOIN (  SELECT MAX (audit_no) audit_no
                                   FROM rajemitra.MST_SRV_TRN_DTL_AUDIT_WEB partition(jul_2013)  auditweb
                                  WHERE                                          
                                         (auditweb.srv_cd, auditweb.version_id) IN
                                               (SELECT srv_cd, version_id
                                                  FROM rajemitra.mst_srv_web web
                                                 WHERE WEB.SRV_MAJ_GROUP_CD = 1)
                               GROUP BY token_no) audit2
                      ON   audit2.audit_no = a.audit_no
                         --AND (a.stat_cd IN (1, 2, 5, 6, 99))                         
                   INNER JOIN rajemitra.TOKEN_NO_RVNU_HD_REL partition(jul_2013)  
                               h
                      ON  1=1  -- h.cancel_flg = 'N'  
                      AND h.trans_dt_time BETWEEN  V_P_FROM_DT AND V_P_TO_DT                       
                         AND h.token_no = a.token_no and h.dept_cd=3137 
                   INNER JOIN rajemitra.MST_SRV_TRN_DTL_WEB partition(jul_2013) f
                      ON f.rcpt_no = h.rcpt_no AND f.token_no = h.token_no
                   INNER JOIN rajemitra.PARA_CHARS_WEB para
                      ON f.PARA_CD = PARA.PARA_CD AND PARA.CHAR_CODE = 14
                   INNER JOIN rajemitra.mst_srv_web srv
                      ON srv.srv_cd = a.srv_cd AND SRV.VERSION_ID = a.version_id
                   INNER JOIN rajemitra.MST_SP_KIOSK kiosk
                      ON KIOSK.KIOSK_CD = a.kiosk_cd AND KIOSK.DC_CD = a.dc_cd
                   INNER JOIN rajemitra.mst_loc_teh tehi
                      ON a.tehsil_code = tehi.teh_cd AND tehi.dist_cd = a.dist_cd
                   INNER JOIN rajemitra.MST_LOC_DIST dist
                      ON DIST.DIST_CD = a.dist_cd
                   INNER JOIN rajemitra.mst_sp_lsp lsp1
                      ON     lsp1.lsp_cd = kiosk.active_lsp_cd
                         AND lsp1.dc_cd = kiosk.dc_cd
                   INNER JOIN rajemitra.MST_TRAN_STST_WEB stst
                      ON STST.STAT_CODE = a.stat_cd
          ) ddd
GROUP BY para,
         father_name,
         kiosk,
         dcd,
         stscd,
         dc_cd,
         lcd,
         rcpt,
         dept_cd,
         token,
         tcd,
         trans_dt_time,
         scd,
         pics,
         eid,
         uid1 order by 12) LOOP 
        dbms_output.put_line(t.token_no); 
        select count(*) into CHK_TOKEN from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG where token_no=t.token_no;
        if CHK_TOKEN > 0 then
            dbms_output.put_line(t.token_no||' chck token-- '||CHK_TOKEN);
            select count(*) into CHK_TOKEN2 from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG where token_no=t.token_no and stscd=1;
            IF  ((t.stscd =99 or t.stscd =6)  and CHK_TOKEN2 > 0)   then
                UPDATE RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG SET CANCEL_DATE=t.UPDT_TIMESTAMP , REMARKS = t.REMARKS , APR_STSCD = t.stscd where STSCD =1 AND TOKEN_NO=t.token_no ;
            ELSE
                UPDATE RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG SET UPDT_TIMESTAMP=t.UPDT_TIMESTAMP , STSCD = t.STSCD , REMARKS = t.REMARKS where STSCD !=1 AND TOKEN_NO=t.token_no ;
            END if; 
            commit;
        else
            select  count(*)  into CNT_NO from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG mdcw where 
            MDCW.APPLICANT_NAME =t.APPLICANT_NAME  and MDCW.FATHER_NAME =t.father_name 
            and MDCW.SRV_CD =t.srv_cd ;
            select  max(DUB_TOKEN)  into SL_TOKEN from RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG mdcw where 
            MDCW.APPLICANT_NAME =t.APPLICANT_NAME  and MDCW.FATHER_NAME =t.father_name 
            and MDCW.SRV_CD =t.srv_cd and MDCW.DUB_NO=0 ;
            if SL_TOKEN is null then
                SL_TOKEN := t.token_no;
            end if;  
           dbms_output.put_line(t.token_no||' else chck token '||SL_TOKEN);         
           
            
            INSERT /*+ parallel(4) */  INTO RAJEMITRA.MST_DIG_CRT_WEB_BAKLOG NOLOGGING  VALUES (t.RCPT_NO,t.TOKEN_NO,t.AUDIT_NO,t.SRV_CD,t.DEPT_CD,t.DIST_CD,t.DC_CD,t.STSCD,t.LSP_CD,t.KIOSK,
            t.TEH_CD,t.UPDT_TIMESTAMP,t.TRANS_DT_TIME,t.APPLICANT_NAME,t.FATHER_NAME,SL_TOKEN,CNT_NO,t.PICS,t.EID,t.UAID,t.REMARKS,NULL,NULL);
            commit;
        end if;
        
        END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END;
/

INSERT /*+ parallel(4) */ INTO rajemitra.mst_dig_crt_web_baklog NOLOGGING
VALUES      (t.rcpt_no,
             t.token_no,
             t.audit_no,
             t.srv_cd,
             t.dept_cd,
             t.dist_cd,
             t.dc_cd,
             t.stscd,
             t.lsp_cd,
             t.kiosk,
             t.teh_cd,
             t.updt_timestamp,
             t.trans_dt_time,
             t.applicant_name,
             t.father_name,
             sl_token,
             cnt_no,
             t.pics,
             t.eid,
             t.uaid,
             t.remarks,
             NULL,
             NULL); 

AND H.TRANS_DT_TIME BETWEEN V_P_FROM_DT AND V_P_TO_DT

AND H.TRANS_DT_TIME BETWEEN p_from_dt AND p_to_dt