1

I'm loosing my mind. I have a procedure named foo() which takes no arguments. I'd like to execute it, let's say, every 3 minutes. The code I wrote looks like:

BEGIN
    dbms_scheduler.create_job(job_name => FooJob,
                                  job_type => 'PLSQL_BLOCK',
                                  job_action => '
                                                 BEGIN 
                                                  foo();
                                                  END;',
                                  start_date => SYSTIMESTAMP,
                                  repeat_interval => 'FREQ=MINUTELY;INTERVAL=3;BYHOUR=17;BYMINUTE=35;',
                                  enabled => TRUE
                                  comments => 'A comment.');
END;
/

This gives me an error: identificator 'applyjobpenalities' should be defined.

I based on this example: How to execute a procedure with DBMS_SCHEDULER.CREATE_JOB procedure

Also:

1) How to execute dbms_output.put_line() after execution of foo();? Is it possible to just put this line strightly away?

2) How to check if procedure foo() is (was) executing on behalf of scheduler?

UPDATE: Ok so what I've done is:

1) I typed in SQL Plus 'set serveroutput on'

2) I made a procedure:

create or replace procedure proc1
IS
BEGIN
dbms_output.put_line('sth');
end;
/

3) I changed scheduler code to:

BEGIN
dbms_scheduler.create_job(  job_name    => 'JustATest',
                                job_type    => 'PLSQL_BLOCK',
                                job_action  => 
                                                    'BEGIN 
                                                    proc1();
                                                    END;',
                                start_date  => systimestamp + interval '10' second,
                                repeat_interval => 'FREQ=SECONDLY',
                                enabled     => TRUE);
END;
/

But I can't see any result in SQL Plus. What am I missing? Both procedures compiled succesfully and I can see this job when I type:

SELECT * FROM DBA_SCHEDULER_JOBS;
Community
  • 1
  • 1
monterinio
  • 49
  • 2
  • 11
  • Read the docs - https://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin002.htm#ADMIN12039 Please show the complete error stack trace. you cannot "see" dbms_output from a background job. – OldProgrammer Dec 06 '16 at 21:30
  • proc1 is running in the background. You cannot see the output from dbms_output. Not sure how to make that any clearer. Write to a log table if needed. – OldProgrammer Dec 06 '16 at 23:18
  • I understand now, thanks. But if I use other procedure which updates data I don't see it actually running. What's wrong then? – monterinio Dec 06 '16 at 23:35

3 Answers3

2

1) There is no way to extract DBMS_OUTPUT from a scheduled job.

2) To check if FOO was executing, I use the following SQL (extracted from TOAD's "Spool SQL to Screen" option. If you are going to be spending any time at all developing in Oracle, get TOAD for Oracle).

  SELECT l.job_name
  , l.JOB_SUBNAME
       , l.log_id "Log ID"
       , l.log_date "Log Date"
       , l.operation "Operation"
       , l.status "Status"
       , l.user_name "User Name"
       , l.client_id "Client ID"
       , l.global_uid "Global UID"
       , r.req_start_date "Required Start Date"
       , r.actual_start_date "Actual Start Date"
       , r.run_duration "Run Duration"
       , r.instance_id "Instance ID"
       , r.session_id "Session ID"
       , r.slave_pid "Slave PID"
       , TO_CHAR (r.cpu_used) "CPU Used"
       , r.additional_info "Additional Info (Run)"
    FROM dba_scheduler_job_log l, dba_scheduler_job_run_details r
   WHERE l.log_id = r.log_id(+)
   and l.job_name like 'FooJob'
ORDER BY 1 DESC NULLS LAST;

2b) To see jobs that are currently running:

 SELECT *
  FROM dba_scheduler_running_jobs;

3) If you want to see results from your job, you need to have your job do something, such as insert a record into a table.

Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • So it is impossible to see notifications from an executed procedure as I posted above in the edit section? – monterinio Dec 06 '16 at 23:10
  • Yes, it is impossible. Write to a table if needed. – OldProgrammer Dec 06 '16 at 23:14
  • Fine. I changed this procedure with procedure which does bunch of select/updates operations and now when I execute this and type select from 2b) of your post I see as a result 'No rows where selected'. What am I doing wrong? – monterinio Dec 06 '16 at 23:19
  • It may be that the job ran and finished. It may also be that the job scheduler is not running. To see if jobs are queued up, execute "select * from SYS.DBA_SCHEDULER_JOBS". To see a history of jobs, execute "SELECT * FROM SYS.DBA_SCHEDULER_JOB_LOG". If these have been queued as the person who is logged in, substitute 'USER' for 'DBA'. – Brian Leach Dec 07 '16 at 23:35
0

Change Job_action => 'proc1';

And in another notepad type 

    BEGIN
    DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'justATest',
    USE_CURRENT_SESSION => FALSE);
    END;

And then execute the above code

Simas Joneliunas
  • 2,890
  • 20
  • 28
  • 35
user3236907
  • 37
  • 6
0

You can see result of dbms_output.put_line() in dba_scheduler_job_run_details column "output". Put in job action

BEGIN 
  foo();
  dbms_output.put_line( 'foo executed in job' ) ;
END;
vasu2005s
  • 21
  • 1