Re: Dumping Formatted Tables

A copy of this was sent to "Ashok Rathi" <arathi_at_impresse.com> (if that email address didn't require changing) On Tue, 22 Sep 1998 12:22:36 -0700, you wrote:

>Are there any PL/SQL scripts or some utility to generate the table contents
>in the following format:
>
>field1,field2,field3..........
>
>The script should automatically take a look at the table fields etc.
>
>Tx.
>
>-- Ashok
>

in unix this works great from the command line:

#!/bin/sh

if [ "$1" = "" ]
then

    cat << EOF
usage: flat un/pw [tables|views]

example: flat scott/tiger emp dept

description: Select over standard out all rows of table or view with

        columns delimited by tabs.
EOF
    exit
fi

PW=$1
shift

for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off
prompt select
select lower(column_name)||'||'',''||'
from user_tab_columns
where table_name = upper('$X') and

    column_id != (select max(column_id) from user_tab_columns where

             table_name = upper('$X'))
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and

    column_id = (select max(column_id) from user_tab_columns where

             table_name = upper('$X'))
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.