DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Win NT batch and shell script, simple?

  1. #1
    Join Date
    Jan 2001
    Posts
    3,134

    Win NT batch and shell script, simple?

    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
    I remember when this place was cool.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    You should be able to issue the following in your CMD file

    Code:
    sqlplus username/password @scriptname
    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    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
    Last edited by Mr.Hanky; 01-07-2003 at 12:20 PM.
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    Code:
    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.
    Jeff Hunter

  7. #7
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    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'
    Last edited by supower; 01-07-2003 at 12:43 PM.

  8. #8
    Join Date
    Nov 2002
    Posts
    170
    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

  9. #9
    Join Date
    Jan 2001
    Posts
    3,134
    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
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width