Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Languages and Scripting
cancel
Showing results for 
Search instead for 
Did you mean: 

Sqlplus subshell question

Allanm
Super Advisor

Sqlplus subshell question

In the following script the value of $i is not being picked up within the sqlplus subshell...

#!/bin/bash
. $HOME/.bashrc

for i in `cat ~allanm/list`
do
(~allanm/bin/sql_db1 passwd <
select COM_1 from table_1 where COM_2=$i

EOF
)
done

Can you figure out why? and how to pass it ...

Thanks,
Allan
10 REPLIES
Highlighted
Jeff Schussele
Honored Contributor

Re: Sqlplus subshell question

Hi AllanM,

Do a set -x up top in that script.
I suspect $i is not being set.

My $0.02,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Allanm
Super Advisor

Re: Sqlplus subshell question

Well the value of $i is picked up outside of the subshell but not inside I believe. The question is how to pass $i to the sqlplus subshell.
Allanm
Super Advisor

Re: Sqlplus subshell question

I just ran an update instead of a select and it didnt make a change to the table. So $i's value is not getting passed to the sql statement. Has anybody experienced this before.

Thanks,
Allan
Michael Mike Reaser
Valued Contributor

Re: Sqlplus subshell question

Maybe give this a twirl? This way, we're ensuring that the value from the outer script is firmly exported into its environment, so it *should* be automagically adopted by the subshell:


#!/bin/bash
. $HOME/.bashrc

for i in `cat ~allanm/list`
do
( export i_out=$i
~allanm/bin/sql_db1 passwd <
select COM_1 from table_1 where COM_2=$i_out

EOF
)
done
There's no place like 127.0.0.1

HP-Server-Literate since 1979
Allanm
Super Advisor

Re: Sqlplus subshell question

Thanks for replying, the subshell also catches the value but not the sql statement itself. I am lost :(

James R. Ferguson
Acclaimed Contributor

Re: Sqlplus subshell question

Hi Allan:

Try changing:

select COM_1 from table_1 where COM_2=$i

...to:

$(select COM_1 from table_1 where COM_2=$i)

Regards!

...JRF...
James R. Ferguson
Acclaimed Contributor

Re: Sqlplus subshell question

Hi (again) Allan:

By the way, save a process and eliminate the 'cat'. Let the shell do the work of reading your input.

Instead of:

for i in `cat ~allanm/list`

...which would be better written:

for i in $(cat ~allanm/list)

...but still wastes a process, do:

for i in $(< ~allanm/list)


Regards!

...JRF...
Allanm
Super Advisor

Re: Sqlplus subshell question

JRF , that didnt work either :-(
Allanm
Super Advisor

Re: Sqlplus subshell question

Ok found out ... the select statement required a semi-colon after the statement.
Allanm
Super Advisor

Re: Sqlplus subshell question

Thanks All!