procedure to list all table's data

From: Jianqin Zhou <zhou1963_at_263.net>
Date: Thu, 26 Aug 1999 07:38:25 GMT
Message-ID: <7q2qtg$91c$1@nnrp1.deja.com>

Hi;

       Thanks for any help.
       I want to write a procedure that list all table's data.

We can get the list of table names from user_tables. but with each table name, because it is a variable, we can not use :

select * from table_name;

here is the code

CREATE OR REPLACE procedure vtabledata (

v_table_to_view in varchar2 default null

)
is

cursor c_name is

	select distinct table_name from user_tables
	order by table_name;

v_table user_tables.table_name%type;

v_source user_source.name%type;
v_type user_source.type%type;

begin

open c_name;

htp.htmlOpen;
htp.headOpen;
htp.title('vtable');
htp.headClose;

htp.p('<BODY onload="window.focus()">');

if v_table_to_view is null then

  htp.print('<CENTER>');
  htp.print('Please select a table from the list and press "View Table".');
  htp.print('</CENTER>');

else

htp.print('<PRE>');

htp.print('<XMP>');

for x in (select * from v_table_to_view) loop

      for y in (  select
          column_name from user_tab_columns
            where table_name = v_table_to_view)
       loop
          htp.print('   ' || x.y.column_name);
       end loop;

end loop;

htp.print('</XMP>');

htp.print('</PRE>');

htp.bodyClose;

end if;

htp.line;

htp.print('<CENTER>');

htp.header(3,'View Table:');

htp.formOpen('vtables');

htp.formSelectOpen('v_table_to_view',null);   loop
    fetch c_name into v_table;
    exit when c_name%notfound;

    htp.prn('<OPTION ');
    if v_table = v_table_to_view then
      htp.prn('SELECTED ');
    else
      htp.prn(' ');
    end if;

      htp.prn('Value="' || v_table || '">');
      htp.print(v_table );

end loop;

htp.formSelectClose;

htp.formSubmit(NULL,'View Table');

htp.formClose;

--htp.nl;
--htp.nl;

htp.print('</CENTER>');

htp.bodyClose;

close c_name;

end;
/

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 26 1999 - 02:38:25 CDT