As an Oracle DBA, I'd strongly recommend some changes to the way you're doing things.

don't use:

Code:

sqlplus -s <login>/<password>@<CONNECTION_NAME>

That makes your password visible to anyone logged onto the system via

Code:

ps -ef

command

Instead ... consider doing something like:

Code:

sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF

The advantages of using this structure over what you have are many:

1) keeps your password slightly more protected from prying eyes. it's much harder to sniff out (probably not impossible, but I'm not aware of a simple way to do it at this time).

2) you can build sql scripts in stand alone files, that actually "LOOK" like sql scripts. So they can have "set" commands at the start, var commands set if needed, parameters passed into them (using "&1", "&2", etc to catch them), and many queries if need be. Or as you need, even a simple, single SQL.

3) you can wrap unix braces around that block to capture all output in log file, variable, or whatever you need:

ie:

Code:

{
sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF
}   > $my_log_file.log  2>&1

or you can assign it to a variable:

Code:

my_var = 
$(sqlplus -s /nolog << EOF         
   connect <login>/<password>@<CONNECTION_NAME>
   @your_sql_script.sql
   exit
EOF)

(that one's untested, sorry, I don't use that format alot myself)

but yeah, two main points are:

a) don't expose your password - use the connect keyword to delay the login and keep the password off the command line, or use External Ids, or some other login mechanism to help protect your password.
b) keep your sql in standalone sql scripts so you can easily test/modify it. Then all you need is "pass it through" to sql, via some wrapper, or such as above.