Thursday, January 15, 2009

ORA-02800 when use_current_session=False in DBMS_SCHEDULER.RUN_JOB

You have written following block to schedule a job in 10g

BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'ins_test',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN insert into test values(to_char(sysdate,''hh24:mi:ss'')); commit; END;',
enabled => TRUE,
comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');
END;

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_job_definition',
program_name => 'ins_test',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; byminute=1',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined by existing program and inline schedule.');
END;


When you try to run the job :

BEGIN
-- Run job asynchronously.
DBMS_SCHEDULER.run_job (job_name => 'test_prog_job_definition',
use_current_session => TRUE);
END;


You got the error:

ORA-6512: at line 1
ERROR at line 1: ORA-2800: Requests timed out
ORA-6512: at "SYS.DBMS_ISCHED", line 150
ORA-6512: at "SYS.DBMS_SCHEDULER", line 441

This is because the timezone patches are not installed on database server.
Please verify that you have installed the correct patched.

No comments: