Oracle Trigger - How can I capture and correct on the fly ORA-01847
623335Feb 15 2008 — edited Feb 17 2008Oracle Trigger - How can I capture and correct on the fly ORA-01847 day of month must be between 1.
I have a table that has a date column. Sometimes an invalid date is passed and I'd like to capture and initilize the incoming value to null and have it inserted without error. Currently I get ORA-01847 day of month must be between 1.
Example:
create table ep1
(
col_1 number,
col_2 date
)
Now if a user does the following, I want no error to occure but rather for the date value to be set to null and inserted.
insert into ep1 values('1', '41-JAN-13')
The insert may come from any client and I can't change the calling code hence can't used a precedure to replace the insert. I'm guessing there is some way to catch system level exceptions on the DB level. This doesn't need to be nice, it just needs to work.
Here is the question on experts exchange. I'm told there is no way, but there must be. After all the exception is on a DB system level and I would think one could extend or override these, or disable them.
http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_23045306.html