script problem solved, but I still don't understand ....

Platform - Oracle 9.2 EE on Solaris 8

I was putting together a note asking for a second set of eyes, but as I reviewed it before posting, I found the solution. But I still don't understand everything about it.

Running a shell script that calls SQL*Plus to execute a sql script. Ran fine from the command line but failed when run from crontab. Output showed that after connecting, the first SQL statement returned a ORA-01034: ORACLE not available.

I finally realized that even though I was setting ORACLE_SID in the shell script, I wasn't exporting it so that SQLPlus could see it. After adding 'export ORACLE_SID' to the shell script it ran like a champ.

But I still don't understand why it got (or seemed to get) a successful connection in the first place, or once it had it, a DML statement would return 'ORACLE not available'.

The actual invocation of sqlplus in the shell script looks like this:

sqlplus "/ as sysdba" @mysqlscript

and the output looked like this. Note the successfull 'connected' just before the DELETE statement.

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 13 14:42:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> DELETE FROM mytable
  2 WHERE sample_time < sysdate - &1
  3 ;
old 2: WHERE sample_time < sysdate - &1 new 2: WHERE sample_time < sysdate - 15 DELETE FROM mytable
*
ERROR at line 1:
ORA-01034: ORACLE not available

I'm sure this is something I should know. Maybe I do know it, but am just having a DSA (Dumb S*** Attack (tm)) I think my memory is maxed out, and everytime I have to remember something new, something else has to be tossed to make room. One of these days it will be my own name that gets thrown out to make room for the names of seven new databases to be created . . . Received on Wed Oct 13 2004 - 16:04:22 CDT