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

On Thu, 14 Oct 2004 08:38:43 +0200, Holger Baer <holger.baer_at_science-computing.de> wrote:

>Ed Stevens wrote:
>> 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 . . .
>
>Personally I'd expect an 'Connected to an idle instance'. Try what you get
>if you do
>
>export ORACLE_SID=foo
>sqlplus "/ as sysdba"
>
>since I don't have a Solaris at hand I can't check the outcome. But it should
>get you on the right track anyway.
>
>HTH
>
>Holger

If it had given 'Connected to an idle instance' it would all make perfect sense. But as you can see from the original post, it clearly said 'Connected'. Here's what trying to connect to a null ORACLE_SID gives at the command line, followed by an attempt with a bogus value. Both gave expected results. This was just from a command prompt, not within a script and certainly not in a script executed by cron.

oracle_at_ncensn130: echo $ORACLE_SID
VITXD01
oracle_at_ncensn130: ORACLE_SID=
oracle_at_ncensn130: echo $ORACLE_SID

oracle_at_ncensn130: sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 06:26:54 2004

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

ERROR:
ORA-12162: TNS:service name is incorrectly specified

Enter user-name: ^C
oracle_at_ncensn130: ORACLE_SID=FOO
oracle_at_ncensn130: echo $ORACLE_SID
FOO
oracle_at_ncensn130: sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 14 06:27:31 2004

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

Connected to an idle instance.

SQL> exit
Disconnected Received on Thu Oct 14 2004 - 07:33:31 CDT