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

Christodoulou Demetris wrote:
> Thanks Frank for your answer.
> Let me give you some more detail regarding this date problem.
>
> My operating system is Win2000 Professional and my client settings as
> these are specified in my registry are
> NLS_DATE_FORMAT: DD/MON/RR
Not a good idea: I thought the Y2K pandemonium taught us to use full, 4-digit years? As you are in the process of migrating, change that, too. Use YYYY, not RR

>
> Also i found that the following parameters are specified in the
> Database:
> NLS_DATE_FORMAT (in table 'nls_session_parameters') : DD/MON/RR
> NLS_DATE_FORMAT (in table 'nls_database_parameters') : DD/MON/YY
> NLS_DATE_FORMAT (in table 'nls_instance_parameters') : null
>
> Ok let me give you a snapshot from the Stored procedure where the date
> problem is found:
> /* --------------------------------------------------*/
> PROCEDURE OU550_S12(p_start_date1 IN date,
> p_last_date1 IN date,
> P_RC OUT NUMBER) IS
> ....
> ....
> CURSOR acco_cursor IS
> SELECT acco_trans_number, entry_date_time
> FROM accounts_transactions
> WHERE entry_date_time BETWEEN p_start_date AND p_last_date
> ORDER BY receipt_no ASC;
>

Odd piece of code - you order by a field you do not fetch.

>
> /* In order to make some more investigation about the reason for which
> i do not receive the correct results i use 2 local constant variables
> instead. (I do this in order to exclude the case that the date
> parameter is not correctly passed to the Stored Procedure). Thus
> parameters p_start_date1 and p_last_date1 are not used in the
> cursor, but the below local fixed variables are used.*/
> p_start_date := to_date('01/Jan/97','DD/Mon/RR');
> p_last_date := to_date('01/Dec/02','DD/Mon/RR');
>
> open acco_cursor;
> /* fetch the result set row by row.

Process cursor, I suppose?
>
>
> */
> close acco_cursor;
>
> /*--------------------------------------------------*/
>
> Comments:
> While the above query fetches the correct result when it is executed
> from a)the SQL Plus environment and b) from with in a program unit in
> Oracle Forms,

Both CLIENT side queries...

the same query does not work when specified in the above
> cursor inside the Stored Procedure OU550_S12
SERVER side query.

>
> I do not understand the reason for which the cursor inside the Stored
> Procedure does not work.
> Please let me know how to proceed. I am really stack at this point and
> i cannot proceed.
>
> Thanks a lot in advance. Any help will be precious to me

What _are_ the results you are getting? Or better: what results do you expect, and do you not get?

Grtz, Frank Received on Sat Dec 28 2002 - 17:55:08 CET