oracle dbms scheduler job is failing

Question

I have scheduled the below job in oracle dbms scheduler , rite now the job is executing with the below error , the scheduled job is

begin
    DBMS_SCHEDULER.CREATE_JOB (
         job_name             => 'KEEP_STATS_DBNEW4',
         job_type             => 'PLSQL_BLOCK',
         job_action           => 'begin insert into my_log_table2 (MUSER,MCNT) (select osuser, count(osuser) as active_conn_count from v$session group by osuser  order by active_conn_count desc);commit;end;',
         start_date           => timestamp '2016-11-08 12:40:00',
         repeat_interval      => 'FREQ=MINUTELY;INTERVAL=10;',
         enabled              => TRUE);
end;
/

structure of the table is :-

CREATE TABLE my_log_table2
(
MUSER varchar(255),
MCNT varchar(255),
MDATE  TIMESTAMP(6)
);

error that is logged in table

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like '%KEEP_STATS_DBNEW4%'

below is the stack trace

ORA-06550: line 1, column 878:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 1, column 756:
PL/SQL: SQL Statement ignored

Alex Poole · Answer 1 · 2016-11-08T12:51:53.767

1

It's nothing to do with it being scheduled; the insert statement inside the anonymous PL/SQL block you're submitting is invalid. You have (reformatted):

begin
  insert into my_log_table2 (MUSER,MCNT)
  (
    select osuser, count(osuser) as active_conn_count
    from v$session
    group by osuser
    order by active_conn_count desc
  );
  commit;
end;

but an insert ... select should not have parentheses around the query part; it should just be:

begin
  insert into my_log_table2 (MUSER,MCNT)
  select osuser, count(osuser) as active_conn_count
  from v$session
  group by osuser
  order by active_conn_count desc;
  commit;
end;

... although the order by is probably not doing anything useful - it won't affect how the data is retrieved later.

And unless you're clearing that log table out somewhere you haven't shown, or already have a trigger doing this automatically, adding a date column set with sysdate would probably be useful; if you called that column MDATE then your job could do something like:

begin
  insert into my_log_table2 (MDATE, MUSER, MCNT)
  select sysdate, osuser, count(*)
  from v$session
  group by osuser;
  commit;
end;

edited Nov 08 '16 at 12:51

answered Nov 08 '16 at 12:36

Alex Poole

183,384
11
179
318

Thanks a lot for your timely advise request you to please show how can i add date column also, i mean in table i am adding it but in the above pl/sql block i want to add it also so that every time the name and the count of connection is stored along with the time stamp also – user1906154 Nov 08 '16 at 12:49
@user1906154 - you'd just need to include that new column name in the insert column list, and add sysdate to the select list. Updated to show that. (That assumes you have a date column rather than a timestamp - since you don't need the extra precision. If you do have a timestamp column you can use `systimestamp` instead of `sysdate`). – Alex Poole Nov 08 '16 at 12:53
@Alaex poole Thanks , so i making date column as MDATE DATE in create table statement – user1906154 Nov 08 '16 at 13:04

oracle dbms scheduler job is failing

1 Answers1