SQLplus in Shell scripts

Shell Programming and Scripting
1

How to execute a query which is stored in a variable.
Say for example :

v_source_query=�select count(*) from emp�
v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF | tr '\n' ' '

                    set feed off
                    set pagesize 0
                    set head off
                    set linesize 2000

$v_source_query (I want to execute the query here)

EOF
`

echo $v_source_value

Any help is appreciated.

Trupti.

2

variables are case sensitive. Remove | tr '\n' ' '.
Rest is ok.

v_source_query=�select count(*) from emp�
v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF 

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query
EOF
`

echo $v_source_value
3

Well, thanks.
It is small v, may be it changed while copy pasting or something. Sorry about that.
But it is not working.

This is the error I get:

Syntax error at line 2 : `;' unexpected

4

Add semicolon

v_source_query=�select count(*) from emp;�

Did you do any modification to your script?

5

Okay, this is the actual program.
The column source_query from the table source has the query �select count(*) from emp;�

v_source_query=`sqlplus -S "$DATABASE_LOGIN" << EOF

                    set feed off
                    set pagesize 0
                    set head off
                    set linesize 2000

select source_query from source where source_id = $1;

EOF
`
echo $v_source_query

v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF

                    set feed off
                    set pagesize 0
                    set head off
                    set linesize 2000

$v_source_query;

EOF
`
echo $v_source_value

6

try this

v_source_query="select source_query from source where source_id = $1;"

echo $v_source_query

v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query;

EOF
`
echo $v_source_value
7

Okay i tried your example

It throws an error:

SP2-0223: No lines in SQL buffer.

8

Remove semicolon after $v_source_query

v_source_query="select source_query from source where source_id = $1;"

echo $v_source_query

v_source_value=`sqlplus -S "$DATABASE_LOGIN" << EOF

set feed off
set pagesize 0
set head off
set linesize 2000

$v_source_query

EOF
`
echo $v_source_value
9

Hey thanks, i am being such a pest, but i am so new to Shell scripting. Just a week old.

Your example is simply printing the query on the screen, i want it to execute and give me the output.

Say if the query is "select count(*) from emp", i want 14 as an answer.

10

check if this gives the result.

echo $v_source_value | sed "s/.*SQL> \([0-9]\{1,\}\).*/\1/"

Else can you show the value of echo $v_source_value?

11

It is printing the query stored in the database.
But i dont want it to print the query, i want it to execute the query and print the output.

12

check the previous post

13 
$ query="select count(1) from emp;"
$ val="$(sqlplus -s scott/tiger < <(printf "set pages 0 feed off\n$query"))"
$ echo $val
14

Or, if your shell doesn't support process substitution:

$ val="$(printf "set pages 0 feed off\n$query"|sqlplus -s scott/tiger)"
$ echo $val
14

P.S. Adjust user/pass for your needs.