1 2 Previous Next 22 Replies Latest reply: Mar 22, 2012 12:50 AM by Luthfi RSS

    Cursor returning wrong result

    Luthfi
      Hi,

      Oracle Application Server / Oracle Form Server: Oracle 10g 9.0.4
      Operating System: Linux

      I've 3 tables, a package and procedure as below:
      CREATE TABLE PAPER
      (
        BRNCD       VARCHAR2(5 BYTE) NOT NULL,
        MODCD       VARCHAR2(2 BYTE) NOT NULL,
        ACNO        NUMBER(6)        NOT NULL,
        CHKDGT      NUMBER(2)        NOT NULL,
        CRLINE      VARCHAR2(2 BYTE) NOT NULL,
        PSBKBAL     NUMBER(16,2)     DEFAULT 0
      )
      *Primary key: BRNCD, MODCD, ACNO, CHKDGT
      Sample Data:
      
      INSERT INTO PAPER(BRNCD, MODCD, ACNO, CHKDGT, CRLINE, PSBKBAL) VALUES('00000', '01', 123, 12, 'SA', 1000);
      INSERT INTO PAPER(BRNCD, MODCD, ACNO, CHKDGT, CRLINE, PSBKBAL) VALUES('00001', '02', 456, 23, 'CA', 150.30);
      
      BRNCD  MODCD  ACNO  CHKDGT  CRLINE  PSBKBAL
      -----  -----  ----  ------  ------  -------
      00000  01     123   12      SA      1000
      00001  02     456   23      CA      150.30
      CREATE TABLE TRANX
      (
        TRNBRN     VARCHAR2(5 BYTE)  NOT NULL,
        CRLINE     VARCHAR2(2 BYTE)  NOT NULL,
        YR         NUMBER(4)         NOT NULL,
        SEQNO      NUMBER(6)         NOT NULL,
        SEQCNT     NUMBER(6)         NOT NULL,
        BRNCD      VARCHAR2(5 BYTE)  NOT NULL,
        MODCD      VARCHAR2(2 BYTE)  NOT NULL,
        ACNO       NUMBER(6)         NOT NULL,
        CHKDGT     NUMBER(2)         NOT NULL,
        POST       VARCHAR2(1 BYTE)  NOT NULL,
        TRNAMT     NUMBER(16,2)      DEFAULT 0 NOT NULL,
        TRNCD      VARCHAR2(3 BYTE)  NOT NULL,
        TRNDT      DATE              NOT NULL,
        OVRRD      VARCHAR2(1 BYTE)  DEFAULT 'N',
        PSBKBAL    NUMBER(16,2)      DEFAULT 0,
        TIME       DATE     
      )
      *Primary key: TRNBRN, CRLINE, YR, SEQNO, SEQCNT
        FK (PAPER) : BRNCD, MODCD, ACNO, CHKDGT
      Sample Data:
      
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 1, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('15/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('15/12/2012 10:10:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 2, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('20/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('20/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 3, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('21/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('21/12/2012 11:21:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 4, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('22/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('22/12/2012 07:09:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 5, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('23/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('23/12/2012 13:09:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00001', 'CA', 2012, 1, 1, '00001', '02', 456, 23, 'S', -5.25, '101', 
                            TO_DATE('20/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('20/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 6, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                            TO_DATE('24/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('24/12/2012 19:09:00','DD/MM/YYYY HH24:MI:SS');
      INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                        TRNDT, OVRRD, PSBKBAL, TIME)
                     VALUES('00000', 'SA', 2012, 7, 1, '00000', '01', 123, 12, 'U', -70, '102', 
                            TO_DATE('30/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('30/12/2012 09:09:00','DD/MM/YYYY HH24:MI:SS');
      
      TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
      ------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
      00000   SA      2012  1      1       00000  01     123   12      U     -50     111    15/11/2012   N      0        15/12/2012 10:10:00 AM
      00000   SA      2012  2      1       00000  01     123   12      U     -50     111    20/11/2012   N      0        20/12/2012 09:09:00 AM
      00000   SA      2012  3      1       00000  01     123   12      U     -50     111    21/11/2012   N      0        21/12/2012 11:21:00 AM
      00000   SA      2012  4      1       00000  01     123   12      U     -50     111    22/11/2012   N      0        22/12/2012 07:09:00 AM
      00000   SA      2012  5      1       00000  01     123   12      U     -50     111    23/11/2012   N      0        23/12/2012 13:09:00 PM
      00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
      00000   SA      2012  6      1       00000  01     123   12      U     -50     111    24/11/2012   N      0        24/12/2012 19:09:00 PM
      00000   SA      2012  7      1       00000  01     123   12      U     -70     102    30/11/2012   N      0        30/12/2012 09:09:00 AM
      CREATE TABLE CODE(
        CRLINE     VARCHAR2(2 BYTE)  NOT NULL,
        TRNCD      VARCHAR2(3 BYTE)  NOT NULL,
        DESCR      VARCHAR2(40 BYTE)
      )
      *Primary key: CRLINE, TRNCD
      Sample Data:
      
      INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('SA', '102', 'Goodies');
      INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('SA', '111', 'Online');
      INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '101', 'Bag');
      INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '411', 'Book');
      INSERT INTO CODE(CRLINE, TRNCD, DESCR) VALUES('CA', '413', 'Pencil');
      
      CRLINE  TRNCD  DESCR
      ------  -----  -----
      SA      102    Goodies
      SA      111    Online
      CA      101    Bag
      CA      411    Book
      CA      413    Pencil
      PACKAGE SAFO_INIT IS
          n_Lineno      number(2);
          n_Psbkbal     number(16,2);
      END;
      PROCEDURE I_Psbk_Cont(B_FOFLG IN BOOLEAN, V_CRLINE IN VARCHAR2, V_BRNCD IN VARCHAR2, V_MODCD IN VARCHAR2,
      N_ACNO IN NUMBER, N_CHKDGT IN NUMBER, V_TRNCD IN VARCHAR2, D_SYSDATE IN VARCHAR2) IS
      
           CURSOR psbcon IS
           SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt 
           FROM tranx a, code b
           WHERE a.crline = v_crline
                AND brncd = v_brncd
                AND modcd = v_modcd
                AND acno = n_acno
                AND chkdgt = n_chkdgt
                AND ((a.crline in ('SA','IS') and post in ('U', 'S') OR (post = 'C' and psbkbal <> 0))
                or (a.crline in ('CA','IC') and post<>'C'))
                AND ovrrd IN ('N','A')
                AND a.crline = b.crline
                AND a.trncd = b.trncd
                and a.trncd != '455'
           ORDER BY trndt,TIME, seqno, seqcnt;
      
           CURSOR psbcon3 IS     
           SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt 
           FROM tranx a, code b
           WHERE a.crline = v_crline
                AND brncd = v_brncd
                AND modcd = v_modcd
                AND acno = n_acno
                AND ((a.crline in ('SA','IS') and post in ('U', 'S')) or (a.crline in ('CA','IC') and post = 'S'))
                AND ovrrd IN ('N','A')
                AND a.crline = b.crline
                AND a.trncd = b.trncd
           ORDER BY trndt, TIME, seqno, seqcnt;
      
           prt_rec psbcon%ROWTYPE;
      
           n_bfbal NUMBER(16,2) := 0;
      BEGIN
           IF b_foflg = TRUE THEN
                SELECT psbkbal INTO safo_init.n_psbkbal
                FROM paper
                WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
                
                IF v_trncd IN ('411','413') THEN
                     n_bfbal := safo_init.n_psbkbal;
                END IF;
      
                n_bal := safo_init.n_psbkbal;
      
                IF v_trncd IN ('411','412','413') THEN
                     OPEN psbcon3;
                     FETCH psbcon3 INTO prt_rec;
      
                     IF psbcon3%NOTFOUND THEN
                          CLOSE psbcon3;
                          RETURN;
                     END IF;
                ELSE
                     OPEN psbcon;
                     FETCH psbcon INTO prt_rec;
      
                     IF psbcon%NOTFOUND THEN
                          message('No');
                          CLOSE psbcon;
                          RETURN;
                     END IF;
                END IF;
      
                IF v_trncd IN ('411','412','413') THEN
                     CLOSE psbcon3;
                     OPEN psbcon3;
                ELSE
                     CLOSE psbcon;
                     OPEN psbcon;
                END IF;
      
                LOOP
                     IF v_trncd IN ('411','412','413') THEN
                          FETCH psbcon3 INTO prt_rec;
                     ELSE
                          FETCH psbcon INTO prt_rec;
                     END IF;
                     v_scode := prt_rec.scode;
      
                     IF v_trncd IN ('411','412','413') THEN
                          EXIT WHEN psbcon3%NOTFOUND;
                     ELSE
                          EXIT WHEN psbcon%NOTFOUND;
                     END IF;
                     IF prt_rec.trncd IN ('411','413') THEN
                          safo_init.n_psbkbal := n_bal;
                     END IF;
      
                     safo_init.n_psbkbal := safo_init.n_psbkbal + prt_rec.trnamt;
      
                     if v_crline in ('CA','IC') then  
                          UPDATE tranx SET psbkbal = safo_init.n_psbkbal                        
                          WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                               AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                               AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                     else 
                          UPDATE tranx SET post = 'P', psbkbal = safo_init.n_psbkbal                            
                          WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                               AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                               AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                     end if;
      
                END LOOP;  -- psbcon loop
      
                IF v_trncd IN ('411','412','413') THEN
                     CLOSE psbcon3;
                ELSE
                     CLOSE psbcon;
                END IF;
      
                COMMIT;
      
           ELSE
                Message('Problem');
           END IF;
      END;
      I run this procedure using the input as below:
      I_Psbk_Cont(TRUE, 'SA', '00000', '01', 123, 12, '102', '30/11/2012')
      The result (TRANX.POST & TRANX.PSBKBAL):
      TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
      ------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
      00000   SA      2012  1      1       00000  01     123   12      P     -50     111    15/11/2012   N      950      15/12/2012 10:10:00 AM
      00000   SA      2012  2      1       00000  01     123   12      P     -50     111    20/11/2012   N      900      20/12/2012 09:09:00 AM
      00000   SA      2012  3      1       00000  01     123   12      P     -50     111    21/11/2012   N      850      21/12/2012 11:21:00 AM
      00000   SA      2012  4      1       00000  01     123   12      P     -50     111    22/11/2012   N      800      22/12/2012 07:09:00 AM
      00000   SA      2012  5      1       00000  01     123   12      P     -50     111    23/11/2012   N      794.75   23/12/2012 13:09:00 PM
      00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
      00000   SA      2012  6      1       00000  01     123   12      P     -50     111    24/11/2012   N      744.75   24/12/2012 19:09:00 PM
      00000   SA      2012  7      1       00000  01     123   12      P     -70     102    30/11/2012   N      674.75   30/12/2012 09:09:00 AM
      Above result was wrong+ on TRANX.PSBKBAL+ started from *794.75 - 674.75*, it show something like the cursor has took the wrong result (wrong TRANX.TRNAMT) to update TRANX.PSBKBAL, it does not follow the WHERE clause in the cursor at TRANX.SEQNO = 5 only, but for the rest it return to the correct amount.

      Noticed that it took the TRANX.TRNAMT = -5.25 from TRANX.CRLINE = 'CA'.

      The correct results should be:
      TRNBRN  CRLINE  YR    SEQNO  SEQCNT  BRNCD  MODCD  ACNO  CHKDGT  POST  TRNAMT  TRNCD  TRNDT        OVRRD  PSBKBAL  TIME
      ------  ------  ----  -----  ------  -----  -----  ----  ------  ----  ------  -----  ----------  -----  -------  ---------------------
      00000   SA      2012  1      1       00000  01     123   12      P     -50     111    15/11/2012   N      950      15/12/2012 10:10:00 AM
      00000   SA      2012  2      1       00000  01     123   12      P     -50     111    20/11/2012   N      900      20/12/2012 09:09:00 AM
      00000   SA      2012  3      1       00000  01     123   12      P     -50     111    21/11/2012   N      850      21/12/2012 11:21:00 AM
      00000   SA      2012  4      1       00000  01     123   12      P     -50     111    22/11/2012   N      800      22/12/2012 07:09:00 AM
      00000   SA      2012  5      1       00000  01     123   12      P     -50     111    23/11/2012   N      750      23/12/2012 13:09:00 PM
      00001   CA      2012  1      1       00001  02     456   23      S     -5.25   101    20/11/2012   N      0        20/12/2012 09:09:00 AM
      00000   SA      2012  6      1       00000  01     123   12      P     -50     111    24/11/2012   N      700      24/12/2012 19:09:00 PM
      00000   SA      2012  7      1       00000  01     123   12      P     -70     102    30/11/2012   N      630      30/12/2012 09:09:00 AM
      My Questions:

      1. Why and How this situation could occur? What make me so confuse is the cursor took the wrong result for 1 record only, but others were return to correct result again.

      2. Is this a bug? Or there have something wrong with my codes that can make the cursor not consistent?

      3. Or this situation could happen because of global variable?

      Any explanation or helps would be appreciated.


      Thanks & Regards,

      Luthfi

      Edited by: Luthfi on Mar 14, 2012 10:48 AM

      Edited by: Luthfi on Mar 14, 2012 11:09 AM
        • 1. Re: Cursor returning wrong result
          rp0428
          >
               IF b_foflg = TRUE THEN
                    SELECT psbkbal INTO safo_init.n_psbkbal
                    FROM paper
                    WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
          >
          1. You are selecting into a variable but the query could return no rows. What happens then? You aren't checking for that.

          2. When you only want to process when a condition is true
               ELSE
                    Message('Problem');
               END IF;
          Put the short code first and then return so developers don't have to search for the ELSE. Then you don't need to put the bulk of the code in an IF block.
               IF b_foflg != TRUE THEN
                    Message('Problem');
                                          RETURN;
               END IF;
          • 2. Re: Cursor returning wrong result
            Luthfi
            Hi,

            Thanks for your reply.
            1. You are selecting into a variable but the query could return no rows. What happens then? You aren't checking for that.
            The query has return 1 row if we refer to PAPER+ table. Assume that i run the procedure only for existing record in PAPER+.
            2. When you only want to process when a condition is true
            Actually, for ELSE+ condition have another process which it calls another procedure. I just put the message for the example because i run the procedure using TRUE+:
            I_Psbk_Cont(TRUE, 'SA', '00000', '01', 123, 12, '102', '30/11/2012')
            It return correctly for the first 4 rows, but 5th row it is wrongly updated.


            Thanks & Regards,

            Luthfi
            • 3. Re: Cursor returning wrong result
              Paul  Horth
              Please post EXACT table and procedure definitions + insert statements that allow us to put
              data in.

              I say EXACT as

              SELECT psbkbal INTO safo_init.n_psbkbal
                        FROM paper
                        WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;

              cannot possibly work as there is no crline in your paper table.
              • 4. Re: Cursor returning wrong result
                rp0428
                OP just updated his original post to include this.
                CRLINE      VARCHAR2(2 BYTE) NOT NULL,
                Maybe they think we won't notice it wasn't there to begin with. So they probably won't retest anything now that it is present.
                • 5. Re: Cursor returning wrong result
                  Luthfi
                  Hi,

                  Sorry, I've updated the post. Actually in my table have that column but I forgot to put it in my post before. I also provides the insert statement as per request. Thanks for giving alert to me. Kindly help me why and how this issue happened.

                  Thanks & Regards,

                  Luthfi
                  • 6. Re: Cursor returning wrong result
                    Luthfi
                    Hi,

                    Thanks for your reply.
                    Maybe they think we won't notice it wasn't there to begin with. So they probably won't retest anything now that it is present.
                    Sorry, that is my mistake.
                    It return correctly for the first 4 rows, but 5th row it is wrongly updated.
                    This is what make me confused. The cursor was returned 7 rows. For the first 4 rows it correctly updated according the correct amount, but at 5th row it take wrong amount *(-5.25)* to update the TRANX.PSBKBAL. Then at 6th row it return to correct amount again. Why this situation could occur?

                    Any explanation or helps would be appreciated.

                    Thanks & Regards,

                    Luthfi
                    • 7. Re: Cursor returning wrong result
                      Paul  Horth
                      Please post a correct procedure as well please: the one you posted doesn't compile (for example: and a.trncd '455') also what is safo_init.n_psbkbal, is it an global in another package or another mistake?

                      Alos what is n_bal? It doesn't seem to be defined.

                      Also, insert statements for tranx do not work: can you correct and test please?
                      • 8. Re: Cursor returning wrong result
                        Marwim
                        and a.trncd '455'
                        should be
                        and a.trncd != '455'
                        The forum software does not show "tags" like &lt; &gt;

                        Regards
                        Marcus

                        Edited by: Marwim on 14.03.2012 10:17
                        See [url https://wikis.oracle.com/display/Forums/Forums+FAQ#ForumsFAQ-Cautions] FAQ - Cautions
                        • 9. Re: Cursor returning wrong result
                          Paul  Horth
                          Thanks, didn't realise that - it's a bit of a nuisance.

                          Still don't know what n_bal is though.
                          • 10. Re: Cursor returning wrong result
                            Luthfi
                            Hi Paul Horth,

                            Thanks for reply.
                            what is safo_init.n_psbkbal, is it an global in another package or another mistake?
                            It is global.
                            what is n_bal? It doesn't seem to be defined.
                            Im sorry, this is another mistake.
                            n_bal NUMBER(16,2) := 0;
                            Insert statement for tranx, I missed the right parenthesis at the last. Oh, too many mistakes in my post, my bad.
                            INSERT INTO TRANX(TRNBRN, CRLINE, YR, SEQNO, SEQCNT, BRNCD, MODCD, ACNO, CHKDGT, POST, TRNAMT, TRNCD,
                                              TRNDT, OVRRD, PSBKBAL, TIME)
                                           VALUES('00000', 'SA', 2012, 1, 1, '00000', '01', 123, 12, 'U', -50, '111', 
                                                  TO_DATE('15/11/2012','DD/MM/YYYY'), 'N', 0, TO_DATE('15/12/2012 10:10:00','DD/MM/YYYY HH24:MI:SS'));
                            Any explanation or helps would be appreciated.

                            Thanks & Regards,

                            Luthfi
                            • 11. Re: Cursor returning wrong result
                              Paul  Horth
                              I'm sorry, still can't compile:

                              What's v_scode := prt_rec.scode ?

                              What is prt_rec.trnbrn?

                              Until you post something we can compile, we can't really help.
                              • 12. Re: Cursor returning wrong result
                                Luthfi
                                Hi,

                                This is the latest procedure, I've corrected and compiled it and it should not have the problem. I am sorry to have trouble you, thank you for being patient with me.
                                PROCEDURE I_Psbk_Cont(B_FOFLG IN BOOLEAN, V_CRLINE IN VARCHAR2, V_BRNCD IN VARCHAR2, V_MODCD IN VARCHAR2,
                                N_ACNO IN NUMBER, N_CHKDGT IN NUMBER, V_TRNCD IN VARCHAR2, D_SYSDATE IN VARCHAR2) IS
                                
                                     CURSOR psbcon IS
                                     SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt, trnbrn 
                                     FROM tranx a, code b
                                     WHERE a.crline = v_crline
                                          AND brncd = v_brncd
                                          AND modcd = v_modcd
                                          AND acno = n_acno
                                          AND chkdgt = n_chkdgt
                                          AND ((a.crline in ('SA','IS') and post in ('U', 'S') OR (post = 'C' and psbkbal <> 0))
                                          or (a.crline in ('CA','IC') and post<>'C'))
                                          AND ovrrd IN ('N','A')
                                          AND a.crline = b.crline
                                          AND a.trncd = b.trncd
                                          and a.trncd != '455'
                                     ORDER BY trndt,TIME, seqno, seqcnt;
                                
                                     CURSOR psbcon3 IS     
                                     SELECT trndt, a.trncd, trnamt, psbkbal, seqno, seqcnt, trnbrn 
                                     FROM tranx a, code b
                                     WHERE a.crline = v_crline
                                          AND brncd = v_brncd
                                          AND modcd = v_modcd
                                          AND acno = n_acno
                                          AND ((a.crline in ('SA','IS') and post in ('U', 'S')) or (a.crline in ('CA','IC') and post = 'S'))
                                          AND ovrrd IN ('N','A')
                                          AND a.crline = b.crline
                                          AND a.trncd = b.trncd
                                     ORDER BY trndt, TIME, seqno, seqcnt;
                                
                                     prt_rec psbcon%ROWTYPE;
                                
                                     n_bfbal NUMBER(16,2) := 0;
                                     
                                     n_bal NUMBER(16,2) := 0;
                                
                                BEGIN
                                     IF b_foflg = TRUE THEN
                                          SELECT psbkbal INTO safo_init.n_psbkbal
                                          FROM paper
                                          WHERE crline = v_crline AND brncd = v_brncd AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt;
                                          
                                          IF v_trncd IN ('411','413') THEN
                                               n_bfbal := safo_init.n_psbkbal;
                                          END IF;
                                
                                          n_bal := safo_init.n_psbkbal;
                                
                                          IF v_trncd IN ('411','412','413') THEN
                                               OPEN psbcon3;
                                               FETCH psbcon3 INTO prt_rec;
                                
                                               IF psbcon3%NOTFOUND THEN
                                                    CLOSE psbcon3;
                                                    RETURN;
                                               END IF;
                                          ELSE
                                               OPEN psbcon;
                                               FETCH psbcon INTO prt_rec;
                                
                                               IF psbcon%NOTFOUND THEN
                                                    Message('No');
                                                    CLOSE psbcon;
                                                    RETURN;
                                               END IF;
                                          END IF;
                                
                                          IF v_trncd IN ('411','412','413') THEN
                                               CLOSE psbcon3;
                                               OPEN psbcon3;
                                          ELSE
                                               CLOSE psbcon;
                                               OPEN psbcon;
                                          END IF;
                                
                                          LOOP
                                               IF v_trncd IN ('411','412','413') THEN
                                                    FETCH psbcon3 INTO prt_rec;
                                               ELSE
                                                    FETCH psbcon INTO prt_rec;
                                               END IF;
                                               --v_scode := prt_rec.scode;
                                
                                               IF v_trncd IN ('411','412','413') THEN
                                                    EXIT WHEN psbcon3%NOTFOUND;
                                               ELSE
                                                    EXIT WHEN psbcon%NOTFOUND;
                                               END IF;
                                               IF prt_rec.trncd IN ('411','413') THEN
                                                    safo_init.n_psbkbal := n_bal;
                                               END IF;
                                
                                               safo_init.n_psbkbal := safo_init.n_psbkbal + prt_rec.trnamt;
                                
                                               if v_crline in ('CA','IC') then  
                                                    UPDATE tranx SET psbkbal = safo_init.n_psbkbal                        
                                                    WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                                                         AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                                                         AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                                               else 
                                                    UPDATE tranx SET post = 'P', psbkbal = safo_init.n_psbkbal                            
                                                    WHERE seqno = prt_rec.seqno AND seqcnt = prt_rec.seqcnt AND brncd = v_brncd
                                                         AND modcd = v_modcd AND acno = n_acno AND chkdgt = n_chkdgt
                                                         AND trnbrn = prt_rec.trnbrn AND post <> 'C';
                                               end if;
                                
                                          END LOOP;  -- psbcon loop
                                
                                          IF v_trncd IN ('411','412','413') THEN
                                               CLOSE psbcon3;
                                          ELSE
                                               CLOSE psbcon;
                                          END IF;
                                
                                          COMMIT;
                                
                                     ELSE
                                          Message('Problem');
                                     END IF;
                                END;
                                Any explanation or helps would be appreciated.

                                Thanks & Regards,

                                Luthfi
                                • 13. Re: Cursor returning wrong result
                                  Paul  Horth
                                  I'm getting the correct results:
                                  TRNBRN     CRLINE     YR     SEQNO     SEQCNT     BRNCD     MODCD     ACNO     CHKDGT     POST     TRNAMT     TRNCD     TRNDT     OVRRD     PSBKBAL     TIME
                                  00000     SA     2012     1     1     00000     01     123     12     P     -50.00     111     15-Nov-12     N     950.00     15-Dec-12 10:10:00 AM
                                  00000     SA     2012     2     1     00000     01     123     12     P     -50.00     111     20-Nov-12     N     900.00     20-Dec-12 9:09:00 AM
                                  00000     SA     2012     3     1     00000     01     123     12     P     -50.00     111     21-Nov-12     N     850.00     21-Dec-12 11:21:00 AM
                                  00000     SA     2012     4     1     00000     01     123     12     P     -50.00     111     22-Nov-12     N     800.00     22-Dec-12 7:09:00 AM
                                  00000     SA     2012     5     1     00000     01     123     12     P     -50.00     111     23-Nov-12     N     750.00     23-Dec-12 1:09:00 PM
                                  00001     CA     2012     1     1     00001     02     456     23     S     -5.25     101     20-Nov-12     N     0.00     20-Dec-12 9:09:00 AM
                                  00000     SA     2012     6     1     00000     01     123     12     P     -50.00     111     24-Nov-12     N     700.00     24-Dec-12 7:09:00 PM
                                  00000     SA     2012     7     1     00000     01     123     12     P     -70.00     102     30-Nov-12     N     630.00     30-Dec-12 9:09:00 AM
                                  • 14. Re: Cursor returning wrong result
                                    Luthfi
                                    Hi Paul Horth,

                                    Thanks for your helps.

                                    Ok, now you got the correct results. Actually, I also got the correct results. But, what happen if many users or programs are using the
                                    Procedure I_Psbkbal_Cont()
                                    at the same time within more than thousand records? And assume that more than 1 record have the TRANX.TRNAMT = -5.25 with different TRANX.BRNCD, TRANX.MODCD, TRANX.ACNO, & TRANX.CHKDGT.
                                    Is it possible for the error occur? So now if it possible, back to my questions,
                                    My Questions:
                                    1. Why and How this situation could occur? What make me so confuse is the cursor took the wrong result for 1 record only, but others were return to correct result again.

                                    2. Is this a bug? Or there have something wrong with my codes that can make the cursor not consistent?

                                    3. Or this situation could happen because of global variable?>

                                    Any explanation or helps would be appreciated.

                                    Thanks & Regards,

                                    Luthfi
                                    1 2 Previous Next