Shell Programming and Scripting

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

Code:

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

[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

Very weird ....

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

Code:

#!/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:

Code:

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

I didn't encountered with any problem ...

Nir

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

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

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