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

From: Christodoulou Demetris <christdm_at_ceid.upatras.gr>
Date: 27 Dec 2002 03:13:57 -0800
Message-ID: <8288bbda.0212270313.79d8ba84_at_posting.google.com>


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 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;

  

/* 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.    

 */
 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, the same query does not work when specified in the above cursor inside the Stored Procedure OU550_S12  

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 Received on Fri Dec 27 2002 - 12:13:57 CET

Original text of this message