Creating Jobs on PL / SQL Developer

[mauryrv]

And the guys, all quietly?
I need to create a Schedule in PL / SQL Developer, but as I am new to PL / SQL I do not know how to do it. I need to create a job that runs every day 5 of each month at 11:00 p.m.

I hope you can help me!

[stcoutinho]

Hello Mauryrv,

I am not accustomed to using the PL / SQL Developer to create Oracle Jobs. I'd rather run the dbms_job package myself to schedule a job.

Regardless, I gave a shapage in my tool and it seems to me that the process is very simple. I'm going to detail the steps, and I inform you that the version of my PL / SQL Developer is 8.04. 1514.

Well, come on:

1) Enter PL / SQL Developer, connecting to the database;
2) On the main screen of your PL / SQL, you will notice a column on the left, as a kind of "Object Explorer";
3) Navigate the "Object Tree" within this column, positioning in the "dbms_jobs" folder;
4) Positioned in this folder, press the right mouse button to appear the options menu. Choose the New option and press ENTER;
5) A small window should appear with the CREATE DBMS_JOB title. You will be prompted to fill some information:
5.1) What: What must be executed (procedure) with any parameters. Example:

Select all

BEGIN  SP_PURGA_XPTO_LOG_OPRO; END;

5.2) Next: The next run date. Enter the date and time. Ex: 5/12/2012 23:00:00
5.3) Interval: the routine execution range. You want every five of the month at 23 o'clock. In this case, enter this command: to_date ('05 / '|| to_char (add_months (sysdate, 1),' mm / rrrr ') ||' 23:00:00 ',' DD / MM / RRRR HH24: MI: SS ').
For you to understand what this function returns, run in a window aside this desks:

Select all

SELECT SYSDATE,TO_DATE('05/'||TO_CHAR(ADD_MONTHS(SYSDATE,1),'MM/RRRR')||' 23:00:00','DD/MM/RRRR HH24:MI:SS') FROM DUAL

You will notice that regardless of the current date, the querie always returns the day 05 Next month at 23 o'clock;
6) Done all, press the Apply button from the window. If there is some syntax error, you must receive an error message;
7) If everything is OK, you will notice that below the dbms_job folder will appear a new pastry with a number. This number corresponds to the job you just created.

Remember that you need to periodically monitor the routine, to see if the job is self-collecting successfully. I suggest until your regular routine execution logs when it is fired, especially when some error occurs.

I personally do not really like to leave a job running "ad-eternum" on the bank. Maybe it's some trauma of the old 8i version - rsss. A job scheduled this way (self-repeat) generally was broken over time and stopped working.

What I usually do is add the job schedule in the procedure itself.

I made an example - soon below how this could be done. The procedure below is self-calendar to perform a single next time in the future, both in case or not to delete data from a table. With each new run, a single future date is again scheduled to run one more time. The advantage is that I can always check in dbms_job which will be the next date of execution of the procedure.

follows the sample code:

Select all

CREATE OR REPLACE PROCEDURE SP_PURGA_XPTO_LOG_OPRO 
IS 
  -- 
  wnum_job                     NUMBER; 
  wprox_data                   DATE         := NULL; 
  --- Cursor para remover JOB agendado da USER_JOBS 
  CURSOR cv_remove_job_coleta IS 
     SELECT job 
       FROM user_jobs 
      WHERE what LIKE 'BEGIN SP_PURGA_XPTO_LOG_OPRO%'; 
  -- 
  r_sql VARCHAR2(1000); 
 
BEGIN 
  ------ 
  OPEN cv_remove_job_coleta; 
  LOOP 
    FETCH cv_remove_job_coleta INTO wnum_job; 
    EXIT WHEN cv_remove_job_coleta%NOTFOUND; 
    DBMS_JOB.REMOVE(wnum_job); 
    COMMIT; 
  END LOOP; 
  CLOSE cv_remove_job_coleta; 
 
  DELETE 
    FROM XPTO_LOG_OPRO 
   WHERE DH_OPRO < SYSDATE-5; 
  COMMIT; 
 
  --- REAGENDANDO PARA PROXIMO DIA - 2:00 A 
  SELECT TRUNC(SYSDATE+1)+2/24 
    INTO wprox_data 
    FROM DUAL; 
  DBMS_JOB.SUBMIT(wnum_job,'BEGIN SP_PURGA_XPTO_LOG_OPRO; END;',wprox_data); 
  COMMIT; 
 
EXCEPTION 
  WHEN OTHERS THEN 
       ROLLBACK; 
       --- REAGENDANDO PARA PROXIMO DIA - 2:00 A 
       SELECT TRUNC(SYSDATE+1)+2/24 
         INTO wprox_data 
         FROM DUAL; 
       DBMS_JOB.SUBMIT(wnum_job,'BEGIN SP_PURGA_XPTO_LOG_OPRO; END;',wprox_data); 
       COMMIT; 
       RAISE_APPLICATION_ERROR(-20120,'ERROR ORACLE:SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM); 
END SP_PURGA_XPTO_LOG_OPRO;

Hugs and good luck!

Sergio Coutinho