Sqlplus subshell question

[Allanm]

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

[Jeff Schussele]

Hi AllanM,

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

My $0.02,
Jeff

[Allanm]

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]

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]

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

[Allanm]

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

[James R. Ferguson]

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]

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]

JRF , that didnt work either :-(

[Allanm]

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

[Allanm]

Thanks All!