| Alter Session [message #474175] |
Fri, 03 September 2010 06:55  |
eight8ball@gmail.com
Messages: 20
Registered: February 2010
|
Junior Member |
|
|
Is it possible to alter a session within Oracle forms as I get the following error when trying to compile the following block.
BEGIN
EXECUTE IMMEDIATE ( 'alter session set nls_date_format = 'DD-MON-YYYY''');
END;
Error: Encountered the symbol "IMMEDIATE" when expecting one of the following
The basic requirement is to validate a date field on the form against the current system date, but as the form is in format DD-MON-YYYY and the Database on M/D/YYYY I am struggling to compare dates successfully.
New to forms so not sure how this should be done.
Thanks
Scott
|
|
|
|
| Re: Alter Session [message #474177 is a reply to message #474175] |
Fri, 03 September 2010 07:05  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If the date field is of date datatype it doesn't matter what the format is. Oracle knows how to compare dates.
Presumably your code is converting the date to a char to do the comparison, which'd be the wrong way to do it.
|
|
|
|
| Re: Alter Session [message #474179 is a reply to message #474177] |
Fri, 03 September 2010 07:16 |
eight8ball@gmail.com
Messages: 20
Registered: February 2010
|
Junior Member |
|
|
The Implementation is as follows:
PROCEDURE Validate_Selection
IS
BEGIN
-- The From Date Cannot Be in the Future
IF :XXXE_FIND.A_DATE_FROM > trunc(sysdate) THEN
Set_Display_Msg('The From-Date Cannot be in the future, Please make a valid selection');
END IF;
EXCEPTION
WHEN OTHERS THEN
Set_Display_Msg('An unexpected Error Has Occured: ' || sqlerrm);
END Validate_Selection;
The problem I have is that :XXXE_FIND.A_DATE_FROM is a TEXT_ITEM_DATE which has a list of values assigned to it which displays a popup calendar.
So if the user selects a date as 10-AUG-2010, it is validated against todays date and it thinks its higher.
Any ideas??
|
|
|
|
| Re: Alter Session [message #474182 is a reply to message #474179] |
Fri, 03 September 2010 07:36 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What's the actual datatype of XXXE_FIND.A_DATE_FROM?
Because if it's date that should work.
If it's not a date make it a date and use a proper calendar implementation - you should be able to find examples if you search this site.
|
|
|
|
|
|
| Re: Alter Session [message #474185 is a reply to message #474183] |
Fri, 03 September 2010 07:51 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Not directly.
You can either:
1) put the execute immediate in a stored proc in the db and call that.
2) use forms_ddl builtin.
I'm surprised that code doesn't work though. What happens if you change it to:
IF to_date(:XXXE_FIND.A_DATE_FROM) > trunc(sysdate) THEN
Set_Display_Msg('The From-Date Cannot be in the future, Please make a valid selection');
END IF;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
