On Apr 26, 11:55 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> "joeNOS..._at_BEA.com" <joe.weinst..._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
Jonathan, thank you very much. It works like a charm!
Joe Weinstein at BEA Systems
Received on Fri Apr 27 2007 - 11:46:55 CDT
