Re: Write table columns to an external data file

From: daven <davenport_at_hotmail.com>
Date: 25 Jul 2001 11:41:13 -0500
Message-ID: <3b5ef60f$0$85148$45beb828@newscene.com>

I got a script to do that from the Ask Tom web page and made some changes to increase speed and some added features.

The nice thing is that you call it giving just the logon (u can also get it from profile is its there by exporting the logon to the script), the file you want it exported it and the names of the tables or views you want to export to a flat file.

This is nice if you need to export a table that has dozens or even 100s of columns all you need to do is just give it the name of the table/views.

#!/bin/sh

if [ "$1" = "" ]
then

        cat << EOF
usage:          flatfile.sh spool_file_name  user/password [tables|views]

example:        flatfile /land5/spoolout.txt    big123/324 T003_prem_sales

description: writes the resultset from a Select to a Unix file delimited by tabs.

EOF
        exit
fi

FL=$1

PW = $2

shift

for X in $*
do
sqlplus -s $PW << EOF > ./flatfile$$.sql

set     wrap off
set     feedback off
set     pagesize 0

set maxdata 60000
set arraysize 3000
set verify off

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT prompt select
select lower(column_name)||'||chr(9)||' 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 pagesize 0
set maxdata 60000
set arraysize 3000
set termout off
set heading off
set linesize 5000
set trimspool on
set wrap off

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE spool $FL
start ./flatfile$$.sql
spool off
exit
EOF
san=$?
if [ $san -ne 0 ]
then
echo 'exit code' $san
echo 'failure'
exit 1
else
rm ./flatfile$$.sql
fi
done

In article <J0m77.8406$A4.825440_at_news20.bellglobal.com>, "news.bellglobal.com" <azzeddine.saadi_at_pcsinnovations.com> wrote:
>Try this:
>
>SQL> spool filename.ext
>SQL> select name, address_1, address_2, city, state, amt
> from customer;
>SQL> spool off
>
>
>"E. Paul" <pwinston99_at_gsm.uci.edu> wrote in message
>news:3B5DE550.923C74C8_at_gsm.uci.edu...
>> How would I write the result of a select to an external data file?
>>
>> something like this
>>
>> select name, address_1, address_2, city, state, amt
>> from customer
>>
>> "to an external ouput file"
>>
>>
>> Thanks in advance
>>
>
>
Received on Wed Jul 25 2001 - 11:41:13 CDT