Help! Forms 4 restricted routines

I am trying to use Forms 4 to query an external data source if the queried data is not found in the Oracle database. The functionality I want is that the user enters the selection criteria into the block with the form in enter-query mode and then presses 'F8' to execute the query. If the record is not found I want forms to exit enter-query mode, call a userexit to extract the details from another source (RMS file), create a new record,insert these values into the new record and then 'Commit_Form'. So it looks to the user that the record was successfully retrieved from the Oracle database.

I have tried the following:

1. On-Message trigger to detect 40301 'Query caused no records to be retrieved'. But this is no good since EXIT_FORM and COMMIT_FORM are restricted procedures and no allowed in On-Message triggers.
2. On-Select and On-Fetch triggers to redefine the query processing. This doesn't work since you have to use Create_Queried_Record in the On-Fetch trigger and Forms does not recognise the new record as a record which must be commited with the standard Forms_Commit processing. Additionally Commit_Form is a restricted routine which cannot be used in an On-Fetch trigger.
3. Using PL/SQL in the On-Select trigger. I queried the Oracle database with a select statement to see if the requested record exists. If it doesn't I call the userexit to retrieve the data from teh external source. I then wanted to use an insert statement to insert the record into the Oracle database which could then be selected by the following Select_Records command. This doesn't work as I can't use the insert statement in the On-Select trigger.

Is what I want to achieve impossible??

Is there something else I haven't tried??

The userexit side of this works OK. Received on Wed Mar 08 1995 - 03:10:06 CET