Execute Oracle gather stats via shell script

Shell Programming and Scripting
1

Hi ,

I am trying to automate a gather stats in shell script

#!/usr/bin/ksh

export ORACLE_HOME=/orcl/app/oracle/product/11.2.0.1/db_1
export PATH="$PATH:$ORACLE_HOME/bin"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$ORACLE_HOME/lib32"
export TNS_ADMIN=/opt/netprobe/config
username=username
password=password

partname=$(
set pagesize 0 feedback off verify off heading off echo off;
sqlplus -s $username/$password@database<<pname
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)

sqlplus -s $username/$password@database<<gather
exec dbms_stats.gather_table_stats(ownname=>'XYZ', tabname =>'TABLE_NAME_1', partname=>'"$partname"',granularity=>'PARTITION');
gather

But I am getting this error

ERROR:
ORA-01756: quoted string not properly terminated

How do I reference the variable partname's value in the here doc?

Thanks!

2

I have quoted the relevant part of your script. You seem to think that a single-quoted string is somehow "stronger quoted" than a double-quoted one and that differently quoted strings can be nested. This is a - common - misconception.

wrong concept:
'........"............................"........'
 <------------- single-quoted ---------------->
          <- single+double-quoted---->"
'<----->    single-quoted only areas   <------>

In fact it is like this: the shell maintains a flag "inside string y/n", which is flipped when a (fitting) quote is encountered. The first time the shell "sees" a quote char (single or double) it flips the flag to "y" and then waits until it gets the next fitting quote-char in the input. When getting this it flips the flag back. Everything read in the meantime is considered to be inside this string.

correct concept:
start reading, flag="outside string"               right quote, flip flag to
|                                                  |  "outside string" again
|   flag="inside (single-quoted) string"           |
|   |                                              |
|   |        |wrong quotes, flag unchanged|        v
v   v        v                            v
....'........"............................"........'....>
     <------------- single-quoted ---------------->

For your problem this means to do it like this:

partname=>"\'${partname}\'"

Notice the usage of escape-characters: they will preserve the single quotes for one pass of shell interpretation so that the resulting string will be a singly-quoted string.

Btw.: this

partname=$(
set pagesize 0 feedback off verify off heading off echo off;
sqlplus -s $username/$password@database<<pname
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)

may work but probably not in the way you intend it to do so. Consider changing the line order:

partname=$(
sqlplus -s $username/$password@database<<pname
set pagesize 0 feedback off verify off heading off echo off;
select A.PARTITION_NAME from DBA_TAB_STATISTICS  A where  table_name='TABLE_NAME_1' AND  STALE_STATS='YES';
pname
)

I hope this helps.

bakunin

2 Likes