Forum Stats

  • 3,838,652 Users
  • 2,262,389 Discussions
  • 7,900,723 Comments

Discussions

ORA-00604 on after server trigger to trap 12899 and raise old 1401 instead

716619
716619 Member Posts: 10
Hi,

I'm using a 4GL IDE (SQLWindows from Unify, former Gupta) to access an OracleXE (also tested on a "real" 10gR2) database. If a too long character value is inserted I get an ORA-12899 error instead of the old ORA-1401. The problem is that the connectivity DLL between the application and Oracle does not recognize this newer error. As my app is the only app accessing this database I tried to trap the ORA-12899 error and raise the old 1401 instead using a after servererror database trigger:

-- database trigger to trapp ORA-12899 and raise old 1401/value too large error instead
create or replace trigger TRG_DB_SERVERERROR_12899
after servererror on database
when (SYS.SERVER_ERROR(1) = 12899)
declare
VALUE_TOO_LARGE exception;
pragma exception_init(VALUE_TOO_LARGE, -1401);
begin
raise VALUE_TOO_LARGE;
end TRG_DB_SERVERERROR_12899;

-- create test table
drop table TMP_TEST;
create table TMP_TEST (col1 varchar(10) not null);

-- check if 1401 gets raised instead of 12899
insert into TMP_TEST values ('1234567890123');


The problem now is that now I get error ORA-00604. This is the error stack:

ORA-00604: error occurred at recursive SQL level 1
ORA-01401: value too large
ORA-06512: in line5
ORA-12899: value too large for column "TMP_TEST"."COL1" (actual: 13, maximum: 10)

How can I prevent this? What is wrong with my trigger?

Thanks,
Stefan
Tagged:
This discussion has been closed.