Scheduler Oracle Stored Procedure

0
votes

I have a simple stored procedure I wanted Oracle to execute weekly and I have put it in the Oracle Scheduler. It was created fine and the scheduled task seems to execute (no error) but my stored procedure does not execute. I have admin right on the database and I do not get any error. Just do not get the result I wanted.

Below is the details of the job:

enter image description here

The creation was successful

enter image description here

The stored procedure is a simple one

create or replace 
PROCEDURE DELETEBOGUSLETTERRECORDS AS 
BEGIN
    DELETE FROM BOGUSLETTERS;
    COMMIT;
END DELETEBOGUSLETTERRECORDS;

The procedure was tested outside the scheduler, executed fine and all records in the specified tables were deleted. However, that same procedure was not executed properly when it was scheduled. I even had the job run immediately, but after it was run, the records were not deleted. No error whatsoever.

What is the issue? Thanks!

1
oracledbms-scheduler
Check *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS (replace the * with ALL, USER or DBA) views – Evgeni Dimitrov
I am surprised it did not throw an error. A PL/SQL block does not need (and I believe cannot have) the keyword EXEC. That command is used in SQLPlus to execute a procedure outside of a block. All you should have is BEGIN deletebogusletterrecords; END;. – Chris Hep
Well, checked the log and details as suggested. It turned out the task was never run?! I am a bit confused now as of why it was not. I have made it to run immediately and it was ignored. I have also tried to schedule it on specific time and when the time passed, nothing was run. How do I force the scheduled task to run? Also, I will remove the exec command as suggested. – user1205746
@HepC: You are correct! I removed the command "exec" and the task actually completed successfully. There was no error and the task was just ignore it silently...go figure! – user1205746
@HelpC The answer is removing the key word EXEC. If you post it as answer, I will mark it as such. Thank you for your prompt instruction. Very much appreciated! – user1205746

1 Answers

0
votes

I am not sure, but we execute the procedure like this:

[ Type of Job: PL/SQL Block ]

BEGIN   
SCHEMA_NAME.STORED_PROC_NAME;   
END;

And they run fine like this.