Re: procedure to list all table's data

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 26 Aug 1999 10:23:01 +0200
Message-ID: <7q2tib$98l$1@oceanite.cybercable.fr>

Here's a little script to dump all the tables of a user. It doesn't work for table with RAW, LONG RAW, ... columns. It's a bit simple but you can enhance it.

Set heading off
Set feedback off
Set pagesize 10000
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 1000
Set long 500
Set maxdata 32767
Set arraysize 5
Set sqlterminator Off
Spool t
Prompt Set heading on
Select 'Prompt ### '||table_name||' ### Prompt
Select * from <your user>.'||table_name||'; Prompt
Prompt'
from all_tables where owner='<your user>' order by table_name
/
Spool off
Set sqlterminator On
Spool dump
@t.LST
Spool off
Set termout on
Set heading on
Set termout on
Set linesize 80

Jianqin Zhou a écrit dans le message <7q2qtg$91c$1_at_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 - 03:23:01 CDT