Home -> Usenet -> comp.databases.oracle -> date problem in migrating Oracle Forms&Reports 4 to Oracle Forms&Reports 6

date problem in migrating Oracle Forms&Reports 4 to Oracle Forms&Reports 6

From: Christodoulou Demetris <christdm_at_ceid.upatras.gr>
Date: 23 Dec 2002 03:55:31 -0800
Message-ID: <8288bbda.0212230355.22b47e0b_at_posting.google.com> 






I have a date problem in migrating Oracle Forms&Reports 4 to Oracle
Forms&Reports 6.
 


While these reports work fine with Oracle reports 4, they do not work
correctly with Oracle Reports6.


I have isolated the problem to the way that the Oracle Server handles
dates.



The database server is Oracle8 Enterprise Edition Release 8.0.5.2.1
The NLS_DATE_FORMAT is DD/MON/RR.



ok let me describe the problem.


There is 1 query that fetches some data from the database based on
some time period.


While this query works just fine (ie. fetches the correct data)


    
  
  1.   from any SQL command line editor (SQL PLUS, TOAD prompt etc)
  
  2.   from a program unit inside an Oracle Forms 6 (query declared
inside a Cursor)
yet, it can not be successfully executed from within a stored
procedure.






I have even set the variables inside the SP so that they are not host
variables but constant vars instead.


ie. i set inside the SP


 p_start_date := to_date('02/Jan/97', 'dd/Mon/RR');
 p_last_date  := to_date('30/Dec/99', 'dd/Mon/RR');  



 Again the SP did not fetch the correct data. 
 Also notice that in order for these queries to be successfully
executed (when called from within a stored procedure),
 the time period has to concern the same year.
 eg start_date:'01/Jan/97', last_date: '31/Dec/97' 
 or start_date:'01/Jan/02', last_date: '31/Dec/02' 
 


 Any ideas?


Kind Regards,


Jim
Received on Mon Dec 23 2002 - 12:55:31 CET












Original text of this message