Languages and Scripting
Showing results for 
Search instead for 
Do you mean 

Sqlplus subshell question

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
Honored Contributor 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!
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.
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
Valued Contributor 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
Super Advisor

Re: Sqlplus subshell question

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

Acclaimed Contributor 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...
Acclaimed Contributor 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...
Super Advisor

Re: Sqlplus subshell question

JRF , that didnt work either :-(
Super Advisor

Re: Sqlplus subshell question

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

Re: Sqlplus subshell question

Thanks All!