title: ORA-20762: Error-01002: fetch out of sequence encountered on line procedure
body: <p>I have a scheduler job(procedure), this scheduler job should run flawless. Its working as per the logic.  This procedure doesn't have any input parameter. It has three to four cursor fetching the data and update the in same table condition wise the cursors work. The problem is... let me tell a scenario. Let say a table called 'sample' inside the procedure I just using a cursor fetch the record and updating the value in the 'sample' table, I commit next to end loop of the cursor. I'm using plsql developer IDE. I'm trying to access the data from table 'sample' as same record fetched in the cursor by putting
  <code>select val1,val2 from sample where val3='A' for update;</code> in plsql developer ide without click commit button at the same time the job i.e. procedure runs. Its throwing error that <code>ORA-20762: Error-01002: fetch out of sequence encountered on line procedure </code> the error pointing <code>fetch cursor into ..</code>. I have used the <code>'FOR UPDATE NOWAIT'</code> in cursor sql.</p>
  <pre><code>         CREATE OR REPLACE PROCEDURE scheduler_job_test
              
               AS
                  VARIABLE declarations;
                  .        . . . . cur_ex_hnd SYS_REFCURSOR;
                  TYPE rec_typ IS RECORD(
                      sch_cde         temp_table1.sch_cde%TYPE,
                      j_tx_n          temp_table1.jh1_pan_no%TYPE,
                      j2_tx_n         temp_table1.jh2_pan_no%TYPE,
                      g_tax_no        temp_table1.guardian_panno%TYPE,
                      tax_no          temp_table1.tax_no%TYPE,
                      cons_code       temp_table1.brok_dlr_code%TYPE,
                      tax_status_code temp_table1.tax_status_code%TYPE,
                      inv_name        temp_table1.inv_name%TYPE,
                      inv_mid_name    temp_table1.inv_mid_name%TYPE,
                      inv_lst_name    temp_table1.inv_lst_name%TYPE);
                  ci_tt_dts      rec_typ;
                  ls_sql         CLOB;
                  ls_sql_1       VARCHAR2(4000);
                  ls_sql_2       VARCHAR2(4000);
                  ls_sql_3       CLOB;
                  ls_sql_4       CLOB;
                  dt_ref_cur     SYS_REFCURSOR;
                  tt_ref_cur     SYS_REFCURSOR;
                  dt_trxn_avl    BOOLEAN DEFAULT FALSE;
                  TYPE ttcurtyp IS REF CURSOR;
                  v_tt_cursor    ttcurtyp;
                  lld_entry_date temp_table1.entry_date%TYPE;
              
              BEGIN
              
                  SELECT trunc(SYSDATE)
                    INTO ld_entry_date
                    FROM dual;
                  SELECT trunc(SYSDATE) - 1
                    INTO ld_previous_date
                    FROM dual;
              
                  ld_entry_date    := to_char(to_date(ld_entry_date), 'dd-Mon-YYYY');
                  ld_previous_date := to_char(to_date(ld_previous_date), 'dd-Mon-YYYY');
              
                  ls_sql := 'SELECT
                     sch_cde,
                     j_tx_n,
                     j2_tx_n,
                     g_tax_no,
                     tax_no,
                     cons_code,
                     tax_status_code,
                     inv_name,
                     inv_mid_name,
                     inv_lst_name
                     --fl_no,
                FROM (SELECT
                 dt.sch_cde,
                 nvl(j1.tax_no,j1.pan_exem_ref_no) j_tx_n,
                 nvl(j2.tax_no,j2.pan_exem_ref_no) j2_tx_n,
                .
                .
                .
                
                 ,im.inv_lst_name 
                  FROM dt_trd dt,
                 inv_mst im,
                 jnt_hlds j1,
                 jnt_hlds j2,
                 trxn_charges_setup tc,
                  TAX_STATUS t,
                  sch_setp ss
                 WHERE dt.fl_no = im.fl_no AND
                 dt.fl_no = j1.fl_no(+) AND
                 j1.joint_no(+) = 1 AND
                .
                .
                .
                .';
              
                  ls_sql_1 := 'SELECT tt.sch_cde sch_cde,
                 nvl(nvl(tt.jh1_pan_no,tt.jh1_pan_exem_ref_no), nvl(j1.tax_no,j1.pan_exem_ref_no)) j_tx_n,
                .
                .
                .
                
                  FROM temp_table1 tt,
                 inv_mst im,
                 jnt_hlds j1,
                 jnt_hlds j2,
                 trxn_charges_setup tc,
                 tax_status t,
                 sch_setp ss
                 WHERE tt.fl_no = im.fl_no(+) AND
                 tt.fl_no = j1.fl_no(+) AND
                 j1.joint_no(+) = 1 AND
                 tt.fl_no = j2.fl_no(+) AND
                 j2.joint_no(+) = 2 AND
                 .
                 .
                 .
                 .
                   GROUP BY sch_cde,
                    j_tx_n,
                    j2_tx_n,
                    g_tax_no,
                    tax_no,
                    cons_code,
                    tax_status_code,
                    inv_name,
                    inv_mid_name,
                    inv_lst_name 
              
                  HAVING COUNT(*) &gt; 1';
              
                  ls_sql_2 := 'SELECT dt.sch_cde,
                   nvl(j1.tax_no,j1.pan_exem_ref_no)               j_tx_n,
                  .
                  .
                  .
                  
                   im.inv_lst_name
                  FROM dt_trd         dt,
                   inv_mst    im,
                   jnt_hlds      j1,
                   jnt_hlds      j2,
                   trxn_charges_setup tc,
                   TAX_STATUS         t
                 WHERE dt.fl_no = im.fl_no
                  .
                  .
                  .
                  .
                  
                   AND tc.trxn_db_cr = ''P''
                   AND tc.trxn_type_flag IN (''AP'', ''FP'')
                  .
                  .
                  .
                  AND
                   NOT EXISTS
                     (SELECT 1
                FROM temp_table1 tt
                     WHERE dt.fl_no = tt.fl_no AND
                     dt.trxn_no = tt.original_trxn_no AND
                     nvl(dt.trxn_mode, ''N'') &lt;&gt; ''R'') AND
               .
               .
               .
               ';
                  ls_sql_3 := '
                 SELECT sch_cde,
                 j_tx_n,
                 j2_tx_n,
                 g_tax_no,
                 tax_no,
                 cons_code,
                 tax_status_code,
                  NULL inv_name,
                  NULL inv_mid_name,
                  NULL inv_lst_name
                  FROM (
                  SELECT tt.sch_cde sch_cde,
                 nvl(nvl(tt.jh1_pan_no,tt.jh1_pan_exem_ref_no), nvl(j1.tax_no,j1.pan_exem_ref_no)) j_tx_n,
                .
                .
                .
                   nvl(tt.inv_lst_name, im.inv_lst_name)
                  FROM temp_table1 tt,
                   inv_mst           im,
                   jnt_hlds             j1,
                   jnt_hlds             j2,
                   trxn_charges_setup        tc,
                   tax_status                t,
                   sch_setp ss
                 WHERE tt.fl_no = im.fl_no(+)
                  .
                  .
                  .
                   AND nvl(tt.tax_status_code, im.tax_status_code) = t.tax_status_code
                   and upper(nvl(tt.sub_trxn_type,''N'')) IN (''NORMAL'', ''N'')
                   AND tt.trxn_type LIKE ''P%''
                   .
                    . 
              .
              .
              .
              .
              
                 GROUP BY sch_cde,
                      j_tx_n,
                      j2_tx_n,
                      g_tax_no,
                      tax_no,
                      cons_code, 
                      tax_status_code
                HAVING COUNT(*) &gt; 1';
              
                  BEGIN
                      ls_sql_4 := ls_sql || ' UNION ALL ' || ls_sql_1 || ' UNION ALL ' ||
                              ls_sql_2 || ' UNION ALL ' || ls_sql_3;
                      OPEN cur_ex_hnd FOR ls_sql_4;
                      LOOP
                          FETCH cur_ex_hnd
                              INTO ci_tt_dts;
                          EXIT WHEN cur_ex_hnd%NOTFOUND;
                          ls_sql      := NULL;
                          dt_trxn_avl := FALSE;
                          ls_sql      := 'SELECT
                               dt.sch_cde,
                              .
                              .
                              .
                              
                               im.guardian_panno g_tax_no,
                               im.tax_no         tax_no,
                               nvl(itn_plf_last_broker_conscode(dt.brok_dlr_code), ''ARN-DIRECT'') cons_code,
                              dt.entry_date,
                               im.tax_status_code tax_status_code
                                 ,im.inv_name
                                 ,im.inv_mid_name
                                 ,im.inv_lst_name
                              FROM dt_trd         dt,
                               inv_mst    im,
                               jnt_hlds      j1,
                               jnt_hlds      j2,
                               trxn_charges_setup tc
                             WHERE dt.fl_no = im.fl_no AND
                               dt.fl_no = j1.fl_no(+) AND
                               j1.joint_no(+) = 1 AND
                               dt.fl_no = j2.fl_no(+) AND
                               j2.joint_no(+) = 2 AND
              .
              .
              .
              ';
                      
                          IF ld_entry_date IS NOT NULL THEN
                              ls_sql := ls_sql ||
                                    ' AND trunc(dt.entry_date) BETWEEN trunc(tc.from_date) AND trunc(tc.to_date) ';
                              ls_sql := ls_sql ||
                                    ' AND dt.trxn_type_flag NOT LIKE ''NFO%'' AND dt.entry_date between to_date(:bv_p_as_date || ''15:00:00'',''dd-mon-yyyy hh24:mi:ss'') and to_date(:bv_entry_date || ''14:59:59'',''dd-mon-yyyy hh24:mi:ss'')';
                          ELSE
                              ls_sql := ls_sql || ' AND 1 = :bv_entry_date ';
                              ls_sql := ls_sql || ' AND 1 = :bv_p_as_date ';
                          END IF;
                      
                          IF ci_tt_dts.tax_no IS NOT NULL THEN
                              ls_sql := ls_sql ||
                                    ' AND (im.tax_no = :bv_tax_no or im.pan_exem_ref_no = :bv_tax_no)  ';
                          ELSE
                              ls_sql := ls_sql ||
                                    'and (1 =:bv_tax_no or 1 =:bv_tax_no) ';
                          END IF;
                      
                          IF ci_tt_dts.j_tx_n IS NOT NULL THEN
                              ls_sql := ls_sql ||
                                    ' AND (j1.tax_no = :bv_j1_tax_no or j1.pan_exem_ref_no = :bv_j1_tax_no) ';
                          ELSE
                              ls_sql := ls_sql ||
                                    'AND (1 = :bv_j1_tax_no or 1 = :bv_j1_tax_no) ';
                          END IF;
                      
                      .
                      .
                      .
                      .
                          IF ci_tt_dts.inv_lst_name IS NOT NULL THEN
                              ls_sql := ls_sql ||
                                    ' AND upper(im.inv_lst_name) = upper(:bv_investor_last_name) ';
                          ELSE
                              ls_sql := ls_sql || 'AND 1 = :bv_investor_last_name ';
                          END IF;
                          OPEN dt_ref_cur FOR to_char(ls_sql)
                              USING ci_tt_dts.sch_cde, ci_tt_dts.cons_code, ci_tt_dts.tax_status_code, ld_previous_date, ld_entry_date, nvl(ci_tt_dts.tax_no,
                                                                                        '1'), nvl(ci_tt_dts.tax_no,
                                                                                          '1'), nvl(ci_tt_dts.j_tx_n,
                                                                                              .
                                                                                              .
                                                                                              .
                                                                                              .
                                                                                              
                                                                                                                                        '1');
                      
                          ll_dt_rowcount := 0;
                          LOOP
                              FETCH dt_ref_cur
                                  INTO ls_dt_scheme_code,
                                       ls_dt_user_code,
                                       .
                                       .
                                       .
                                       .
                                       ;
                              EXIT WHEN dt_ref_cur%NOTFOUND;
                          
                              ll_dt_rowcount := dt_ref_cur%ROWCOUNT;
                          
                              IF ll_dt_rowcount = 1 THEN
                                  ls_temp_user_code   := ls_dt_user_code;
                                  ls_temp_user_trxnno := ls_dt_user_trxnno;
                                  dt_trxn_avl         := TRUE;
                                  EXIT;
                              END IF;
                          END LOOP;
                          IF dt_trxn_avl = TRUE THEN
                              ls_sql := NULL;
                              ls_sql := 'SELECT tt.fl_no fl_no,
                                 tt.user_code,
                                 tt.user_trxnno,
                                .
                                .
                                .
                                
                                ,nvl(tt.inv_mid_name,im.inv_mid_name) 
                                ,nvl(tt.inv_lst_name,im.inv_lst_name) 
                            FROM temp_table1 tt,
                                 inv_mst           im,
                                 jnt_hlds             j1,
                                 jnt_hlds             j2,
                                 trxn_charges_setup        tc,
                                 sch_setp ss 
                           WHERE tt.fl_no = im.fl_no(+) AND
                                 tt.fl_no = j1.fl_no(+) AND
                                 j1.joint_no(+) = 1 AND
                                 tt.fl_no = j2.fl_no(+) AND
                                 j2.joint_no(+) = 2 AND
              .
              .
              .
              .
              ';
                          
                              IF ld_entry_date IS NOT NULL THEN
                                  ls_sql := ls_sql || ' AND
                                    ((tt.entry_date BETWEEN
                                    to_date(:bv_p_as_date || '' 15 :00 :00 '',
                                          '' dd - mon - yyyy hh24 :mi :ss '') AND
                                    to_date(:bv_entry_date || '' 14 :59 :59 '',
                                          '' dd - mon - yyyy hh24 :mi :ss ''))
                                     ';
                                  ls_sql := ls_sql || ' OR
                                     ((tt.trade_date &lt; trunc(tt.entry_date) AND
                                     tt.trade_date = :bv_entry_date) AND
                                     upper(tt.source_program) = '' appl
                                      '' AND upper(tt.user_code) &lt;&gt; '' mobile ''))
                                    ';
                                  ls_sql := ls_sql || ' AND
                                    ((ss.open_date &lt; :bv_entry_date) AND
                                    (nvl(ss.allotment_date, ss.open_date) &lt;
                                    :bv_entry_date)) ';
                              ELSE
                                  ls_sql := ls_sql || ' AND
                                    1 = :bv_entry_date ';
                                  ls_sql := ls_sql || ' AND
                                    1 = :bv_p_as_date
                                    ';
                              END IF;
                          
                              .
                              .
                              .
                              .
                              .
                              .
                              ';
                              END IF;
                              ls_sql := ls_sql || ' FOR UPDATE NOWAIT ';
                              OPEN tt_ref_cur FOR to_char(ls_sql)
                                  USING ls_dt_scheme_code, ls_dt_tax_status_code, ls_dt_brok_dlr_code, ld_previous_date, ld_entry_date, ld_entry_date, ld_entry_date, ld_entry_date, nvl(ls_dt_tax_no,
                                                                                                                 ' 1 '), nvl(ls_dt_tax_no,
                                                                                                                     ' 1 '), nvl(ls_dt_guardian_panno,.
                                                                                                                     .
                                                                                                                     .
                                                                                                                     .
                                                                                                                     .
                                                                                                                     
                                                                                                                                                                     ' 1 '), nvl(ls_dt_investor_last_name,
                                                                                                                                                                             ' 1 ');
                          
                              LOOP
                              
                                  FETCH tt_ref_cur
                                      INTO ls_folio_no,
                                           ls_user_code,
                                           ls_user_trxnno,
                                           ls_scheme_code,
                                           .
                                           .
                                           .
                                           .
                                  EXIT WHEN tt_ref_cur%NOTFOUND;
                              
                                  ll_tt_rowcount := tt_ref_cur%ROWCOUNT;
                                  as_strflag     := ' entry_date ';
                                  UPDATE temp_table1 tt
                                     SET tc_option = ' na ',
                                         remarks   = substr(REPLACE(remarks,
                                                    ' &lt; # ' ||
                                                    ls_temp_user_code ||
                                                    ' $ ' ||
                                                    to_char(ls_temp_user_trxnno) ||
                                                    ' # &gt; ',
                                                    NULL) || ' &lt; # ' ||
                                                ls_temp_user_code ||
                                                ' $ ' ||
                                                to_char(ls_temp_user_trxnno) ||
                                                ' # &gt; ' ||
                                                ' tc OPTION updated ' ||
                                                ' _ ' || as_strflag ||
                                                ' - ' || ld_entry_date ||
                                                ' AND located ON ' ||
                                                to_char(SYSDATE,
                                                    ' dd - mon - yyyy hh24 :mi :ss ') ||
                                                ' &gt; ',
                                                1,
                                                1000)
                                   WHERE tt.user_code = ls_user_code AND
                                         tt.user_trxnno = ls_user_trxnno AND
                                         tt.sch_cde = ls_scheme_code AND
                                         tt.trxn_type = ls_trxn_type;
                              
                                  IF SQL%ROWCOUNT = 0 THEN
                                      as_result := ' RECORD NOT available IN cdt ';
                                  ELSE
                                      as_result := ' updated ';
                                  END IF;
                                  COMMIT;
                              
                              END LOOP;
                              CLOSE tt_ref_cur;
                          ELSE
                              ls_sql := NULL;
                              ls_sql := ' SELECT tt.fl_no fl_no, tt.user_code, tt.user_trxnno, tt.sch_cde sch_cde, tt.trxn_type, tt.amount, nvl(tt.jh1_pan_no, j1.tax_no) j_tx_n, nvl(tt.jh2_pan_no, j2.tax_no) j2_tx_n, nvl(tt.guardian_panno, im.guardian_panno) g_tax_no, nvl(tt.tax_no, im.tax_no) tax_no,
              
                               nvl(tt.cons_code, nvl(itn_plf_last_broker_conscode(tt.brok_dlr_code), '' arn - direct '')) cons_code, tt.entry_date, nvl(tt.tax_status_code, im.tax_status_code) tax_status_code, nvl(tt.inv_name, im.inv_name)
                               , nvl(tt.inv_mid_name, im.inv_mid_name)
                               , nvl(tt.inv_lst_name, im.inv_lst_name)
                               FROM temp_table1 tt, inv_mst im, jnt_hlds j1, jnt_hlds j2, trxn_charges_setup tc, sch_setp ss WHERE tt.fl_no = im.fl_no(+) AND .
                               .
                               .
                               .
                               .
                               .
                               ';
                          
                              IF ld_entry_date IS NOT NULL THEN
                                  ls_sql := ls_sql ||
                                        ' AND ((tt.entry_date BETWEEN to_date(:bv_p_as_date || '' 15 :00 :00 '', '' dd - mon - yyyy hh24 :mi :ss '') AND to_date(:bv_entry_date || '' 14 :59 :59 '', '' dd - mon - yyyy hh24 :mi :ss '')) ';
                                  ls_sql := ls_sql ||
                                        ' OR ((tt.trade_date &lt; trunc(tt.entry_date) AND tt.trade_date = :bv_as_date) AND upper(tt.source_program) = '' appl '' AND upper(tt.user_code) &lt;&gt; '' mobile '')) ';
                                  ls_sql := ls_sql ||
                                        ' AND ((ss.open_date &lt; :bv_entry_date) AND (nvl(ss.allotment_date, ss.open_date) &lt; :bv_entry_date)) ';
                              ELSE
                                  ls_sql := ls_sql || ' AND 1 = :bv_entry_date ';
                                  ls_sql := ls_sql || ' AND 1 = :bv_p_as_date ';
                              END IF;
                          
                              IF ci_tt_dts.tax_no IS NOT NULL THEN
                                  ls_sql := ls_sql ||
                                        ' AND (nvl(tt.tax_no, im.tax_no) = :bv_tax_no OR nvl(tt.pan_exem_ref_no, im.pan_exem_ref_no) = :bv_tax_no) ';
                              ELSE
                                  ls_sql := ls_sql ||
                                        ' AND (1 = :bv_tax_no OR 1 = :bv_tax_no) ';
                              END IF;
                              . . .
                              
                              ls_sql := ls_sql || ' FOR UPDATE NOWAIT ';
                              OPEN tt_ref_cur FOR to_char(ls_sql)
                                  USING ci_tt_dts.sch_cde, ci_tt_dts.tax_status_code, ci_tt_dts.cons_code, ld_previous_date, ld_entry_date, ld_entry_date, ld_entry_date, ld_entry_date, .
                                  .
                                  .
                                  .
                                  .
                                  .
                                  nvl(ci_tt_dts.inv_lst_name,
                                                                                                                                                                             ' 1 ');
                          
                              ll_tt_rowcount := 0;
                              LOOP
                                  FETCH tt_ref_cur
                                      INTO 
                                      ls_folio_no,ls_user_code,.
                                      .
                                      .
                                      .
                                      ;
                                  EXIT WHEN tt_ref_cur%NOTFOUND;
                                  ll_tt_rowcount := tt_ref_cur%ROWCOUNT;
                                  IF nvl(ll_tt_rowcount, 0) = 1 THEN
                                      ls_dt_user_code   := ls_user_code;
                                      ls_dt_user_trxnno := ls_user_trxnno;
                                  ELSE
                                      UPDATE temp_table1 tt
                                         SET tc_option = ' na ',
                                             remarks   = substr(REPLACE(remarks,
                                                        ' &lt; # ' ||
                                                        ls_dt_user_code ||
                                                        ' $ ' ||
                                                        to_char(ls_dt_user_trxnno) ||
                                                        ' # &gt; ',
                                                        NULL) ||
                                                    ' &lt; # ' ||
                                                    ls_dt_user_code ||
                                                    ' $ ' ||
                                                    to_char(ls_dt_user_trxnno) ||
                                                    ' # &gt; ' ||
                                                    ' tc OPTION updated ' ||
                                                    ' _ ' ||
                                                    as_strflag ||
                                                    ' - ' ||
                                                    ld_entry_date ||
                                                    ' AND located ON ' ||
                                                    to_char(SYSDATE,
                                                        ' dd - mon - yyyy hh24 :mi :ss ') ||
                                                    ' &gt; ',
                                                    1,
                                                    1000)
                                       WHERE tt.user_code = ls_user_code AND
                                             tt.user_trxnno = ls_user_trxnno AND
                                             tt.sch_cde = ls_scheme_code AND
                                             tt.trxn_type = ls_trxn_type;
                                      IF SQL%ROWCOUNT = 0 THEN
                                          as_result := ' RECORD NOT available IN cdt ';
                                      ELSE
                                          as_result := ' updated ';
                                      END IF;
                                  
                                      COMMIT;
                                  END IF;
                              END LOOP;
                              CLOSE tt_ref_cur;
                          END IF;
                      END LOOP;
                  
                      CLOSE cur_ex_hnd;
                  
                      ls_sql := '';
                      ls_sql := ls_sql ||
                            ' SELECT tt.user_code, tt.user_trxnno, tt.sch_cde, tt.trxn_type, tt.multi_invest_tc_check FROM temp_table1 tt, sch_setp ss WHERE tt.sch_cde = ss.sch_cde AND tt.trxn_type LIKE '' p% '' AND nvl(tt.multi_invest_tc_check, '' N'') &lt;&gt; '' y '' AND ((tt.entry_date BETWEEN to_date(:bv_p_as_date || '' 15 :00 :00 '', '' dd - mon - yyyy hh24 :mi :ss '') AND to_date(:bv_entry_date || '' 14 :59 :59 '', '' dd - mon - yyyy hh24 :mi :ss '')) OR ((tt.trade_date &lt; trunc(tt.entry_date) AND tt.trade_date = :bv_entry_date) AND upper(tt.source_program) = '' appl '' AND upper(tt.user_code) &lt;&gt; '' mobile '')) AND ((ss.open_date &lt; :bv_entry_date) AND (nvl(ss.allotment_date, ss.open_date) &lt; :bv_entry_date)) ';
                      ls_sql := ls_sql || ' FOR UPDATE NOWAIT ';
                      OPEN v_tt_cursor FOR ls_sql
                          USING ld_previous_date, ld_entry_date, ld_entry_date, ld_entry_date, ld_entry_date;
                      LOOP
                          FETCH v_tt_cursor
                              INTO ls_user_code,
                                   ls_user_trxnno,
                                   ls_scheme_code,
                                   ls_trxn_type,
                                   ls_multi_invest_tc_check;
                          EXIT WHEN v_tt_cursor%NOTFOUND;
                          as_strflag := ' entry_date ';
                          IF nvl(TRIM(ls_multi_invest_tc_check), ' N') &lt;&gt; ' y ' THEN
                              BEGIN
                                  UPDATE temp_table1 tt
                                     SET tt.multi_invest_tc_check = ' y ',
                                         tt.remarks               = substr(remarks ||
                                                       ' &lt; multi inv tc CHECK updated ' ||
                                                       ' _ ' ||
                                                       as_strflag ||
                                                       ' - ' ||
                                                       ld_entry_date ||
                                                       ' AND located ON ' ||
                                                       to_char(SYSDATE,
                                                           ' dd - mon - yyyy hh24 :mi :ss ') ||
                                                       ' &gt; ',
                                                       1,
                                                       1000)
                                   WHERE tt.user_code = ls_user_code AND
                                         tt.user_trxnno = ls_user_trxnno AND
                                         tt.sch_cde = ls_scheme_code AND
                                         tt.trxn_type = ls_trxn_type;
                                  COMMIT;
                                  as_result    := ' updated ';
                                  li_row_count := li_row_count + 1;
                              EXCEPTION
                                  WHEN OTHERS THEN
                                      raise_application_error(-20760,
                                                  error_trace);
                              END;
                          
                          END IF;
                          BEGIN
                              ld_end_time := SYSDATE;
                              SELECT tt.tc_option,
                                     tt.multi_invest_tc_check
                                INTO ls_tc_option,
                                     ls_multiinvest_check
                                FROM temp_table1 tt
                               WHERE tt.user_code = ls_user_code AND
                                     tt.user_trxnno = ls_user_trxnno;
                              DELETE FROM report_log c
                               WHERE c.table_name = ' schedule_check ' AND
                                     trunc(c.start_time) =
                                     trunc(add_months(SYSDATE, -2));
                              SELECT seq_report_log.nextval
                                INTO ls_sno
                                FROM dual;
                              INSERT INTO report_log
                                  (table_name,
                                   report_desc,
                                   start_time,
                                   end_time,
                                   serial_no,
                                   created_date,
                                   selection,
                                   elapsed_time)
                              VALUES
                                  (' schedule_check ',
                                   as_result || ' $ ' || ls_user_code || ' $ ' ||
                                   ls_user_trxnno || ' $ ' || ls_scheme_code ||
                                   ' $ ' || ls_tc_option || ' $ ' ||
                                   ls_multiinvest_check,
                                   ld_start_time,
                                   ld_end_time,
                                   ls_sno,
                                   trunc(SYSDATE),
                                   substr(' updated COUNT - ' || li_row_count,
                                      1,
                                      4000),
                                   plf_time_between(ld_start_time,
                                            ld_end_time,
                                            ' m '));
                              COMMIT;
                          EXCEPTION
                              WHEN OTHERS THEN
                                  raise_application_error(-20761, error_trace);
                          END;
                      
                      END LOOP;
                      CLOSE v_tt_cursor;
                  END;
              EXCEPTION
                  WHEN OTHERS THEN
                      raise_application_error(-20762, error_trace);
              END;
  </code></pre>