„ora-1401…value too large for column…“ sometimes not thrown

[agfauser]

Hello,

we have problems with the TD 1.5.1-PTF6 on Oracle DB.

Problem: „ora-1401…value too large for column…“ and maybe some other exceptions sometimes not thrown:
E.g.: The max. length of a string column is 15. But the data to insert has 18 characters. Normaly the application has to throw the exception „ora-1401…value too large for column“. If we try to insert these data directly on the database (SqlPlus), the exception appears in every trial. If we try to insert data by debugging the application, sometimes the exception appears and sometimes not, without changing the source code and with the same data. If we use a merge-version of the complete source we have the same effects. But if we create an „.exe“ the exception appears never ever.

While debugging, the „Return FALSE“ of „When SqlError“ and the „Return FALSE“ of „…NOT SqlPrepareAndExecute…“ is never reached. The error is shown at the SqlMonitor, but not in the „Centura.log“.

If we log the insert by debugging with TOAD.SqlMonitor we can see the sqlerror, but the exception could not reach the Centura-applikation.

Remarks:
- There is no trigger on the table L_LIEFER.
- Oracle 9.x and 10.x

The part of the source:
When SqlError
Return (FALSE)
If NOT SqlPrepareAndExecute(hpSql, '
insert into l_liefer
( ............. a_ident !(15 characters)
)
values
( ..............:cKreditor.sKreditorUstIdent !(18 characters)
)')
Return FALSE

The problem is urgent, because one of our customers can not use the application till we solfe the problem!

[cschubert]

Hi,

this is a known problem. The Oracle error number has changed to 12899 in Oracle 9.X (IIRC) and older versions of TD won't display this error any longer. Use this trigger to throw a decent error. Note that the error number generated here is -1401 because error numbers of user raised errors have to be negative. This however should be no big problem, at least there is an error again Execute the script in SQLPlus or any Oracle tool.

---------snip -----------

CREATE OR REPLACE TRIGGER trg_error_1401
AFTER SERVERERROR
ON DATABASE
DECLARE
VALUE_TOO_LARGE EXCEPTION;
PRAGMA EXCEPTION_INIT (VALUE_TOO_LARGE, -1401);
BEGIN
IF dbms_standard.server_error(1) = 12899 THEN
RAISE VALUE_TOO_LARGE;
END IF;
END;

---------snip -----------

HTH

Christian Schubert

[agfauser]

Thank you very much for your answer. After using the trigger the problem is fixed. But we had to exchange the two values, because we use a 10.x ociw32.dll on a 9.x database.

Now we use this one:
CREATE OR REPLACE TRIGGER sys.trg_error_1401
AFTER SERVERERROR
ON DATABASE
WHEN (SYS.SERVER_ERROR(1) = 1401)
DECLARE
VALUE_TOO_LARGE EXCEPTION;
PRAGMA EXCEPTION_INIT (VALUE_TOO_LARGE, -12899);
BEGIN
RAISE VALUE_TOO_LARGE;
END;

[Jean-Marc Gemperle]

Hi

This started to happen because of the change in ORACLE 9 that affect the interface to the non OCI8 (we use by default OCI7), even though TD 4.2 is certified on 10g you would get the problem as Christian mentioned. Now if you turn on USE_LOB=1 (in effect switching to OCI8) in sql.ini or SqSetParameterAll() the error would be caught but with an invalid error message now. TD 5.1 now uses by default the OCI8 and there is no longer such a problem (including proper error), in the SP1 though executing a SP in the same schema causes problem (refer to other posts) because of the use of OCI8, this also shows in TD 4.2 if you would use USE_LOB=1. This problem of SP has been fixed in the SP2.

That was just for your information.

Cheers
JM