Home » Developer & Programmer » Forms » trigger error (oracle 11g r2 windows 7 ultimate)
trigger error [message #635461] Mon, 30 March 2015 11:11 Go to next message
hisham99
Messages: 105
Registered: October 2008
Location: united arab emirates
Senior Member
i convert this form from oracle 6i windows xp to oracle 11g r2 windows 7 ultimate

i have form non database
:mon1 is char(2)
:date1 char(10)
:date2 char(10)
---
post-change trigger
declare
year1 number(4):=2015;
begin

if :mon1 = 1 then
:date1 :=('01'||'\'||:mon1||'\'||year1);
:date2 := to_char(last_day(:date1),'dd\mm\yyyy');
end if;
end;
----
first when i run this form in oracle 6i it work but with 11g i have this two problem
1) Ora:-01843

2)my problem in this code
:a:=substr(:date1,4,2);

in oralce 61 it give me correct answer (only the month in number EX. if :mon1=02 then :a=02)

in oralce 11g it give me the month in char (ex if :mon1=02 then :a=FE)

please the main question why its work with 61 but not with 11g
i have same configuration
Re: trigger error [message #635467 is a reply to message #635461] Mon, 30 March 2015 13:39 Go to previous message
Littlefoot
Messages: 21580
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First of all, you should post accurate information. There's no ":a" nor any "substr" in code you posted. It is difficult to debug code you can't see.

Anyway: ORA-01843 (not a valid month) means that month portion of a date value is NOT a valid month. It appears that you expect '02' but get 'FE' (as in 'FEBRUARY') somewhere in your code. How come? NLS settings are probably different from the previous setup.

LAST_DAY function expects a valid date; you provided a string - DATE1 is a string, CHAR(10) as you said (note that you'd rather switch to VARCHAR2). Previously, Oracle performed implicit conversion from a string you provided and managed to convert it to a DATE datatype value so LAST_DAY worked OK. However, Oracle failed to do the same now and, as a result, you got an error.
Previous Topic: execute a software
Next Topic: how to count checked check boxes along with their checked and unchecked conditon
Goto Forum:
  


Current Time: Fri Aug 14 09:12:57 CDT 2020