Hi All,

I want to write a unix script to convert a table to a flat file with a pipe delimiter. I have 25 tables and i want to write a single shell script so that i can convert the 25 tables into 25 flat files.

i wrote the follwoing for a single table to single flat file. can anyone make this file so that it can take 25 tables to 25 flat files(differnt)

SCRIPTOUTPUT=$TMPPATH/$test.txt
cat /dev/null>$SCRIPTOUTPUT
sqlplus -s / << EOF
-- Blank Line --
-- Blank Line --
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
spool $SCRIPTOUTPUT;
set heading off
set echo off
select col1 '|' col2 '|' from table1;
exit;
EOF

thanks
kiran

How about using a for loop? Something along the lines of:

Something along those lines is what we use at work (not there atm so can't check complete syntax/script for ya).

// this is very similar to previous solution, jsut using WHILE loop

you may create a file (table_file) that has entries for all your 25 tables
and then use it while loop.



while read aTable;
do
sqlplus -s / << EOF
-- Blank Line --
-- Blank Line --
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
spool $SCRIPTOUTPUT;
set heading off
set echo off
select col1 '|' col2 '|' from $aTable;
exit;
EOF
done < table_file;