Solved

create dbms_job and submit

Posted on 2013-10-28
11
431 Views
Last Modified: 2013-10-29
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
Comment
Question by:bkreynolds48
  • 5
  • 5
11 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39606360
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39606363
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39606415
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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39606487
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39606541
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:bkreynolds48
ID: 39606794
do I just run this in oracle?  to get it into the scheduler?
how do I see or monitor the job then?

Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39606891
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
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39608509
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39608623
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
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 39608757
Thanks
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 39608828
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now