Store results in table

Shell Programming and Scripting
1

Hello everyone,

I have a shell script, which connects to the database and runs .sql file.
after executing of .sql file, i need to store the results in error table.
How can i achieve this one? could you please give your suggestions.
here is my code.

#!/bin/sh
#set -vx

SCHEMA_NAME=$1
LOG="/db01/apps/applvis/VIS/apps/apps_st/appl/xxhex/12.0.0/sql/test1.log_rundate_`date +%Y%m%d`.`date +%H%M%S`"

echo ' '>>$LOG
echo 'start sqlplus .sql '>>$LOG
date >>$LOG

sqlplus apps/apps@VIS @/db01/apps/applvis/VIS/apps/apps_st/appl/xxhex/12.0.0/sql/test1.sql ${SCHEMA_NAME} 1>>$LOG

EXIT_CODE=$?
if [$EXIT_CODE = 0]
then 
   echo "Program successfull" >>$LOG
   echo '<<< End of program ' >>$LOG
   date >>$LOG
else
   echo "ERROR Program did not successfully execute " >> $LOG
   echo "EXIT CODE = $EXIT_CODE"
   date >>$LOG
   exit 1
fi
echo '  ' >>$LOG
echo 'end sqlplus .sql ' >>$LOG
echo '  ' >>$LOG

Thanks,
Rami Reddy

2

Which database? I see sqlplus. Oracle has a facility to map files as tables, but very much for the DBA to set up and control. There is bcp and similar tools for SQL Server and Sybase, DB2 has a loader and you can always sed the output into insert statements. I even wrote a UDF that allowed you, with a SP, to take in a file stream, flat or pipe, as if it was a table, with a single insert-select. http://stackoverflow.com/questions/7306405/best-way-to-bulk-insert-data-into-oracle-database

1 Like