Re: Sqlplus subshell question (146 Views)
Reply
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 1 of 11 (146 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
Please use plain text.
Honored Contributor
Jeff Schussele
Posts: 6,795
Registered: ‎02-18-2002
Message 2 of 11 (146 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!
Please use plain text.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 3 of 11 (146 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.
Please use plain text.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 4 of 11 (146 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
Please use plain text.
Valued Contributor
Michael Mike Reaser
Posts: 122
Registered: ‎08-31-2007
Message 5 of 11 (146 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
Please use plain text.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 6 of 11 (146 Views)

Re: Sqlplus subshell question

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

Please use plain text.
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 7 of 11 (146 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...
Please use plain text.
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 8 of 11 (146 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...
Please use plain text.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 9 of 11 (146 Views)

Re: Sqlplus subshell question

JRF , that didnt work either :-(
Please use plain text.
Super Advisor
Allanm
Posts: 287
Registered: ‎08-13-2007
Message 10 of 11 (146 Views)

Re: Sqlplus subshell question

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

Re: Sqlplus subshell question

Thanks All!
Please use plain text.
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