Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

create dbms_job and submit

Posted on 2013-10-28
11
Medium Priority
?
487 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 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 78

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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses

886 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