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

Christodoulou Demetris wrote:
> Hi Grtz, Frank
>
> Let me provide you with an other experiment i did with this date
> problem
>
> Below is some code inside my stored procedure:
>
> 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;
>
> /* End of sample code inside my stored Procedure */
>
> 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.

First off, how about this:
PROCEDURE OU550_S12(p_start_date1 IN date,

                     p_last_date1  IN date,
                     P_RC	 OUT NUMBER) IS
CURSOR c_acco 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;

          ' entered: '||to_char(r_acco.entry_date_time,'DDMMYYYY HH:MI');     END LOOP; Next, are the date fileds in the table really date fileds? Could you post the relevant partion or all of the table description?

Frank Received on Mon Jan 06 2003 - 21:20:03 CET