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

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

From: Christodoulou Demetris <christdm_at_ceid.upatras.gr>
Date: 3 Jan 2003 05:29:41 -0800
Message-ID: <8288bbda.0301030529.700a5d4_at_posting.google.com> 







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.
Received on Fri Jan 03 2003 - 14:29:41 CET












Original text of this message