Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create dbms_job and submit

Posted on 2013-10-28
11
Medium Priority
?
479 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

715 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