This discussion is archived
9 Replies Latest reply: Dec 24, 2012 2:49 AM by Karthick_Arp RSS

Scheduling Job not happening in dbms_scheduler

970843 Newbie
Currently Being Moderated
Hi,

Below is the dbms_scheduler i had created, according to the job i have created it have to delete employee "simon" from empp table at the time i mentioned, the procedure got successfully executed but the row didnt got deleted, Please help me with solution
begin
dbms_scheduler.create_job
(job_name => 'test_full_job_def',
job_type => 'PLSQL_BLOCK',
job_action=>
'begin
delete from empp where NAME="simon";
commit;
end;',
repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=30',
enabled=>true,
auto_drop=>false,
comments=>'Delete simon matches from the empp table');
end;

Thanks in advance
  • 1. Re: Scheduling Job not happening in dbms_scheduler
    Karthick_Arp Guru
    Currently Being Moderated
    job_action=>
    'begin
    delete from empp where NAME="simon";
    commit;
    end;',
    You have used Double quotes before and after simon. That is incorrect. Change you job_action like this
    job_action => q'[begin delete from empp where name = 'simon'; commit; end;]'
    Edited by: Karthick_Arp on Dec 24, 2012 12:56 AM
    My previous comment about ENABLE wrong.
  • 2. Re: Scheduling Job not happening in dbms_scheduler
    970843 Newbie
    Currently Being Moderated
    hi,
    Thankyou for the reply.

    When i change it in single quotes like this 'simon' i am getting error.

    PLS-00103: Encountered the symbol "SIMON" when expecting one of the following:
    The symbol ", was inserted before "SIMON" to continue.
    Thankyou

    Edited by: im_user1 on 24 Dec, 2012 1:06 AM
  • 3. Re: Scheduling Job not happening in dbms_scheduler
    Karthick_Arp Guru
    Currently Being Moderated
    im_user1 wrote:
    hi,
    Thankyou for the reply.

    When i change it in single quotes like this 'simon' i am getting error.

    PLS-00103: Encountered the symbol "SIMON" when expecting one of the following:
    The symbol ", was inserted before "SIMON" to continue.
    Thankyou

    Edited by: im_user1 on 24 Dec, 2012 1:06 AM
    Show you code
  • 4. Re: Scheduling Job not happening in dbms_scheduler
    970843 Newbie
    Currently Being Moderated
    The same code which i posted above i am using but with one changes

    delete from empp where NAME='simon';


    Note:If i run the procedure i posted its getting successful.
  • 5. Re: Scheduling Job not happening in dbms_scheduler
    Karthick_Arp Guru
    Currently Being Moderated
    im_user1 wrote:
    The same code which i posted above i am using but with one changes

    delete from empp where NAME='simon';


    Note:If i run the procedure i posted its getting successful.
    No see my post the code that i have given use it.

    This one
    job_action => q'[begin delete from empp where name = 'simon'; commit; end;]'
  • 6. Re: Scheduling Job not happening in dbms_scheduler
    970843 Newbie
    Currently Being Moderated
    Hi sorry i misunderstood, below is the procedure i used and i am getting error

    begin
    dbms_scheduler.create_job
    (job_name => 'testtt',
    job_type => 'PLSQL_BLOCK',
    job_action => q'[begin delete from empp where name = 'simon'; commit; end;]'
    repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=30',
    enabled=>true,
    auto_drop=>false,
    comments=>'Delete simon matches from the empp table');
    end;

    Now this is the error i am getting:

    repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=30',
    *
    ERROR at line 6:
    ORA-06550: line 6, column 1:
    PLS-00103: Encountered the symbol "REPEAT_INTERVAL" when expecting one of the
    following:
    ) , * & = - + < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
    The symbol "," was substituted for "REPEAT_INTERVAL" to continue.
  • 7. Re: Scheduling Job not happening in dbms_scheduler
    Karthick_Arp Guru
    Currently Being Moderated
    You missed a comma now :(

    Try this
    begin
         dbms_scheduler.create_job
         (
              job_name     => 'testtt',
              job_type     => 'PLSQL_BLOCK',
              job_action     => q'[begin delete from empp where name = 'simon'; commit; end;]',
              repeat_interval     => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=30',
              enabled          => true,
              auto_drop     => false,
              comments     => 'Delete simon matches from the empp table'
         );
    end;
  • 8. Re: Scheduling Job not happening in dbms_scheduler
    970843 Newbie
    Currently Being Moderated
    oh no, thank you so much its happening :)) , the record got deleted.
    I wonder whats this q'[ used for?                                                                                                                                                                                                               
  • 9. Re: Scheduling Job not happening in dbms_scheduler
    Karthick_Arp Guru
    Currently Being Moderated
    im_user1 wrote:
    oh no, thank you so much its happening :)) , the record got deleted.
    I wonder whats this q'[ used for?
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i38404

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points