How to use sql result stored on shell variable?

season0891發表於2011-10-21
SQL

I know now how to store the result of sql procédure in shell variable

testvar=$(sqlplus foo/bar @test.sql)

my test.sql return a list of integer that i would use in another select calling by my shell. The aim of this procedure is to show the progress avancement of my treatment like

x=0;
while x <testvar.size
sqlplus foo/bar @test2.sql $testvar.x
print (x*100/testvar.size + "%")
end while

(i dont know shell programming but it's easiest than my current problem...

sql oracle bash shell
link|improve this question


Which shell are you using ? – Kevin Burton Sep 12 at 15:23

I'm using bash script. and I don't even know how to make it but i have people around me to help me on that but no on this problem – Baltius Sep 12 at 15:30

I am not familiar with the output of sqlplus. Could you paste some sample output when you run it with your script. as input? If so I can tell you how to parse it form. bash. – Sorpigal Sep 12 at 16:14
feedback

1 Answer

up vote 2 down vote accepted

If the output of sqlplus is literally "A list of integers" as you describe, then this is easy enough.

SOMETHING=5

while -r read n ; do
    if [ $n -ge $SOMETHING ] ; then
        break
    fi
    sqlplus foo/bar @test2.sql "$n"
    printf '%.2f%%\n' $(expr "$n" \* 100 / "$SOMETHING")
done < sqlplus foo/bar @test.sql)

Presuming that "A list of integers" really means "A newline separated list of integers"

It's not clear to me what $testvar.x and testvar.size are supposed to represent, so I made some guesses and left the variable $SOMETHING to stand in for whatever testvar.size is supposed to be. If you want the total number of integers in the list from sqlplus then that would be different, and done like this:

intlist=($(sqlplus foo/bar @test.sql))

for n in "${intlist[@]}"; do
    if [ $n -ge ${#intlist[@]} ] ; then
        break
    fi
    sqlplus foo/bar @test2.sql "$n"
    printf '%.2f%%\n' $(expr "$n" \* 100 / ${#intlist[@]})
done < sqlplus foo/bar @test.sql)

If your output is more complex some additional filtering will need to be done up front.

link|improve this answer

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-709489/，如需轉載，請註明出處，否則將追究法律責任。

相關文章