1 Reply Latest reply: Jul 26, 2011 1:08 PM by Frank Kulash RSS

    dbms_job.submit    inserting values in a table using stored procedure

    853151
      inserting values in a table using stored procedure by using sequence and creating a job to fire after every 10 seconds..

      create table test (col number);

      table created.

      create sequence seq_test
      start with 1
      increment by 1;

      sequence created.

      create or replace procedure sp_test is
      begin
      loop
      insert into test values(seq_test.nextval) ;
      end loop;
      commit;
      end;

      stored procedure created

      declare jobno number;
      BEGIN
      DBMS_JOB.SUBMIT (
      job => :jobno,
      what => 'sp_test;',
      next_date => TRUNC(SYSDATE + 1/1440),
      interval => 'SYSDATE + 10/86400',
      no_parse => TRUE
      );
      COMMIT;
      END;

      stored procedure created successfully



      every thing is done but why the functionality is still wrong...
      please help me
        • 1. Re: dbms_job.submit    inserting values in a table using stored procedure
          Frank Kulash
          Hi,
          Aviral wrote:
          ...
          create or replace procedure sp_test is
          begin
          loop
          insert into test values(seq_test.nextval) ;
          end loop;
          commit;
          end; ...
          Did you test that procedure first? It has an infinite loop. If it's running, it's inserting row after row into the table, but never COMMITting, because it never leaves the loop.

          It looks like what you posted is a much simplified version of what you're really doing. Simplifying things for posting on this forum is a good idea, but this one is so simplified that I can't see the point of it, so I can't suggest a better way to do it. Maybe you shouldn't have a loop in the procedure. Whatever you do, test it before submitting it as a job.