Re: Passing query results from a user exit to SQL*Forms 3.0

In article <1992Dec15.170402.28872_at_eng.ufl.edu> mjw_at_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;

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_at_idt.unit.no

--
Oystein Groevlen 
Division of Computer Systems and Telematics
The Norwegian Institute of Technology
The University of Trondheim
Email: oysteing_at_idt.unit.no