Re: Sqlplus subshell question (166 Views)
Reply
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 1 of 11 (166 Views)

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
Honored Contributor
Jeff Schussele
Posts: 6,795
Registered: ‎02-18-2002
Message 2 of 11 (166 Views)

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
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 3 of 11 (166 Views)

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
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 4 of 11 (166 Views)

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
Michael Mike Reaser
Posts: 122
Registered: ‎08-31-2007
Message 5 of 11 (166 Views)

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
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 6 of 11 (166 Views)

Re: Sqlplus subshell question

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

Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 7 of 11 (166 Views)

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
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 8 of 11 (166 Views)

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
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 9 of 11 (166 Views)

Re: Sqlplus subshell question

JRF , that didnt work either :-(
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 10 of 11 (166 Views)

Re: Sqlplus subshell question

Ok found out ... the select statement required a semi-colon after the statement.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 11 of 11 (166 Views)

Re: Sqlplus subshell question

Thanks All!
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.