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?
-------------------------------------------
Gary Norwell, Database Developer
Hybrid Turkeys a Division of Nutreco Canada
650 Riverbend Drive, Suite "C"
Kitchener Ontario Canada N2K 3S2
(519) 578-2740 Fax (519) 578-1870
===========================================
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gary Norwell
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
