Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-20
5
Medium Priority
?
592 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 800 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 1200 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

719 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