Solved

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

Posted on 2011-09-20
5
591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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 77

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

632 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