| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> WHERE Clause of Forms Block fails to find record!!!!
Sorry if this end up being a duplicate posting, but it did not show up in either my ORACLE-L digest or my Developer list messages yesterday so I assumed the post was "missing in action"......
I'm posting this to both the Database and Developer Lists in the hopes that someone has seen this at either the Form or Database level.
We have a form in an application (Forms 6.0) designed to allow the user to create and update records in a table called FLOCKS. To create a new record, the user enters a new Flock ID and is presented with a blank record in the form to update and eventually save. The user can update an existing FLOCK record by entering the desired Flock ID. The form will look up the record and present the existing information in the form and allow it to be modified.
Under the hood, the user is actually entering the Flock ID into a text item in a CONTROL block. The WHEN-VALIDATE-ITEM trigger checks a number of things, including whether or not a record for the specified Flock ID already exists. If it exists, the variable :GLOBAL.NEW_FLOCK is set to "N", otherwise it is set to "Y". After the validation is complete and the variable is set, the default block navigation takes the user to the DETAIL block.
The WHEN-NEW-BLOCK-INSTANCE trigger then fires with the following code:
if :global.new_flock = 'N' then
execute_query;
else
clear_block(NO_COMMIT);
end if;
In the Property Palette of the DETAIL block the Database "Where" clause is specified as "FLOCK_ID = :CONTROL.FLOCK_ID" so that the EXECUTE_QUERY built-in will populate the block with the one (and only one) matching record.
About 2 weeks ago, one of the application users called me to report a problem accessing one of the FLOCK records using this form. She kept receiving a "FRM-40401: Query returned no records." error for one and (so far) only one record in the table. Using the debugger, I was able to verify that the WHEN-VALIDATE- ITEM trigger was correctly locating the record in question and setting the :GLOBAL.NEW_FLOCK record as expected. However, when the EXECUTE_QUERY built-in executed, the FRM-40401 error popped up.
It seemed that the WHERE clause in the Block Properties was failing to find the record. SQL*PLUS, as well as several other forms were able to locate and display or list the record contents with no problem - but they did not use the WHERE clause property of a data block. The situation arose on a production database running Oracle 8.1.5 Standard Edition on NT 4.0, but the same record worked fine on my testbed machine (Personal Oracle 8.0.4 on NT Workstation).
Over the last 2 weeks, Oracle Support has come up dry in terms of giving me any rational explanation for this. Under pressure to get the problem fixed, I discovered that deleting the record and copying the same record from my testbed back to the server did not resolve the problem, but deleting the record and asking the user to re-enter the details worked. Problem solved, but by the time the TAR finally made it through the triage process and a knowledgeable Analyst started working on it, I could no longer reporoduce the problem. The TAR is now soft closed and if the same error happens again, I have a short list of things to poke at in the database.
On the Forms side, has anyone had a similar problem appear in the WHERE clause of a Block, and on the Database side has anyone run into a situation where a query for a record (specifically part of a Forms Application) has failed even though the records exists?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Norwell INET: gnorwell_at_hybridturkeys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Feb 01 2001 - 10:43:08 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |