• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

create dbms_job and submit

I want to run this job every 30 minutes.
Do I have the syntax correct?

declare crlf varchar2(2):= CHR( 13 ) || CHR( 10 );
message_v varchar2(32000);
vJobNumber binary_integer;
begin
dbms_job.submit(
job => vJobNumber,
next_date => to_date('14:00','HH24:MI')
interval => (trunc(sysdate) +30/1440),what =>'
for i in
(select user_id, account_status, lock_date
from dba_users where username = 'AGENT_USER'
and account_status = 'LOCKED')
loop message_v := message_v
|| i.user_id
|| ' is in '
|| i.account_status
|| ' status since '
|| to_char(i.lock_date,'MM/DD/YYYY')
|| crlf;
end loop;
utl_mail.send
( sender => 'bev@someaccount.com',
recipients => 'bev@someaccount.com',
subject => 'AGENT_USER Account is LOCKED', message => message_v );
end;
/

Open in new window

0
bkreynolds48
Asked:
bkreynolds48
  • 5
  • 5
1 Solution
 
sdstuberCommented:
no

(trunc(sysdate) +30/1440) will schedule the job to run today at 12:30 am, even after it is done


sysdate + 30/1440 is what you want for the interval  no trunc


if you're using 10g or later (and utl_mail indicates you are) you should consider using dbms_scheduler instead
0
 
slightwv (䄆 Netminder) Commented:
Also, I don't believe you can just place a for loop in the WHAT of dbms_job.

Best case, it needs to be an anonymous pl/sql block with it's own begin/end.

Worst case, just create a procedure and call it with dbms_job.
0
 
sdstuberCommented:
also, if you want to schedule for "on" the half-hour  2:00, 2:30, 3:00, 3:30, etc.

then you want something like this...

TRUNC(sysdate, 'hh24') + FLOOR((TO_CHAR(sysdate, 'mi') + 30) / 30) * 30 / 1440

It's a little tricky in the date/time math.

Using dbms_scheduler it would be easier
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bkreynolds48Author Commented:
ok - I don't know how to use dbms_scheduler
can you give me an example of how to do this with the dbms_scheduler?
0
 
sdstuberCommented:
try something like this...
Note, I also fixed the anonymous block as noted above


Just change the strings

'YOUR_HALF_HOUR_JOB_NAME'
and
'Your Half-hour job.'

to something meaningful for your system

Also note I used the "q" syntax for the job action,  it makes it MUCH easier to embed code that has other quotes within it.


BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'YOUR_HALF_HOUR_JOB_NAME',
        job_type          => 'PLSQL_BLOCK',
        job_action        => q'[
DECLARE
    crlf      VARCHAR2(2) := CHR(13) || CHR(10);
    message_v VARCHAR2(32000);
BEGIN
    FOR i IN (SELECT user_id, account_status, lock_date
                FROM dba_users
               WHERE username = 'AGENT_USER' AND account_status = 'LOCKED')
    LOOP
        message_v :=
               message_v
            || i.user_id
            || 'is in '
            || i.account_status
            || 'status since '
            || TO_CHAR(i.lock_date, 'MM/DD/YYYY')
            || crlf;
    END LOOP;

    UTL_MAIL.send(
        sender       => 'bev@someaccount.com',
        recipients   => 'bev@someaccount.com',
        subject      => 'AGENT_USER Account is LOCKED',
        MESSAGE      => message_v
    );
END;
]',
        start_date        => TO_DATE('14:00', 'HH24:MI'),
        repeat_interval   => 'BYMINUTE=30;BYSECOND=0',
        enabled           => TRUE,
        comments          => 'Your Half-hour job.'
    );
END;
/

Open in new window

0
 
bkreynolds48Author Commented:
do I just run this in oracle?  to get it into the scheduler?
how do I see or monitor the job then?

Thanks
0
 
sdstuberCommented:
There are many view in the data dictionary, here are some of the major ones


DBA_SCHEDULER_JOBS

DBA_SCHEDULER_JOB_LOG

DBA_SCHEDULER_JOB_RUN_DETAILS


depending on privileges you may need to use ALL_ or USER_   instead of DBA_


Also see the Oracle Documentation about the scheduler package
and view.

Oracle Reference has info on all of them.
http://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm


package and types reference page
 http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235
0
 
bkreynolds48Author Commented:
I appreciate the documentation links, however please answer these

do I just run this in oracle?  to get it into the scheduler?
If I have to add something like an email address do I just run this again.

I didn't see any thing like
create or replace
so just trying to clarify
0
 
sdstuberCommented:
yes, just run it - dbms_scheudler.create_job is like dbms_job.submit_job


if you want to change it then drop the job and recreate it.
There are numerous procedures for altering various facets of a job; but for something relatively small and self-contained like this, it's probably easier to just drop and recreate
0
 
bkreynolds48Author Commented:
Thanks
0
 
bkreynolds48Author Commented:
ERROR at line 1:
ORA-27465: invalid value BYMINUTE=30;BYSECOND=0 for attribute REPEAT_INTERVAL
ORA-27420: Following  message from PL/SQL interval interpreter
ORA-00907: missing right parenthesis
ORA-27420: Following  message from ICAL interval interpreter
ORA-27415: repeat interval or calendar must start with a frequency clause
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now