From: oysteing@garm.idt.unit.no (Oystein Groevlen)
Subject: Re: Passing query results from a user exit to SQL*Forms 3.0
Organization: Div. of CS & Telematics, Norwegian Institute of Technology
Date: 19 Dec 92 12:57:34

In article <1992Dec15.170402.28872@eng.ufl.edu> mjw@mailbox.eng.ufl.edu (Mike Wohlgemuth) writes:

   I need to run a select query in a user exit, and have the query
   results show up in a multi-record block.  I would like to be able
   to open the cursor, fetch the records, and then assign them to
   sucessive rows in the block.  As I understand it, IAF PUT will
   put values into the fields of the current record in the block.
   So, how do I create new rows and navigate to them within a user
   exit?

I have made an user exit for performing queries which presents the
query result in Forms. 

The reason for making this user exit was that I needed to be able
execute queries which combines criteria on fields from different
tables (and blocks.) This was an query application, so I could have
used a view, but the performance of such a view with an outer join
over 28 tables is extremely poor on a Microvax. In addition queries
executed in Forms are compiled when the application is compiled
(before the values of the Forms fields used in the query are known).
Thus, Oracle will often not use indexes even if it is possible.

In the master block of the form, I made query fields for all fields of
the details blocks which it should be possible to put search criteria
on. These fields is displayed when the user enter query mode of the
master block.

For each of the query fields, I wrote queries which returned the
primary key of the master block for the records which satisfied the
criteria on that field. The queries are stored in a file which is read
by the user exit. Before a query from that file is compiled and
executed by the user exit each occurences of Forms field names in the
query is replaced with the contents of that field with the help of IAF
GET.

The user-exit will store the primary key of the records satisfying all
the queries executed in a table called QUERY_SET. The table QUERY_SET
contains 2 fields: KEY which is the primary key of the master block,
and SEARCH_ID which identify this particular execution of the user
exit. SEARCH_ID is stored in the primary key field of the master block
by IAF PUT.

The user exit is called in the PRE-QUERY trigger. The parameters to
the user exit is the name of the file containing the queries, and a
list indentifying the queries to be executed (line numbers in file).
This way only the queries for fields which is non-empty is executed.

When the user exit returns, the primary key field contains the
reference to the table QUERY_SET. To have Forms restrict the search to
the queries found by the user_exit, the following code has to be
executed (The primary key field is here DOC.DATID):

   search_id := name_in('DOC.DATID');
   query_string := '#in (select KEY from QUERY_SET where SEARCH_ID = ' ||
                   search_id;
   copy(query_string, 'DOC.DATID');

Then Forms performs a search as usual to get the records satisfying
the criteria on the base table fields.

Hope this gave some ideas on how to solve your problem.

I will be away on christmas vacation until January 4, so mail me if
you have any comments as the postings will have been deleted from our
news server by then. 

Oystein Groevlen 
Division of Computer Systems and Telematics
The Norwegian Institute of Technology
The University of Trondheim
Email: oysteing@idt.unit.no


--
Oystein Groevlen 
Division of Computer Systems and Telematics
The Norwegian Institute of Technology
The University of Trondheim
Email: oysteing@idt.unit.no
Newsgroups: comp.databases.oracle