4 Replies Latest reply: Jan 10, 2014 9:33 AM by rp0428 RSS

    Logging Ref Cursor Execution

    Art T

      I am trying to log the timing of executing a procedure that returns a ref cursor.

      My code looks like this:

       

      begin

      v_log_id := 1;

      pr_log ('start',v_log_id,sysadate);

      open refcur1 for

      select ...;

      pr_log ('end',v_log_id,sysdate);

      end;

       

      My log shows start and end at most 1 sec apart. Looks like the end log is executed when the cursor is opened or maybe when the first row is fetched.

      How do I log the time it takes to return all rows?

       

      Thanks

      -Art

        • 1. Re: Logging Ref Cursor Execution
          Ramin Hashimzadeh

          What is the purpose of this? What are you trying to solve? When cursor opened it doesn't fetch any records.

           

          ----

          Ramin Hashimzade

          • 2. Re: Logging Ref Cursor Execution
            odie_63

            How do I log the time it takes to return all rows?

            Obviously, after all rows have been fetched.

             

            As Ramin said, opening a cursor doesn't fetch anything. You have to perform your logging in the code that consumes the cursor.

            • 4. Re: Logging Ref Cursor Execution
              rp0428
              I am trying to log the timing of executing a procedure that returns a ref cursor.

              That is EXACTLY what your code is already doing.

              How do I log the time it takes to return all rows?

              Your procedure does NOT return any rows.

               

              1. It logs an entry that the proc started

              2. It opens a ref cursor

              3. it logs an entry that the proc ended

              4. it exits and returns the ref cursor.

               

              That's all that it does. Your procedure is finished - it has NO connection with or knowledge of who, or what, called it or what that caller does, if anything, with that ref cursor.

               

              The caller of that procedure can:

               

              1. do nothing at all with the ref cursor

              2. pass the ref cursor to some other process (which may, or may not, do something with it)

              3. FETCH one or more rows

              4. FETCH all rows

               

              Since that 'caller' could be a 'client' using sql*plus to do the fetch there isn't anything you can code on the back end to determine the time to 'return all rows'.