ORACLE_PATH and SQLPATH - continue from yesterday

Continue on yesterday's post.

I found out that the temp.sql that I actually run is located at the following directory:

oracle@dhpnbe5b(391) PRODDB /home/oracle/dba/tools

And its contents are:

$ cat temp.sql
create or replace procedure sel_all_sql
                            ( sid_in         in     number  )
is
begin
# set verify off
# set feedback  off
# column sid     heading "OracleSessionId"
# column sqltext heading "Sql text"
select sql_text
from V$SESSION a , V$SQLTEXT b
where a.sql_address =  b.address
and a.sql_hash_value =  b.hash_value
  and a.sid   =  sid_in
order by piece;
end;
/

It looks no harm.

Did a little google, I learned that there are two envrionment variables that are relevent to this issue:

(1) ORACLE_PATH

Defines the path names to locate files. If ORACLE_PATH is set, the application searches the directories specified by $ORACLE_PATH first, then the current directory. If ORACLE_PATH is not set, then the application uses the current directory. This variable is used by SQLPlus, Forms and Menu.

(2) SQLPATH

Specifies the directory or list of directories that SQLPlus searches for a login.sql file.

I have an impression that the Oracle searches SQLPATH, never known befeore that Oracle acutually searches ORACLE_PATH.

Regarding the environment in which I run the temp.sql, I did not find any temp.sql under SQLPATH, however, the above temp.sql is really under ORACLE_PATH. See the following:

oracle@dhpnbe5b(385) PRODDB /home/oracle
$ echo $ORACLE_PATH
/home/oracle/dba/tools:.:/home/oracle/dba/sql:/home/oracle/dba/tools

Notice that the '/home/oracle/dba/tools' appears twice, one is preceding the current dir '.', this explains what happend yesterday, which Oracle did not pick up the temp.sql to run from my current directory.

I will test out that if ORACLE_PATH is not set whether Oracle will search SQLPATH. But I know it will at least with my 10g SQL Plus/database on Windows XP pc.