Alternative Solution for Viewing Parallel Processes Starting through DBMS_SCHEDULER Jobs
New editing: short discussion of the main problem
(Edit: 10/03/2014) I added this discussion after some useful feedback from one of the posters looking at this topic.
Opening Notes . Other sections of the discussion mention the use of some output value from the function call itself. This is not possible due to existing DBMS_SCHEDULER functions.
There are no parameter values of type OUT or output functions associated with the notification of the state caused by the called procedure. The most immediate problem is this: how do we run a series of related tasks in parallel through a PL / SQL stored procedure? (i.e., without waiting for one of them to be completed before the start of each launch).
No matter what procedure task calls should wait for status output to complete. The response time is likely to vary widely, and any procedure calls in this way will also hang. Related processes will wait for the completion of the procedure or return of the specified result.
Recommended Approach . Other comments on this issue are on the right track. Have custom output written to a table where it can be requested later when the answer is ready. If you really want to make this a pick-up task, try placing the trigger on the output table. Each time a message of a certain value is displayed (representing the filled status of the request), a procedure with the Oracle Mail package is called, which sends your notification email.
How To Track Your Called Jobs Understanding DBMS_SCHEDULER Features
Using a scheduled task is a good way to initiate and view a set of procedure calls that are not sequentially dependent on each other. I was able to follow a similar approach using the original DBMS_JOB functionality of previous versions of the Oracle database.
Example: Using the web-based application interface (Oracle Application Express), I had a project that allowed the user to initiate an intensive series of database operations. Initiating a request was all that was needed.
Actual Use Scenario: The user did not need to wait for its completion. The problem was that connecting the web request form directly to the call of this database package, as well as its procedures, also bound the control of the form and its session, forcing the user to "wait" for the procedure itself.
Disabling the scheduled task that caused this process separated the interaction with the web page from the wait for the actual process to complete. Scheduling a job assignment was almost instantaneous, so the wait between submitting a request and returning control back to the web page also had a slight wait.
Using Oracle DBMS_SCHEDULER: Introduction to the Approach
The current problem and solution is in the Discussion section . Use your own DBMS_SCHEDULER status views to track the progress of your process. There are a lot of them, but ALL_SCHEDULER_JOB_LOG is simpler in collection and is a good start for what we are trying to accomplish.
- Use an easily identifiable name for each assignment ... as well as each assignment that may be related to each other.
- Initiate an additional task to view all parallel tasks until the latter is completed. Change this “observational” work to the end as soon as this condition is fulfilled.
The basic syntax for starting a database job in the scheduler
The DBMS_SCHEDULER.CREATE_JOB procedure creates a task in one call without using an existing program or schedule:
DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
These are the input parameters that you should pay close attention to:
job_name . You can leave it by default or organize the ordering of job requests using an agreed naming convention. JOB_NAME is a special parameter because it has a helper function called * GENERATE_JOB_NAME *, where you can specify a naming prefix to combine with an internal name assignment. This is not entirely necessary, but it helps.
DBMS_SCHEDULER.GENERATE_JOB_NAME (prefix IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2;
An example call in the create_job definition:
job_name => dbms_scheduler.generate_job_name( prefix => 'MY_EXAMPLE_JOB_')
So, in the above example, we could have a series of jobs with names like MY_EXAMPLE_JOB_0001, MY_EXAMPLE_JOB_0002, MY_EXAMPLE_JOB_0003 ...
job_type This is straight from the Oracle documentation. Most likely, it will be type: * plsql_block * (The value may also be case sensitive).
repeat_interval Do not set this value for your one-time concurrent tasks. The task will identify itself as completed as soon as the reference procedure is completed, or errors.
end_date Leave this value null or unassigned. This value is not used to execute the procedure that it is viewing once.
start_date Leave this value null or not assigned. A small value means to initiate a task as soon as the task is turned on.
included . By default, FALSE you need to set the value to TRUE as soon as you create the task, or when you are ready to initiate the process "thread".
auto_drop This is important. The rest of this method depends on the metadata of each job remaining in the DBMS_SCHEDULER log tables, even after they have thrown an exception or reached completion. Set this to FALSE.
job_action . This will depend on the number of parallel processes initiated. First, you must initiate the first of your parallel processes ... as well as the associated Monitoring process, which will be active for a particular request. The operation steps for setting the type plsql_block look something like this:
PL / SQL Block example: START my_procedure (a, b, c); END;
Establishment of a work monitoring process
Part of the problem you are facing is that DBMS_SCHEDULER can observe the process of changing the runtime, but it is not very good to let you know when it happened, or if it encountered an exception.
Your “observer” process should be another scheduled task that queries the ALL_SCHEDULER_JOB_LOG table for the procedures that it is responsible for and finds out if they all have reached the desired closing status.
We assume: for this request, you will know the number of parallel processes (remotely triggered switching events) required to complete this type of request ... all processes should not be started exactly at the same time, but the observer will need to know that he still you need to wait, even if all the related processes that he sees correspond to his criteria as "completed".
Types of tasks for which your viewing procedure should be performed:
WATCHING SQL Criteria Example: WITH MONITOR_QUERY AS ( SELECT COUNT(LOG_ID) AS COMPLETED_PROCESS_COUNT FROM ALL_SCHEDULER_JOB_LOG WHERE JOB_NAME LIKE '001-REQUEST%') SELECT CASE WHEN COMPLETED_PROCESS_COUNT = <TOTAL_PROCESSES> THEN 'DONE' ELSE 'IN-PROGRESS' END as REQUEST_STATUS FROM MONITOR_QUERY
Also note that when you invoke a task that starts the monitoring process, it may be useful for you to create a unique task name ahead of time before starting the second task (it should be performed only once per request or set of parallel tasks):
DECLARE who_am_i VARCHAR2(65); BEGIN SELECT dbms_scheduler.generate_job_name INTO who_am_i FROM DUAL; -- DBMS_SCHEDULER.CREATE_JOB (job_name => who_am_i, job_type => 'plsql_block', job_action => 'BEGIN my_monitoring_task(p_1, p_2, who_am_i); END', repeat_interval => 300, comments => 'Interval time units are defaulted to SECONDS'; ...); END;
This work is most effective if it is created at the same time or shortly after the start of the first parallel task in the series.
When the control request is completed
When the selection criteria are met (i.e. all related processes are closed in some way), then it's time to turn off your notification, as well as stop the observer for this request.
Stopping the Monitoring Job DBMS_SCHEDULER.STOP_JOB ( job_name IN VARCHAR2 force IN BOOLEAN DEFAULT FALSE);
event_name If you used a custom naming scheme for each job you initiated, you can also save this value as an input parameter to your monitoring procedure call. The observer would then know how to close himself when this is done. Remember that if you use the GENERATE_JOB_NAME function call, you specify only the prefix for the entire job name used in the scheduler.
force Set this flag to FALSE (default) or leave it undefined. Better let Oracle find a way to gracefully shut down the watcher.
Final thoughts and comments
If the result or completion of several of your procedures is interconnected, the additional scheduled task can be repeated as a “heartbeat” of monitoring to check whether all dependencies for a discrete process are fulfilled.
Cleaning comment: for this construct, * auto_drop * is set to FALSE. A daily or weekly process can also be scheduled to issue the * drop_job * command, which will clear the logs of the post scheduler related to completed and reported requests.
You can also see by including the calling * username * in the scheduled task itself, you can provide the procedures (procedures) contained in it, the ability to disconnect as soon as the correct conditions are met.