Re: ORA-12899, value too large

"Arto Viitanen" <arto.viitanen_at_csc.fi> a écrit dans le message de news: 43b39ae7$0$10071$ba624cd0_at_newsread.funet.fi...
| How can I avoid ORA-12899, value too large for column error? I have a
| database and program which are not made by me. I have sources, so I
| could make changes, but I will not if it can be avoided. The program
| reads a file and stores it to the database. Reading a file can take
| days. Some of the values are quite large (last night the program stopped
| because the value contained over 8000 characters) but I guess the
| large values are such that they could be truncated.
|
| Since I don't like to change the program (I changed the field from
| varchar2(1500) to varchar2(4000)), I like to make the truncation
| automatic. It occurred to me, that I could use triggers. INSTEAD OF
| trigger seemed fine (instead of inserting the value directly, I'd
| insert SUBSTR(value,1,4000)), but Oracle told that INSTEAD OF trigger
| can be used only with views.
|
| Then I tried BEFORE trigger, but it did nothing:
|
| create of replace trigger testing_trigger
| before insert or update of value on testing
| for each row
| begin
| :new.value := substr(:new.value,1,10);
| end;
| /
|
| (I used a test table TESTING, with fields ID and VALUE;
| value was varchar2(10));
|
| But,
|
| INSERT INTO TESTING VALUES(2,'01234567890');
|
| gave ORA-12899
|
| so it seems that the trigger did nothing.
|
| So question is: can I use triggers in this case? If not, is there
| any way to tell Oracle to truncate itself? (OS: Linux, Oracle: 10g)

No, check is made at parsing/binding time before execution. No one i am aware of.

Regards
Michel Cadot

|
| --
| Arto Viitanen
| CSC, Espoo, Finland
Received on Thu Dec 29 2005 - 03:46:15 CST