OraFAQ Forum: Forms » Not able to get the records while queried through Date field in Oracle Forms

Home » Developer & Programmer » Forms » Not able to get the records while queried through Date field in Oracle Forms

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to get the records while queried through Date field in Oracle Forms [message #568786]

Tue, 16 October 2012 08:57

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.

Report message to a moderator

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

cookiemonster
Messages: 13925
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?

Report message to a moderator

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

joy_division
Messages: 4963
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.

Report message to a moderator

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

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.

Report message to a moderator

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

Littlefoot
Messages: 21813
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.

Report message to a moderator

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

joy_division
Messages: 4963
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.

Report message to a moderator

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

Littlefoot
Messages: 21813
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.

Report message to a moderator

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

joy_division
Messages: 4963
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.

Report message to a moderator

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

Littlefoot
Messages: 21813
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*.

Report message to a moderator

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

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.

Report message to a moderator

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

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, as the date column - in that case - doesn't contain time component.

Report message to a moderator

Previous Topic:	Connect Oracle software on more than one device
Next Topic:	templates or themes for oracle forms application ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 11:17:22 CDT 2024