7 Replies Latest reply: May 15, 2012 7:57 AM by InoL RSS

    Cursors are not closed when using Ref Cursor Query in a report  ORA-01000

    Mostafa Abolaynain
      Dear Experts

      Oracel database 11g,
      developer suite 10.1.2.0.2,
      application server 10.1.2.0.2,
      Windows xp platform



      For a long time, I'm hitting ORA-01000

      I have a 2 group report (master and detail) using Ref Cusor query, when this report is run, I found that it opens several cursors (should be only one cursor) for the detail query although it should not, I found that the number of these cursors is equal to the number of master records.


      Moreover, after the report is finished, these cursors are not closed, and they are increasing cumulatively each time I run the report, and finally the maximum number of open cursors is exceeded, and thus I get ORA-01000.


      I increased the open cursors parameter for the database to an unbeleivable value 30000, but of course it will be exceeded during the session because the cursors are increasing cumulatively.

      I Found that this problem is solved when using only one master Ref Cursor Query and create a breake group, the problem is solved also if we use SQL Query instead of Ref Query for the master and detail queries, but for some considerations, I should not use neither breake group nor SQL Query, I have to use REF Cursor queries.

      Is this an oracle bug , and how can I overcome ?

      Thanks

      Edited by: Mostafa Abolaynain on May 6, 2012 9:58 AM
        • 1. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-01000
          InoL
          I Found that this problem is solved when using a breake group report, but for some considerations, I should not use breake group report, I have to use REF Cursor queries.
          Since this is a master-detail report, I don't see why you can't create a break report. Here is a master-detail Group Above report using ref cursors:
          http://docs.oracle.com/html/B10602_01/orbr_refcur.htm

          IMHO, it is hardly ever necessary to use ref cursors. Within Reports you can do so much with lexical parameters, use views etc.
          • 2. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
            Mostafa Abolaynain
            Thank you Inol for your answer, However


            Ref Cursor give me felxibility to control the query, for example see the following query :




            function QR_1RefCurDS return DEF_CURSORS.JOURHEAD_REFCUR is
            temp_JOURHEAD DEF_CURSORS.JOURHEAD_refcur;
                 
                      v_from_date DATE;
                      v_to_date DATE;
                      V_SERIAL_TYPE number;
            begin
                 

            SELECT SERIAL_TYPE INTO V_SERIAL_TYPE
            FROM ACC_VOUCHER_TYPES
            where voucher_type='J'
            and IDENT_NO=:IDENT
            AND COMP_NO=TO_NUMBER(:COMPANY_NO);

                 

                 IF :no_date=1 then

                           IF V_SERIAL_TYPE =1 THEN     
                           open temp_JOURHEAD for select VOCH_NO, VOCH_DATE
                           FROM JOURHEAD
                           WHERE COMP_NO=TO_NUMBER(:COMPANY_NO)
                           AND IDENT=:IDENT
                      AND ((TO_NUMBER(VOCH_NO)=:FROM_NO and :FROM_NO IS NOT NULL AND :TO_NO IS NULL)
                      OR (TO_NUMBER(VOCH_NO) BETWEEN :FROM_NO AND :TO_NO and :FROM_NO IS NOT NULL AND :TO_NO IS NOT NULL )
                      OR (TO_NUMBER(VOCH_NO)<=:TO_NO and :FROM_NO IS NULL AND :TO_NO IS NOT NULL )
                      OR (:FROM_NO IS NULL AND :TO_NO IS NULL ))
                           ORDER BY TO_NUMBER(VOCH_NO);

                           ELSE
                           open temp_JOURHEAD for select VOCH_NO, VOCH_DATE
                           FROM JOURHEAD
                           WHERE COMP_NO=TO_NUMBER(:COMPANY_NO)
                           AND IDENT=:IDENT               
                      AND ((VOCH_NO=:FROM_NO and :FROM_NO IS NOT NULL AND :TO_NO IS NULL)
                      OR (VOCH_NO BETWEEN :FROM_NO AND :TO_NO and :FROM_NO IS NOT NULL AND :TO_NO IS NOT NULL )
                      OR (VOCH_NO<=:TO_NO and :FROM_NO IS NULL AND :TO_NO IS NOT NULL )
                      OR (:FROM_NO IS NULL AND :TO_NO IS NULL ))     
                           ORDER BY VOCH_NO;          
                           
                           END IF;
                           

                 ELSE
                 
                           v_from_date:=to_DATE(:from_date);
                           v_to_date:=to_DATE(:to_date);                         
                      
                      IF V_SERIAL_TYPE =1 THEN
                           open temp_JOURHEAD for select VOCH_NO, VOCH_DATE
                           FROM JOURHEAD
                           WHERE COMP_NO=TO_NUMBER(:COMPANY_NO)
                      AND IDENT=:IDENT                         
                           AND ((voch_date between v_from_date and v_to_date and :from_date is not null and :to_date is not null)
                           OR (voch_date <= v_to_date and :from_date is null and :to_date is not null)
                           OR (voch_date = v_from_date and :from_date is not null and :to_date is null)
                           OR (:from_date is null and :to_date is null ))     
                           ORDER BY VOCH_DATE,TO_NUMBER(VOCH_NO);     
                      ELSE
                           open temp_JOURHEAD for select VOCH_NO, VOCH_DATE
                           FROM JOURHEAD
                           WHERE COMP_NO=TO_NUMBER(:COMPANY_NO)
                           AND IDENT=:IDENT                         
                      AND ((voch_date between v_from_date and v_to_date and :from_date is not null and :to_date is not null)
                           OR (voch_date <= v_to_date and :from_date is null and :to_date is not null)
                           OR (voch_date = v_from_date and :from_date is not null and :to_date is null)
                           OR (:from_date is null and :to_date is null ))     
                           ORDER BY VOCH_DATE,VOCH_NO;          
                      END IF;
                      

                 END IF;               
                 

                 return temp_JOURHEAD;
                 
            end;
            • 3. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
              InoL
              Ref Cursor give me felxibility to control the query,
              A quick scan of your code shows that a ref cursor is not necessary at all here. Just create this query in your data model:
              select VOCH_NO, VOCH_DATE
              FROM JOURHEAD
              &WHERE
              See the Report Builder Help on how to use lexical parameters.
              • 4. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
                Mostafa Abolaynain
                Hi Inol

                Thank you for your answer which I marked as helpful.

                Your answer means that you will pass the where clause from the form as a parameter after you concatenate it, this means that in reports builder you can't run the report for debuging because the where clause is not found.

                Anyway, Do you agree with me that there is a bug that is " not closing child Ref Cursor Query in a report " ????

                Why Oracle Doesn't fix this bug ???
                • 5. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
                  InoL
                  this means that in reports builder you can't run the report for debuging
                  You still can. Just put the where clause in your input parameter like it would when you call the report from Forms.
                  I would not make the where clause as one parameter at all actually. Make 4 parameters (from_no, to_no, from_date, to_date) and build the where clause in the report after-parameter-form trigger.
                  Do you agree with me that there is a bug
                  No, You said that you don't have a break group. Very likely a cursor is opened and closed for every break. Since you don't have a break, cursors are opened, but not closed.
                  I cannot say this for sure of course, but it sounds very plausible that Reports works that way. You already found out yourself that there is no problem when creating a break group.
                  • 6. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
                    Mostafa Abolaynain
                    Since you don't have a break, cursors are opened, but not closed.

                    Inol, This will be Memory Resource Consuming,


                    Oracle Recommend in it's manuals to use Ref cursors, there are too many examples there for master detail queries using Ref Cursors.


                    I agree with you that using either breake group or sql queries will solve the problem,but Using Ref Cursor in Master detail queries is the case I'm talking about.


                    What shall I do if I already used Master Detail Ref Cusors queries in all my reports, I need too much time to update all of my reports.
                    • 7. Re: Cursors are not closed when using Ref Cursor Query in a report  ORA-010
                      InoL
                      I don't agree that Oracle recommends using ref cursors. It's possible, but that's not necessarily a recommendation.
                      There is a good discussion about ref cursors here:
                      PL/SQL 101 : Understanding Ref Cursors
                      especially this part:
                      3. What is the point of ref cursors?

                      On the other hand it may be some bug, so you should first patch your developer and application server version. Version 10.1.2.0.2 is the base, unpatched version. You need 10.1.3.x for database 11.2. See My Oracle Support note 887365.1.