Thanks for the question, Ramasubbu.

Asked: November 13, 2001 - 10:40 pm UTC

Last updated: February 20, 2004 - 10:01 am UTC

Version: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production

Viewed 1000+ times

You Asked

Hi Tom,

Platform:Win NT,Oracle 8i,R-3

My scenario is like there are three DT's clubbed into a single procedure,say

create or replace procedure club_proc is
begin
p1;
p2;
p3;
exception
when others then null;
end;
Now execution is happening squentially one by one procedure,from p1 to p3.
I want to execute three procedures cuncurrently since there is no dependency on each procedure.
How do I achieve it?.

Awaiting ur reply.
muthu

and Tom said...

first -- the code:

exception
when others then null;
end;

is a bug -- it is always a bug, if you actually have code that looks like this, you have a bug.

You can configure the job queues and use the dbms_job package to submit each:

declare
l_job number;
begin
dbms_job.submit( l_job, 'p1;' );
dbms_job.submit( l_job, 'p2;' );
dbms_job.submit( l_job, 'p3;' );
commit;
end;
/

Now, if you have job_queue_processes set to three or above, you have a chance that all three will run concurrently, in the background. Note that if the job queues are already processing stuff -- your jobs will be in the queue and will run as soon as a job queue process is free.

In 8i, you can have 36 job queue processes, in 9i, 1,000.

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How could someone know so much about Oracle?

Eric Worthy, November 14, 2001 - 2:46 pm UTC

Kinda like he isn't human.....

A Java and OS approach

Andre Whittick Nasser, November 14, 2001 - 4:21 pm UTC

You can execute concurrently at OS level. Unfortunately this won't run on NT, which I think is your case, since DOS won't take parallel execution as simple as UNIX.

It is important to notice that it is a different alternative to Tom's suggestion. His is simpler for what the reader wants.

But this code is more powerful in the sense you can run things in the OS environment.

Use this code tip to publish the "shellcmd" PL/SQL function:

</code> https://asktom.oracle.com/magazine-archive.htm


Or take a look at Oracle Magazine:

http://asktom.oracle.com/magazine-archive.htm/oracle/00-Mar/index.html?o20java.html <code>

Then you have something like:

CREATE OR REPLACE PROCEDURE club_proc
IS
r1 NUMBER;
r2 NUMBER;
r3 NUMBER;
BEGIN
r1=shellcmd(<os_cmd1>);
r2=shellcmd(<os_cmd2>);
r3=shellcmd(<os_cmd3>);
END;

Where each <os_cmd> command will execute serially.

If you want to run OS commands "concurrently", use the UNIX ampersand (&) suntax.

So, if you want to run your procedures, run SQLPLUS starting them, just like:

CREATE OR REPLACE PROCEDURE club_proc
IS
r1 NUMBER;
r2 NUMBER;
r3 NUMBER;
BEGIN
r1=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host1 @p1.sql &");
r2=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host2 @p2.sql &");
r3=shellcmd("$ORACLE_HOME/bin/sqlplus scott/tiger@host3 @p3.sql &");
END;

Notice you can run whichever OS command you like, including starting SQLPLUS sessions in other machines, like in host1, host2 and host3.

Just take care:

- Beware of Java privileges in Oracle -- they changed from 8.1.5 to 8.1.6, differently from the second article I mentioned (8.1.5), you should use "direct Java privileges" (look it up).

- Your username and password would be visible, so you could use the wrap utility to "hide" your code. Run:

$ wrap iname=p1.sql

Even so, some of your data is visible in the wrapped code.

Hope this helps !

Helena Markova, November 15, 2001 - 8:17 am UTC

Cuncurrent execution of procedures

Michael, November 15, 2001 - 10:27 pm UTC

Why there is alwayas a bug in
<<
exception
when others then null;
end;
>>?

I can only see that it will miss some error or it is difficult to debug.

November 16, 2001 - 8:01 am UTC

That is EXACTLY why there is a bug here -- because

"it will miss some error"

that IS the bug.  I see it time and time again.  Someone says "well, I ran the procedure and got "PLSQL procedure successfully completed" but it didn't work.  Whats wrong with Oracle?  why isn't it saving my data properly?  It only did half the job!

When in fact, the programmer purposely caught and ignored the error -- causing the database to preserve half of the work.  

Consider the difference between these two blocks:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2      insert into t values ( 1 );
  3      RAISE program_error;
  4      insert into t values ( 1 );
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at line 3


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2      insert into t values ( 1 );
  3      RAISE program_error;
  4      insert into t values ( 1 );
  5  exception
  6      when others then
  7          null;
  8          -- silently ignore the error
  9  end;
 10  /
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM>


One, the one that lets the error propagate, saves NO data -- the statement is considered atomic, since it failed -- it failed entirely.  On the other hand, the second block CAUGHT the error, ignored it and "succeeded".  therefore the partial work it did was saved.

This is the way exceptions are explicitly designed to work.  In general, it works very nicely (recovering from that DUP_VAL_ON_INDEX exception for example, or the SELECT INTO that returns no_data_found) but when you catch just ANY error and basically ignore it -- you have a BUG.  I can assure you of that, 100%.  A tablespace fills up -- error -- partial work, no clue that it happened.  A constraint that should not be violated gets violated -- error -- partial work, no clue that it happened.  (you get the picture)

