Re: date problem in migrating Oracle Forms&Reports 4 to Oracle Forms&Reports 6
Date: 29 Dec 2002 23:34:42 -0800
Message-ID: <8288bbda.0212292334.1a1b1f14_at_posting.google.com>
Frank <fbortel_at_home.nl> wrote in message news:<3E0DD76C.7030101_at_home.nl>...
> 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
Hi Grtz, Frank
Let me provide you with an other experiment i did with this date problem
PROCEDURE OU550_S12(p_start_date1 IN date,
p_last_date1 IN date,
P_RC OUT NUMBER) IS
/*
Actually that cursor fetches many other fields (including receipt_no) but i did not write them down in order to achieve more readiness. Which of course was wrong.
Sorry if i created any confusion. My purpose was to identify exactly where the date problem was.
*/
--Declare cursor-->
CURSOR acco_cursor IS
SELECT acco_trans_number, entry_date_time, payment_method,
fee_paid, adjustment_amount, refund_amount, transaction_type,
operator_id,receipt_no,operator_id,terminal_id
FROM accounts_transactions
WHERE entry_date_time BETWEEN p_start_date AND p_last_date
ORDER BY receipt_no ASC;
/* 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.*/
/* !!!!! Also notice that here i use the 'YYYY' format !!!!!! */ p_start_date := to_date('01/01/1997','DD/MM/YYYY'); p_last_date := to_date('31/12/2002','DD/MM/YYYY');
OPEN acco_cursor;
/*Get accounts transaction information fro
/*Fetch the first record */
FETCH acco_cursor INTO h_acco_trans_number, h_entry_date_time,
h_payment_method, h_fee_paid,
h_adjustment_amount,
h_refund_amount, h_transaction_type, h_operator_id,
h_receipt_no,h_operator_id,h_terminal_id;
WHILE acco_cursor%FOUND LOOP
/* Process cursor */
/* Some application logic is actually here */
/* Get another row */
FETCH acco_cursor INTO h_acco_trans_number, h_entry_date_time,
h_payment_method, h_fee_paid,
h_adjustment_amount, h_refund_amount,
h_transaction_type, h_operator_id,h_receipt_no,
h_operator_id,h_terminal_id;
END LOOP;
Ok, and my remarks are as follows:
While the above query fetches the correct results when it is
executed
from a)the SQL Plus environment and b) from with in a program unit in
Oracle Forms. More exactly it fetches 6404 records when executed on
the testing Database and some million when executed on the production
DB.
However the same query fetches only one record (instead of 6404) when executed in the above cursor inside the Stored Procedure OU550_S12. The 1 (and only) row in the result is as follows: ACCO_TRANS_NUMBER, RECEIPT_NO, ENTRY_DATE_TIME, ....... 5918 1 02/01/2000
It seems that the date format YYYY is never handled by the stored
procedure and this looks very strange to me.
Do you think that changing the database format to dd/mon/yyyy will
solve the problem?
What about the database parameters below :
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
Do they play some role in the way the database server handles dates?
ALSO, is there a way to solve this date problem and keep the DD/MON/RR
date format, too?
This is because several hundreds of forms and reports have to be
migrated and i have already migrated more than 100. I think that if i
go through the process of using the DD/MON/YYYY then i may spend a lot
of time, more than i really have to finish this project.
Of course if there is no other solution then i will go through this
process.
Any other ideas?
Many Thanks in advance!!!
Christodoulou Demetris.
Received on Mon Dec 30 2002 - 08:34:42 CET