PUZZLE: A call that succeeds N times then fails for any single connection

PUZZLE: A call that succeeds N times then fails for any single connection

Post by joeNOSPA » Sat, 28 Apr 2007 12:01:02


Hi all. I want some parameter-less SQL or procedure that will run
uneventfully N times for any given DBMS connection, but will then
*fail* such that the caller gets some ORA-XXXXXX error. I would
like this as light-weight and quick as possible. Much thanks in
advance for any cool ideas.
Joe Weinstein at BEA Systems
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by Galen Boye » Sat, 28 Apr 2007 12:30:02


Is this procedure called by java and therefore java has been forced to
deal with an exception, or are you trying to mimic a connection in the
connection pool all of the sudden croaking?

--
Galen Boyer

 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by Jonathan L » Sat, 28 Apr 2007 15:55:20


create or replace package fail_n as
procedure fail;
end;
/

create or replace package body fail_n as

g_target number(10) := 3;
g_ct number(10) := 0;

procedure fail is
begin
g_ct := g_ct + 1;
if g_ct = g_target then
g_ct := 0;
raise_application_error(-20000,'Call Count ' || g_target || ' reached');
end if;
end;


end;
/


SQL> execute fail_n.fail

PL/SQL procedure successfully completed.

SQL> execute fail_n.fail

PL/SQL procedure successfully completed.

SQL> execute fail_n.fail
BEGIN fail_n.fail; END;

*
ERROR at line 1:
ORA-20000: Call Count 3 reached
ORA-06512: at "TEST_USER.FAIL_N", line 11
ORA-06512: at line 1


The reset of the counter is optional - depending on
whether you want to stop all future executions in this
session, or just restart the cycle.

--
Regards

Jonathan Lewis
http://www.yqcomputer.com/

Author: Cost Based Oracle: Fundamentals
http://www.yqcomputer.com/

The Co-operative Oracle Users' FAQ
http://www.yqcomputer.com/
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by joeNOSPA » Sun, 29 Apr 2007 00:37:49


Hi. I want a pool to execute this procedure as part of it's "is this
connection still good"
test, to enforce that the connection test fail because of receiving a
DBMS-based
exception after N such tests.
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by joeNOSPA » Sun, 29 Apr 2007 01:46:55

On Apr 26, 11:55 pm, "Jonathan Lewis" < XXXX@XXXXX.COM >




Jonathan, thank you very much. It works like a charm!
Joe Weinstein at BEA Systems
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by Mladen Gog » Sun, 29 Apr 2007 11:55:17


Jonathan, one question: why didn't you create a public package
variable, in the package spec? Putting the counter in the package
counter would give you more control as the counter would be adjustable
from an anonymous block, outside the package?

--
http://www.yqcomputer.com/
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by Jonathan L » Sun, 29 Apr 2007 21:01:32


Mladen,

No real reason - it's just some code I jotted
down in a little less than 5 minutes so I wasn't
planning for posterity.

With more time and a more thorough spec I might
still have made the variables private to the package
body, though, but then supplied a procedure and
function to set the target , and report the current.
(I might also have used number(N,0) for the two
variables if I'd spent a moment more thinking about it).

--
Regards

Jonathan Lewis
http://www.yqcomputer.com/

Author: Cost Based Oracle: Fundamentals
http://www.yqcomputer.com/

The Co-operative Oracle Users' FAQ
http://www.yqcomputer.com/
 
 
 

PUZZLE: A call that succeeds N times then fails for any single connection

Post by Charel » Sun, 29 Apr 2007 21:03:25


if you want to add some true (pseudo) randomness to the failure pattern you
can use

select 1/decode(least(dbms_random.normal(),1.96),1.96,0,1) from dual

a value of 1.96 will give 2.5 % failures.
Some other values

1.645 : 5% failures
1 : 16 % failures
0 : 50 % faillures