Solved

create dbms_job and submit

Posted on 2013-10-28
11
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 74

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 77

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 74

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 74

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
 
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 74

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 74

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

733 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