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: The Magnet on 20 Jul 2010 00:49 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
From: Eric Stahl on 20 Jul 2010 01:03 On Mon, 19 Jul 2010 21:49:07 -0700 (PDT), The Magnet <art(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 > "table" as a reserved keyword?
From: Eric on 20 Jul 2010 02:55 On 2010-07-20, The Magnet <art(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 > > Why have you put single quotes around $table ? That prevents the substitution from taking place, hence the error. E.
From: gazzag on 20 Jul 2010 05:46 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
From: The Magnet on 20 Jul 2010 10:10
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 |