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

From: Frank <fbortel_at_home.nl>
Date: Tue, 24 Dec 2002 10:30:17 +0100
Message-ID: <3E082929.6060206_at_home.nl>


Christodoulou Demetris wrote:
> 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

The CLIENT handles date FORMATS, the database just stores them in a numeric format.
Check you client settings - these may even be in the form of a registry setting on the server...

And posting a code snippet might help. This should work, as long as you reformat you dates.

Frank Received on Tue Dec 24 2002 - 10:30:17 CET

Original text of this message