8 Replies Latest reply: Aug 29, 2013 6:51 AM by Billy~Verreynne RSS

    refcursor

    lattu

      how to count the rows in refcursor?

        • 1. Re: refcursor
          BCV

          Not sure , Are u looking for get the number of rows from table like this...

           


          SQL> CREATE OR REPLACE PROCEDURE my_pro (ov_count OUT sys_refcursor)
            2  AS
            3  BEGIN
            4     OPEN ov_count FOR
            5        SELECT COUNT (*)
            6          FROM emp;
            7  END;
            8  /

          Procedure created.


          SQL> var x refcursor;
          SQL> execute my_pro(:x);

          PL/SQL procedure successfully completed.

          SQL> print x;

            COUNT(*)
          ----------
                  14

          • 2. Re: refcursor
            Karthick_Arp

            Just fetch them all and at the end your_refcursor%ROWCOUNT will give you the count. But if you want to know the count without fetching from it, ITS NOT POSSIBLE!!

             

            That is because the OPEN operation of a cursor does not execute the instructions in the cursor work area, only FETCH does it. So you need to FETCH all the rows to know the total number of rows.

            • 3. Re: refcursor
              lattu

              create or replace(...,p_ref_cur out sys_refcursor)as

              ---

              ----------

              l_str := '.....'

              execute immediate l_str using out p_ref_cur

               

              dbms_output.put_line(p_ref_cur%rowcount);

              ---------------

              end;

               

              o/p:- 0

              but records fetched morethan 1

              • 4. Re: refcursor
                lattu

                thank u..

                • 5. Re: refcursor
                  Karthick_Arp

                  > execute immediate l_str using out p_ref_cur

                   

                  This is a incorrect syntax. You cant use refcursor like this.

                  • 6. Re: refcursor
                    BluShadow

                    I think you need to read this:

                     

                    PL/SQL 101 : Understanding Ref Cursors

                    • 7. Re: refcursor
                      BCV

                      You can't Use Refcursor in Execute Immediate Statement,

                       

                      Execute As shown if you dont want execute in Sql*plus,

                       

                      DECLARE
                         rc          sys_refcursor;
                         row_count   number;
                      BEGIN
                         my_pro (rc);

                         LOOP
                            FETCH rc
                             INTO row_count;

                            EXIT WHEN rc%NOTFOUND;
                            dbms_output.put_line (row_count);
                         END LOOP;

                         CLOSE rc;
                      END;

                       

                       


                      • 8. Re: refcursor
                        Billy~Verreynne

                        lattu wrote:

                         

                        how to count the rows in refcursor?

                         

                        A ref cursor is an interface (or pointer) to a SQL cursor in the database's memory.

                         

                        A SQL cursor is a program. Not a result set (set of data results in memory).

                         

                        Question. How many rows do the SQL cursor return (SQL cursor program output)?

                         

                        Answer. The caller need to consume (fetch) all the output (until no more data rows returned by cursor) to determine how many rows were returned by the cursor.