Shell Programming and Scripting

Hi,

I m trying to connect oracle database from inside while loop.
I m trying to put the output of sql query in a flat file.
Anyone please help me out.

cat $FILE_NAME | \
while read da_name
do

$ORACLE_HOME/bin/sqlplus -s user_name/password@instance << EOF >> $OUTPUT_FILE

select global_name from global_name;

EOF
done

Getting output as output.

thanks in advance

###############################

while read da_name
do


RETVAL=`sqlplus -l user_name/password@instance <<EOF

insert into ODS_T_CFS_DUMP(FILE_NAME, REGION_ID, SDP_ID, SEQUENCE_ID, TOTAL_RECORDS) values ('$file','$region_id','$sdp_id','$sequence_id', '$sub_records');

EXIT
EOF`

done


Here you can get the SQL-Query output in the RETVAL Variable.
You can use this $RETVAL valve anywhere in the script.

#####################################

Hi,

You Can Use Following shell Script instead for database connection and pulling all the details into flat file.

echo "set time off;" > cpled_wkrep.sql
echo "set timing off;" >> cpled_wkrep.sql
echo "set pagesize 0;" >> cpled_wkrep.sql
echo "set linesize 1300;" >> cpled_wkrep.sql
echo "set trimspool on;" >> cpled_wkrep.sql
echo "set heading off;" >> cpled_wkrep.sql
echo "set echo off;" >> cpled_wkrep.sql
echo "set verify off;" >> cpled_wkrep.sql
echo "set termout off;" >> cpled_wkrep.sql
echo "set feedback off;" >> cpled_wkrep.sql
echo "" >> cpled_wkrep.sql
echo "spool VVMFNAMES;" >> cpled_wkrep.sql
echo "" >> cpled_wkrep.sql
echo "select V_VMF_CODE||'|'||V_VMF_NAME||'|'||V_VMF_BUS_STAT||'|' from V_VMF_CLASS where V_VM
F_BUS_STAT = 'SUPPORTED' order by V_VMF_CODE;" >> cpled_wkrep.sql
echo "" >> cpled_wkrep.sql
echo "" >> cpled_wkrep.sql
echo "spool off;" >> cpled_wkrep.sql
echo "exit;" >> cpled_wkrep.sql

sqlplus username/password@instance @cpled_wkrep.sql
rm -rf cpled_wkrep.sql


OutPut Will be in the file VVMFNAMES.lst

thanks for the help