Re: Oracle Shell Script

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 20 Jul 2010 16:27:14 +0200
Message-ID: <4C45B242.6060807_at_gmail.com>

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) Received on Tue Jul 20 2010 - 09:27:14 CDT