Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: Schedule a SQL script to run

    I'm new to oracle and need a little help.

    My version is 10.1.0.5.0.

    I would like to schedule the following sql script to run daily. I *think* I should use DBMS_SCHEDULER to do this, but I'm not real sure what the syntax would be.

    daily-script.sql...
    Code:
    spool /tmp/mleach.lst
    select count(*) from table-name where column-a='Y' and column-b< sysdate -1;
    spool off
    Thank you,
    Mike Leach

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SPOOL is a SQL*Plus command.
    You would need to create a shell script to invoke sqlplus; but this is Operating System dependent (which you decided we did not need to know).

    DBMS_SCHEDULER can be used to invoke PL/SQL procedure at a specific time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    knowign the operating system might help :-) sry

    Red Hat Enterprise Linux ES release 4 (Nahant Update 3)

    thanks for replying. i will create a shell script.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    sqlplus  <<EOF
    username/password
    spool /tmp/mleach.lst
    select count(*) from table_name where column_a='Y' and column_b< sysdate -1;
    spool off
    exit
    EOF
    To schedule this script use cron.
    BTW dash characters are NOT valid in objectnames!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    that did it! Thanks

  6. #6
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    you can use OEM to schedule a job and you need not know the syntext proper ly
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

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