?
Solved

Schedule a Packaged Proc in Oracle.

Posted on 2011-04-21
8
Medium Priority
?
1,160 Views
Last Modified: 2012-06-27
How do i schedule a packed proc to run every moday at 8am.
For example, I would execute as follows in toad:
execute SCHEMA1.SPKG_PACKAGE1.SP_PROC1;
0
Comment
Question by:Rao_S
  • 4
  • 2
  • 2
8 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1800 total points
ID: 35443131
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'YOUR_MONDAY_JOB_NAME',
        job_type          => 'PLSQL_BLOCK',
        job_action        => 'begin SCHEMA1.SPKG_PACKAGE1.SP_PROC1; end; ',
        start_date        => trunc(next_date(sysdate,'Mon')+8/24,
        repeat_interval   => 'FREQ=weekly',
        enabled           => TRUE,
        comments          => 'Your Monday job.'
    );
END;
/
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 35443141
dbms_scheduler?
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schedadmin006.htm


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'myjob',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN  SCHEMA1.SPKG_PACKAGE1.SP_PROC1;  END;',
   repeat_interval      => 'FREQ=DAILY;BYHOUR=8',
   enabled              =>  TRUE
);
END;
/
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35443145
>> repeat_interval      => 'FREQ=DAILY;BYHOUR=8',

sdstuber beat me and is correct.  Mine was daily.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35443154
actually I have a syntax error in my start_date


 start_date        => trunc(next_day(sysdate,'Mon'))+8/24,
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1800 total points
ID: 35443189
to be even more explicit, use the byxxxx options on the repeat
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name         => 'YOUR_MONDAY_JOB_NAME',
        job_type         => 'PLSQL_BLOCK',
        job_action       => 'begin SCHEMA1.SPKG_PACKAGE1.SP_PROC1; end; ',
        start_date       => TRUNC(NEXT_DAY(SYSDATE, 'Mon')) + 8 / 24,
        repeat_interval  => 'FREQ=weekly;BYDAY=MON;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
        enabled          => TRUE,
        comments         => 'Your Monday job.');
END;
/

Open in new window

0
 

Author Closing Comment

by:Rao_S
ID: 35443523
Thanks a lot!
I changed the repeat_interval as above using 'set_attribute'.
And also ran the following select to make sure everything was fine:
select owner, job_name, job_class, job_action, start_date, repeat_interval, enabled FROM dba_scheduler_jobs;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35443551
also note,  since your process is just a procedure

you could use

job_type         => 'STORED_PROCEDURE',
job_action       => 'SCHEMA1.SPKG_PACKAGE1.SP_PROC1'

no real advantage one way or the other, but this one might be more intuitive
0
 

Author Comment

by:Rao_S
ID: 35443697
When I changed the  job_type it becane 'enabled' FALSE.
I tried
BEGIN
DBMS_SCHEDULER.set_attribute (
    name       => 'ERRORS_PURGE_ROUTINE',
    attribute   => 'enabled',
    value        => TRUE);
END;
I get the following error:
ORA-27469: ENABLED is not a valid job attribute
ORA-06512: at "SYS.DBMS_ISCHED", line 2792
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1895
ORA-06512: at line 2
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
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 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
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 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