sql command batch file

[tareq_2]

hi all
i have hp-ux 11i v1 with oracle 10g r2 installed i want to create batch file to run sql command monthly i have write this line first in the batch
su - oracle -c sqlplus
the sqlplus is run when i run this batch but its tell me to enter user name and password for sql user what i need is to put this user and password in this batch to run the sql
but i dont know how.
any help please.
regards

[Aneesh Mohan · Accepted solution]

Hi,

You can invoke sql commands(or using cron) from shell script similar to this.

# su - oracle -c "sqlplus ane/ane123 @/tmp/ss.sql


Aneesh

[Hein van den Heuvel]

Do you 'normally' not have to specify a user/pass?

Please define 'normal'
- user
- su ?
- SQlplus command.

One way NOT to have to specify a password is when the user is created "IDENTIFIED EXTERNALLY".
Check the Oracle Doc, or help, or:
http://www.orafaq.com/wiki/Oracle_database_Security_FAQ

Is that the method you use to get in 'normally'?

There is really no (good!) reason to switch to the Oracle account to run an oracle task. It's more clear, clean, accountable, to use a proper user or role name.

I suspect the in the case of the batch job the ORA* environment variables were not set up, or set up differently.
Fix that, and perhaps the PATH and be happy?!

hth,
Hein.

[tareq_2]

hi
thanks for help what i need now is to make the .sql file to run my sql command but how i can make this file could you send me example file to see how .

su - oracle -c "sqlplus ane/ane123 @/tmp/ss.sql
i have write this command but the user name feild is appear to put the user name and password for the sql

"Hein" i cant understand what you want to tell me exactly .
thanks for help

[Aneesh Mohan]

>>what i need now is to make the .sql file to run my sql command

Create a file with your sql statements.

# cat /tmp/ss.sql
select * from tab;
exit
#

Create a file with your sql statements.


As Hein stated above , its is better to use external authentication to accomplish this task.



External Authentication ( Using OS ) for a sql user "ops"
-----------------------------------------------


1) SQL> show parameter os_authent_prefix

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$

2) SQL> alter system set os_authent_prefix='' scope=spfile;

3) create user ops identified externally;

4) grant connect,resource to ops;

5) shutdown and startup the database.


On Unix
-----------

6) useradd -g 106 -d /home/ops -m -k /etc/skel -s /usr/bin/sh ops.

(use dba gid instead of 106 in your case)



7) passwd ops

8) set ORACLE_SID ,PATH,ORACLE_HOME to appropriate values in the ops user .profile.

9)login as ops (unix)

10) schedule sql scripts without giving the oracle username/password.

example :- sqlplus / @myscript.sql ( login as ops (os user)

If you want to execute it from root ,then

su - ops -c "sqlplus / @myscript.sql"



Regards,
Aneesh

[Hein van den Heuvel]

Tareq,

What I tried to ask is whether this ever works for you.
When you use is interactively ( normal ), does this command work?
- sqlplus ane/ane123 @/tmp/ss.sql

This relies on ORA_SID to point to the right database.
Maybe that when run as batcg job, or after the "su -" that ORA_SID is a different DB, or not defined at all??

Things to try:

- sqlplus ane/ane123@ @/tmp/ss.sql

is the desired entry in TNSNAMES.ORA

and

su - oracle -c "env | grep -i ora"

The other thing I tried to ask was to get you to think about how you normally log in?
Do you normally pass a username and password or not?
And why not run the oracle job under your own account? Why switch user to oracle?

Hein.

[tareq_2]

sqlplus ane/ane123 @/tmp/ss.sql
this command is working normal without the sid coz i have one sid and its the default sid for one database is not Necessary to provide the sid if you have one database as default. but my main issue is not this section (sqlplus ane/ane123) my issue is the second section (@/tmp/ss.sql) this section is not work.
when i write the complete command like this
su - oracle -c sqlplus ane/ane123 @/tmp/ss.sql the result is he login to the oracle user just that he didnt run the complete command and go to the sql.
i have made new batch this batch contain :
sqlplus ane/ane123

the main batch contain like this:

su - oracle -c ./sqlbatch.sh

this working good and take me to sqlplus command line but he didnt this section:
@/tmp/ss.sql

what i can do for that
any help please
thnks

[Arturo Galbiati]

Hello,
don't use sqlplus ane/ane123 but:
sqlplus
conn ane/ane123
....
exit

reason why is that by 'ps -ef|grep [s]qlplus' in teh first way is it possible to cach database password!!

HTH,
Art

[John Brooks_1]

Please keep in mind, if you use the command sqlplus ane/ane123 @/tmp/ss.sql and another user does a ps -ef, your Oracle user and password will be displayed. If you are concerned with security, you may want to look at OS anthentication. Create an Oracle user with the OPS$ prefix e.i. ops$ane and create a UNIX user as ane. The command will be sqlplus @/tmp/ss.sql and logged into the ane UNIX account.

To be very secure, you can also change the prefix. Just set a new value for the os_authent_prefix parameter.

[tareq_2]

thanks for all
your help was usefull but still i have one thing, in my batch file i have this line

spool /u02/dw.sql

but i face this error
"cannot create spool file"
what i can do to resolve this error
thank for help again
best regards

[Aneesh Mohan]

Check whether you have proper permission on /u02...

Regards,
Aneesh

[Jean-Luc Oudart]

Hi

either this is a permission problem (check the permissions associated with the owner of the sqlplus process) or the file system is full.

Regards
Jean-Luc

[tareq_2]

thanks for all
your help was very usefull thanks again
best regards for you