Shell Programming and Scripting

Guys,
Please suggest me what's wrong with this script?


#!/usr/bin/sh
##############################################################################
# Author : Bhagat Singh
#
#
# Date : Nov 13,2006
#
##############################################################################
OUTPUT=$(sqlplus -s xxx/yyy#@zzz << EOF | grep -v "^$"
set head off
select max(ctrl_perd) from prod where code='123';
select max(ctrl_perd) from geo where code='456';
exit
EOF)
PROD_CTRL_PERD=$(echo $OUTPUT | awk '{ print $1 }')
GEO_CTRL_PERD=$(echo $OUTPUT | awk '{ print $2 }')

I'm getting error below:
ERROR at line 1:
ORA-00904: "123": invalid identifier
(note:the output of both the queries are of "date" data type)
Help me out..
Thanks,
Bhagat

Are these columns code and ctrl_perd valid in your database? Did you execute these queries in SQL Plus?

.Thanks for your reply.
They are perfectly valid in my database.
I get the result when I execute the same query in SQL plux..
Intruiging...

It's weird !!. I ran your script with the table and column names replaced with valid table and column names in my database. It works perfectly for me.

I checked the syntax of the shell script and it's OK.
I agree with mona that you have problem with the sql statements.
Try to remove the commas from the sql statements:
select max(ctrl_perd) from prod where code=123;
select max(ctrl_perd) from geo where code=456;
Execute the statements first in sqlplus and see if they run successfully.
Send us the output you get.

Nir

The colon should not cause any problems. Oracle will do internal type conversion if the "code" column is number.

Hello,

Well the error 0094 symbolize :
oerr ora 904
00904, 00000, "%s: invalid identifier"

Hence i believe that there z error in declaration of yr variable output so , its better u use the variable and before that mention the default directories of yr oracle file like

mention in starting of yr script

. /in/local/bin/.tools.sh

where tools .sh is a hidden script to call and set yr env variables of oracle and then u cal directly call sql .

KRegards,
Aparna