DBMS_SCHEDULER job calls SP but doesn't update the date on each execution

149 Views Asked by At

I'm creating a DBMS_SCHEDULER job that runs a stored procedure. This procedure builds a report based on the input parameters, and the result is left in a file, like this: Job:

DBMS_SCHEDULER.CREATE_JOB(job_name            => 'REPORT',
                            job_type            => 'STORED_PROCEDURE',
                            job_action          => 'prc_report',
                            number_of_arguments => 1,
                            start_date          => Null,
                            repeat_interval     => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1', 
                            end_date            => Null,
                            enabled             => False,
                            auto_drop           => False);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name          => 'REPORT',
                                            argument_position => 1,
                                            argument_value    => '25');
DBMS_SCHEDULER.enable(Name => 'REPORT');

Stored procedure:

Create Or Replace Procedure prc_report(v_corte    In Varchar2)
(...)
If v_corte Like '%2%' Then
  v_fecha_inicio := To_Char(add_months(Sysdate, -1), 'YYYYMM') || '26';

  v_fecha_fin := To_Char(Sysdate, 'YYYYMM') || '25';
Else
  v_fecha_inicio := To_Char(trunc(add_months(Sysdate, -1), 'MM'),
                            'YYYYMMDD');

  v_fecha_fin := To_Char(last_day(add_months(Sysdate, -1)), 'YYYYMMDD');

End If;

The job that calls this code executes on the 26th of every month or on the 1st, based on the v_corte argument.

However, for whatever reason, when we go into the next month, v_fecha_inicio and v_fecha_fin do not get updated based on the current date. For instance, if I create the procedure and the job on October 10, 2018, the job will run on October 26, 2018, but when it runs on November 26, 2018 it seems it still takes the October 10, 2018, meaning the report will be created with the October 26, 2018 date.

I am not sure why this happens, it's like the date it stuck on the compilation date. The procedure is called and it runs without any errors, but date with which it runs is not updated on each execution.

The data inside the report uses the v_fecha_inicio and v_fecha_fin, so it's consistent with this error.

Any clues on what might be the issue?

0

There are 0 best solutions below