Problem with shell script...ORA-00904:invalid identifier

Shell Programming and Scripting
1

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

2

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

3

.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...

4

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.

5

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

6

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

7

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

8

The problem is only the sql statements.
Please connect to sqlplus and run:

desc prod;
desc geo;
select max(ctrl_perd) from prod where code='123';
select max(ctrl_perd) from geo where code='456';

Please send the output.

Regards,
Nir

9

[pre]SQL> desc prod
Name Null? Type
----------------------------------------- -------- ----------------------------
CTRL_PERD NOT NULL DATE
CODE NOT NULL VARCHAR2(4)

SQL> desc geo
Name Null? Type
----------------------------------------- -------- ----------------------------
CTRL_PERD NOT NULL DATE
CODE NOT NULL VARCHAR2(4)

SQL>select max(ctrl_perd) from prod where code='123';

MAX(CTRL_
---------
10-NOV-06

SQL>select max(ctrl_perd) from geo where code='456';

MAX(CTRL_
---------
10-NOV-06

10

Very weird ....

I created prod,geo tables and populated them with data.
I ran the script:

#!/usr/bin/ksh

OUTPUT=$(sqlplus -s  nir/nir@DB10g << 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 }')
echo $PROD_CTRL_PERD
echo $GEO_CTRL_PERD

I ran the script:

:/tmp > ./check_sql.ksh 
14-DEC-06
20-NOV-06

I didn't encountered with any problem ...

Nir

11

mm..Intriguing...
My script invokes tables across a database link...
Could that perhaps be the reason for the error?

12

It might be the reason ...
However,I don't see any db link in the script.

If you run:
select max(ctrl_perd) from prod@db_link where code='123';
select max(ctrl_perd) from geo@db_link where code='456';

What would you get?
Nir

13

Thanks everyone who offered a helping hand to solve my problem.

I used TO_CHAR function and finally it works..
SELECT MAX(CTRL_PERD) FROM PROD WHERE CODE=TO_CHAR(123);

Thanks again!!!!
Appreciate everyone's guidance.

Regards,
Bhagat