Shell Programming and Scripting

#!/bin/ksh

FILE="Table.txt"
FLAT_FILE="flat.txt"
sqlplus -s user/password@DB <<EOF

SET HEADING OFF
SET PAGESIZE 50000
SET COLSEP "|"
SET LINESIZE 200
SPOOL $FILE
select * from activity;
SPOOL OFF
EXIT
EOF
sed -e '/^\s*$/d' -e 's/^ *//' -e 's/ *| */|/g' $FILE > $FLAT_FILE

#!/bin/ksh

FILE="Table.txt"
FLAT_FILE="flat.txt"
sqlplus -s user/password@DB <<EOF > $FILE
SET HEADING OFF
SET PAGESIZE 0
SET COLSEP "|"
SET LINESIZE 32767
select * from activity;
EXIT;
EOF

sed -e 's/ *|/|/g' -e 's/| */|/g' $FILE > $FLAT_FILE

sed 's/ *| */|/g' "$FILE" > "$FLAT_FILE"

TABLE_NAME.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET COLSEP "|"
SET LINESIZE 32767
SET TRIMSPOOL ON
SPOOL $OUTPUT
SELECT * FROM TABLE_NAME;
SPOOL OFF
EXIT;
EOF

flat_file.ksh
#!/bin/ksh
 
db_connection="$DBUSER/$DBPWD@$ORA_SID"

SQL_FILE=$1
TABLE_NAME=`echo $SQL_FILE | awk -F"." '{print $1}'`

OUTPUT="${TABLE_NAME}.dat"
 
sqlplus -s $db_connection << EOF @TABLE_NAME.sql

 sqlplus -s $db_connection << EOF @TABLE_NAME.sql > /dev/null

SET NULL ''