Re: Help - Forms 3.0 bind variables

From: Peter Moore <pt_at_chaff.demon.co.uk>
Date: 1996/02/20
Message-ID: <368144667wnr_at_chaff.demon.co.uk>#1/1


In article: <323077172wnr_at_b-mc.demon.co.uk> Bryan Bell <bryan_at_b-mc.demon.co.uk> writes:
>
>Hi, this is just a general question about the use of bind variables in
>Forms 3.0.16.12.9.
>
>We've noticed on our V7.1.4 database that there are a lot of similar
>SELECT statements in the v$sqlarea table, e.g.
>
> Select col1, col2, col3, col4 from table where col3 <> 'D' and
> col3 <> 'C' and (primarykey = number) order by primarykey
>
>The 'table' is a base table for a table maintenance block. Out of all
>of these statements, only the 'number' is different. I believe that
>this is due to the user entering a specific value in the primarykey
>field in ENTER QUERY mode (sorry this is vague but I do not know which
>program is issueing the statements). The statement looks like a dynamic
>sql statement that runform creates at runtime for a query block.
>
>Incidentally, the forms were originally developed under V6 and
>recompiled under V7.
>
>Questions:
>1) Are we correct in our assumption of what we can see in the
>v$sqlarea table, ie bad performance?
>
>2) If (1) = 'yes' then is there a way of changing the block so that
>it uses a bind variable for each of the 'query allowed' fields that
>will reduce the amount of statements being seen in the v$sqlarea table.
>

(1) Yes. Query data entered into a key field on a base-table block is passed directly into the SQL and therefore is not shared in the SQL area.

We found a solution that works for most cases as follows:

  1. Define a non-base-table (NBT) field for each of your key base-table fields.
  2. Define a PRE-QUERY trigger to copy values from each key field into the equivalent NBT field.
  3. Alter the block's WHERE..ORDER BY clause to read something like:

   WHERE key_column_1 LIKE NVL(:block.nbt_key_field_1, '%')    AND key_column_2 LIKE NVL(:block.nbt_key_field_2, '%')

This will force the query to use bind variables and therefore share the code.

This method only works where the key fields are defined as NOT NULL in the base table. We found a way around the problem of NULL key fields, but I can't remember it just now. E-mail me if you want details.

To check if it's working, try putting a comment with the form.block name in the WHERE..ORDER BY clause (e.g. "/* FORM1.BLOCK5 */"). Ensure that the comment appears AFTER the WHERE clause, if it is before the form will not run.

HTH, Pete

-- 

------------------------------------------------------------------------
|  Peter Moore - Database Administrator - MAT Transport Ltd, London, UK
|  pt_at_chaff.demon.co.uk : +44 (171) 410 6373
|  "With a little study you'll go a long ways & I wish you'd start now!"
Received on Tue Feb 20 1996 - 00:00:00 CET

Original text of this message