The Man

David, March 15, 2002 - 9:52 am UTC

Tom,

Where have you been all of my [Oracle] life?

Thanks for your efforts.

Eye opening

Gabriel, April 17, 2002 - 11:57 am UTC

Sort of on a related note...

Mike, September 30, 2003 - 11:54 am UTC

Hi,

I'm currently trying to move an application away from the coding standard of...

when others then
call_logging_procedure;
raise;
end;

to having nothing and allowing errors to propogate up and out of Oracle to the caller, who can then log the full error stack including the line of text that raised the exception...

One of the other things I'm trying to remove is a job scheduler written in PRO C. it pretty good, but does cause some problems, and provides little that DBMS_JOB does not...

however one of the things it does provide is the ability to log error messages for the PL/SQL it executes.

I've set up a simple DBMS_JOB that will fail due to a PK violation and it does fail...

However DBMS_JOB will keep on trying it (fair enough) and I can't seem to find any trace of the error anywhere...

Ideally I would want the job to fail, not to be retried and the error to go somewhere. If i attempt to do any of this in PL/SQL I must catch the excpetion, which will cause me to lose the main thing I'm after.

Am I not looking in the right places or is DBMS_JOB not appropriate?

Thanks,

Mike.

September 30, 2003 - 12:32 pm UTC

for the job -- instead of submitting:

dbms_job.submit( :job, 'proc;' );

you would submit:

dbms_job.submit( :job, 'begin proc; exception when others then LOGIT; COMMIT; <optionally re-raise if that is appropriate>; end;' );

(looks familar! its the when others then log, raise trick :)

your errors right now are in the alert log on the server.

As usual...

Franco, October 01, 2003 - 3:03 am UTC

... every your word is so useful... Thanks for this wonderful site, and your books that i just received two minutes ago :-))

OK then...

Mike, October 01, 2003 - 3:46 am UTC

Hi Tom,

Thanks for that, but putting a when others then log is what I am trying to get away from, as it means I'll lose the error stack... A NDF is really useful if you also know the line number, but if it's one of 20,000 lines of code, it's a lot less helpful...

I'll have to have a hard think about sticking with the pro c or not, but as much for my own knowledge a couple of questions?

Is there anyway to get the exceptions raised by DBMS_JOB to go somewhere other than the alert log? even if another file.

Is it possible to stop DBMS_JOB retrying failed jobs?

Thanks,

Mike.

October 01, 2003 - 8:37 am UTC

the errors go into the alert log.

you could use dbms_trace to great effect as well -- search this site for dbms_trace to see what it can do for you.

the job would have to succeed in order for dbms_job to "stop" -- so, you can use dbms_trace to capture the details you want and the when others to make it so it does'nt retry.

When others then null

Tony, February 20, 2004 - 4:37 am UTC

<quote>
first -- the code:

exception
when others then null;
end;

is a bug -- it is always a bug, if you actually have code that looks like this,
you have a bug.
</quote>

In the application that I am supporting at the moment I see this kind of thing all the time.
There are lots of procedures that contain

WHEN OTHERS THEN
NULL;

I think this is a really BAD practice and a lazy way to code exceptions but I need to go to the developers and try to explain why this is not good.
Can you give my some feedback as to why this is not a good way to code exception?
I can then build a strong argument to take to the development team and argue my point.
If you disagree with me, can you tell me why?

February 20, 2004 - 9:38 am UTC

it is not "lazy" (heck, they had to type extra code). It is just "horrible".

Ask them -- Ok, so when your insert as select fails due to ora-1555, or your update fails or <whatever fails> -- how does the invoker understand this, find out what happened, get an error message, not blindly ignore the fact that the error occurred?

Do they even understand what it means to transactional consistency? That is totally changes the atomicity of statement invocations? consider a procedure/function like this:

create procedure p1
as
begin
update t set ....;
exception
when others then
null;
end;

create procedure p2
as
begin
update t2 set ...;
p1;
end;

ask them -- so, what happens when p1 fails? the update of t2 will persist, and the procedure p2 is sort of "clueless" that "something bad (tm)" has happened.

if they chop out the exception block -- what'll happen is

o the invoker of p2 will understand "error happened" and have access to the information regarding the error

o all work by both P1 and P2 will be undone, the database will be in a known, consistent, stable state.

when others then null; is a bug in 99.99999% of the cases (the only place it is OK is something like:

begin
number_var := str_var;
return TRUE;
exception
when others then return FALSE;
end;

that'd be a function to test if a string contained a number in it.)

when whenother then null is bad...

A reader, February 20, 2004 - 9:36 am UTC

It is bad because the developers are coding...

When ever any, ANY, *ANY* error occurs in this block of code, ignore it and move on...

How can you EVER want to code that??

It's Java!

Invisible, February 20, 2004 - 10:01 am UTC

"If EXCEPTION do NOTHING."

Hmmm, sounds like a Java programmer's trick to me! ;-)

[In Java, you have to catch every exception that could possibly occur and specify what to do with it - or specify that the function might produce that error - so everyone gets so hacked off with it they just ignore all exceptions.

Sounds like people are just carrying it straight over to Oracle, even though Oracle is a tad more intelligent about much thigns!]

Just my opinion... [can't even BEGIN to imagine how the data such applications work on can stay consistent!]

Breadcrumb

Question and Answer