1 2 Previous Next 15 Replies Latest reply: Sep 10, 2014 7:14 AM by 2745693 RSS

    Ref cursor returns one row lesser while using fetch.

    2745693

      Hi all,

       

      I have opened a ref cursor for a select statement and then fetched it to check data found or not and then returned that ref cursor.In the result i am getting one row lesser.

       

      Without fetch all the rows are returned. The issue is with the fetch only. Please help me to check no record found in the ref cursor without using fetch.

       

       

      Open refcusrsor1 for select name from proj1

       

      fetch refcursor1 into var1

       

      if var1%notfound then

      ...

      Else

      ...

      End if

       

      return.

       

      If we use the fetch, the first row is not getting returned in the procedure. without fetch all the rows returned.

       

       

       


        • 1. Re: Ref cursor returns one row lesser while using fetch.
          Frank Kulash

          Hi,

           

          FETCH reads 1 row.  If you use FETCH to read a row right after you OPEN the cursor, then that row has been read, and it won't be read again when you read the other rows.

          • 2. Re: Ref cursor returns one row lesser while using fetch.
            rp0428

            375586f9-17a1-444b-b9d6-ea2afe1dfd8e wrote:

             

            Hi all,

             

            I have opened a ref cursor for a select statement and then fetched it to check data found or not and then returned that ref cursor.In the result i am getting one row lesser.

             

            Without fetch all the rows are returned. The issue is with the fetch only. Please help me to check no record found in the ref cursor without using fetch.

             

             

            Open refcusrsor1 for select name from proj1

             

            fetch refcursor1 into var1

             

            if var1%notfound then

            ...

            Else

            ...

            End if

             

            return.

             

            If we use the fetch, the first row is not getting returned in the procedure. without fetch all the rows returned.

            Yes - what is your question?

             

            1. We have a box of Girl Scout Cookies. Unknown to us the box contains EXACTLY 100 cookies.

            2. I open the box and eat ONE cookie.

            3. Then I give the opened box to you

            4. You count the cookies and find 99 cookies in the box

             

            Question: you only found 99 cookies in the box - what happened to the other cookie?

             

            Moral: you need to open the box but do NOT eat any cookies before passing the box back to the person that ask for it.

            • 3. Re: Ref cursor returns one row lesser while using fetch.
              2745693

              Then is there any other ways to check whether data found or not in ref cursor and to return all the records?

              • 5. Re: Ref cursor returns one row lesser while using fetch.
                2683628

                If only the dv was not greedy and looked at the Cookie he could leave it there ! He doesn't need to eat it !

                • 6. Re: Ref cursor returns one row lesser while using fetch.
                  ORA-00007

                  You need to use the loop to fetch the data , FETCH by default returns only one row .

                  • 7. Re: Ref cursor returns one row lesser while using fetch.
                    JustinCave

                    2745693 wrote:

                     

                    Then is there any other ways to check whether data found or not in ref cursor and to return all the records?

                    Probably not (or at least not efficiently).

                     

                    You could run a COUNT(*) first and then open the cursor if the COUNT(*) is at least 1.  That's inefficient since you're running basically the same query twice.  And unless you've set your transaction isolation level to serializable, you may get a different number of rows from the count than when the cursor is opened moments later.  So the caller would still need to be able to deal with an empty result set.

                     

                    You could materialize the result internally (either in a collection or in a temporary table), count the results, and then then open the cursor based on that materialized data.  That's inefficient as well particularly if you're pulling a lot of data into a collection that is using up precious PGA space.

                     

                    Realistically, the right answer is not to try.  Return the cursor to the caller.  The caller is going to fetch the data.  The caller will, therefore, obviously know when no rows are returned.  So the caller is perfectly placed to do something if the cursor does not return any data.  Since the caller is perfectly placed to do this, let the caller handle that bit of logic rather than putting it in your procedure.

                     

                    Justin

                    • 8. Re: Ref cursor returns one row lesser while using fetch.
                      BluShadow

                      As Justin says, it's up to the calling code to handle whether there is data or not...

                       

                      SQL> create or replace function fetch_employees(deptno in number) return sys_refcursor is
                        2    rC sys_refcursor;
                        3  begin
                        4    open rC for 'select * from emp where deptno = :1' using fetch_employees.deptno;
                        5    -- At this point this function does not care whether the ref cursor will return any data or not
                        6    -- that is up to the calling code to handle
                        7    return rC;
                        8  end;
                        9  /

                       

                      Function created.

                       

                      SQL> declare
                        2    rC   sys_refcursor;
                        3    eRec emp%ROWTYPE;
                        4    eCnt number := 0;
                        5  begin
                        6    rC := fetch_employees(&deptno);
                        7    loop
                        8      fetch rC into eRec;
                        9      exit when rC%notfound;
                      10      eCnt := eCnt + 1;
                      11      dbms_output.put_line('Employee: ('||eRec.empno||') '||eRec.ename);
                      12    end loop;
                      13    dbms_output.put_line('Employee Records Found: '||eCnt);
                      14  end;
                      15  /

                      Enter value for deptno: 10
                      old   6:   rC := fetch_employees(&deptno);
                      new   6:   rC := fetch_employees(10);
                      Employee: (7782) CLARK
                      Employee: (7839) KING
                      Employee: (7934) MILLER
                      Employee Records Found: 3

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL> /
                      Enter value for deptno: 60
                      old   6:   rC := fetch_employees(&deptno);
                      new   6:   rC := fetch_employees(60);
                      Employee Records Found: 0

                       

                      PL/SQL procedure successfully completed.

                       

                      There is no purpose in having the function/procedure that creates the ref cursor do any check to see if there is data, it serves no purpose.

                       

                      Also, consider why you are trying to use a ref cursor in PL code anyway?  That's not really what they are intended for.

                      Read: PL/SQL 101 : Understanding Ref Cursors

                      • 9. Re: Ref cursor returns one row lesser while using fetch.
                        rp0428
                        Then is there any other ways to check whether data found or not in ref cursor and to return all the records?

                        No .

                         

                        Why do you care? The client is responsible for FETCHing rows using the CURSOR. If the client issues a FETCH and no row is returned then the result set is empty.

                        • 10. Re: Ref cursor returns one row lesser while using fetch.
                          2745693

                          Hi,

                           

                          Actually we have a stored procedure with first we are checking the count and then using ref cursor to fetch the data. And we dont have access to client using our stored procedure.

                           

                          Now we are planned to optimize the stored procedure by removing the count check query and to use only the ref cursor query to optimize the stored procedure.

                           

                          If we can check the count in the ref cursor itself means we can reduce 50% of DB hit.

                           

                          Please suggest any other way to have only one query.

                          • 11. Re: Ref cursor returns one row lesser while using fetch.
                            JustinCave

                            But that doesn't answer the fundamental question-- why does your procedure need to care whether the cursor will return an empty result set?

                             

                            Why not just eliminate the count and just return a cursor to the caller?  Was the caller somehow written to fail if it doesn't fetch at least one row from the cursor?  If so, that's a problem with the calling code that needs to be fixed in the calling code.  99% of the time, though, that would just cause the caller to iterate 0 times through a loop.

                             

                            I gave you options earlier if you're really insistent on this suboptimal design.  You'd need to materialize the result and return a cursor based on that materialized result.  I just wouldn't recommend it.

                             

                            Justin

                            • 12. Re: Ref cursor returns one row lesser while using fetch.
                              BluShadow

                              2745693 wrote:

                               

                              Hi,

                               

                              Actually we have a stored procedure with first we are checking the count and then using ref cursor to fetch the data. And we dont have access to client using our stored procedure.

                               

                              Now we are planned to optimize the stored procedure by removing the count check query and to use only the ref cursor query to optimize the stored procedure.

                               

                              If we can check the count in the ref cursor itself means we can reduce 50% of DB hit.

                               

                              Please suggest any other way to have only one query.

                               

                              Where do you get the idea that you can reduce 50% of DB hit?

                               

                              determining if a ref cursor is going to return data or not is simply determined at the time you fetch the first row from it.  It makes no difference to how much the database is hit.  The only difference is if you try and count the records first before creating the ref cursor to pass back, as that count will add additional work as well as being transactionally unsound.

                               

                              I've already shown you how you do it... just create the ref cursor and pass it back to the code that wants to use it.  It is then up to the code to fetch and use the data from the query, and, if necessary, determine if any data was returned by it (you can simply set a boolean variable to indicate that data was fetched after fetching the first row where refcursorvariable%NOTFOUND is false.  You don't need to check the count in the ref cursor itself, the information is available to you when you do the first fetch anyway.

                              • 13. Re: Ref cursor returns one row lesser while using fetch.
                                2745693

                                We just use the count query(joining 3 tables) to check whether any record is available or not. If no record is available then "No Record is found" Exception is thrown. else we will go for the ref cursor(joining 3 tables) to get the records.

                                 

                                Thus we are executing the same query twice. Please provide a solution to execute the query only once in the stored procedure. We dont have access to the client side code.

                                • 14. Re: Ref cursor returns one row lesser while using fetch.
                                  BluShadow

                                  I know what you're doing, and it's wrong.  So, you count the number of records and find the count to be zero, so you don't execute the ref cursor, but it's possible that just after you do the count, some data appears, so your logic is transactionally unsound.

                                   

                                  I already provided code to show how to do it, what's wrong with that?

                                  The whole principle of a ref cursor is that you open the cursor for the query and pass it to the client/application that requires the data.  If there is no data to be fetched that is the concern of the client/application code to handle, and is not the concern of the procedure/function that opened the cursor.  The procedure/function opening the cursor doesn't need to check if there is data or not; and it won't make things any quicker for the procedure/function to do such a check; opening the cursor isn't fetching any data itself, so there's no real difference in performance whether there is data or not.  Just open the cursor and pass it back.

                                   


                                  1 2 Previous Next