declare 

temp SYS_REFCURSOR;

n  number;

begin 

 -- open sys_refcursor for select statement

open  temp for select level from dual connect by level < 4 ;


loop -- cursor is in open state , cursor attributes are applicable

  fetch temp  into n; -- fetch current record from temp assign to n 

    dbms_output.put_line('n'||n); 

  exit when temp%notfound;-- loop through the curesor until all the dataset exhausts

end loop;

  if  temp%found then 

    dbms_output.put_line('tempfound'||temp%rowcount);

  end if;

  

  if  temp%notfound then 

  dbms_output.put_line('tempnotfound'||temp%rowcount);

  end if;

 

  if  temp%isopen then 

  dbms_output.put_line('tempisopen'||temp%rowcount);

  end if;

  

  close temp;

  

end;

Comments

Post a Comment

Popular posts from this blog

 declare   n  number; begin     select 1 into n from dual; if sql%found then -- if rows returned dbms_output.put_line('implicit1'||sql%rowcount);  end if; if sql%notfound then --if rows not returned dbms_output.put_line('implicit2'||sql%rowcount);  else  dbms_output.put_line('dump sql%notfound ' );  end if; if sql%isopen then --if cursor is in open state dbms_output.put_line('implicit3'||sql%rowcount);  else  dbms_output.put_line('dump sql%isopen' );  end if; end;
Read more
 declare   n  number; begin  -- Implicit cursor is opened , counter i copied the datatype as %rowtype fetch can't be used. for i in  (select level from dual connect by level < 4 ) loop  n :=i.level;   --fetch i into n;-- ORA-06550: line 10, column 9: PLS-00456: item 'I' is not a cursor     dbms_output.put_line('n'||n);    end loop; end;
Read more