CODEWITHSUNDEEP
oracle-databasedbms-scheduler
user1906154
user1906154

Reputation: 57

oracle dbms scheduler job is failing

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

Upvotes: 1

Views: 536

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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;

Upvotes: 1

Related Questions