Menu
I have a webpage (generated via PL/SQL) that allows someone to toggle a remote device on or off. They are presented with a list of devices and they use checkboxes to select the ones to toggle. UTL_HTTP is used to communicate with the devices. Currently, the devices are toggled serially. Once all have been toggled, an email is sent to the user. Depending on how many devices are selected, doing this serially has the potential to take too long. So I'm looking at using DBMS_SCHEDULER to execute the toggling in parallel.
The problem is that the toggling process returns a status, either 'OK' or the reason it failed. I need that result to include in the email to the user. So, I need the 'main' procedure to create the SCHEDULER jobs and then wait for them to finish (and somehow get their statuses) before sending an email to the user.
Is this possible, short of having each job write it's status to a table which is polled by the 'main' process? I've read references to DBMS_PIPE for inter-process communication, but haven't found a good example (ie, one that makes sense to me) showing how to do it.
252
For auto drop,This flag if TRUE, causes a job to be automatically dropped after it has completed or has been automatically disabled. A job is considered completed if: 1. Its end date (or the end date of the job schedule) has passed. 2.It has run max_runs number of times. max_runs must be set with SET_ATTRIBUTE.
The dbms_scheduler.create_job procedure is used to create scheduler jobs. Here is an example of it being used to call a stored procedure: begin. dbms_scheduler.create_job (
Answer: As a review, the dbms_job.submit procedure accepts three parameters, the name of the job to submit, the start time for the job, and the interval to execute the job: dbms_job.submit( what=>'statspack_alert.sql;', next_date=>sysdate+1/24, -- start next hour.
select log_id, log_date, owner, job_name from ALL_SCHEDULER_JOB_LOG where job_name like 'RMAN_B%' and log_date > sysdate-2; select log_id,log_date, owner, job_name, status, ADDITIONAL_INFO from ALL_SCHEDULER_JOB_LOG where log_id=113708; DBA Scripts.
If there's a way to do it, I couldn't figure it out. I ended up having each job write it's status to a table. The main process knows how many individual jobs were created and polls the table to tell when all jobs are finished (or it timesout after a specified amount of time has passed, in case one of the jobs dies for some reason).
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
