OraFAQ Forum: Forms » MONTHS_BETWEEN problem in Oracle Form 6i

Home » Developer & Programmer » Forms » MONTHS_BETWEEN problem in Oracle Form 6i (Windows 7, Oracle Form 6ia)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MONTHS_BETWEEN problem in Oracle Form 6i [message #605151]

Tue, 07 January 2014 11:43

dark_prince
Messages: 121
Registered: June 2013
Location: India

Senior 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

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605157 is a reply to message #605151]

Tue, 07 January 2014 12:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Are those fields DATEs or VARCHAR2s?

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605159 is a reply to message #605157]

Tue, 07 January 2014 12:07

dark_prince
Messages: 121
Registered: June 2013
Location: India

Senior Member

emi_start_dt & emi_end_dt are DATE columns And others are VARCHAR2(30)

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605170 is a reply to message #605159]

Tue, 07 January 2014 14:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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.

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605171 is a reply to message #605159]

Tue, 07 January 2014 14:13

Littlefoot
Messages: 21808
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

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605176 is a reply to message #605175]

Tue, 07 January 2014 14:22

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605178 is a reply to message #605151]

Tue, 07 January 2014 14:29

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

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

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605193 is a reply to message #605178]

Tue, 07 January 2014 20:50

dark_prince
Messages: 121
Registered: June 2013
Location: India

Senior 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...

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605216 is a reply to message #605193]

Wed, 08 January 2014 02:32

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605228 is a reply to message #605216]

Wed, 08 January 2014 03:43

dark_prince
Messages: 121
Registered: June 2013
Location: India

Senior 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...

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605247 is a reply to message #605228]

Wed, 08 January 2014 04:35

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: MONTHS_BETWEEN problem in Oracle Form 6i [message #605249 is a reply to message #605247]

Wed, 08 January 2014 04:44

dark_prince
Messages: 121
Registered: June 2013
Location: India

Senior Member

Thank u guys...

Report message to a moderator

Previous Topic:	Problem with forms debugging
Next Topic:	Template.fmb

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:55:31 CDT 2024