Search code examples
plsqlplsqldeveloper

DBMS_SCHEDULER.CREATE JOB does not working with DBMS_OUTPUT.PUT_LINE?

I've been searching some Internet before asking this question, but I've not found answer. Short question - is Oracle's job working with DBMS_OUTPUT? I want to write on console some text at given interval, but it's not working for me. For example, I want after every 3 seconds write timestamp on console.

Job - does not work.

Job + program, also does not work.

job + program + scheduler, does not work...

Could anyone give me some hints how to get it? :)

My example code:

begin
dbms_scheduler.create_job (
job_name =>        'some_job',
job_type =>        'PLSQL_BLOCK', 
job_action =>      'BEGIN DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP); END;',
start_date =>       SYSTIMESTAMP,
repeat_interval => 'FREQ=SECONDLY; INTERVAL=3',
end_date =>         NULL,
enabled =>          TRUE,
comments =>        'Example job.');
end;
Solution

  • Each job is run in its own sessions separate to yours; your console would only be listening to the output from your session, so your job is merely speaking into the void and its output buffer is cleared before being written anywhere.

    Since DBMS_OUTPUT is only really intended as a very basic debug output generator, this is not a problem. If you want to see the progress of your job in real time, here are two options to consider:

    1. Insert log entries to a custom table (perhaps commit using an autonomous transaction) - or even better, use an existing logging framework like Logger.

    2. Update the session using DBMS_APPLICATION_INFO (e.g. use set_action or set_session_longops) - this would allow you to see the current state of the job only, however.