Run Many Times The Same Procedure

A simple way to submit several times the same process. Of course, DBMS_JOB should not be used any more, and instead use the DBMS_SCHEDULER, but it’s easier using the DBMS_JOB.

ORCL*ANR> CREATE OR REPLACE PROCEDURE WAIT_ONE_MINUTE IS
  2  BEGIN
  3    DBMS_LOCK.SLEEP (60);
  4  END;
  5  /

Advertencia: Procedimiento creado con errores de compilación.  

Elapsed: 00:00:00.12
ORCL*ANR> SHOW ERR
Errores para PROCEDURE WAIT_ONE_MINUTE:

LINE/COL ERROR
________ _________________________________________________________________
3/3      PL/SQL: Statement ignored
3/3      PLS-00201: el identificador 'DBMS_LOCK' se debe declarar

REM
REM From a privilege session like sys execute "grant execute on dbms_lock to anr"
REM 

ORCL*ANR> CREATE OR REPLACE PROCEDURE WAIT_ONE_MINUTE IS
  2   BEGIN
  3     DBMS_LOCK.SLEEP (60);
  4   END;
  5   /

Procedimiento creado.

Elapsed: 00:00:00.06
ORCL*ANR> show parameter job;

NAME                                 TYPE        VALUE
____________________________________ ___________ ______________________________
job_queue_processes                  integer     5

REM
REM Submit 5 times the procedure
REM 

DECLARE 
   job_n PLS_INTEGER;
BEGIN
   FOR N IN 1..5 LOOP 
      DBMS_JOB.SUBMIT ( job_n, 'WAIT_ONE_MINUTE;');              
   END LOOP;
   COMMIT;
END;
/
REM 
REM FROM OTHER SESSION QUERY THE JOBS RUNNIG
REM 

ORCL*ANR> R
  1* SELECT * FROM DBA_JOBS_RUNNING

No Rows selected


ORCL*ANR> R
  1* SELECT * FROM DBA_JOBS_RUNNING

    SID        JOB   FAILURES LAST_DATE            LAST_SEC THIS_DATE            THIS_SEC   INSTANCE
_______ __________ __________ ____________________ ________ ____________________ ________ __________
   4367    3362223 ø          ø                    ø        10/JUN/2015 15:00:33 15:00:33          0
   4363    3362225 ø          ø                    ø        10/JUN/2015 15:00:34 15:00:34          0
   4325    3362224 ø          ø                    ø        10/JUN/2015 15:00:33 15:00:33          0
   4341    3362215 ø          ø                    ø        10/JUN/2015 15:00:34 15:00:34          0
   4262    3362221 ø          ø                    ø        10/JUN/2015 15:00:34 15:00:34          0

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s