Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: script for DBMS jobs

    Hi

    I wanna execute a script(say select * from emp) daily at 5.00 am. How it cud be incorporated in dbms_jobs.submit?

    Any Ideas!!!!!!!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: script for DBMS jobs

    Originally posted by kondaoracle
    Hi

    I wanna execute a script(say select * from emp) daily at 5.00 am. How it cud be incorporated in dbms_jobs.submit?

    Any Ideas!!!!!!!!!
    1) your example is NOT a "script".
    2) To where is the data to go after it gets selected?
    3) Go back & read the Concepts Manual 'cuz it appears you know some buzz words but little else about Oracle.

    P.S.
    It is ALWAYS a Good Thing(TM)
    to inlcude OS, OS version & DB version to at least three places.

  3. #3
    Join Date
    Sep 2003
    Posts
    33
    HI

    To be more clear

    create or replace procedure jobtest is
    x number(10);
    begin
    select count(*) into x from konda;

    insert into konda1 values(x);

    exception
    when others then
    dbms_output.put_line('Number of lines parsed ='||sqlerrm);
    end;
    /

    I Need to execute this procedure "jobtest", every day at 5.00am.
    I need a script for running the job.

    I am using Windows NT, Oracle 9.2.0

    Hope it is clear Now.

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Well why don't you try it out on the dbms_job sample script that I posted in another thread? In test env...test test test
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    use dbms_job.isubmit.The parameters are :
    job = integer, the number of the job; u can give it any integer you like as long as there is not already a job with that number.
    what = varchar2, the thingie you want to do. Would b like : 'begin procedurename(paramlist); end;' (dont forget the quotation marks!)
    next_date= date, the first time the job is to be executed. could be somthing like trunc(sysdate)+17/24 to strt it this afternoon at 5 pm
    interval= varchar2, the interval for next execution of the job. This is done by oracle by doing the select select INTERVAL from dual and this should return a certain time and date. So INTERVAL would be like 'trunc(sysdate+a)+17/24' to execute the job each day at 5PM (Dont forget the quotation marks)

    So on the sqlplus prompt do

    exec dbms_job.isubmit(job, what,nextdate, interval);

    And youir job is scheduled.

    Hopethis helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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