Oracle Scheduled Job Succeeded, but Stored Procedure isn't executed

Question

I've come across several posts that almost have the same problem as I do, but I haven't found an answer that fits my situation.

I have a stored procedure, that when run manually through SQL Developer, the procedure runs and finishes successfully, I see data updated which suggests that the commits are working.

• I have a job that is scheduled to run daily, and it does.
• I can run the job manually, i.e. an anonymous block in SQL Developer that executes it.
• I can "right-click" --> "Run Job"

Each of these methods work, and they report a Status of "SUCCEEDED" in the run log. However, the execution time is always 00:00:00; and no data is updated in the database.

This is the anonymous block that SQL Developer created when I created the job.

DBMS_SCHEDULER.CREATE_JOB (
    job_name => '"OWNER"."GSS"',
    job_type => 'STORED_PROCEDURE',
    job_action => 'OWNER.PKG_GSS.GENERATE_GSS_DATA',
    number_of_arguments => 2,
    start_date => TO_TIMESTAMP_TZ('2018-05-09 11:47:15.000000000 AMERICA/NEW_YORK','YYYY-MM-DD HH24:MI:SS.FF TZR'),
    repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=6',
    end_date => NULL,
    enabled => TRUE,
    auto_drop => FALSE,
    comments => '');

The 2 arguments are set as well, I just didn't add it here.

Any suggestions would be greatly appreciated!!!!!

Answer 1

I have the same issue, my issue is resolved by granting the permission to the Procedure and recreating the job scheduler on daily basis.

Below is the procedure I wrote:

create or replace PROCEDURE dist_auto_eod AS
    v_sp_code     VARCHAR2(100);
    v_count_eod   NUMBER;
    v_count_atn   NUMBER;

BEGIN
    FOR data IN (
        SELECT
            sp_code,
            company_code,
            branch_code
        FROM
            dist_login_user
        WHERE
            is_mobile = 'Y'
    ) LOOP
        BEGIN
            SELECT
                COUNT(track_type)
            INTO
                v_count_eod
            FROM
                dist_lm_location_tracking
            WHERE
                    track_type = 'EOD'
                AND
                    TO_DATE(submit_date) = trunc(SYSDATE)
                AND
                    sp_code = data.sp_code;

        END;

        BEGIN
            SELECT
                COUNT(track_type)
            INTO
                v_count_atn
            FROM
                dist_lm_location_tracking
            WHERE
                    track_type = 'ATN'
                AND
                    TO_DATE(submit_date) = trunc(SYSDATE)
                AND
                    sp_code = data.sp_code;

            IF
                v_count_eod = 0 AND v_count_atn = 1
            THEN
                INSERT INTO dist_lm_location_tracking (
                    sp_code,
                    submit_date,
                    latitude,
                    longitude,
                    track_type,
                    company_code,
                    branch_code,
                    remarks
                ) VALUES (
                    data.sp_code,
                    TO_DATE(
                        trunc(SYSDATE) || ' 07:22 PM',
                        'dd-mon-yy hh:mi AM'
                    ),
                    '',
                    '',
                    'EOD',
                    data.company_code,
                    data.branch_code,
                    'Automatic EOD'
                );

            END IF;

        END;

    END LOOP;

    COMMIT;
END;