9 Replies Latest reply: Dec 24, 2012 4:49 AM by _Karthick_ RSS

    Scheduling Job not happening in dbms_scheduler

    970843
      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_
          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
            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_
              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
                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_
                  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
                    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_
                      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
                        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_
                          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