2 Replies Latest reply on Nov 5, 2004 3:39 AM by 423876

    Duplicate records returned by Interconnect DB adapter

    423876
      Hi,

      This is Vinod S Nair from Malayala Manorama. We are implementing AS10g Protal,
      Oracle Interconnect Integration suite and a host of adapters including SAP
      adapter. We needs some help from you.

      We have a Synchronous Request/Response DB Adapter design as following.

      From a J2EE application, an invoking procedure at the adapter is triggered.This
      invokes the DB adapter which inturn invokes the implemetned procedure which
      returns a VARRAY.J2EE application is at the AS10g Protal side.

      The implemented procedure further invokes a legacy procedure which returns a
      Refcursor.Inside this implemetned procedure, the cursor is fetched into a
      Recortype and each element of the record type is added to an Object (of type
      MPLUS_MPLUS_BankGurDet_OAI_V1). Then each of this object is added to a VARRAY
      and this VARRAY is returned to the invoking procedure.

      The problem we face is that, in the returned the varray, there is always a
      duplicate of the last recored. That is if we have , say 5 records , then when
      we manipulate the VARRAY, there will 6 recored and the sixth record will be a
      duplicate of the 5th one. We checked the query used inside the cursor and it is
      not returning duplicate records.

      Why is this happening. I have attached the code I used and log file. This is
      happening for all cases where VARRAY is returned. Please find below the pl/sql
      i have used and below this PL/SQL, a snap shot of the log file is pasted. I am
      attaching the full log file with sql along with this mail.

      Regards
      Vinod S Nair

      -- All these is defined at the Legacy system
      -- Following is a object definition used by the implemented procedure to return
      a collection of objects.

      TYPE MPLUS_MPLUS_BankGurDet_OAI_V1 IS OBJECT (
      AG_CODE VARCHAR2(1000),
      AMT_NO NUMBER,
      BANK VARCHAR2(1000),
      VAL_FROM DATE,
      VAL_TO DATE,
      GUAR_NO VARCHAR2(1000)
      );

      -- Following is the defintion of Varray returned by the implemented procedure.

      TYPE MPLUS_MPLUS_BankGurDet_OAI_Arr IS VARRAY(1000) OF
      MPLUS_MPLUS_BankGurDet_OAI_V1;


      -- This is the implemented procedure called by adapter. This returns a Varray of
      the above object.(MPLUS_MPLUS_BankGurDet_OAI_V1).

      PACKAGE Mplus AS
      TYPE MY_TYPE IS REF CURSOR;

      PROCEDURE imp_bankGurDet_OAI_V1(
      i_CUSTOMER_CODE IN LONG,
      i_CURRENT_DATE IN DATE,
      o_RESULT OUT MPLUS_MPLUS_BankGurDet_OAI_Arr
      );

      END Mplus ;

      PACKAGE BODY Mplus AS

      PROCEDURE imp_bankGurDet_OAI_V1(
      i_CUSTOMER_CODE IN LONG,
      i_CURRENT_DATE IN DATE,
      o_RESULT OUT MPLUS_MPLUS_BankGurDet_OAI_Arr
      )
      AS
      Type BANK IS RECORD(agencyname VARCHAR2(1000),Amout NUMBER,BankName
      VARCHAR2(1000),validfrom date , validto date,guarenteeno varchar2(1000));
      RECS MY_TYPE; -- Ref cursor
      BANKDETAILS_REC BANK ;
      BANKDETAILS_OBJ MPLUS_MPLUS_BANKGURDET_OAI_V1;
      i Number;
      dummy number;
      BEGIN

      WF_MP_ADVT.SP_ADV_AGE_BANK_GRTY(i_CUSTOMER_CODE,i_CURRENT_DATE,RECS);
      dummy:= 0;
      BANKDETAILS_OBJ:=new MPLUS_MPLUS_BankGurDet_OAI_V1('',0,'',sysdate,sysdate,'');
      o_RESULT:=MPLUS_MPLUS_BankGurDet_OAI_Arr();
      i:=1;
      LOOP
      fetch RECS INTO BANKDETAILS_REC;
      BANKDETAILS_OBJ.AG_CODE:=BANKDETAILS_REC.agencyname;
      BANKDETAILS_OBJ.AMT_NO:=BANKDETAILS_REC.Amout;
      BANKDETAILS_OBJ.BANK:=BANKDETAILS_REC.BankName;
      BANKDETAILS_OBJ.VAL_FROM:=to_date(BANKDETAILS_REC.validfrom,'dd-mon-yyyy');
      BANKDETAILS_OBJ.VAL_TO:=to_date(BANKDETAILS_REC.validto,'dd-mon-yyyy');
      BANKDETAILS_OBJ.GUAR_NO:=BANKDETAILS_REC.guarenteeno;
      o_RESULT.EXTEND;
      o_RESULT(i):=BANKDETAILS_OBJ;

      i:=i+1;
      EXIT WHEN RECS%NOTFOUND;
      END LOOP;

      END imp_bankGurDet_OAI_V1;
      END Mplus ;


      -- The following is a legacy procedure which return a refcursor which is
      iterated by the implemented procedure to create object and then this object is
      added to VARRAY in the.

      PACKAGE WF_MP_ADVT AS

      PROCEDURE SP_ADV_AGE_BANK_GRTY(
      Customer_Code IN CHAR,
      curdate in date,
      RESULT OUT Record_Type);
      END;

      PACKAGE BODY WF_MP_ADVT AS

      PROCEDURE SP_ADV_AGE_BANK_GRTY(
      Customer_Code IN CHAR,
      curdate in date,
      RESULT OUT Record_Type) IS
      BEGIN
      -- OPEN RESULT FOR SELECT
      AG_CODE,AMT_NO,BANK,to_char(VAL_FROM,'dd-mm-yyyy'),to_char(VAL_TO,'dd-mm-yyyy'),GUAR_NO
      FROM WF_MP_ADVT_BANKGAR WHERE AG_CODE=CUSTOMER_CODE;
      --OPEN RESULT FOR SELECT * FROM WF_MP_ADVT_BANKGAR WHERE AG_CODE=CUSTOMER_CODE;
      OPEN RESULT FOR SELECT
      AG_CODE,AMT_NO,BANK,to_char(VAL_FROM,'dd-mon-yyyy'),to_char(VAL_TO,'dd-mon-yyyy'),GUAR_NO
      FROM WF_MP_ADVT_BANKGAR WHERE AG_CODE=CUSTOMER_CODE;
      -- null;
      END SP_ADV_AGE_BANK_GRTY;

      END;


      -----
      The Log file

      Tue Nov 02 14:56:14 GMT+05:30 2004: db_bridge_writer_1 added the OUT argument
      RESULT to the reply: Mplus.bankGurDet:OAI/V1,OAI/V1,true,2
      RESULT[0]
      AG_CODE: 1
      AMT_NO: 200000.0
      BANK: STATE BANK OF INDIA
      VAL_FROM: 0004-04-01
      VAL_TO: 0005-03-31
      GUAR_NO: SBI01
      RESULT[1]
      AG_CODE: 1
      AMT_NO: 200000.0
      BANK: HDFC BANK
      VAL_FROM: 0004-04-01
      VAL_TO: 0005-03-31
      GUAR_NO: SBI01
      RESULT[2]
      AG_CODE: 1
      AMT_NO: 200000.0
      BANK: HDFC BANK
      VAL_FROM: 0004-04-01
      VAL_TO: 0005-03-31
      GUAR_NO: SBI01
      .
      Tue Nov 02 14:56:14 GMT+05:30 2004: db_bridge_writer_1 wrote the message to the
      database successfully.
      Tue Nov 02 14:56:14 GMT+05:30 2004: Agent: sending reply message.
      Mplus.bankGurDet:OAI/V1,OAI/V1,true,2
        • 1. Re: Duplicate records returned by Interconnect DB adapter
          349404
          Vinod,

          As far as I can see the problem with duplicating the last record is a result of the EXIT condition in the loop in procedure 'imp_bankGurDet_OAI_V1'. Your loop looks as follows:

          LOOP
          FETCH recs INTO bankdetails_rec;
          bankdetails_obj.ag_code := bankdetails_rec.agencyname;
          bankdetails_obj.amt_no := bankdetails_rec.amout;
          bankdetails_obj.bank := bankdetails_rec.bankname;
          bankdetails_obj.val_from := TO_DATE(bankdetails_rec.validfrom,'DD-MON-YYYY');
          bankdetails_obj.val_to := TO_DATE(bankdetails_rec.validto,'DD-MON-YYYY');
          bankdetails_obj.guar_no := bankdetails_rec.guarenteeno;
          o_result.EXTEND;
          o_result(i):= bankdetails_obj;
          i:=i+1;

          EXIT WHEN recs%NOTFOUND;
          END LOOP;

          The problem is that checking for recs%NOTFOUND at the end of the loop results in going through the loop one more time even though you can't fetch another row from the recs cursor. The solution is to put the EXIT condition right after the FETCH statement. You now exit the loop if you can't fetch another row without assigning the last fetched record to bankdetails_obj again:

          LOOP
          FETCH recs INTO bankdetails_rec;
          EXIT WHEN recs%NOTFOUND;

          bankdetails_obj.ag_code := bankdetails_rec.agencyname;
          bankdetails_obj.amt_no := bankdetails_rec.amout;
          bankdetails_obj.bank := bankdetails_rec.bankname;
          bankdetails_obj.val_from := TO_DATE(bankdetails_rec.validfrom,'DD-MON-YYYY');
          bankdetails_obj.val_to := TO_DATE(bankdetails_rec.validto,'DD-MON-YYYY');
          bankdetails_obj.guar_no := bankdetails_rec.guarenteeno;
          o_result.EXTEND;
          o_result(i):= bankdetails_obj;

          i:=i+1;

          END LOOP;
          CLOSE recs;

          You also might want to consider to close the ref cursor after exiting the loop... 'too many open cursors' is not a good exception to get. ;-)

          Hope this is helpful.

          Thanks,
          Markus