11 Replies Latest reply: Apr 2, 2013 10:12 AM by 1000344 RSS

    Statements  called by DBMS_JOB is not executing

    1000035
      Hi,

      I am trying to insert a row using dbms_job.submit. Table dba_jobs is inserted with a new job id but my insert statement is not getting executed. Here is the simple code and ouput I am getting.

      declare
      jobno number;
      BEGIN
      dbms_output.put_line('Begin');
      DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO test (val1,val2) VALUES(''1111'',''D'');commit;end; ');
      commit;
      dbms_output.put_line('Inserted'|| jobno);
      dbms_job.remove(jobno);
      dbms_output.put_line('removed'|| jobno);
      END;
      /

      Output:

      SQL> @TESTING.SQL
      Begin
      Inserted96
      removed96

      PL/SQL procedure successfully completed.


      I have a row inserted and deleted in dba_jobs , but no row is in table test.

      Quick help is appreciated.

      Thanks,
        • 1. Re: Statements  called by DBMS_JOB is not executing
          Ashu_Neo
          It will work. Check below
          SQL> ed
          Wrote file afiedt.buf
          
            1  declare
            2  jobno number;
            3  BEGIN
            4  dbms_output.put_line('Begin');
            5  DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO test12 (val1,val2) VALUES(''1111'',''D'');commit;end; ');
            6  commit;
            7  dbms_output.put_line('Inserted'|| jobno);
            8  dbms_job.remove(jobno);
            9  dbms_output.put_line('removed'|| jobno);
           10* END;
          SQL> /
          Begin
          Inserted7
          removed7
          
          PL/SQL procedure successfully completed.
          
          SQL> select * from test12;
          
          VAL1                 VAL2
          -------------------- ----------
          1111                 D
          Thanks!
          • 2. Re: Statements  called by DBMS_JOB is not executing
            Peter Gjelstrup
            Hi Jane and welcome to the forum,

            Maybe you do not have any job queue processes?
            SQL> show parameter JOB_QUEUE_PROCESSES
            
            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- -------
            job_queue_processes                  integer     4
            SQL>
            Regards
            Peter
            • 3. Re: Statements  called by DBMS_JOB is not executing
              1000035
              Thanks for the response.

              See my screen copy below.

              15:44:15 SQL> declare
              15:44:26 2 jobno number;
              15:44:26 3 BEGIN
              15:44:26 4 dbms_output.put_line('Begin');
              15:44:26 5 DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO X_REG_TEST_PARALLEL (MEAS
              URE_ID,FREQUENCY,START_DATE,END_DATE,TIME_INSERT)
              15:44:26 6 VALUES(''1111'',''D'',''2012-MAY-03'',''2012-MAY-04'',SUBSTR(CURRE
              NT_TIMESTAMP, 1,35));commit;end; ');
              15:44:26 7 commit;
              15:44:26 8 dbms_output.put_line('tested'|| jobno);
              15:44:26 9 dbms_job.remove(jobno);
              15:44:26 10 dbms_output.put_line('removed'|| jobno);
              15:44:26 11 END;
              15:44:28 12 /
              Begin
              tested102
              removed102

              PL/SQL procedure successfully completed.

              Elapsed: 00:00:00.01
              15:44:29 SQL> SELECT * FROM X_REG_TEST_PARALLEL;

              no rows selected

              Elapsed: 00:00:00.00


              I am not getting row into my table. Is some thing missing at the environment level???
              • 4. Re: Statements  called by DBMS_JOB is not executing
                Ashu_Neo
                Quite agree with Peter..!

                But it will not be working on 1st execution of program and when you run it 2nd time, then it will work.
                By the way, try to add one more extra parameter (next_date Default sysdate) to your block. Then it will work on 1st run.
                DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO test12 (val1,val2) VALUES(''1111'',''D'');commit;end; ', SYSDATE);
                Thanks!
                • 5. Re: Statements  called by DBMS_JOB is not executing
                  1000035
                  Hey Peter,

                  Thank You

                  Parameter is looking fine and 1 see only one row in DBA_JOBS right now.

                  15:44:44 SQL> show parameter JOB_QUEUE_PROCESSES

                  NAME TYPE VALUE
                  ------------------------------------ ----------- -------------------
                  job_queue_processes integer 10

                  Regards,
                  • 6. Re: Statements  called by DBMS_JOB is not executing
                    Ashu_Neo
                    Try with this modified line code and execute the changed block. It may work!
                    DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO X_REG_TEST_PARALLEL (MEAS
                    URE_ID,FREQUENCY,START_DATE,END_DATE,TIME_INSERT)
                    15:44:26 6 VALUES(''1111'',''D'',''2012-MAY-03'',''2012-MAY-04'',SUBSTR(CURRE
                    NT_TIMESTAMP, 1,35));commit;end; ', SYSDATE); -- ADDED one parameter as sysdate
                    Thanks!
                    • 7. Re: Statements  called by DBMS_JOB is not executing
                      Dom Brooks
                      declare
                      jobno number;
                      BEGIN
                      dbms_output.put_line('Begin');
                      DBMS_JOB.SUBMIT(jobno,'begin INSERT INTO test (val1,val2) VALUES(''1111'',''D'');commit;end; ');
                      commit;
                      dbms_output.put_line('Inserted'|| jobno);
                      dbms_job.remove(jobno);
                      dbms_output.put_line('removed'|| jobno);
                      END;
                      /
                      The running of the job is asynchronous.
                      For this to work, you are reliant on the job queue processes picking up and running the job in the split second between your commit and your call to remove the job.
                      It's a non-repeatable job. Why do you need to remove it?
                      It will be removed once run successfully.
                      • 8. Re: Statements  called by DBMS_JOB is not executing
                        1000344
                        Hi,

                        I took out the remove statement from the PLSQL block. Job row is entered in dba_jobs and I could see the dba_jobs.failures count increase as the time goes on.
                        Need a way to debug the problem?

                        Thank you
                        • 9. Re: Statements  called by DBMS_JOB is not executing
                          1000344
                          Ashu,

                          I did changes as per your suggestion (adding SYSDATE) . Still am not getting the expected reult :( Any other way to debug??

                          Thanks
                          • 10. Re: Statements  called by DBMS_JOB is not executing
                            Peter Gjelstrup
                            997341 wrote:
                            I took out the remove statement from the PLSQL block. Job row is entered in dba_jobs and I could see the dba_jobs.failures count increase as the time goes on.
                            Need a way to debug the problem?
                            Hi Jane,

                            Actual errors are found in alert.log


                            Regards
                            Peter
                            • 11. Re: Statements  called by DBMS_JOB is not executing
                              1000344
                              Thanks Peter. I could reach the error by checking alert.log . The error is ORA-01861: literal does not match format string .

                              I will take care of this..

                              -Jane