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

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://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"joeNOSPAM_at_BEA.com" <joe.weinstein_at_gmail.com> wrote in message 
news:1177642861.896868.282260_at_b40g2000prd.googlegroups.com...


> 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

>