Oracle Plsql REF CURSOR

Working with REF CURSOR
ref cursor example



ref cursor are used only for select



from scott

set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname user_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select table_name from user_tables ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_tabname;

open c_ref2 for 'select count(1) from "'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/



Table_name Row_Counts
========== =========
DEPT 4
BONUS 0
SALGRADE 5
MYEMP 2
EMP 14
ANUJ_BIG_TABLE 9999
ANUJ_BIG_TABLE1 9999
AA 14
ANUJ10 0
MYEMP_WORK 0
MYEMP_WORK 0
.
============end==================

********************************************************************************


from sys



set serveroutput on
declare

type r_c is ref cursor;
c_ref r_c;
c_ref2 r_c;
v_tabname dba_tables.table_name%TYPE;
v_owner dba_tables.table_name%TYPE;
v_cnt number(10);

begin

open c_ref for select owner,table_name from dba_tables where owner='SCOTT' ;

dbms_output.put_line('Table_name Row_Counts ');

dbms_output.put_line('========== ========= ');

loop

fetch c_ref into v_owner,v_tabname;

open c_ref2 for 'select count(1) from "'||v_owner||'"."'||v_tabname||'"';

fetch c_ref2 into v_cnt;

close c_ref2;

dbms_output.put_line(rpad(v_tabname,30,' ')||v_cnt);

exit when c_ref%NOTFOUND;

end loop;

close c_ref;

dbms_output.put_line('. ');
dbms_output.put_line('============end================== ');

end;
/



===================================

for testing


begin
dbms_output.put_line('select count(1) from "'||'v_owner'||'"."'||'v_tabname'||'"') ;
end;
/