Help on Oracle insert from shell script

UNIX for Beginners Q & A
1

Hi All

I am trying to create a shell script to insert in to oracle table
so far insert happens with out an issue,

but i cant pass message variable to sqlplus script insert. i have highlighted the variable in red.

Please help.
thanks in advance.

#!/bin/sh
df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;
do
 # echo $output
  usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1  )
  partition=$(echo $output | awk '{ print $2 }' )
  if [ $usep -ge 70 ]; then
       message=$("Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)")
        echo "$message"

export ORACLE_SID=sandiqa
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_BIN=${ORACLE_HOME}/bin
export ORACLE_TERM=vt220
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export PATH=$PATH:${ORACLE_BIN}
sqlplus -S "abc/123" << _EOF_

set heading off;


INSERT INTO ALERT_EMAIL(PUID, ID, TxnID, Recipients,Subject,Body,IsNew,CreationDate,LastModifiedBy,IsSMS)
VALUES(ALERT_EMAIL_PUID_SEQ.NEXTVAL, '111111','','','$message','body',1, SYSDATE, 'axienta',1);

exit;
_EOF_

  
  fi
done
2

This may be because you are using single quotes in the here document. Try double quotes.

What do you see it doing if you add a set -x on the second line? The trace should show the commands being executed and some of the values being used.

Robin

1 Like
3

I also noticed the command substitution around the message declaration ( message=$(...) ), but it's SH and the OS isn't mentioned, so I don't know if that's significant.

1 Like
4

It is significant.

The subshell call message=$("this is my message $var whatever $othervar") will fail with command not found error.

There is also an issue with subshell call, which will not work in such form in legacy shells, so `command` is to be used in those.
Subshell expects a valid command not $("arbitrary text") or `"arbitrary text"`

For instance :

$ cat mess.sh 
message=$("This is my $(date +%s)")
echo $message
$./mess.sh
./mess.sh: line 1: This is my 1515156364: command not found

Try :

message="Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)"
echo "$message"
.. further code processing / inserting $message
cat <<EOF
'$message'
EOF

Notice that no subshells are required and your program should produce results.

Have you considered writing an sql file and passing message as a parameter ?

Something in the lines of :

sqlplus user/passs @insertscript.sql "$message"
# where in your insertscript.sql you have :
INSERT INTO ALERT_EMAIL(PUID, ID, TxnID, Recipients,Subject,Body,IsNew,CreationDate,LastModifiedBy,IsSMS)
VALUES(ALERT_EMAIL_PUID_SEQ.NEXTVAL, '111111','','','&1,'body',1, SYSDATE, 'axienta',1);
/

Hope that clears things out
Regards
Peasant.

1 Like