Re: Oracle Shell Script

From: The Magnet <art_at_unsu.com>
Date: Tue, 20 Jul 2010 14:54:55 -0700 (PDT)
Message-ID: <9edb758a-7a39-4887-8268-a8b428b464d5_at_d37g2000yqm.googlegroups.com>

On Jul 20, 9:27 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> On 20.07.2010 16:10, The Magnet wrote:
>
>
>
>
>
> > On Jul 20, 4:46 am, gazzag<gar..._at_jamms.org>  wrote:
> >> On 20 July, 05:49, The Magnet<a..._at_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_at_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_at_muclx13:~ >./not_working.sh
> cnt =     SELECT count(*) FROM $name
>                           *
> ERROR at line 1:
> ORA-00911: invalid character
> oracle_at_muclx13:~ >
> oracle_at_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_at_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. Received on Tue Jul 20 2010 - 16:54:55 CDT