Skip to Main Content

Hide an error from the application using a servererror trigger?

353089Dec 13 2010 — edited Dec 14 2010
We have an application designed for an old oracle version which issues some sql which is no more supported in todays database version.
We want to use the application unchanged with a new database server.

Old Server Version: 7.3.4 (still in production...)
New Server Version: 10.2 or 11.2

The application issues an
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS ;
which results in ORA-01986 and the application dies.

We would like to hide the error 01986 from the application using a trigger:
create or replace
trigger catch01986
  after servererror
  on schema
  begin
    if (ora_is_servererror (1986)) then
      null; -- what to do here? we want clear the ora-01986 from the error stack
    end if;
  end catch01986;
How to handle the error, so that the alter session set ... statement is just ignored and no error code is returned to the application?

I asked already some days ago in Database-General Forum, but triggers belong to PL/SQL, so i repost here.

Tnx for help in advance!
Comments
Post Details
Added on Dec 13 2010
10 comments
182 views