Oracle Shell Script
in [Oracle]
|
Prev: convert XIDUSN,XIDSLOT,XIDSQN to start_scn for old transaction to find first archived redo log file for logminer session
Next: Simple Hack TO Get $1500 To Your PayPal Account.
From: Maxim Demenko on 20 Jul 2010 10:27 On 20.07.2010 16:10, The Magnet wrote: > On Jul 20, 4:46 am, gazzag<gar...(a)jamms.org> wrote: >> On 20 July, 05:49, The Magnet<a...(a)unsu.com> wrote: >> >> >> >> >> >>> I know how to use a shell variable in a query from shell. Can anyone >>> help? >> >>> This works fine >>> ---------------------- >>> data_rec=`sqlplus -s $user/${password}<< "EOF" >>> set heading off feedback off verify off timing off >>> SELECT owner||':'||name >>> FROM replicated_tables >>> WHERE obj_type = '$type'; >>> exit >>> EOF` >> >>> However, I cannot seem to replace the FROM table: >> >>> This Fails >>> -------------- >>> cnt=`sqlplus -s $user/${password}<< "EOF" >>> set heading off feedback off verify off timing off >>> SELECT count(*) FROM '$table'; >>> exit; >>> #EOF` >> >>> + cnt= SELECT count(*) FROM '$table' >>> * >>> ERROR at line 1: >>> ORA-00903: invalid table name >> >> Try: >> >> cnt=`sqlplus -s $user/${password}<< "EOF" >> set heading off feedback off verify off timing off >> SELECT count(*) FROM $table; >> exit; >> #EOF` >> >> Note: No quote (') around $table. >> >> HTH >> -g > > > Put some things together, removing the quotes and changing the name > with no luck: > > cnt=`sqlplus -s $user/${password}<< "EOF" > set heading off feedback off verify off timing off > SELECT count(*) FROM $name; > exit; > EOF` > > > + cnt= SELECT count(*) FROM $name > * > ERROR at line 1: > ORA-00911: invalid character > > > It has nothing to do with luck, but rather with (not)reading of documentation. You doesn't state in which shell are you working, but i'll assume following quote will aplly. http://tldp.org/LDP/abs/html/here-docs.html <quote> Quoting or escaping the "limit string" at the head of a here document disables parameter substitution within its body. The reason for this is that quoting/escaping the limit string effectively escapes the $, `, and \ special characters, and causes them to be interpreted literally. </quote> On my linux box (with bash shell) it works as advertised: oracle(a)muclx13:~ >cat not_working.sh #!/bin/bash user=scott password=tiger name=emp cnt=`sqlplus -s $user/${password} <<"EOF" set heading off feedback off verify off timing off SELECT count(*) FROM $name; exit; EOF` echo "cnt = $cnt" oracle(a)muclx13:~ >./not_working.sh cnt = SELECT count(*) FROM $name * ERROR at line 1: ORA-00911: invalid character oracle(a)muclx13:~ > oracle(a)muclx13:~ >cat working.sh #!/bin/bash user=scott password=tiger name=emp cnt=`sqlplus -s $user/${password} <<EOF set heading off feedback off verify off timing off SELECT count(*) FROM $name; exit; EOF` echo "cnt = $cnt" oracle(a)muclx13:~ >./working.sh cnt = 14 Best regards Maxim (btw, you don't necessarily need the exit command in here documents, as spawned processes are automatically closed)
From: The Magnet on 20 Jul 2010 17:54
On Jul 20, 9:27 am, Maxim Demenko <mdeme...(a)gmail.com> wrote: > On 20.07.2010 16:10, The Magnet wrote: > > > > > > > On Jul 20, 4:46 am, gazzag<gar...(a)jamms.org> wrote: > >> On 20 July, 05:49, The Magnet<a...(a)unsu.com> wrote: > > >>> I know how to use a shell variable in a query from shell. Can anyone > >>> help? > > >>> This works fine > >>> ---------------------- > >>> data_rec=`sqlplus -s $user/${password}<< "EOF" > >>> set heading off feedback off verify off timing off > >>> SELECT owner||':'||name > >>> FROM replicated_tables > >>> WHERE obj_type = '$type'; > >>> exit > >>> EOF` > > >>> However, I cannot seem to replace the FROM table: > > >>> This Fails > >>> -------------- > >>> cnt=`sqlplus -s $user/${password}<< "EOF" > >>> set heading off feedback off verify off timing off > >>> SELECT count(*) FROM '$table'; > >>> exit; > >>> #EOF` > > >>> + cnt= SELECT count(*) FROM '$table' > >>> * > >>> ERROR at line 1: > >>> ORA-00903: invalid table name > > >> Try: > > >> cnt=`sqlplus -s $user/${password}<< "EOF" > >> set heading off feedback off verify off timing off > >> SELECT count(*) FROM $table; > >> exit; > >> #EOF` > > >> Note: No quote (') around $table. > > >> HTH > >> -g > > > Put some things together, removing the quotes and changing the name > > with no luck: > > > cnt=`sqlplus -s $user/${password}<< "EOF" > > set heading off feedback off verify off timing off > > SELECT count(*) FROM $name; > > exit; > > EOF` > > > + cnt= SELECT count(*) FROM $name > > * > > ERROR at line 1: > > ORA-00911: invalid character > > It has nothing to do with luck, but rather with (not)reading of > documentation. You doesn't state in which shell are you working, but > i'll assume following quote will aplly.http://tldp.org/LDP/abs/html/here-docs.html > > <quote> > Quoting or escaping the "limit string" at the head of a here document > disables parameter substitution within its body. The reason for this is > that quoting/escaping the limit string effectively escapes the $, `, > and \ special characters, and causes them to be interpreted literally. > </quote> > > On my linux box (with bash shell) it works as advertised: > > oracle(a)muclx13:~ >cat not_working.sh > #!/bin/bash > user=scott > password=tiger > name=emp > cnt=`sqlplus -s $user/${password} <<"EOF" > set heading off feedback off verify off timing off > SELECT count(*) FROM $name; > exit; > EOF` > echo "cnt = $cnt" > oracle(a)muclx13:~ >./not_working.sh > cnt = SELECT count(*) FROM $name > * > ERROR at line 1: > ORA-00911: invalid character > oracle(a)muclx13:~ > > oracle(a)muclx13:~ >cat working.sh > #!/bin/bash > user=scott > password=tiger > name=emp > cnt=`sqlplus -s $user/${password} <<EOF > set heading off feedback off verify off timing off > SELECT count(*) FROM $name; > exit; > EOF` > echo "cnt = $cnt" > oracle(a)muclx13:~ >./working.sh > cnt = > 14 > > Best regards > > Maxim > (btw, you don't necessarily need the exit command in here documents, as > spawned processes are automatically closed) Maxim, Good point. I am using the Korn shell. |