Suppose I have the following:
table: mytable
field: username (VARCHAR2(35))
field: zip (VARCHAR2(5))
I am importing data from an excel file with columns username and zip. A couple of the usernames contain more than 35 characters, and some of the zipcodes are in the xxxxx-xxxx format, thus containing more than 5 characters.
I attempted to write a before insert trigger to truncate the data before they are inserted. For any username that is above 35 characters from the excel file, just truncate the username to the first 35 characters. Similarly, for any zip code that is above 5 characters, just truncate the zip to the first 5 characters.
The trigger for the zip codes:
CREATE OR REPLACE
TRIGGER trigger_zipc
BEFORE INSERT
ON mytable
FOR EACH ROW
BEGIN
SELECT substr(:new.zip, 1, 5)
INTO :new.zip
from dual;
END;
However, I get the error: ORA-12899: value too large for column
I understand that this happens because the value is read before my validation, and because the value is too large, it is not allowed to be inserted.
I'm unsure on where to go from here. Any suggestions?