how to execute multiple .sql scripts from within a shell script using sqlplus

Linux and Unix-Like UNIX and Linux Applications
1

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks

any help would be appreciated
:wall:

2

This will work for you.

shell script:

$cat dbms_output.ksh
#!/bin/ksh

ORA_DB=mydb
ORA_USER=myuser
ORA_PWD=$(cat ~/.pwd/.${ORA_USER}_${ORA_DB})

SQLFILE=~/scripts/sql/dbms_output.sql
LOGFILE=~/scripts/logs/dbms_output.log

sqlplus -s /nolog <<-EOF >> ${LOGFILE}
   connect $ORA_USER/$ORA_PWD@$ORA_DB

   set serveroutput on format wrapped
   set pages 0 lines 300 trimout on trimspool on
   set echo off feedback off sqlprompt ''

   @$SQLFILE

   exit

EOF

exit

sql script:

$ cat ../sql/dbms_output.sql 
begin

  dbms_output.put_line(to_char(sysdate, 'yyyymmdd hh24:mi')||'|'||'Testing dbms_output to a logfile');

end;
/

results:

$ ./dbms_output.ksh 
$ cat ../logs/dbms_output.log
20110514 03:46|Testing dbms_output to a logfile