Q: Triggers and raise_application_error

[Tony Farrow]

I am trying to remove any additional errors that are being produced
when I run the exception handling.

...

if (condition) then
raise my_error;
end if;
...
exception

when my_error then
raise_application(-20001, ' My Error.');
....

and this seems to work EXCEPT I get the following additional errors
when I insert into the table.

ORA-20001: My Error. << This is EXCELLENT
ORA-06512: at line 18 << I DO NOT WANT to see this
ORA-04088: error during execution of trigger 'tablename.trigger'

Is there any way to handle the remaining two errors within my
exception routine?

regards
tony
to...@isotro.com

[Stephen Lappin]

> Tony Farrow <to...@isotro.com> wrote in article
<32590D...@isotro.com>...

Try the following:

when my_error then
raise_application(-20001, ' My Error.', TRUE);

The third parameter determine if the error is put at the top of the error
stack or overwrites the error stack. The default (FALSE) just adds your
error to the error stack, TRUE will replace the error stack.
(NB. I have never actually used this, so let me know if it works).

--
Slainte mhath
Stephen Lappin

[Tony Farrow]

Stephen Lappin wrote:
>
> > Tony Farrow <to...@isotro.com> wrote in article
> <32590D...@isotro.com>...
> > I am trying to remove any additional errors that are being produced
> > when I run the exception handling.
> >
> > ...
> >
> > if (condition) then
> > raise my_error;
> > end if;
> > ...
> > exception
> >
> > when my_error then
> > raise_application(-20001, ' My Error.');
> > ....
> >
> > and this seems to work EXCEPT I get the following additional errors
> > when I insert into the table.
> >
> > ORA-20001: My Error. << This is EXCELLENT
> > ORA-06512: at line 18 << I DO NOT WANT to see this
> > ORA-04088: error during execution of trigger 'tablename.trigger'
> >
> > Is there any way to handle the remaining two errors within my
> > exception routine?
>
> Try the following:
>
> when my_error then
> raise_application(-20001, ' My Error.', TRUE);
>

I tried this and still no go. My documentation does not reference a
third parameter for
the raise_application_error. Any other pointers to clearing the error
stack would be
greatly appreciated.

[Stephen Lappin]

The following is an extract from the PL/SQL User's Guide and Reference:

Using raise_application_error

Package DBMS_STANDARD, which is supplied with Oracle7, provides language
facilities that help your application interact with Oracle.
For example, the procedure raise_application_error lets you issue
user-defined error messages from stored subprograms. That way,
you can report errors to your application and avoid returning unhandled
exceptions.

To call raise_application_error, you use the syntax

raise_application_error(error_number, message[, {TRUE | FALSE}]);

where error_number is a negative integer in the range -20000 .. -20999 and
message is a character string up to 2048 bytes long. If the optional third
parameter is TRUE, the error is placed on the stack of previous errors. If
the parameter is FALSE (the default), the error replaces all previous
errors. Package DBMS_STANDARD is an extension of package STANDARD, so you
need not qualify references to it.

[dmo...@gpu.com]

In <32590D...@isotro.com>, Tony Farrow <to...@isotro.com> writes:
>I am trying to remove any additional errors that are being produced
>when I run the exception handling.
>

>....

>
>if (condition) then
> raise my_error;
>end if;

>....

>exception
>
>when my_error then
> raise_application(-20001, ' My Error.');

>.....

>
>and this seems to work EXCEPT I get the following additional errors
>when I insert into the table.
>
>ORA-20001: My Error. << This is EXCELLENT
>ORA-06512: at line 18 << I DO NOT WANT to see this
>ORA-04088: error during execution of trigger 'tablename.trigger'
>
>Is there any way to handle the remaining two errors within my
>exception routine?
>

>regards
>tony
>to...@isotro.com

These last two errors are part of your exception handling. I don't know of any
way to suppress them. When they are sent back to FORMS it will have just your
error in the variables and you could produce your own error message inside an
On-Error Trigger.

D. Scott Moyer, Jr. dmo...@gpu.com (preferred for work)
GPU Service Corporation dsm...@enter.net (preferred for home)
Reading, PA

[Jeff Waugh]

Tony Farrow (to...@isotro.com) wrote:
> I am trying to remove any additional errors that are being produced
> when I run the exception handling.

> ...

> if (condition) then

> raise my_error;
> end if;
> ...

> exception

> when my_error then
> raise_application(-20001, ' My Error.');
> ....

> and this seems to work EXCEPT I get the following additional errors

> when I insert into the table.

> ORA-20001: My Error. << This is EXCELLENT
> ORA-06512: at line 18 << I DO NOT WANT to see this
> ORA-04088: error during execution of trigger 'tablename.trigger'

> Is there any way to handle the remaining two errors within my
> exception routine?

> regards
> tony
> to...@isotro.com

In your exception block add a handler for 'when others' and handle them as
you desire. Exceptions will bubble up the call stack until they are handled
( either by you or by oracle (the error messages that you don't want to see))

HTH,
Jeff Waugh
Fairlane Training & Development Center
Dearborn, MI

[Tony Farrow]

I am trying to prevent the last two lines of this error message from
being displayed.

> ORA-20001: My Error. << This is EXCELLENT
> ORA-06512: at line 18 << I DO NOT WANT to see this
> ORA-04088: error during execution of trigger 'tablename.trigger'

I have received a few suggestions ( which do not seem to work as
I have implemented them )

1) use FALSE | TRUE values in the
raise_application_error(num,message,TRUE)
to replace error stack before returning

2) use when others then clause during the exception handling

3) pragma exception_init(exception_str, -4088 (or -6512));

It seems that once your trigger hits a raise_application_error
within the exception handling it returns with your message/number
on the stack. I believe this prevents the exception_init and the
when others from catching the -6512 or -4088 messages.

The other two messages do not get placed on the stack until you raise
theapplication_error at which point it -seems- to late to handle
them.

Any other suggestions would be welcome.

tony

Tony Farrow wrote:
>
> I am trying to remove any additional errors that are being produced
> when I run the exception handling.
>
> ...
>
> if (condition) then
> raise my_error;
> end if;
> ...
> exception
>
> when my_error then
> raise_application(-20001, ' My Error.');
> ....
>
> and this seems to work EXCEPT I get the following additional errors
> when I insert into the table.
>

>