Return value from sql script to shell script

Question

I have shell script that calls the following sql script:

     INSERT INTO SEMANTIC.COUNT_STATISTICS (...);
     UPDATE SEMANTIC.COUNT_STATISTICS 
     SET PRNCT_CHANGE = 1.1;


  --want to store result of this bellow select statement in model_count variable

      select PRNCT_CHANGE
      FROM SEMANTIC.COUNT_STATISTICS
      WHERE model = '&MY_MODEL'
      AND NEW_DATE = (
                      select max(NEW_DATE)
                      from SEMANTIC.COUNT_STATISTICS
                      where MODEL = '&MY_MODEL'
                     );

Now, how do I return this PERCENTAGE_NUMBER variable back to my shell script?

My shell script is as follows:

#!/bin/bash
#
# setup oracle, java, and d2rq environment
. /etc/profile.d/oracle.sh
. /etc/profile.d/java.sh
. /etc/profile.d/d2rq.sh

cd /opt/D2RQ

model_count=$(sqlplus user/pass @count.sql 'MODEL')

if ["$model_count" > 0]; then
   echo "percentage count is positive"
else
   echo "its negative"

I would like for that last SELECT statement result to be stored into my model_count variable in shell script.

Anyone knows why is not working?

Bjarte Brandt · Accepted Answer · 2013-07-18 19:10:53Z

15

A bash example with the use of a bash-function (note! database OS-authentication "/")

#!/bin/bash

get_count () {
    sqlplus -s / <<!
    set heading off
    set feedback off
    set pages 0
    select count(*) from all_objects where object_type = '$1'; 
!
}

count=$(get_count $1)

echo $count

if [ "$count" -gt 0 ]; then
    echo "is greater than zero"
else
    echo "is less or equal to zero"
fi


~/tmp/ $ ./count.sh INDEX
2922
is greater than zero
~/tmp/ $ ./count.sh TABLE
1911
is greater than zero
~/tmp/ $ ./count.sh FUNCTION
226
is greater than zero
~/tmp/ $ ./count.sh "SUPEROBJECT"
0
is less or equal to zero

answered Jul 18, 2013 at 19:10

Bjarte Brandt

4,5212 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Angelina Over a year ago

Thank you @Bjarte Brandt so much for your help.

Alex Pi Over a year ago

Very clean and helpful answer.

Glenn Over a year ago

I need to quote the parameter e.g. count=$(get_count "$1") if it had a space or some other special character.

Angelina · Accepted Answer · 2013-07-18 20:02:25Z

3

What I actually did is I separated those 2 queires and called them separatelly in my shell script:

sqlplus -S user/pass << EOF
whenever sqlerror exit 1;
set echo on
@/opt/D2RQ/model_count.sql '$MODEL'  <--model_count.sql still has those INSERT & UPDATE statements
exit;
EOF

model_count=`sqlplus -S user/pass << EOF
SELECT PRNCT_CHANGE
 FROM COUNT_STATISTICS
WHERE model = '$MODEL'
AND NEW_DATE = (
                select max(NEW_DATE)
                from COUNT_STATISTICS
                where MODEL = '$MODEL'
               );
exit;
EOF`


if [ $model_count >= 0 ]; then
    echo "$model_count"
else
         echo "'$MODEL' is negative " | mail -s "scripts issues" -c [email protected]
fi

answered Jul 18, 2013 at 20:02

Angelina

2,28512 gold badges47 silver badges92 bronze badges

2 Comments

sjd Over a year ago

what if there are more than 1 column i want from oracle? for eg : A tabular data of 5 columns and 10 rows

Gopi Over a year ago

If there are more than one column then do :: sqlplus / @myscript.sql | read var1 var2 var3 and more rows then sqlplus / @myscript.sql | while read var1 var2 var3 do <more shell stuff here> done Reference

Collectives™ on Stack Overflow

Return value from sql script to shell script

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related