su - newuser in the shell script

[pho01]

I need to run a sql statement on a certain time interval on the day. I log in as root and need to change the user to oracle to run the sql statement.

here what's I have:

#!/usr/bin/sh
su - oracle
sqlresult=`sqlplus -s username/pwd <<end
set pagesize 0 feedback off ver off heading off echo off
select something
from something
where some criteria
exit;
end`
echo $sqlresult >>somelog.txt
exit

that supposedly should change the user to oracle, run the sql statement and write the result to the log file. However, it changed the usr to oracle, but didn't run the sql statement.

if I changed the usr to oracle, take &quot;su - oracle&quot; out and and run the script, it worked fine. Why the sqlstatement doesn't get run when su is in the script?

We have the reason not to set a cronjob at 'oracle' user. We want to set it run at the root level.
Thanks

 

[dsanchez2]

If you want to su and execute a command you will need the -c argument, as shown:

su - oracle -c command

So in your case you will need to put into a shell script the statements that you want to execute. Then use the su command to execute the script. This script must be executable by, and in a location that can be found by the oracle user. For example, create a script file (name it run_sql) containing:

#!/usr/bin/sh
sqlresult=`sqlplus -s username/pwd <<end
set pagesize 0 feedback off ver off heading off echo off
select something
from something
where some criteria
exit;
end`
echo $sqlresult >>somelog.txt
exit


Now from root you can run this script by executing the command: su - oracle -c run_sql

 

[pete91z]

Alternatively, you could do


su - oracle << EOC

< other commands..>

EOC

 

[KenCunningham]

And make sure any necessary environment variables (PATH etc) are set implicitly within your script.

 

[pho01]

Thank you all for your help.
- dsanchez2 solution works, but it opens the exe permission to all users (for oracle to run).

- pete91z solution, I don't have it worked at the momment. Error:
sqlplus: not found
stty: not a typewrite (??? don't know what it is)

- KenCunningham solution: what environment variables (Path) do I need to set in the script?

Agains, thanks much

 

[KenCunningham]

su - oracle and echo $PATH to find out what PATH you need to set in your cron script. You should also examine oracle's .profile and set $ORACLE_HOME and whatever other variables are in there in your script too. HTH.

 

[bi]

pho01,

regarding dsanchez2 solution: you don't have to open permissions to all users for oracle to run it. just make oracle the owner and give exe permission to the owner.

pete91z's solution will work if you do what KenCunningham says about setting the PATH. And don't worry about the stty: not a typewriter error.

You will also, besides the ORACLE_HOME, have to set ORACLE_SID.

 

[pho01]

This is the script that we push down from the management server to the managed nodes. So, the agent will be run as root. That's the reason why we need to su user to oracle user.

any specific example (website?) you suggest I can take a look on how to set the path for sqlplus to run as mentioned above.
Thanks

 

[bi]

su - oracle
echo $ORACLE_HOME
echo $ORACLE_SID
echo $PATH

Then, in the script use the values you got from all all those echos. add this to the script:

ORACLE_HOME=/path/from/echo
ORACLE_SID=name_of_SID
PATH=$PATH:/all/the/dirs/in/echo/PATH
export ORACLE_HOME, ORACLE_SID, PATH