Re: Raise exception in the function that used in a select.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 22 Nov 1999 20:08:57 -0000
Message-ID: <943301456.6918.0.nnrp-14.9e984b29@news.demon.co.uk>

You can do something like this,
combined with exception_init
and raise_application_error.

create or replace function breaker
return varchar2
is
begin

    raise dup_val_on_index;
    return 'good';
exception

    when dup_val_on_index then

        return sqlerrm;
        when others then null;

end;
.
/

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Lev Yunak wrote in message <01bf34c8$87fbe880$101aa8c0_at_lev.vaz.ru>...
>This problem isn't funny. I really need to sovle it.
>
>I want to raise exception in fucntion like
>
>function test(...) return number is
>begin
> ...
> raise_application_error(-20800, 'Error because lalala');
> ...
>end;
>
>AND use it in a select like
>
>select test(...) from dual
>
>I can't do it because oracle "doesn't garantee that test() doesn't
>change database".
>Well. I define test() in package and use pragma
>
>PRAGMA RESTRICT_REFERENCES (test, WNDS);
>
>Oracle say: "PLS-00452: Subprogram 'TEST' break its pragma"
>
>In this case raise_application_error changes database.
>That's problem.
>How I can raise exception with good number and good message in test()
>function.
>
>I can use follow construction but I can get only error
>number without error message when it raised.
>
>function test return number is
> errtest EXCEPTION;
> PRAGMA EXCEPTION_INIT(errtest, -20800);
>begin
> ...
> raise errtest;
> ...
>end;
>
>For example
>select test from dual
>
>printed:
>ORA-20800: ORA-06512: in "test", line ....
>
>I need in error message...
>
>
Received on Mon Nov 22 1999 - 14:08:57 CST