Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

sql command batch file

SOLVED
Go to solution
tareq_2
Regular Advisor

sql command batch file

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
12 REPLIES
Aneesh Mohan
Honored Contributor
Solution

Re: sql command batch file


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
Honored Contributor

Re: sql command batch file

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
Regular Advisor

Re: sql command batch file

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
Honored Contributor

Re: sql command batch file

>>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
Honored Contributor

Re: sql command batch file

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
Regular Advisor

Re: sql command batch file



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
Esteemed Contributor

Re: sql command batch file

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

Re: sql command batch file

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
Regular Advisor

Re: sql command batch file

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
Honored Contributor

Re: sql command batch file

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

Regards,
Aneesh
Jean-Luc Oudart
Honored Contributor

Re: sql command batch file

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
fiat lux
tareq_2
Regular Advisor

Re: sql command batch file

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