Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

How to run sql or pl/sql block using shell script?

I am running simple sql query using shell script and store the result in a variable. I ran the below code but result is not storing in the output. Please advise.

# Oracle Environment Variables
############################################################################
. $HOME/.profile

ORA_USER=user
ORA_PWD=password
############################################################################
# Local Variables
############################################################################
run_date=`date '+%Y%m%d'`
run_time=`date '+%H%M%S'`

shell_dir=$HOME/analytics/bin
sql_dir=$HOME/analytics/sql
tmp_dir=$HOME/analytics/tmp
log_dir=$HOME/analytics/log

progName=${0##*/}
sql_file=${sql_dir}/${progName%%.*}.sql
tmp_file1=${tmp_dir}/${progName%%.*}_${run_date}${run_time}.1.sql
tmp_file2=${tmp_dir}/${progName%%.*}_${run_date}${run_time}.2.sql
log_file=${log_dir}/${progName%%.*}_${run_date}${run_time}.log

start_time=`date +%Y-%m-%d-%H:%M:%S`

echo "Sample script is started at $start_time." >> ${log_file}

cat <<- EOF > $tmp_file1
set echo off;
set term off;
set heading off;
set feedback off;
whenever sqlerror exit;
select sysdate
from dual;
exit;
EOF

seg_snap_date=$(sqlplus ${ORA_USER}/${ORA_PWD}@DATABASE @$tmp_file1) >> ${log_file}

echo "Today's date $seg_snap_date" >> ${log_file}

cat $log_file

exit $?
0
IT_ETL
Asked:
IT_ETL
  • 2
  • 2
2 Solutions
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Use Method 1

var= `sqlplus -s username/pass@db @file_to_execute.sql`

Method 2:

sqlplus -L -silent username/pass@db |&

print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- " set sqlprompt '' "
print -p -- "select sysdate from dua;"

read -p ORACLE_DT
# YOU CAN USE UNIX COMMAND HERE

print -p exit ;

0
 
IT_ETLAuthor Commented:
I have modified above code as you suggested below. But still result from sql block is not stored in the variable.

cat <<- EOF > $tmp_file1
set echo off
set term off
set heading off
set feedback off
whenever sqlerror exit
select sysdate
from dual;
exit;
EOF

seg_snap_date=`sqlplus -s ${ORA_USER}/${ORA_PWD}@ADWPRD @$tmp_file1`
0
 
slightwv (䄆 Netminder) Commented:
Try this:
...
set term on
...
0
 
IT_ETLAuthor Commented:
Thanks slightwv! It worked.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.  did you go back and try your original code?  My shell scripting is rusty but it should work as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now