Home » Developer & Programmer » Forms » MONTHS_BETWEEN problem in Oracle Form 6i (Windows 7, Oracle Form 6ia)
MONTHS_BETWEEN problem in Oracle Form 6i [message #605151] Tue, 07 January 2014 11:43 Go to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
I used following coding in form to find the months between

BEGIN
	:FINANCED_DETAILS.due_date	:=	TO_CHAR(:FINANCED_DETAILS.emi_end_dt, 'ddth') || ' OF MONTH';
	:FINANCED_DETAILS.fin_tenure	:=	MONTHS_BETWEEN (:FINANCED_DETAILS.emi_end_dt, :FINANCED_DETAILS.emi_start_dt) || ' MONTHS';
END;


If i am giving input
emi_end_dt = '07-jan-2017' and
emi_start_dt = '07-jan-2014' its working fine and giving output 36 months

But If i giving input
emi_end_dt = '31-mar-2014' and
emi_start_dt = '01-feb-2016' its not giving output but giving me error ORA-06502

Can anyone tell me how to solved this
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605157 is a reply to message #605151] Tue, 07 January 2014 12:02 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
Are those fields DATEs or VARCHAR2s?
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605159 is a reply to message #605157] Tue, 07 January 2014 12:07 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
emi_start_dt & emi_end_dt are DATE columns And others are VARCHAR2(30)
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605170 is a reply to message #605159] Tue, 07 January 2014 14:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1092
Registered: May 2013
Location: Hi-tech city
Senior Member
dark_prince wrote on Tue, 07 January 2014 23:37
emi_start_dt & emi_end_dt are DATE columns And others are VARCHAR2(30)


What is data type of fin_tenure?
MONTHS_BETWEEN returns the NUMBER of months. So, if you assign number to a character type you would get ORA-06502.
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605171 is a reply to message #605159] Tue, 07 January 2014 14:13 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is, I suspect, because :FINANCED_DETAILS.FIN_TENURE length is too short to accept the whole value of MONTHS_BETWEEN concatenated with 'MONTHS'.

It was OK for a whole number ("36 months"), but failed for a (negative) decimal number ("-22.032258 months").

[Updated on: Tue, 07 January 2014 14:13]

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605174 is a reply to message #605170] Tue, 07 January 2014 14:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1092
Registered: May 2013
Location: Hi-tech city
Senior Member
In addition to what I said above, fin_tenure field might be too short to hold the entire characters being assigned. Just test a bit more and check.

[LF was too quick to post around the same time I posted Smile ]

[Updated on: Tue, 07 January 2014 14:19]

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605175 is a reply to message #605170] Tue, 07 January 2014 14:20 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lalit Kumar B

... if you assign number to a character type you would get ORA-06502.


You would? Poor you, most of us (who use Oracle) wouldn't.
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605176 is a reply to message #605175] Tue, 07 January 2014 14:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1092
Registered: May 2013
Location: Hi-tech city
Senior Member
Littlefoot wrote on Wed, 08 January 2014 01:50
Lalit Kumar B

... if you assign number to a character type you would get ORA-06502.


You would? Poor you, most of us (who use Oracle) wouldn't.


I meant the other way round, but just typed the opposite. Must go to sleep now Smile
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605178 is a reply to message #605151] Tue, 07 January 2014 14:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1092
Registered: May 2013
Location: Hi-tech city
Senior Member
As LF already said the negative value might be an issue, I suspect the problem might be the input values. The end date being less than the start sate. @OP, is it a typo?

dark_prince wrote on Tue, 07 January 2014 23:13

But If i giving input
emi_end_dt = '31-mar-2014' and
emi_start_dt = '01-feb-2016' its not giving output but giving me error ORA-06502

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605193 is a reply to message #605178] Tue, 07 January 2014 20:50 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
thank you guys for your reply...

the datatype of field tenure is VARCHAR2(30) and what if the input is
emi_end_dt = '01-feb-2016' and
emi_start_dt = '31-mar-2014' but its still giving error.

so I add ROUND function after function MONTHS_BETWEEN and now its giving output

BEGIN
	:FINANCED_DETAILS.due_date	:=	TO_CHAR(:FINANCED_DETAILS.emi_end_dt, 'ddth') || ' OF MONTH';
	:FINANCED_DETAILS.fin_tenure	:=	ROUND(MONTHS_BETWEEN (:FINANCED_DETAILS.emi_end_dt, :FINANCED_DETAILS.emi_start_dt)) || ' MONTHS';
END;

Or should I use following code as you guys said number => character storing giving error

BEGIN
	:FINANCED_DETAILS.due_date	:=	TO_CHAR(:FINANCED_DETAILS.emi_end_dt, 'ddth') || ' OF MONTH';
	:FINANCED_DETAILS.fin_tenure	:=	TO_CHAR(ROUND(MONTHS_BETWEEN (:FINANCED_DETAILS.emi_end_dt, :FINANCED_DETAILS.emi_start_dt))) || ' MONTHS';
END;


But I want to know which code should i used or do I have to do something different here...
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605216 is a reply to message #605193] Wed, 08 January 2014 02:32 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're not going to specify a format mask for the to_char there's no real difference between them.
Oracle implicitly to_chars the number when concatenating it to a string.
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605228 is a reply to message #605216] Wed, 08 January 2014 03:43 Go to previous messageGo to next message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Hey thanks, so i guess have to use to_char ('data', '999'). And what about the 1st method which doesn't contain to_char, is there a problem for not using it...
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605247 is a reply to message #605228] Wed, 08 January 2014 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem; Cookiemonster told you, Oracle will implicitly convert number to character. However, TO_CHAR would indicate that you care about what you are doing and have control over it.
Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605249 is a reply to message #605247] Wed, 08 January 2014 04:44 Go to previous message
dark_prince
Messages: 98
Registered: June 2013
Location: India
Member
Thank u guys...
Previous Topic: Problem with forms debugging
Next Topic: Template.fmb
Goto Forum:
  


Current Time: Mon Apr 21 00:36:57 CDT 2014

Total time taken to generate the page: 0.05264 seconds