ORA-1847 day of month must be between 1 & last day of month

[Barabas]

Hello

Im trying to debug a troublesome trigger.

In my when clause im using this type of logic....

nvl(old.cust_flag, 'z') <> nvl(new.cust_flag,'z') or
nvl(old.last_updated_by, 99999) <> nvl(new.last_updated_by,99999) or

etc

but im having problems with the date format by this i mean

nvl(old.last_update,'9999') <> nvl(new.last_update,'9999')

Table Struct

cust_flag = VARCHAR150
last_updated_by = Number 15
last_update = Date




The problem is prob the format (ie '9999) above) but im not too sure what to put in when referencing a date???

I really hope someone can help...

Thanks in Advacne to anyone who can...

[ales]

Hello,
If the second argument does not the same datatype as the first argument, it must be converted. Obviously, '9999' is not a valid date.

I believe you can use sysdate here:
nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)

... quam minimum credula postero.

[jmodic]

Originally posted by ales
I believe you can use sysdate here:
nvl(old.last_update, sysdate+1) <> nvl(new.last_update, sysdate+1)

Since the original poster stated that his code is PL/SQL (trigger), I wouldn't use sysdate as suggested in the above code! If both :NEW.last_update and :OLD.last_update are NULL, then the above condition will most of the time result in FALSE, but sumetimes it will result in TRUE!!!!!

Better use some constant date instead of SYSDATE, like:

NVL(:OLD.last_update, TO_DATE(1,'J')) <> NVL(:NEW.last_update, TO_DATE(1,'J'))

[ales]

Brabas wrote that he's using that in the WHEN clause of the trigger, not in the trigger body. The WHEN clause is a boolean SQL expression, not a PL/SQL code. The SYSDATE function is evaluated only once in a SQL expression, thus it's safe to use it in the manner I suggested.
Please correct me if I'm wrong.

[jmodic]

Yep, you are righ! I missed that "small" detail about code being part of the WHEN trigger clause (that's why I used colons before NEW and OLD).

In case of WHEN condition you are correct - sysdate is evaluated only once per expression, so your code is perfectly OK.