topic Re: Sqlplus subshell question in Operating System - HP-UX

Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 18:00:58 GMT

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

Re: Sqlplus subshell question

Jeff Schussele — Tue, 07 Apr 2009 18:03:36 GMT

Hi AllanM,

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

My $0.02,
Jeff

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 18:12:01 GMT

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.

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 18:33:12 GMT

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

Re: Sqlplus subshell question

Michael Mike Reaser — Tue, 07 Apr 2009 18:43:07 GMT

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

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 19:34:07 GMT

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

Re: Sqlplus subshell question

James R. Ferguson — Tue, 07 Apr 2009 19:44:41 GMT

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...

Re: Sqlplus subshell question

James R. Ferguson — Tue, 07 Apr 2009 19:58:11 GMT

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...

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 21:24:57 GMT

JRF , that didnt work either :-(

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 21:57:42 GMT

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

Re: Sqlplus subshell question

Allanm — Tue, 07 Apr 2009 21:58:03 GMT

Thanks All!