Home » Developer & Programmer » Forms » Not able to get the records while queried through Date field in Oracle Forms
Not able to get the records while queried through Date field in Oracle Forms [message #568786] Tue, 16 October 2012 08:57 Go to next message
rponmani
Messages: 13
Registered: April 2012
Location: Chennai
Junior Member
In a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records. Please help me to solve the issue.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #568792 is a reply to message #568786] Tue, 16 October 2012 10:51 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you run a query in sqplus for records with that date do you get any results?
Re: Not able to get the records while queried through Date field in Oracle Forms [message #568797 is a reply to message #568786] Tue, 16 October 2012 12:44 Go to previous messageGo to next message
joy_division
Messages: 4531
Registered: February 2005
Location: East Coast USA
Senior Member
This is probably a classic example of forgetting that the column also has a time component to it, so entering in a date literal to search by will only get you exact matches for dates at midnight.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #568827 is a reply to message #568797] Wed, 17 October 2012 01:00 Go to previous messageGo to next message
rponmani
Messages: 13
Registered: April 2012
Location: Chennai
Junior Member
Yes, Thank you. I got the point. Only the dates which have time as 00:00:00 are retrieved in the data block and also in SQL Plus. So is there any other way to search the records in query mode, only by entering the date in the data block.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569009 is a reply to message #568827] Thu, 18 October 2012 12:14 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try the following: enter query mode. Insert :a (colon a) into the date item. Press <Execute query>. A new window will open. Enter something like this:
trunc(:a) = to_date('18.10.2012', 'dd.mm.yyyy')
into it and press <OK> (I believe it is OK ... if not, I hope you'll sort it out). Finally, records you are interested in should appear in the form.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569017 is a reply to message #569009] Thu, 18 October 2012 12:50 Go to previous messageGo to next message
joy_division
Messages: 4531
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Thu, 18 October 2012 13:14
Try the following: enter query mode. Insert :a (colon a) into the date item. Press <Execute query>.


I believe this is disabled in forms 10g and above (maybe even 9i). It was done for security reasons (ie. sql injection).
I think there is a way to turn it off but requires a configuration change.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569114 is a reply to message #569017] Sat, 20 October 2012 12:08 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I just tried it on our 9.0.4 application (which is, actually, an early 10g) and it works just fine.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569186 is a reply to message #569114] Mon, 22 October 2012 08:28 Go to previous messageGo to next message
joy_division
Messages: 4531
Registered: February 2005
Location: East Coast USA
Senior Member
It doesn't work in my 10.1.2.0.2.
I don't know when it was implemented, or maybe your configuration setting has it as "allowed." It's a setting in either formsweb.cfg or default.env.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569208 is a reply to message #569186] Mon, 22 October 2012 14:02 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, I wouldn't know that - I'm not the one who sets such things up, I just know that it *works*.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569218 is a reply to message #569186] Tue, 23 October 2012 01:19 Go to previous messageGo to next message
rponmani
Messages: 13
Registered: April 2012
Location: Chennai
Junior Member
While inserting in the date field itself if we use TRUNC(SYSDATE) then it is possible to search in the data block only by entering the date.
Re: Not able to get the records while queried through Date field in Oracle Forms [message #569233 is a reply to message #569218] Tue, 23 October 2012 05:36 Go to previous message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, as the date column - in that case - doesn't contain time component.
Previous Topic: Connect Oracle software on more than one device
Next Topic: templates or themes for oracle forms application ?
Goto Forum:
  


Current Time: Sat Oct 25 06:45:23 CDT 2014

Total time taken to generate the page: 0.08668 seconds