Run Automatic Update - Oracle

Question

I often run a Update on Oracle to change the status in my database, but I would like this update to run automatically, once a day.

UPDATE inativa_produto set compras = 'N', estoque = 'N', distribuição = 'N' em que data_final <sysdate

The above update checks the item inactivity surveillance data and is the same for the lower current data or column status (purchases, inventory and distribution) that are changed to 'N'.

In that case, how could I create a job to run or update above every day?

Answer 1

In this case you can use a Job Scheduler to perform this procedure on a daily frequency.

The ideal is that you change the values of’S' and 'N' to 1 or 0 thinking about the performance issue, but following your update above, I advise you to create a process this way:

CREATE PROCEDURE P_INATIVA_PRODUTO IS

BEGIN

UPDATE inativa_produto
   SET compras = 'N', estoque = 'N', distribuição = 'N'
 WHERE data_final < TRUNC(SYSDATE); 

END;

And create a Scheduler job using the dbms_scheduler package from Oracle:

begin
  dbms_scheduler.create_job(job_name            => 'NOME_DO_JOB',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'P_INATIVA_PRODUTO',
                                start_date          => SYSDATE + INTERVAL '60' SECOND,
                                repeat_interval     => 'Freq=Daily;Interval=1',
                                end_date            => NULL,
                                job_class           => 'DBMS_JOB$',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;
/

If you want to read the Oracle documentation: https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHHBGGI