Help on Oracle insert from shell script

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Help on Oracle insert from shell script
# 1  
Old 01-04-2018
Wrench Help on Oracle insert from shell script

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.
Code:
#!/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


Last edited by Scott; 01-05-2018 at 03:39 AM.. Reason: Please use code tags
# 2  
Old 01-05-2018
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

Last edited by rbatte1; 01-05-2018 at 07:30 AM.. Reason: Added line about trace.
This User Gave Thanks to rbatte1 For This Post:
# 3  
Old 01-05-2018
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.
This User Gave Thanks to Scott For This Post:
# 4  
Old 01-05-2018
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 :
Code:
$ 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 :
Code:
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 :
Code:
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.
This User Gave Thanks to Peasant For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Script to ingest a csv, validate data and insert into Oracle

Hi all i would appreciate your help... I am looking for a set of unix commands which i can use to 1) ingest a csv file with a known format 2) validate the filename 3) validate the data/datatypes 4) Insert into an oracle db Can you help get me started? yogz888 (1 Reply)
Discussion started by: yogz888
1 Replies

2. UNIX for Dummies Questions & Answers

Insert text into a file using shell script

Hi, I need to insert "Hello World" text into a file called hai.txt using shell scripting. Kindly help me. For eg: If I open the file hai.txt by giving linux command cat hai.txt, the content of the file should have the text Hello World in it. Thanks (5 Replies)
Discussion started by: karthick nath
5 Replies

3. Shell Programming and Scripting

Shell Script to insert text after Tag

Hello, I'm doing an Shell Script to insert a text on XML file, i tried sed, awk, perl... i'm doing something wrong, please help me :) well, the script is a bit large, i get some infos on script before 'run' this part to insert the text on XML... domobile() { let i++ echo ... (1 Reply)
Discussion started by: tassomanoel
1 Replies

4. Shell Programming and Scripting

Insert script result into Oracle Table

Hi All, I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table. Eg: insert into tbl_daily_status values(STAT,ENDTIME); Please help me on this. #!/bin/ksh joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det" for i in $joblist do... (8 Replies)
Discussion started by: vichuelaa
8 Replies

5. Solaris

Execution problem in shell script while insert into DB

Hi, am facing some problem while inserting a record into a script Please find script below. `sqlplus -s asdf/asdf123 <<eof! set feedback off; set heading off; set verify off; insert into... (2 Replies)
Discussion started by: senkerth
2 Replies

6. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

7. Shell Programming and Scripting

shell script - insert oracle

Hi Frnds, in shell script I have one problem while inserting into oracle table . my script #! /usr/bin/sh while read record do echo $record X=`sqlplus -s STN/errrmddb20@MAHFDR <<eof insert into STN.STN_ERROR_TABLE values($record); eof`... (12 Replies)
Discussion started by: Gopal_Engg
12 Replies

8. Shell Programming and Scripting

Insert C code in shell script

Hi, Anybody know on how to insert C code in shell script. I am writing BLOB data to a database table in C but I don't know on how to insert the C code in shell script. Thanks in advance. (1 Reply)
Discussion started by: badbunny9316
1 Replies

9. UNIX for Advanced & Expert Users

unix script for update or insert records from a file to a oracle table

Hi, I have delimited file(|). Sample data: 1|name|50009|DS24|0|12 2|name|30009|DS24|0|13 3|name|20409|DS24|0|14 4|name|20009|DS24|0|15 5|name|10009|DS24|0|16 I want to load this data into a oracle table (update and insert) Please help me the commands and also... (1 Reply)
Discussion started by: unihp1
1 Replies

10. Shell Programming and Scripting

Shell Script: want to insert values in database when update script runs

Hi , I am new to linux and also also to shell scripting. I have one shell script which unpacks .tgz file and install software on machine. When this script runs I want to insert id,filename,description(which will be in readme file),log(which will be in log file) and name of unpacked folder... (1 Reply)
Discussion started by: ring
1 Replies
Login or Register to Ask a Question