Win NT batch and shell script, simple?

[Mr.Hanky]

Hey boys and girs;

OS= Win NT
Oracle= 8.1.6

I am trying to write a simple batch program to run my analyze script once a week. I have the analyze.sql, that was the easy part.

What I want to do is create a batch program that will log into SQL and call the .SQL script I have created.

I have gotten as far as getting the .CMD to log on to the correct instance but how do you get it to call the .sql script once you are in a sql session?

Thanks in advance

MH

[jovery]

You should be able to issue the following in your CMD file

sqlplus username/password @scriptname

Regards

[stmontgo]

why not schedule through dbms_jobs, the logic would be encapsulated in the database and would be portable, ie.. if you moved the db to a new machine or a new OS you would not have to worry about an extra .bat and .sql file or having to rewrite the .bat file if you ever moved to unix.

I have sample code if you want to me to post for scheduling analyze in dbms_jobs

steve

[supower]

Go to Dos in NT and issue
c:\> at 4:30 /every:m c:\xxx\yourjob.cmd

set yourjob.cmd job every monday morning 4:30am

Make sure that you have privileges to set job.

[Mr.Hanky]

Thanks guys, as we can tell my scripting abilities are near nill.

Jovery, that works and I tried it before...honest.
For some reason it does not work when I call the script on the next line, am I missing something here?

Ex...

sqlplus Mr.Hanky/X-maspoo@xxx.oracledb.blah.com
@C:\test_sql.sql

This does not execute the second line, if I put it on one line it works fine.

Steve, that is a good suggestion too, I will look into that as well.

supower, thanks. Scheduling in "AT" is not my problem, I just need to get the .cmd to call the .sql. Thanks for the suggestion and the syntax though.

MH

[marist89]

Originally posted by Mr.Hanky

sqlplus Mr.Hanky/X-maspoo@xxx.oracledb.blah.com
@C:\test_sql.sql

This does not execute the second line, if I put it on one line it works fine.

MH

If you type @c:\test_sql.sql from a c:> prompt would it run? Of course not. By the time the Command Intrepreter gets to @c:\test_sql.sql the sqlplus command is already done.

In unix, the command would be something like:

sqlplus system/yourpw@xxx.oracledb.blah.com << EOF
@c:\test_sql.sql
EOF

This would tell the command intrepreter to continue reading the following stream as input until you reach the string "EOF". I don't waste my time with WindoZ, so I can't tell you if DOS (which WindoZ is really based on) has a way to do redirection.

[supower]

yourjob.cmd

sqlplus < C:\test_sql.sql > c:\test.log
exit

put
Mr.Hanky/X-maspoo@xxx.oracledb.blah.com
as first line of 'C:\test_sql.sql'

[dbasupuser]

Try
sqlplus Mr.Hanky/X-maspoo@xxx.oracledb.blah.com \
@C:\test_sql.sql

The back slash lets the command interpreter knoiw that the line still continues

[Mr.Hanky]

Well well well, my ol' pal Jeff, back from his 12 week vacation in Guam. How the heck are ya?

Thanks for all the suggestions guys!!
I will find a way to make this happen.

MH