whomever the script is running as would likewise need execute on /home and /home/oracle
put an "/usr/bin/id" call in there instead and see exactly who the script is running as.
If you are connecting to the database using a direct connection - the script will likely be running as "you"
If you are connecting via a listener, the script will likely be running as "the OS account that started the listener"
If you are connecting via shared server, the script will likely be running as "the OS account that started the database"
I think you are likely connecting directly and you cannot actually 'see' that directory.
Here is the problem in reverse - I have a script run_ls.sh that cd's to MY (tkyte) directory /home/tkyte/tmp and does an ls.
[tkyte@localhost 11gr2]$ sqlplus /
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 6 15:22:10 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$tkyte%ORA11GR2> select * from ls;
LINE
-------------------------------------------------------------------------------
test.sql
tk.prf
xxx
ops$tkyte%ORA11GR2>
but, if I connect over sqlnet:
[tkyte@localhost 11gr2]$ sqlplus 'ops$tkyte/foobar@ora11gr2_2'
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 6 15:22:38 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$tkyte%ORA11GR2> select * from ls;
select * from ls
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /tmp/run_ls.sh encountered error
"/tmp/run_ls.sh: line 2: cd: /home/tkyte/tmp: Not a directory
"
ops$tkyte%ORA11GR2>
If I replace the cd/ls with /usr/bin/id - you can see what is happening:
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select * from ls;
LINE
-------------------------------------------------------------------------------
uid=500(tkyte) gid=500(tkyte) groups=500(tkyte),501(ora11gr2) context=user_u:sy
stem_r:unconfined_t
ops$tkyte%ORA11GR2> connect ops$tkyte/foobar@ora11gr2_2
Connected.
ops$tkyte%ORA11GR2> select * from ls;
LINE
-------------------------------------------------------------------------------
uid=501(ora11gr2) gid=501(ora11gr2) groups=501(ora11gr2) context=user_u:system_
r:unconfined_t
ops$tkyte%ORA11GR2>