Home » Developer & Programmer » Forms » Different output in forms and pl sql
Different output in forms and pl sql [message #539232] Thu, 12 January 2012 06:19 Go to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Hi everyone,

Thanks for your help for my previous post's.

Here i have one PL/SQL block which will returns the age of an employee using his id.

SET SERVEROUTPUT ON;
DECLARE
  v_num  NUMBER;
  v_days NUMBER;
BEGIN
  SELECT to_date(sysdate)-to_date(dob)
  INTO v_num
  FROM customer_details
  WHERE application_id=103;
  v_days             :=floor(v_num/366);
  dbms_output.put_line(v_days);
END;
/


It will returns an output of 27

I tried the same in Forms 6i using a text field and a button with a trigger
"when_button_pressed". when i am entering the same id of employee i am getting a totally different answer

DECLARE
  v_num  NUMBER;
  v_days NUMBER;
BEGIN
  SELECT to_date(sysdate)-to_date(dob)
  INTO v_num
  FROM customer_details
  WHERE application_id=:block3.day;
  v_days             :=floor(v_num/366);
  MESSAGE(v_days);
  MESSAGE(v_days);
END;

It is giving me a result of -73

i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.

i am expecting your valuable help
Re: Different output in forms and pl sql [message #539233 is a reply to message #539232] Thu, 12 January 2012 06:31 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's probably because you are using to_date on a date. Sysdate is a date, I imagine dob is as well. Doing so is a bug.
to_date does not accept a date parameter. So oracle implicitly converts the date to a varchar using your sessions date format.
i.e.
to_date(sysdate)

is really
to_date(to_char(sysdate, <nls_date_format>), nls_date_format)


Try getting rid of the to_dates.
Re: Different output in forms and pl sql [message #539234 is a reply to message #539233] Thu, 12 January 2012 07:20 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Thanks for your valuable reply cookiemonster.

it is really helpful for me to get the accurate result.

Re: Different output in forms and pl sql [message #539256 is a reply to message #539234] Thu, 12 January 2012 09:33 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
So did that fix it?
Re: Different output in forms and pl sql [message #539279 is a reply to message #539256] Thu, 12 January 2012 21:39 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Yes now it is giving correct output.

I thought that before to_date()-to_date() only can give the output.
Oh ...it has really gave me some bugs only.
This is the one lead to me the correct way.

SELECT to_date(TO_CHAR(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')-
  to_date(TO_CHAR(dob,'DD/MM/YYYY'),'DD/MM/YYYY')
  INTO v_sub
  FROM customer_details
  WHERE application_id=p_id;
  v_dobcalc          :=ROUND(v_sub/366);


But what is that -73 indicate? is it a ASCII value which we get as a result of the subtraction.

Thank u.

[Updated on: Thu, 12 January 2012 21:45]

Report message to a moderator

Re: Different output in forms and pl sql [message #539282 is a reply to message #539279] Fri, 13 January 2012 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I still believe that query you posted in your last message is ... well, not wrong, but far too complicated.

If DOB column's datatype is DATE, then
select sysdate - dob
  into customer_details
  from ...

is all you need. "DATE - DATE" (as datatypes) is crucial here, not "TO_DATE - TO_DATE" (as functions). These functions are to be used when you need to convert a string into a date (if, for example, you incorrectly chose to store DATE values into a VARCHAR2 column).

As you've already been told: SYSDATE is a function that returns DATE, so there's no sense in converting (SYS)DATE -> CHAR -> DATE; why would you do that? The same goes to DOB column (if it is a DATE).
Re: Different output in forms and pl sql [message #539293 is a reply to message #539279] Fri, 13 January 2012 01:38 Go to previous messageGo to next message
flyboy
Messages: 1750
Registered: November 2006
Senior Member
Out of topic: your solution just reminded me this comic (although you have still some place to improve): http://xkcd.com/763/

Tomcrzy wrote on Fri, 13 January 2012 04:39
But what is that -73 indicate? is it a ASCII value which we get as a result of the subtraction.

As the value is probably supposed to be date difference in years (by the way incorrect - although year 2012 has 366 days, most other ones do not) and 27-(-73)=100, this is just a side effect of having two digit year (most probably 'YY') in NLS_DATE_FORMAT mask. So, DOB is converted to e.g. string DD-MM-85, which is supposed to be in year 2085 and not 1985. Anyway, when you avoid implicit conversion, NLS_DATE_FORMAT will not be used and it will be processed correctly.

To the difference count: subtraction gives the difference in days; for getting years, use MONTHS_BETWEEN function and divide it by 12 (fortunately, all years have 12 months).
Re: Different output in forms and pl sql [message #539294 is a reply to message #539293] Fri, 13 January 2012 01:53 Go to previous messageGo to next message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

That is a good comic.


well.. your reply gave me some idea .
thank you,expecting help from you in the future.
Re: Different output in forms and pl sql [message #539305 is a reply to message #539294] Fri, 13 January 2012 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
I tell you that using to_Date on a date is a bug. I explain that it's a bug because to_date doesn't accept a date parameter and oracle has to implicitly to_char it first.
I then explicitly tell you to remove the to_date.

You instead add the implicit to_char explicitly.

You really didn't read my post very carefully did you?
Don't change datatypes if you don't need to.
Re: Different output in forms and pl sql [message #539306 is a reply to message #539293] Fri, 13 January 2012 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
flyboy wrote on Fri, 13 January 2012 07:38
To the difference count: subtraction gives the difference in days; for getting years, use MONTHS_BETWEEN function and divide it by 12 (fortunately, all years have 12 months).


The difference in the counts will have been due to different nls_date_formats being used by sqlplus and forms. Forms will have been using YY and sqlplus YYYY.
I agree that months_between is the best way to do this.
Re: Different output in forms and pl sql [message #539308 is a reply to message #539306] Fri, 13 January 2012 04:09 Go to previous message
Tomcrzy
Messages: 101
Registered: December 2011
Location: Chennai,India
Senior Member

Thank u cookiemonster,i got it.

Previous Topic: Run batch file through Form
Next Topic: Display Specified Image
Goto Forum:
  


Current Time: Wed Apr 16 23:33:28 CDT 2014

Total time taken to generate the page: 0.14118 seconds