CODEWITHSUNDEEP
sqlplsqloracle10gcursororacle-sqldeveloper
tacos_tacos_tacos
tacos_tacos_tacos

Reputation: 10585

Determine how long it takes for Oracle to fill a cursor

I am trying to assess the performance of a stored procedure and I am trying to run it in a SQL Developer worksheet. The basic idea is:

DECLARE
some_cursor SYS_REFCURSOR;
...
BEGIN

dbms_output.put_line('BEGIN: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-YYYY HH12:MI:SSxFF TZH:TZM'));

OPEN some_cursor FOR (...);

dbms_output.put_line('END: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-YYYY HH12:MI:SSxFF TZH:TZM'));

END;

Both timestamps are the same; I am assuming this because calling OPEN some_cursor FOR only kicks off the selection.

Is there a way to block the original thread until the cursor is filled?

Upvotes: 0

Views: 118

Answers (1)

ajmalmhd04
ajmalmhd04

Reputation: 2602

From your question title, "...to fill a cursor",

cursor for oracle can be meant as a pointer,

From the definition: A cursor is a handle or name for a private SQL area—an area in memory that holds a parsed statement and other information for processing.

This will explain better: OraFAQ

With a slight modification: from: here

set serveroutput on
DECLARE 
  l_cur SYS_REFCURSOR; 
  l_col VARCHAR2 (10); 
BEGIN 


  OPEN l_cur FOR 
  SELECT 'Hi there'||LEVEL col 
      from dual 
      where 1 = 1
      CONNECT BY LEVEL <= 20; 

  DBMS_OUTPUT.PUT_LINE('Opened cursor'); 

dbms_output.put_line('BEGIN: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-YYYY HH12:MI:SSxFF TZH:TZM'));
  <<cursor_loop>>

  loop

    fetch l_cur into l_col; 
    DBMS_OUTPUT.PUT_LINE('Fetched from cursor'); 

    EXIT cursor_loop WHEN l_cur%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Process data fetched from cursor');    
  end loop;  -- cursor_loop
dbms_output.put_line('END: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-YYYY HH12:MI:SSxFF TZH:TZM'));
  CLOSE l_cur; 
  dbms_output.put_line('Closed cursor');
end; 
/

Upvotes: 2

Related Questions