4 Replies Latest reply: Apr 29, 2013 10:01 AM by CraigB RSS

    Please check the below Cursor Procedure and correct that code Please

    991535
      Hai Every One.
      Please check the below code I have two issues in that code
      1. Invalid cursor
      2. Record must be enter

      Please correct this code and send me pls its urgent.I cont understand where i done mistake




      PROCEDURE fetch_detail_PROC IS

      cursor c1 is select * from quota_mast where quota_mast.divn=:quota_mast.divn and quota_mast.QUOT_tyPE=:quota_mast.QUOT_tyPE and quota_mast.INQNO=:quota_mast.INQNO;
      cursor c6 is select * from quota_det
      where quota_det.divn=:quota_mast.divn AND quota_det.QUOT_TYPE=:quota_mast.QUOT_tyPE and quota_det.enq_num=:quota_mast.INQNO
      ORDER BY quota_det.quot_no;
      --cursor c5 is select * from acc_mst where cdp=:quota_mast.ccdp and divn=:quota_mast.divn;
      --vn acc_mst%rowtype;
      i quota_mast%rowtype;
      detail1 quota_det%rowtype;

      BEGIN
           GO_BLOCK('quota_mast');
      for i in c1 LOOP
                :quota_mast.INQNO:=i.INQNO;
                          :quota_mast.DIVN:=i.DIVN;
                     :quota_mast.QUOT_NO:=i.QUOT_NO;
                          :quota_mast.quot_type:=i.quot_type;
                          :quota_mast.CCDP:=i.CCDP;
                          select nm into :QUOTA_MAST.PARTY_NAME from acc_mst where acc_mst.DIVN=:QUOTA_MAST.DIVN AND acc_mst.cdp=:ccdp;
                          :quota_mast.pak_for:=i.pack_for_PER;
                          :quota_mast.exc_duty:=i.exc_duty_PER;
                          :quota_mast.vat:=i.vat_PER;
                          :quota_mast.cst:=i.cst_PER;
                          :quota_mast.wct:=i.wct_PER;
                          :quota_mast.ser_tax:=i.ser_tax_PER;
                          :quota_mast.insu:=i.insu_PER;
                          :quota_mast.tot_tax:=i.tot_tax;
                          :quota_mast.tot_val:=i.tot_val;
                     :quota_mast.value:=i.value;
                                    next_record;
      end loop;
           first_record;


      GO_BLOCK('quota_det');

                     for detail1 in c6
                loop
                          :quota_det.quot_no:=detail1.quot_no;
                :quota_det.ENQ_NUM:=detail1.enq_num;
                :quota_det.item_code:=detail1.item_code;
                SELECT ITNM into :NM FROM itm_MST WHERE DIVN=:quota_mast.DIVN AND iCDP=:ITEM_CODE;
                :quota_det.price:=detail1.price;
                :quota_det.qty:=detail1.qty;
                :quota_det.DISC_PER:=detail1.DISC_PER;

                     next_record;
      END LOOP;
           first_record;
           
      close c6;

      --close c1;
      exception
      when others then
      message(sqlerrm);
      message(' ');

      END;



      Thank you..
        • 1. Re: Please check the below Cursor Procedure and correct that code Please
          Amatu Allah Neveen Ebrahim
          Hi
          pls check the logic behind ur cursor loops in assign statement to decide where u should close the cursor because u have not noticed that the cursor has been closed and have tried to continue using it.
          Amatu Allah
          • 2. Re: Please check the below Cursor Procedure and correct that code Please
            Andreas Weiden
            Welcome to the forum.

            Please use
            -tags around your code to format it properly. Also, post the exact error-message along with the error-number.
            
            One issue might be your "close c1" as a cursor will be closed automatically when used in a cursor-for-loop.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: Please check the below Cursor Procedure and correct that code Please
              Christian Erlinger
              Is there any reason why you want to write a bunch of code instead of using a simple master-detail relationship or a from-clause query?

              cheers
              • 4. Re: Please check the below Cursor Procedure and correct that code Please
                CraigB
                Oracle Forms is closely tied to the database meaning you can base your data blocks on your tables and Forms will handle fetching the data. I agree with Christian, your tables lend themselves to using a master - detail configuration and if you need to filter your block then you can use the Data Block WHERE Clause property.
                Please correct this code and send me pls its urgent.
                As to the urgency of your request, the forum is a community of "Volunteers." Enough said.

                Regarding your code, I see a several potential problems.

                First, how are your QUOTA_MAST and QUOTA_DET blocks synchronized? Are you using a block relationship? Are you using a combination of triggers to synchonize the QUOTA_DET block when you user navigates to a different record in the QUOTA_MAST block?

                Second, your Procedure name is "Fetch_Detail_Proc" but you are also fetching Master data as well. What is the purpose of your procedure and where do you call this procedure?

                Third, you're using SELECT * in your cursor queries. You should always explicitly name each column. What happens when your table changes and you need to search your code for a column that was dropped?

                Fourth, your cursor queries are referencing the same data blocks you are fetching data to populate. This gives the impression that you already have data in your block. This doesn't make sense - could you explain what you are trying to accomplish?

                Fifth, with respects to your code, the "Invalid Cursor" error (is this really the error you're getting?) is most likely caused by your explicit call to close the C1 cursor. As Andreas stated, when using a CURSOR FOR LOOP, the cursor is automatically opened, fetched and closed using this construct. There is no need to explicitly close the cursor, in fact - doing so will result in an invalid cursor error.

                As to the "Record must be entered" error, is sounds like you have required items in your block or your QUOTA_MAST block is not synchronized with your QUOTA_DET block, so when you attempt to navigate to a different block or record within your the same block the navigation cursor can't move because you haven't entered data that is required.

                Please tell us what you are attempting to do with this code so we can tell you if you are using the correct method.

                Craig...