Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle Trigger - How can I capture and correct on the fly ORA-01847

623335Feb 15 2008 — edited Feb 17 2008
Oracle 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 16 2008
Added on Feb 15 2008
10 comments
1,162 views