Home » RDBMS Server » Server Administration » Re: make table name dynamic
Re: make table name dynamic [message #370878] Mon, 28 February 2000 19:02
Naveen Kumar V
Messages: 16
Registered: February 2000
Junior Member
Here is the modified code.

DECLARE
m_owner all_tables.owner%type;
m_table_name all_tables.table_name%type;
cursor c1 is
select owner, table_name from all_tables where owner = 'SCOTT';
my_cursor c1%rowtype;
num_rows integer:=0;
i number;
j number;
stmt varchar2(200);
kount number;
BEGIN
for c1rec in c1
loop
stmt := 'select count(*) from '||c1rec.table_name;
i := dbms_sql.open_cursor;
dbms_sql.parse(i,stmt,1);
j := dbms_sql.execute(i);
dbms_sql.define_column(i,1,kount);
if dbms_sql.fetch_rows(i) > 0 then
dbms_sql.column_value(i,1,kount);
dbms_output.put_line(c1rec.table_name||'---------->'||kount);
else
exit;
end if;
dbms_sql.close_cursor(i);
end loop;
end;

if you want to use dba_tables that's fine.
Previous Topic: Re: make table name dynamic
Next Topic: Locks on Tables
Goto Forum:
  


Current Time: Thu Oct 22 04:32:45 CDT 2020