Thank You
A reader, July 20, 2002 - 11:00 am UTC
Very Helpfull!
very useful
Ariel Cary, November 25, 2002 - 3:45 pm UTC
Tom,
Very interesting way to implement paralellism.
And now, the next step would be to think of a way to get these processes in sync... that is how to be aware of which processes have finished and which ones haven't yet. (suppose all five processes have carried out a piece of work of a big task, and then you need to get all the small result together).
What would be an effective way to get this?
Appreciate your comments.
November 25, 2002 - 4:04 pm UTC
dbms_job.submit( l_job,
'procedure_1; dbms_alert.signal(...); commit;' );
then, the query coordinator, if you will , waits for the alerts to come in.
That would be one method.
dbms_lock.request
Anthony, November 26, 2002 - 8:00 am UTC
We have almost the same requirement as posted by Ariel. We implement dbms_lock.request and dbms_lock.release. Before we submit the job we issue dbms_lock.request to lock that process and issue dbms_lock.release if the process is successful and if the process encountered an error (of course theres is a handling, I mean, if this process is a pre-requisite of another process it will not submit the next process ). My question is, is this approach the correct one or we should use dbms_alert instead? Will it consume CPU while waiting for the previous process to finish??
TNX..
November 26, 2002 - 8:09 am UTC
how do you know to release the lock?
dbms_alert would be useful for the background processes to alert the foreground parent process "I am done"
I'm not sure how you are using dbms lock to do the same
Using DBMS_LOCK
Anthony V, November 26, 2002 - 8:41 am UTC
It goes like this:
We have a package called batch_process. It has batch coordinator procedure, lock_process function that locks the process sequence number, then the job_submitter.
First, we loop to a table that holds all the process together with process sequence number (inside batch_coordinator), we then call the lock_process to try and lock that old process_seq_no (lockid->process_seq_no, timeout->0) and return if we successfully obtain the lock otherwise if the old process is still executing we wait for it (timeout->maxwait), if the batch_coordinator detects that it is safe to submit this job, it calls the job_submitter. inside the job_submitter it has dbms_lock.release(process_Seq_no). dbms_lock.release(process_Seq_no) is issued if we successfully executed the procedure or an error was encountered. then we save that current process_seq_no to a variable to know what was the last process executed.
we already tested this and it is currently running. My question is, is this approach the correct
way or we should use dbms_alert instead? Will it consume CPU while waiting for
the previous process to finish when using dbms_lock.req(timeout->dbms_lock.maxwait)??
Thanks for your comments.
November 26, 2002 - 9:06 am UTC
you are doing it backwards from the way they are -- your approach is sound for what you are doing.
They want to run N jobs, wait for them to finish and then continue on. For them -- getting ALERTED that the job is done is appropriate.
You -- you want to see if the job is already running. In which case, lock is appropriate.
As far as I can tell, this is apples and oranges -- totally different.
Using dbms_lock
Anthony V, November 26, 2002 - 9:36 am UTC
So you're saying that we can use this as long as it served our purpose. We dont have to worry about the resources that it will consume while waiting for the process to finish...
Tnx again..
November 26, 2002 - 9:39 am UTC
when waiting on dbms_lock to return -- you aren't consuming resources like cpu - you are blocked.
And what if this processing was local to the DB?
Matt, November 26, 2002 - 6:13 pm UTC
As stated earlier, an interesting way to implement parallelism.
So what if I have a data manipulation process (select data, carry out calculation on returned data and insert the result) that takes a long time to run. Would I be better to segment the data that is being processed (in the initial select) and parallelise this process using the dbms_job approach (and therefore parallelise the entire process), or use parallel query (to parallelise the select)?
My guess at the moment is that it is the select that is taking up the most of the time for this process to run....I think I can hear your answer already.
November 26, 2002 - 6:52 pm UTC
It totally depends
Does the select take forever but the processing is very very very fast. Parallel query to speed up the select.
Does the select go ok but the processing is very compute intensive and somewhat slow. Parallelize YOUR operations then, divide and conquer with your code.
Hope that was the answer you were anticipating ;)
Execute same procedure repeatedly
yuga chodagam, February 12, 2003 - 7:52 pm UTC
Tom,
What if I want to execute same procedure concurrently. Is there a way to do that. For example. I have a procedure that processes school information. It takes one school at a time does some processing and then does some aggregates for each school in a different procedure. This aggregate procedure I want to call concurrently....Here goes some pseudo code..
procedure process_school as
begin
for rs in (select schools )loop
do something with <school 1>;
...................
...................
school_aggregate(school1);
end loop;
exception
When no_data_found then
Null;
end;
When I call school_aggregate(school1), I do not want to wait until it finishes but process next school(ex:school2) in the loop and come and fire school_aggregate(school2)for school2 (May be the school_aggregate(school1)is still running) and on and on. Is it possible? If not through PL/SQL is it possible in Java using threads?
Thanks for all your help Tom
Yuga.
February 12, 2003 - 9:52 pm UTC
you can accomplish that to a degree using dbms_job as shown above.
Running procedures in parallel
Mohan, September 16, 2003 - 6:56 am UTC
In a procedure I am submiting 8 jobs to run in parallel in 4 CPU machine. All the 8 threads run the same procedure with different data. At the end I have to merge all the results. The end user will get a prompt only after last operation is complete. The questions I have are
1. I have to monitor the progress of each thread from the main procedure. If I monitor in a loop then the main procedure also will occupy lots of CPU time. Is there any function called "sleep" in Oracle like in UNIX. If I write a sleep routine as external procedure in C will it work. Can the same thing be done in Windows NT/2K environment.
2. If the procedure is executed by another user, in which user's session/privileges the job submited by dbms_job will run.
3. How can I see the error messages produced by the procedure where submitted through dbms_job
Mohan
September 16, 2003 - 8:41 am UTC
1) dbms_lock.sleep
2) if the job is a definers rights stored procedure, the procedure will run as the definer -- as normal.
if the job is an anonymous plsql block of code, the job will run as the submitter of the job.
3) as they are stored in the alert log, you would have to DIY -- eg, instead of submitting:
dbms_job.submit( l_job, 'p;' );
submit:
dbms_job.submit( l_job, 'begin p; exception when others then insert ( a log message into a table with the job id); commit; RAISE; end;' );
Query coordinator.
A reader, October 21, 2003 - 12:56 pm UTC
Tom:
I have a question about your suggestion..
"
dbms_job.submit( l_job,
'procedure_1; dbms_alert.signal(...); commit;' );
then, the query coordinator, if you will , waits for the alerts to come in.
That would be one method.
"
Is this the correct way to do this?
DBMS_ALERT.REGISTER('P1');
DBMS_ALERT.REGISTER('P2');
DBMS_ALERT.REGISTER('P3');
DBMS_ALERT.REGISTER('P4');
DBMS_ALERT.REGISTER('P5');
...
DBMS_JOB.SUBMIT(l_job, 'P1; DBMS_ALERT.SIGNAL(''P1'',''msg1''); COMMIT;');
DBMS_JOB.SUBMIT(l_job, 'P2; DBMS_ALERT.SIGNAL(''P2'',''msg2''); COMMIT;');
DBMS_JOB.SUBMIT(l_job, 'P3; DBMS_ALERT.SIGNAL(''P3'',''msg3''); COMMIT;');
DBMS_JOB.SUBMIT(l_job, 'P4; DBMS_ALERT.SIGNAL(''P4'',''msg4''); COMMIT;');
DBMS_JOB.SUBMIT(l_job, 'P5; DBMS_ALERT.SIGNAL(''P5'',''msg5''); COMMIT;');
...
...
DBMS_ALERT.WAITONE('P1',msg1, status1);
DBMS_ALERT.WAITONE('P2',msg2, status2);
DBMS_ALERT.WAITONE('P3',msg3, status3);
DBMS_ALERT.WAITONE('P4',msg4, status4);
DBMS_ALERT.WAITONE('P5',msg5, status5);
if status1=0 and status2=0 and status3=0 and
status4=0 and status5=0
then do whatever that needs to be done to consolidate results from procedures P1,P2,P3,P4 and P5;
Tom, am I in the right path here?
Thanks.
October 21, 2003 - 5:19 pm UTC
yes, you cold waitany as well and just collect the messages from whomever finished first.
Creating Indexes in Parallel
Doug Wingate, October 21, 2003 - 7:28 pm UTC
I know it's possible that the alternatives I'll list would just have to be tested and compared for their relative performance, but I wonder if you have any general comment. A process creates a table, loads it, creates numerous bitmap indexes on it, and then partition-exchanges the table into a partitioned table. The bitmap indexes are presently created one at a time with parallel DDL. In principle, one could create multiple indexes "in parallel" using the DBMS_Job design discussed in this thread. Is there conceivably any benefit in introducing the parallel DBMS_Job design either in place of parallel DDL or in addition to it? Can any general observation be made at all or does one just need to try various possibilities and compare them? Thanks for your comments.
October 21, 2003 - 9:51 pm UTC
well, there could be some contention introduced by creating many indexes on the same segment at the same time -- as opposed to just increasing the degree of parallelism on the existing creates.
but -- yes -- benchmarking is the way to go.
A reader, October 22, 2003 - 4:13 pm UTC
Controlling the concurrent sessions
Pushparaj Arulappan, January 12, 2004 - 4:15 pm UTC
Tom,
Is there any mechanism available in oracle to execute
two procedures concurrently but the COMMIT or ROLLBACK
of each one should be dependend on the others.
If one fails then both procedures should rollback and
commit should be issued when only both executes sucessfully.
My requirement is something like this:
create or replace procedure driver_proc
as
begin
submit proc1; (do not commit by itself)
submit proc2; (do not commit by itself)
if (both are successfull) then
issue a commit;
else
rollback;
end if;
end;
/
Please advice.
Thanks
Pushparaj
January 13, 2004 - 1:13 am UTC
Umm, that is the way it works.
that is the way transactions "work"
this is not sqlserver!
(if you want lots of details on this -- server concepts guide and/or my book "Expert one on one Oracle" -- i cover this in detail)
Dave, January 13, 2004 - 12:06 pm UTC
Is it possible (and I think it is) that the two concurrent child procedures could communicate with the parent procedure (through DBMS_PIPE, I guess), and could signal their individual completions to the parent. They then wait until the parent signals them to commit or rollback, based its receipt of "successful completion" messages from both children?
Not a perfect solution by any means, but a possible one.
Re: Creating Indexes in Parallel
Dave, January 13, 2004 - 12:14 pm UTC
Thescenario that Doug describes, of creating/rebuilding multiple index partitions simultaneously followinga partition exchange, is exactly the method I have implemented myself.
I have a procedure that selects the index name and partition name of all unusable indexes on a given table (the name of which is passed as the sole parameter to the procedure).
The procedure submits index partition rebuild statements through DBMS_JOB, and waits for all of them to finish.
One important feature was that when the index partition rebuild statements were submitted through DBMS_JOB, they were ordered by partition_name, not index name. This promoted the simultaneous rebuild of different indexes on the same table partition, and tended to avoid the undesirable scenario of multiple table partitions being read simultaneously.
What about passing parameters with the Stored Procedure.
Jeff, June 14, 2004 - 12:08 pm UTC
Tom,
Very good discussion. How would it be setup if you had variables associated with your stored proc like the following:
Current stored proc:
PBK_Log_Review
(
vv_start_dt => vv_start_dt,
vv_end_dt => vv_end_dt,
vv_list_of_locations => vv_list_of_locations,
vv_exec_id => vv_exec_id,
vv_rc => vv_rc
);
How could I use DBMS_JOB.SUBMIT to make this work, assuming I have a jobno variable in place? Thanks
June 14, 2004 - 1:07 pm UTC
I like to do this:
create table pkg_log_review_parameters
( job_id number primary key,
vv_start_dt date,
vv_end_dt date,
......
)
/
and then schedule the job like this:
declare
l_job number;
begin
dbms_job.submit( l_job, 'pbk_log_review( JOB );' );
insert into pbk_log_review_parameters
( job, vv_.....)
values
( l_job, ...... );
commit;
end;
/
and have the procedure that is run in the background simply:
create procedure pbk_review_parameters( p_job in number )
is
l_rec pbk_log_review_parameters;
begin
select * into l_rec from pbk_log_review_parameters where job = p_job;
......
delete from pbk_log_review_parameters where job = p_job;
commit;
end;
/
if you have access to my book "Expert one on one Oracle" -- i cover this in detail in the appendix on dbms_job.
Can dbms_job be used in this kind of situation
Unknown, August 05, 2004 - 3:00 pm UTC
Greetings Tom,
Thanks for having such a great site.
Here is my question.
I want to call a procedure asynchronously from a trigger. This is what i am doing.
I have a for each row trigger defined on table T1, that calls pkg1.prc1. There is one other process (pkg2.prc2) which inserts into T1. Both pkg1.prc1 and pkg2.prc2 have different functionality implemented in them. How do i call pkg1.prc1 asynchronously to return control to pkg2.prc2(from trigger) immediately and not worry about what is happening in there.
Can we use dbms_job for this and how? Is there any other approach for this?
Thanks
August 05, 2004 - 8:25 pm UTC
dbms_job will SAFELY run the procedure pkg2.prc2 after the statement that fired the trigger is committed.
to see "how", i would start with the supplied packages guide which documents dbms_job and if you have Expert one on one Oracle -- you can read in there as to how to best use it (did a section on it in the appendix)
DBMS_JOB for asynchronous procedure call
Mark, April 28, 2005 - 2:10 pm UTC
Hi Tom,
I am using DBMS_JOB to call a procedure asynchronously. My code to do so is very simple:
dbms_job.submit(l_job,
l_job_what, sysdate + 1/24/60/5);
commit; --this is committing an autonomous transaction so that the job is submitted
I am not specifying an interval because the default is null. I want the job to run once then self destruct.
I had originally been using sysdate (the default) as the next_date parameter, but then I changed it to sysdate + 1/24/60/5 because some jobs were being ignored. That is, they sat around and were never run. This seemed to happen more frequently if I submitted several jobs at the same time (within less a second). It seemed almost as if DBMS Jobs couldn't keep up. But now the problem is occurring again and the occasional job is not running.
Right now I am running these stuck-in-limbo jobs manually. If I had to conjecture as to why this is occurring, I would say that 5 seconds elapse between the dbms_job.submit and when the commit completes (see my code above), and so by the time the job is submitted, the next_date is actually in the past, not the future. I know jobs retry when they fail, but these jobs don't even get a chance to fail!
If my conjecture is accurate, how might I go about getting it to run every time?
Thanks a bunch!
April 28, 2005 - 2:32 pm UTC
just use
dbms_job.submit( l_job, l_job_what );
you don't want a next or interval for one offs.
Mark, April 28, 2005 - 2:47 pm UTC
Thanks Tom, I will try that.
If you could quickly explain why that works I'd appreciate it. The reason I request that is because I had been passing SYSDATE as the value of next_date. Looking at the documentation for DBMS_JOB.SUBMIT, I see that the default value for next_date is SYSDATE. Seems to me like six of one, half dozen of the other.
Thanks.
April 28, 2005 - 2:55 pm UTC
I've never once had a job submitted via dbms_job.submit(x,y); not run.
I use jobs probably more than most.
make sure the job isn't "broken"
Mark, April 29, 2005 - 11:54 am UTC
According to OEM, the status of these jobs is always "Normal" as opposed to "Broken". # Failures is always 0. I did what you said, i.e.
dbms_job.submit( l_job, l_job_what );
but the problem still occurs. I am going to open a TAR, but if you have any more ideas in the meantime I'd appreciate hearing them.
Thanks again,
Mark
April 29, 2005 - 12:02 pm UTC
nope, never seen it happen -- nor have i heard of it.
(scan your alert log too, if a job fails, it is recorded there, use sqlplus, just query user_jobs and see what you see as far as failures/broken and so on)
Procedure to create index'es in parallel
A Reader, June 15, 2006 - 1:20 pm UTC
Hi,
heard someone mention they have developed a procedure to kick-off rebuild of multiple indexes on a table in parallel using dbms_jobs
i need to build something like that -
is the approach to :
- query the catalog to get unuasble indexes
- k/o dbms_jobs for each rebuild
- wait for all of them to finish
- when all finish - return sucess
- if any one fails - return failure (bit iffy here - doesn't dbms_jobs keep trying - hate that 'feature' - how did you deal with that.
any pointers (including code ;-) would be appreciated
June 16, 2006 - 6:25 pm UTC
Here is a mini framework to get started with. Function foo, table T and the indexes on table T are for "the demo".
ops$tkyte@ORA10GR2> create or replace
2 function foo( x in number )
3 return number
4 deterministic
5 as
6 begin
7 return 0;
8 end;
9 /
Function created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t
2 as
3 select * from all_objects;
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create index t_idx1 on t(object_id) UNUSABLE;
Index created.
ops$tkyte@ORA10GR2> create index t_idx2 on t(object_name) UNUSABLE;
Index created.
ops$tkyte@ORA10GR2> create index t_idx3 on t(object_type) UNUSABLE;
Index created.
ops$tkyte@ORA10GR2> create index t_idx4 on t(foo(object_id)) UNUSABLE;
Index created.
ops$tkyte@ORA10GR2> drop function foo;
Function dropped.
ops$tkyte@ORA10GR2>
<b>this is our job table - how we pass parameters to the rebuild jobs and how they report back to us:</b>
ops$tkyte@ORA10GR2> create table index_rebuild
2 ( job number primary key,
3 iname varchar2(30) not null,
4 submitted timestamp not null,
5 started timestamp,
6 finished timestamp,
7 status varchar2(20) not null check (status in ('SUBMITTED','RUNNING','COMPLETED','FAILED')),
8 error_msg varchar2(4000)
9 )
10 /
Table created.
<b>this rebuilds our indexes and note that it never really fails (unless the update in the exception block fails of course, that is left as an exercise for you to "correct" if you want - but that is how to not have the job resubmit itself - just let it ALWAYS succeed, but report clearly that "there was an error")</b>
ops$tkyte@ORA10GR2> create or replace procedure rebuild_idx( p_job in number )
2 as
3 l_iname index_rebuild.iname%type;
4 l_error_msg varchar2(4000);
5 begin
6 update index_rebuild
7 set started = systimestamp,
8 status = 'RUNNING'
9 where job = p_job
10 returning iname into l_iname;
11
12 execute immediate 'alter index ' || l_iname || ' rebuild';
13 update index_rebuild
14 set finished = systimestamp,
15 status = 'COMPLETED'
16 where job = p_job;
17 commit;
18 exception
19 when others then
20 l_error_msg := sqlerrm;
21 update index_rebuild
22 set status = 'FAILED',
23 error_msg = l_error_msg
24 where job = p_job;
25 end;
26 /
Procedure created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select index_name from user_indexes where status = 'UNUSABLE';
INDEX_NAME
------------------------------
T_IDX4
T_IDX3
T_IDX2
T_IDX1
<b>t_idx4 cannot rebuild, it'll fail - but 1..3 should</b>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
2 l_cnt number;
3 l_job number;
4 begin
5 for x in ( select * from user_indexes where status = 'UNUSABLE' )
6 loop
7 dbms_job.submit( l_job, 'rebuild_idx(JOB);' );
8 insert into index_rebuild (job,iname,submitted,status)
9 values ( l_job, x.index_name, systimestamp, 'SUBMITTED' );
10 end loop;
11 commit;
12 loop
13 dbms_lock.sleep( 5 );
14 select count(*) into l_cnt from index_rebuild where status in ( 'SUBMITTED', 'RUNNING' );
15 exit when l_cnt = 0;
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
<b>that waited ... and then we can report:</b>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec print_table( 'select * from index_rebuild' );
.JOB : 94
.INAME : T_IDX4
.SUBMITTED : 16-JUN-06 06.18.59.530094 PM
.STARTED : 16-JUN-06 06.19.00.320585 PM
.FINISHED :
.STATUS : FAILED
.ERROR_MSG : ORA-30550: index depends on a package/function
spec/body which is not valid
-----------------
.JOB : 95
.INAME : T_IDX3
.SUBMITTED : 16-JUN-06 06.18.59.535525 PM
.STARTED : 16-JUN-06 06.19.00.404044 PM
.FINISHED : 16-JUN-06 06.19.01.204975 PM
.STATUS : COMPLETED
.ERROR_MSG :
-----------------
.JOB : 96
.INAME : T_IDX2
.SUBMITTED : 16-JUN-06 06.18.59.541486 PM
.STARTED : 16-JUN-06 06.19.00.346727 PM
.FINISHED : 16-JUN-06 06.19.01.231391 PM
.STATUS : COMPLETED
.ERROR_MSG :
-----------------
.JOB : 97
.INAME : T_IDX1
.SUBMITTED : 16-JUN-06 06.18.59.546626 PM
.STARTED : 16-JUN-06 06.19.00.401484 PM
.FINISHED : 16-JUN-06 06.19.01.130064 PM
.STATUS : COMPLETED
.ERROR_MSG :
-----------------
PL/SQL procedure successfully completed.
RE: Query coordinator
Mike, October 10, 2006 - 10:53 am UTC
Tom,
I'm trying to do something similar to what was posted in "Query coordinator." on October 21, 2003. The issue I'm having is that the procedures I call have return codes and messages. There are times where I will get a return code that indicates not to continue dependant processes, even though we let the proc finish successfully. Here is my code:
set timing on
set serveroutput on
declare
vMessage1 varchar2(255);
vMessage2 varchar2(255);
vStatus1 pls_integer;
vStatus2 pls_integer;
vRetCd1 NUMBER;
vRetCd2 NUMBER;
vRetCd3 NUMBER;
vTimeOut pls_integer := 3600;
vJob pls_integer;
begin
--***Register Alert Messages***
DBMS_ALERT.REGISTER('PKG.PROC1');
DBMS_ALERT.REGISTER('PKG.PROC2');
--***Submit Jobs***
DBMS_JOB.SUBMIT(vJob, 'PKG.PROC1(''2006'',''09'',???vRetCd1???); DBMS_ALERT.SIGNAL(''PKG.PROC1'',''PKG.PROC1 Complete''); COMMIT;');
DBMS_OUTPUT.PUT_LINE('JOB NUMBER: ' || to_char(vJob));
DBMS_JOB.SUBMIT(vJob, 'PKG.PROC2(''2006'',''09'',???vRetCd2???); DBMS_ALERT.SIGNAL(''PKG.PROC2'',''PKG.PROC2 Complete''); COMMIT;');
DBMS_OUTPUT.PUT_LINE('JOB NUMBER: ' || to_char(vJob));
--***Wait for Alert Messages***
DBMS_ALERT.WAITONE('PKG.PROC1',vMessage1, vStatus1);
DBMS_OUTPUT.PUT_LINE(vMessage1);
DBMS_ALERT.WAITONE('PKG.PROC2',vMessage2, vStatus2);
DBMS_OUTPUT.PUT_LINE(vMessage2);
--***RUN PROC 3 if Return Code (from Proc1) is Greater than Zero***
If vRetCd1 > 0 then
DBMS_JOB.SUBMIT(vJob, 'PKG.PROC3(''2006'',''09'',???vRetCd3???); DBMS_ALERT.SIGNAL(''PKG.PROC3'',''PKG.PROC3 Complete''); COMMIT;');
end if;
END;
/
Basically, I don't know how to use the Return Codes properly (see the "???vRetCd???" 's) in my Job Submits. Is this even possible? Thanks in advance!
October 10, 2006 - 12:21 pm UTC
not really sure what you mean.
Sorry...One More Try...
Mike, October 10, 2006 - 1:41 pm UTC
Sorry for the confusion. The mysterious "???vRetCd1???" (Return Code) is an OUT Parameter of the called procedure.
Here is the Procedure definition:
PROCEDURE PROC1(I_PROD_YEAR IN VARCHAR2,
I_PROD_MTH IN VARCHAR2,
O_RETURNCD IN OUT NUMBER);
When I call the Procedure using DBMS_JOB, how do I get my O_RETURNCD (OUT Parm) back? Normally I'd run:
PKG.PROC1(PROD_YEAR,PROD_MTH,RETURN_CD);
and RETURN_CD get's passed back from the procedure. But when using DBMS_JOB, I'm not sure of the syntax for an IN/OUT parameter. I can pass the IN parameter no problem. I'm getting hung up on the OUT.
Is this even possible? Maybe I need a middle procedure that can be called using DBMS_JOB, can handle the RETURN_CD, and can send different Alerts back based on the return code?
Hope that helps...Thanks again!
October 10, 2006 - 8:14 pm UTC
you would/could schedule:
dbms_job.submit( l_job, '
declare
l_returncd number;
begin
pkg.proc1( ...., l_returncd );
if (l_returncd <> 0 )
then
raise_application_error( -20001, ''Failed!!!'' );
end if;
end;' );
Thanks.
Mike, October 20, 2006 - 3:38 pm UTC
Thanks for the response. I didn't think it was possible. Expanding on your thought, I've created a procedure so I can pass run any procedure, but I'm getting an error message. I returned the dbms_job command string it's creating and it seemed like a valid command, so I'm stumped.
Here is the Proc:
(note: I know I can probably set the submitted proc's parameters better, but that's my simple way. Also, ignore the last parameter. Different procs have differnt numbers of returnd codes and/or messages so I use this to add more OUT parms if needed)
PROCEDURE SUBMIT_PROCEDURE_WITH_DBMS_JOB(
I_PKG_NAME IN VARCHAR2,
I_PROC_NAME IN VARCHAR2,
I_PARAMETERS IN VARCHAR2,
I_RETURNCD_TYPE IN INTEGER
)
IS
V_JOB PLS_INTEGER;
V_SQL VARCHAR2(4000);
BEGIN
--***Register Alert Message***
DBMS_ALERT.REGISTER('''||I_PROC_NAME||''');
--***Submit Proc via DBMS_JOB***
dbms_job.submit( V_JOB, '
declare
V_RETURNCD number := 1; --***Initalize return code
begin
'||I_PKG_NAME||'.'||I_PROC_NAME||'('||I_PARAMETERS||', V_RETURNCD );
if (V_RETURNCD > 0 ) then
DBMS_ALERT.SIGNAL('''||I_PROC_NAME||''','''||I_PROC_NAME||' Complete.'');
COMMIT;
else
DBMS_ALERT.SIGNAL('''||I_PROC_NAME||''','''||I_PROC_NAME||' Failed!!!'');
COMMIT;
end if;
end;' );
EXCEPTION
WHEN Others THEN
DBMS_OUTPUT.PUT_LINE('Error Executing: '||I_PROC_NAME);
DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE);
DBMS_OUTPUT.PUT_LINE(substr(SQLERRM,1,255));
DBMS_OUTPUT.PUT_LINE(substr(SQLERRM,255));
END SUBMIT_PROCEDURE_WITH_DBMS_JOB ;
and my exec statement:
set timing on;
set serveroutput on;
BEGIN
PSYS.PSYS_STATUS_PKG.SUBMIT_PROCEDURE_WITH_DBMS_JOB ( 'pkg', 'proc', '''200609''', 1 );
COMMIT;
END;
/
Error:
Error Executing: PROC
SQLCODE=-6550
ORA-06550: line 1, column 93:
PLS-00103: Encountered the symbol "" when
expecting one of the following:
begin case declare exit for goto if loop mod
null pragma
raise return select update while with <an identifier>
<a
double-quoted delimited-ide
entifier> <a bind variable> <<
close current delete fetch lock insert open
rollback
savepoint set sql execute commit forall merge
<a single-quoted
SQL string> pipe
Any thoughts?
October 20, 2006 - 5:01 pm UTC
why all of the ''?
DBMS_ALERT.REGISTER('''||I_PROC_NAME||''');
that waits for an alert named 'I_PROC_NAME', not just I_PROC_NAME
but anyway - your procedure there does not "wait" for the job to run at all either? It registers for an alert and then goes away?
Job is failing but not sending the alert
Prashant Tambe, October 10, 2007 - 10:35 am UTC
Tom,
In your answer above to Mohan, you said:
Submit the jobs like this:
dbms_job.submit( l_job, 'begin p; exception when others then insert ( a log message into a table
with the job id); commit; RAISE; end;' );
I am submitting a job like this:
-- Register an interest in the alert.
dbms_alert.register('P1');
-- Submit a job
DBMS_JOB.SUBMIT(job => vv_job1,
WHAT => 'begin P1(JOB); exception when others then raise; end;DBMS_ALERT.SIGNAL(''P1'',''MSG1'');COMMIT;');
-- Insert into a job parameter table.
INSERT INTO job_parameters VALUES (vv_job1, 1, 'P1', v_group_label);
COMMIT;
-- Wait for the job to finish.
dbms_alert.waitone('P1', v_msg, v_status1);
If (v_status1 = 0) then
dbms_output.put_line('P1 finished');
else
dbms_output.put_line('P1 timed out');
end if;
The problem I am having is that even with the 'exception when others then raise' in the WHAT clause of the dbms_job.submit call, my procedure just waits forever at the DBMS_ALERT.WAITONE line even though I can see the job failed in user_jobs view. The 'FAILURES' column keeps increasing from 1 to 2 to 3... But the DBMS_ALERT.SIGNAL never sends a signal and DBMS_ALERT.WAITONE never receives the signal.
What is the problem? How can I solve this?
Do you know?
Prashant Tambe, October 28, 2007 - 12:47 pm UTC
Tom,
Do you know why my above program doesn't work? I did exactly as you have suggested in this thread. But I am not getting any alert generated.
October 29, 2007 - 12:46 pm UTC
you have coded:
DBMS_JOB.SUBMIT(job => vv_job1,
WHAT => 'begin P1(JOB); exception when others then raise;
end;DBMS_ALERT.SIGNAL(''P1'',''MSG1'');COMMIT;');
please, think about the flow of:
begin
something;
exception
when others
then raise;
end;
dbms_alert.signal(...);
If the exception is raised by something, then control goes to that (horrible) when others you have coded, which in turn raises the exception again.
When it raised the exception again - where does control go? Once you answer that, you'll know why the alert is never fired.
If you meant to
a) catch any exception
b) and then raise an alert
c) and then silently ignore it, so the job just "appears to succeed"
you meant to put the alert where you have the raise and remove the raise.
if you meant to
a) catch any exception
b) and then raise an alert
c) and then fail - allowing the job queue to record the error in the alert log and retry your job
then you meant to put the alert call right before the raise.
Embarrassing, indeed!
Prashant Tambe, October 30, 2007 - 11:05 am UTC
You are correct. I see. That was rather embarrassing!
The last thing I want to know is where would I signal the alert when the job does succeed? Code it like following? Call it twice (once when it succeeds and also when it fails)?
begin
P1(JOB);
dbms_alert.signal(...);
exception
when others
then dbms_alert.signal(...);
end;
October 30, 2007 - 1:20 pm UTC
well, technically you are not calling it twice - unless of course the dbms_alert.signal itself is what fails!
but yes, that will invoke dbms_alert upon P1 being successful or upon P1 or dbms_alert failing.
useful guidance but not completely implemetable
Abhijat, October 07, 2013 - 5:54 pm UTC
Hello Tom,
Thanks for discussing this post in such details.
I am testing using
DECLARE
X NUMBER :=1 ;
BEGIN
SYS.DBMS_JOB.SUBMIT ( job => X, what => 'begin procedure_1; end;' );
SYS.DBMS_JOB.SUBMIT ( job => X, what => 'begin procedure_2; end;' );
SYS.DBMS_JOB.SUBMIT ( job => X, what => 'begin procedure_3; end;' );
SYS.DBMS_JOB.SUBMIT ( job => X, what => 'begin procedure_4; end;' );
SYS.DBMS_JOB.SUBMIT ( job => X, what => 'begin procedure_5; end;' );
COMMIT;
END;
/
All the procedure_x do look like this:
create or replace procedure procedure_x is
begin
execute immediate 'insert into test_scheduler values (''procedure_x'',(select max(rownum) from test_scheduler))';
execute immediate 'commit';
end procedure_x;
/
With each run of my pl/sql block, I get different numbers in the second column test_scheduler table against a procedure_x. This makes me think that the parallel execution is indeed working. Next step for me will be to capture the exit status of each of these 5 procedures and if all of them have status of success, run another procedure and if any of them has a failed status, stop the whole system with appropriate error message. Is there a way to go to this deep level of branching in oracle job scheduling system. Thank you very much.
October 09, 2013 - 5:45 pm UTC
you want to look into dbms_scheduler probably.
dbms_job.submit as you are using it will just spawn the job in another session after you commit and let it run. there is no mechanism for rendezvousing with the end of your jobs.
if you wanted to - your jobs would have to insert a status record into a table you monitor and query from time to time. You would have to query this status record AND the user_jobs table to see if (a) it completed successfully (your table) or (b) failed (user_jobs).
using chains with dbms_scheduler would perhaps be more straightforward:
http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedadmin.htm#sthref3840
A reader, January 16, 2017 - 6:43 am UTC
Working fine