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

On 14 Oct 2004 05:34:26 -0700, johnbhurley_at_sbcglobal.net (John Hurley) wrote:

>snip
>
>> 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.
>
>Everyone it seems approaches shell scripts (batch jobs one could say)
>that use oracle utilities a little differently.
>
>My approach is to always dot in some kind of small file that sets (and
>exports) all of the oracle environment that you need to have
>established as the first step.
>
>#!/bin/ksh
>#
># doc notes as needed
>#
>. /u01/app/oracle/local/bin/.9ioraenv
>
>The .9ioraenv file could check what host you are on, what oracle homes
>are available, what sids, ... etc ... then set the environment as
>needed (whatever you require). This type of approach might be useful
>to your environment.
>
>John

I do that as well, and did it in this particular instance. The one 'gotcha' is ORACLE_SID on a box with multiple SIDs. That leaves the following possibilities:

1. A separate 'environment' file specific to each sid. Calling scripts have to know which sid-specific environment file to call.
2. (my approach) A single generic 'environment' file that leaves the setting of ORACLE_SID to the calling script. Or sets it on behalf of the calling script, but would have to have the calling script supply the value as a command line parm.

Either way, the main script will have to be the one to know and specify sid-specific info. It was simply my failure to export it that caused the problem.

And still leaves me with the fundamental question of how I was able to get a good connect -- *not* a 'connected to an idle instance' -- then have the first DML statement result in a ORA-01034: ORACLE not available. Received on Thu Oct 14 2004 - 10:37:08 CDT