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

Thread: dbms_job.submit doesn't work ?

  1. #1
    Join Date
    May 2009
    Posts
    2

    dbms_job.submit doesn't work ?

    hi

    i tried to get an example running that uses dbms_job.submit to execute a procedure every 5 minutes.

    i created a test-table which contains 2 records, my procedure should update one column of every record.

    so far i see, the job is submited buy my procedure doesn't seemed to be executed.

    may be someone can see my mistake...i dont get it

    Code:
    -- create test table with 2 dummy records
    drop table test;
    create table test
    (
    oid integer,
    description varchar2(50)
    );
    insert into test values (1, 'asdf');
    insert into test values (2, 'lalalalla');
    commit;
    
    --procedure that should update the 2 dummy records in my test table
    create or replace procedure testjob is
    begin  
     update test set description ='update';   
     commit;
     exception
      when others then
           raise_application_error(-20001, 'Procedure testjob caused an ERROR!! Message: '||sqlerrm);
    end;
     /
    show errors; 
    
    --submit the job and hope something happens
    set serveroutput on; 
    declare
    v_jobno binary_integer;
    begin  
    
    dbms_job.submit ( job        =>  v_jobno,              
                      what       =>  'testjob;',  
                      next_date  =>  sysdate + numtodsinterval(5,'minute')
                    );
       dbms_output.put_line('Jobnumber: ' || v_jobno);
      commit;
    end;
    /
    show errors

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    did you commit it

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    Check your alert log for errors.

    You should try to set your what parameter as:

    Code:
    what => 'begin testjob; end;',

  4. #4
    Join Date
    May 2009
    Posts
    2
    how can i view the alert.log ?

    i commited the submit, but that didn't help. i found the command dbms_job.run("job-id").

    if i call this command with my just created job id my job gets executed, but my interval still doesn't work. the job don't run again after the time elapsed.

    i took a look in the user_jobs table and found my job(s) the "broken" flag is set to 'N' what (how i suppose) means "no". the interval time seems to be correct aswell.

    any idea why the submit doesn't start automatically and why the job don't get started again?

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well where in the submit job did you put the interval (you didnt)

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Check the "job_queue_processes" parameter. It must be set to value greater than zero.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

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