Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: WHERE Clause of Forms Block fails to find record!!!!

Re: WHERE Clause of Forms Block fails to find record!!!!

From: Oweson Flynn <Oweson.Flynn_at_liberty.co.za>
Date: Fri, 02 Feb 2001 03:21:17 -0800
Message-ID: <F001.002A89EC.20010202025021@fatcity.com>

Hi,

What was the datatype of the Flock ID ?
 I remember having a similar problem a couple of years ago (it was forms 4.5 if I remember correctly), and the datatype in the form was char (I think), which then ended up being padded with spaces ...

HTH Regards
Oweson Flynn



Certified Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: oef_at_icon.co.za
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, February 01, 2001 4:41 PM

> 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: 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-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).
>


This message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Oweson Flynn
  INET: Oweson.Flynn_at_liberty.co.za

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-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).
Received on Fri Feb 02 2001 - 05:21:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US