Xref: alice comp.databases.oracle.server:75124
Path: alice!news-feed.fnsi.net!netnews.com!feed1.news.rcn.net!rcn!dispose.news.demon.net!demon!news.demon.co.uk!demon!jlcomp.demon.co.uk!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: Raise exception in the function that used in a select.
Date: Mon, 22 Nov 1999 20:08:57 -0000
Message-ID: <943301456.6918.0.nnrp-14.9e984b29@news.demon.co.uk>
References: <01bf34c8$87fbe880$101aa8c0@lev.vaz.ru>
X-Trace: news.demon.co.uk 943301456 nnrp-14:6918 NO-IDENT jlcomp.demon.co.uk:158.152.75.41
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Lines: 78

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@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...
>
>