Results 1 to 2 of 2

Thread: Cursor Oracle

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Cursor Oracle

    CREATE OR REPLACE PROCEDURE stp_od_ketemu ( ar_bulan IN VARCHAR2, ar_tahun IN VARCHAR2, ar_kodehost VARCHAR2 )
    AS
    ls_tmkr_cabang VARCHAR2 (3);
    ls_tmkr_cif VARCHAR2 (10);
    li_tmkr_denda NUMBER;
    ls_tmkr_norek VARCHAR2 (25);
    ls_krdt_norek VARCHAR2 (25);
    li_ttlod NUMBER;


    CURSOR Csr_od IS
    SELECT tmkr_cabang, tmkr_cif, tmkr_denda, tmkr_norek
    FROM temp_kredit
    WHERE ( tmkr_updatedata = '1') AND ( tmkr_kodehost = ar_kodehost )
    ORDER BY tmkr_cabang asc, tmkr_cif asc, tmkr_norek asc
    FOR UPDATE ;

    BEGIN
    OPEN Csr_od;
    LOOP
    FETCH Csr_od INTO ls_tmkr_cabang, ls_tmkr_cif, li_tmkr_denda, ls_tmkr_norek;
    SELECT count(*)
    INTO li_ttlod
    FROM t_kredit01
    WHERE krdt_cabang = ls_tmkr_cabang AND krdt_cif = ls_tmkr_cif AND ( SUBSTR( krdt_norek,1,1 ) = ar_kodehost ) AND krdt_bln = ar_bulan AND krdt_thn = ar_tahun ;
    IF li_ttlod > 0 THEN
    BEGIN
    SELECT max(krdt_norek)
    INTO ls_krdt_norek
    FROM t_kredit01
    WHERE krdt_cabang = ls_tmkr_cabang AND krdt_bln = ar_bulan AND krdt_thn = ar_tahun AND krdt_cif = ls_tmkr_cif AND ( SUBSTR( krdt_norek,1,1 ) = ar_kodehost )
    GROUP BY krdt_cabang, krdt_cif
    ORDER BY krdt_cabang ASC, krdt_cif ASC;

    UPDATE t_kredit01
    SET krdt_denda = li_tmkr_denda, krdt_updatedata = 'Y'
    WHERE krdt_cabang = ls_tmkr_cabang AND krdt_cif = ls_tmkr_cif AND krdt_bln = ar_bulan AND krdt_thn = ar_tahun AND krdt_norek = ls_krdt_norek AND ( SUBSTR( krdt_norek,1,1 ) = ar_kodehost ) ;
    COMMIT;

    DELETE din_request
    WHERE ( requ_cifbank = ls_tmkr_cif ) ;
    COMMIT;

    DELETE temp_kredit
    WHERE ( tmkr_cabang = tmkr_cabang ) AND ( tmkr_cif = ls_tmkr_cif ) AND ( tmkr_kodehost = ar_kodehost ) AND ( tmkr_updatedata = '1') ;
    COMMIT;

    END;
    ELSIF li_ttlod = 0 THEN
    BEGIN
    UPDATE temp_kredit
    SET tmkr_sifat = '80', tmkr_skbunga = 60
    WHERE ( tmkr_cabang = tmkr_cabang ) AND ( tmkr_cif = ls_tmkr_cif ) AND ( tmkr_norek = tmkr_norek ) AND ( tmkr_kodehost = ar_kodehost ) AND ( tmkr_updatedata = '1') ;
    COMMIT;
    END;
    END IF;

    EXIT WHEN Csr_od%NOTFOUND;
    END LOOP;
    END;

    Error : Ora-01002:fetch out of sequence

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE OR REPLACE PROCEDURE Stp_od_ketemu
         (ar_bulan     IN VARCHAR2,
          ar_tahun     IN VARCHAR2,
          ar_kodehost  VARCHAR2)
    AS
      ls_tmkr_cabang  VARCHAR2(3);
      ls_tmkr_cif     VARCHAR2(10);
      li_tmkr_denda   NUMBER;
      ls_tmkr_norek   VARCHAR2(25);
      ls_krdt_norek   VARCHAR2(25);
      li_ttlod        NUMBER;
      CURSOR csr_od IS
        SELECT   tmkr_cabang,
                 tmkr_cif,
                 tmkr_denda,
                 tmkr_norek
        FROM     temp_kredit
        WHERE    (tmkr_updatedata = '1')
                 AND (tmkr_kodehost = ar_kodehost)
        ORDER BY tmkr_cabang ASC,
                 tmkr_cif ASC,
                 tmkr_norek ASC
        FOR UPDATE;
    BEGIN
      OPEN csr_od;
      
      LOOP
        FETCH csr_od INTO ls_tmkr_cabang,ls_tmkr_cif,li_tmkr_denda,ls_tmkr_norek;
        
        SELECT Count(* )
        INTO   li_ttlod
        FROM   t_kredit01
        WHERE  krdt_cabang = ls_tmkr_cabang
               AND krdt_cif = ls_tmkr_cif
               AND (Substr(krdt_norek,1,1) = ar_kodehost)
               AND krdt_bln = ar_bulan
               AND krdt_thn = ar_tahun;
        
        IF li_ttlod > 0 THEN
          BEGIN
            SELECT   Max(krdt_norek)
            INTO     ls_krdt_norek
            FROM     t_kredit01
            WHERE    krdt_cabang = ls_tmkr_cabang
                     AND krdt_bln = ar_bulan
                     AND krdt_thn = ar_tahun
                     AND krdt_cif = ls_tmkr_cif
                     AND (Substr(krdt_norek,1,1) = ar_kodehost)
            GROUP BY krdt_cabang,
                     krdt_cif
            ORDER BY krdt_cabang ASC,
                     krdt_cif ASC;
            
            UPDATE t_kredit01
            SET    krdt_denda = li_tmkr_denda,
                   krdt_updatedata = 'Y'
            WHERE  krdt_cabang = ls_tmkr_cabang
                   AND krdt_cif = ls_tmkr_cif
                   AND krdt_bln = ar_bulan
                   AND krdt_thn = ar_tahun
                   AND krdt_norek = ls_krdt_norek
                   AND (Substr(krdt_norek,1,1) = ar_kodehost);
            
            COMMIT;
            
            DELETE din_request
            WHERE  (requ_cifbank = ls_tmkr_cif);
            
            COMMIT;
            
            DELETE temp_kredit
            WHERE  (tmkr_cabang = tmkr_cabang)
                   AND (tmkr_cif = ls_tmkr_cif)
                   AND (tmkr_kodehost = ar_kodehost)
                   AND (tmkr_updatedata = '1');
            
            COMMIT;
          END;
        ELSIF li_ttlod = 0 THEN
          BEGIN
            UPDATE temp_kredit
            SET    tmkr_sifat = '80',
                   tmkr_skbunga = 60
            WHERE  (tmkr_cabang = tmkr_cabang)
                   AND (tmkr_cif = ls_tmkr_cif)
                   AND (tmkr_norek = tmkr_norek)
                   AND (tmkr_kodehost = ar_kodehost)
                   AND (tmkr_updatedata = '1');
            
            COMMIT;
          END;
        END IF;
        
        EXIT WHEN csr_od%NOTFOUND;
      END LOOP;
    END;
    Code:
    01002, 00000, "fetch out of sequence"
    // *Cause: This error means that a fetch has been attempted from a cursor
    //         which is no longer valid.  Note that a PL/SQL cursor loop
    //         implicitly does fetches, and thus may also cause this error.
    //         There are a number of possible causes for this error, including:
    //         1) Fetching from a cursor after the last row has been retrieved
    //            and the ORA-1403 error returned.
    //         2) If the cursor has been opened with the FOR UPDATE clause,
    //            fetching after a COMMIT has been issued will return the error.
    //         3) Rebinding any placeholders in the SQL statement, then issuing
    //            a fetch before reexecuting the statement.
    // *Action: 1) Do not issue a fetch statement after the last row has been
    //             retrieved - there are no more rows to fetch.
    //          2) Do not issue a COMMIT inside a fetch loop for a cursor
    //             that has been opened FOR UPDATE.
    //          3) Reexecute the statement after rebinding, then attempt to
    //             fetch again.
    which line is tagged with this error?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •