Solved

How to create schedule, program and job

Posted on 2009-04-07
20
1,057 Views
Last Modified: 2013-12-18
My application should the program in three different shift. each shift have few managers. So I will create shift and assign manager for the shift. After that I will assign the program for each manager. Database should run the program whenever the responisible manger is active. Sometime I will send request to exeucte the program. that time if the manger is active then it should executed otherwise it should put it in queue and when the manager is active that time program should executed.

Please let me know how to achive this.
0
Comment
Question by:gajmp
  • 9
  • 6
  • 2
  • +1
20 Comments
 
LVL 5

Expert Comment

by:TheVeee
ID: 24086316
Database are to store things, not to process things like  a program.  You will need some type of application to run AGAINST the database to determine what time to run, and so forth.

In Java we use the Quartz scheduler framework to kick jobs off to run against the database.  Also I have written VB services that just read the clock and kick off jobs against the database.  Many ways to skin the cat on this, but one thing for sure, the database wont kick of something, only a application software will do that.

Im thinking you might be able to do this with a stored procedures in oracle that is triggered by something calling it....
0
 
LVL 3

Author Comment

by:gajmp
ID: 24086371
TheVeee
   Thanks for your reply. We can do this by using PL/SQL. I want to know What are all the objects (like Schedule, program, job, job class, window, chain, resource plan) to create and how to use it
0
 
LVL 5

Expert Comment

by:TheVeee
ID: 24086447
0
 
LVL 3

Author Comment

by:gajmp
ID: 24086477
I saw that one. But I didnt get any idea to do my requirement.  thats why i posted here
0
 
LVL 4

Accepted Solution

by:
Nouman earned 500 total points
ID: 24086704
Hi,


You can add jobs and schedules in oracle which will execute your said procedure automatically on defined time interval. you can use oracle advance queues also.

You will en-queue your request to the Oracle Queue and and the de-queue procedure will de-queue your command and take necessary actions against it.

i have attached the script to add a scheduler job.

for Advance queue sample follow the link
http://www.oracle.com/technology/sample_code/tech/dataint/index.html


BEGIN

    SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => '"MYUSER"."ACT_DQUE_CDS_SJOB"',

                                  JOB_TYPE        => 'PLSQL_BLOCK',

                                  JOB_ACTION      => 'begin

   aq_pkg.dequeue_activity_det_qmsg;

end;',

                                  REPEAT_INTERVAL => 'FREQ=SECONDLY',

                                  START_DATE      => SYSTIMESTAMP AT TIME ZONE '0:00',

                                  JOB_CLASS       => 'DEFAULT_JOB_CLASS',

                                  AUTO_DROP       => FALSE,

                                  ENABLED         => FALSE);

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME      => '"MYUSER"."ACT_DQUE_CDS_SJOB"',

                                     ATTRIBUTE => 'logging_level',

                                     VALUE     => DBMS_SCHEDULER.LOGGING_OFF);

    SYS.DBMS_SCHEDULER.ENABLE('"MYUSER"."ACT_DQUE_CDS_SJOB"');
 

	sys.dbms_scheduler.set_attribute( name => '"MYUSER"."ACT_DQUE_CDS_SJOB"', attribute => 'restartable', value => TRUE); 

	sys.dbms_scheduler.enable( '"MYUSER"."ACT_DQUE_CDS_SJOB"' ); 
 

END;

/

Open in new window

0
 
LVL 3

Author Comment

by:gajmp
ID: 24087160
Nouman:
   Thanks for your reply. I want to know how to handle the shift, manger, program. Is i have to create resource manager for each manager and create window for each shift..
0
 
LVL 4

Expert Comment

by:Nouman
ID: 24087815
Hi gajmp, are you talking about the user interface.
0
 
LVL 3

Author Comment

by:gajmp
ID: 24087852
Nouman:
   not GUI... i want to complete everything in backend... user will define shift, manager for the shift and programs which can executed by manager. System should execute the program whenever the specified manager are active.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24088537
>>Database are to store things, not to process things like a program.

You better notify Oracle, they are not aware of this. The Oracle server internals include several highly advanced compilers and runtime engines, including the Java VM and PL/SQL. Oracle is a complete application development system, not just a database.

>>but one thing for sure, the database wont kick of something, only a application software will do that.

Incorrect, as you pointed out, DBMS_SCHEDULER is actually very competent of this.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24088585
@gajmp: Are you familiar at all with PL/SQL or Java? Nouman has posted some direction, but it seems you are looking for more? Which language are you skilled with? If you have a requirement to implement in the Oracle backend, you should choose Java or PL/SQL to write the stored procedures or packages.
0
 
LVL 3

Author Comment

by:gajmp
ID: 24088716
mrjoltcola:
    im familiar with PL/SQL.. So please if you have any idea then let me know the same
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24088835
I would start by writing the individual "programs" or procedures that will be run. I assume you will also have a SHIFT table and a MANAGER table, and the MANAGER table will include each manager's assigned PL/SQL procedure name. You might have an overall SCHEDULER procedure that runs every 30 minutes or hour, based on your company's shifts, and that procedure would query the SHIFT + MANAGER tables to find out who is active and at that time, execute each PL/SQL procedure and pass the MANAGER.ID to the procedure.
0
 
LVL 3

Author Comment

by:gajmp
ID: 24201306
mrjoltcola:
   sorry for the late reply.

Thanks for your reply. I have shift, manager, program table. shift will be link to manager table and the manager will be link to program table. now i hv to create job scheuler for this. As per my knowledge we have to create schedule for rows in shift table and create program for rows in program table. and then create job based on the above two..

   I saw one more option there its window. I didnt understand the full window concepts.. but i feel we have to create resource manger based on the rows in manager table and create window based on the rows in shift and manager table with the help of RM. After that create job based on the links between the manager and program.

   So please suggest me my approach is correct or wrong and then give me one example for window
0
 
LVL 3

Author Comment

by:gajmp
ID: 24310801
Please reply to me
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24310867
1) Please provide the table structures.
2) You said "I saw one more option there its window. I didnt understand the full window concepts.."

What do you mean? Where did you see this option. I understood you were writing the scheduler in PL/SQL based on these 3 tables, but it sounds like you are referring to an existing tool. Are you talking about Oracle Enterprise Manager?
0
 
LVL 3

Author Comment

by:gajmp
ID: 24311205
mrjoltcola:
    shift table - shift_id, shift_name, st_time, end_time, max_parl_prcss
    prog table - pg_id, pg_name
    mgr table - mgr_id, mgr_name, shift_id, slp_secnd
    pg_mgr table - mgr_id, pg_id
   
Im new to this and i hv sme theoritical knwldge in this scheduler.....I saw in OEM...
please explain me
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24311728
I think you have misunderstood what the Oracle scheduler is. It is a general purpose scheduler to run "jobs", which are PL/SQL or Java programs.

Without writing code in PL/SQL or some other language, the scheduler in OEM does not help you.  Your scheduling program is a custom program that must be written first, before you even think about making use of the Oracle scheduler.

So your first step is to insert data into the tables that represent the shifts and schedules you need, and then design and write the PL/SQL program to "tick the clock" so to speak. Each time it runs it will check the time, and perform appropriate record updates to put people on and off shift, and send notifications or whatever your app requirements are. Are these requirements from your company? If so, you should work from that. Writing a complete scheduler is out of scope for this forum, in my opinion, but if you can provide more info on your requirements, I can try to help point you in the right direction. If you actually need a custom scheduler app written in Oracle, and don't have the skillset, you should consider getting a PL/SQL consultant to do it right for you.

For starters, I would write a procedure that iterates a cursor for the mgr table, joined with the shift table and updates the mgr status to ON or OFF shift, based on the current SYSDATE. Schedule the program to run every hour, if your shifts are on hour boundaries.
0
 
LVL 3

Author Comment

by:gajmp
ID: 24312079
mrjoltcola:
    Thanks for your reply...
    my application will insert the rows in alll the necessary tables. I want to create the scheduler. I am confusing how to start and what are all the object to create.
As per my knowledge we have to create PL/SQL object which will fetch the data from this table and execute the job.
    if you can thro some light on this pls help me
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24315332
I cannot teach PL/SQL on this forum, this forum is used for assistance not full learning of a language.

You should learn PL/SQL first. Buy one of the Oracle PL/SQL books

Or use Google to find samples and tutorials, then post new questions here for help on the specific question.

http://www.google.com/search?hl=en&num=20&q=oracle+pl%2Fsql+tutorial
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 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

20 Experts available now in Live!

Get 1:1 Help Now