1
#!/bin/sh

echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null
SET SERVEROUTPUT ON
SET FEED OFF
spool ech.txt create
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
spool off
EOF


if [ $? -ne 0 ]
then
   echo " ech.sh sql   Error"
   exit 1
else
   echo " ech.sh  Successful"
fi

I am getting an SQL error message. Any ideas?

  • Are you doing this from a client? As far as I know, DBMS_OUTPUT.PUT_LINE sends output to a file on the server, not on the client. – Vérace Feb 4 '15 at 16:43
  • Is this the exact text of your script? As posted, it will not run. For example, !/bin/sh should be #!/bin/sh. There are other syntax errors as well. Can you post the full script that runs and gives you the error message? – ansible Feb 4 '15 at 16:52
  • Remove the /dev/null redirect so you can see exactly what's going on, then edit the question with the information – Philᵀᴹ Feb 4 '15 at 16:52
  • Edit made by @Mat to fix the script works for me. – ansible Feb 4 '15 at 16:55
  • (I didn't fix anything in the script, @ansible. It's just markdown that likes to eat stuff that could potentially look like html outside of code blocks. Adding whitespace was enough.) – Mat Feb 4 '15 at 16:59
2

I took some code snippets from a backup script that I use. You can redirect all output with the exec command, then redirect it back. You can also surround everything with braces and redirect that to a log file.

Personally I think bash is a better shell to program in, if you don't have bash ksh works too.

#!/bin/bash

export MY_LOG_FILE=my_log_file.log

exec >${MY_LOG_FILE}
exec 2>&1

export ORACLE_BASE=/home/oracle/admin
export ORACLE_HOME=/software/oracle/product/11.2.0
export TNS_ADMIN=${ORACLE_HOME}/network/admin
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export ORACLE_SID=mysid


echo "Started at" `date +%Y/%m/%d" "%H:%M:%S`

sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
BEGIN
   DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
EOF

exec 1>&-
exec 2>&-
exec >/dev/tty
exec 2>&1

if [ `egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}` -ne 0 ]
then
   echo " ech.sh sql   Error"
   egrep -n "ORA-|RMAN-" ${MY_LOG_FILE}
   exit 1
else
   echo " ech.sh  Successful"
fi

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.