Solved

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

Posted on 2011-09-20
5
572 Views
Last Modified: 2012-05-12
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
Comment
Question by:IT_ETL
  • 2
  • 2
5 Comments
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 200 total points
ID: 36570253
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
 

Author Comment

by:IT_ETL
ID: 36571777
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 36573464
Try this:
...
set term on
...
0
 

Author Comment

by:IT_ETL
ID: 36574735
Thanks slightwv! It worked.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36574763
Glad to help.  did you go back and try your original code?  My shell scripting is rusty but it should work as well.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now