CODEWITHSUNDEEP
oracle-databaseplsqldbms-scheduler
Prashant Mishra
Prashant Mishra

Reputation: 647

Not able to stop dbms_scheduler job

SQL> Exec  Dbms_Scheduler.stop_job('US_ALERT',true);
begin Dbms_Scheduler.stop_job('US_ALERT',true); end;
ORA-27466: internal scheduler error: 1870
ORA-06512: at "SYS.DBMS_ISCHED", line 227
ORA-06512: at "SYS.DBMS_SCHEDULER", line 674
ORA-06512: at line 1

Does anyone has any idea of what's going wrong here ? Database : Oracle Version : 12c

Upvotes: 1

Views: 3587

Answers (1)

Chris
Chris

Reputation: 678

This error is caused by the stack being unwound by unhandled exceptions in pl/SQL code. Most likely the scheduler is executing a procedure with some bad code in it. (See http://www.techonthenet.com/oracle/errors/ora06512.php)

The first thing to do is drop the job

BEGIN
   DBMS_SCHEDULER.DROP_JOB('myjob1');
END;
/

Then start investigating your stored procedure(s) and add in some exception handling.

For example you might have some pl/sql code as follows

DECLARE pe_ratio NUMBER(3,1); 
BEGIN 
SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error 
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio); COMMIT;
End;

If this was called by your job it could result in a ora 06512. (You probably will need to add in some logging/traces into to your pl/SQL to narrow this down, the line numbers in the error messages you reported may also help)

Replace it with

DECLARE pe_ratio NUMBER(3,1);   
BEGIN 
SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = 'XYZ'; -- might cause division-by-zero error
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
 COMMIT; 
EXCEPTION -- exception handlers  begin 
WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error 
INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL); 
COMMIT; ... 
WHEN OTHERS THEN -- handles all other errors 
ROLLBACK; 
END; -- exception handlers and block end here

No more unhandled exceptions should result in no more ora 06512 errors and therefore your job will stop when when requested.

Here's some good info on exception handling in oracle https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm

Hope that helps.

Upvotes: 1

Related Questions