A user exit is a C subroutine written by you and called by Oracle Forms to do special-purpose processing. You can embed SQL statements and PL/SQL blocks in your user exit, then precompile it as you would a host program.

When called by an Oracle Forms V3 trigger, the user exit runs, then returns a status code to Oracle Forms. Your exit can display messages on the Oracle Forms status line, get and set field values, do high-speed computations and table lookups, and manipulate Oracle data.

SQL*Forms provides the ability to use PL/SQL blocks in triggers. So, in most cases, instead of calling a user exit, you can use the procedural power of PL/SQL. If the need arises, you can call user exits from a PL/SQL block with the USER_EXIT function. User exits are harder to write and implement than SQL, PL/SQL, or SQL*Forms commands. So, you will probably use them only to do processing that is beyond the scope of SQL, PL/SQL, and SQL*Forms. Some common uses follow:

• Operations more quickly or easily done in a third generation languages like C (numerical integration, for instance)

• Controlling real time devices or processes (issuing a sequence of instructions to a printer or graphics device, for example)

• Data manipulations that need extended procedural capabilities (recursive sorting, for example)

• Special file I/O operations

This section outlines the way to develop a SQL*Forms user exit; later sections go into more detail.

To incorporate a user exit into a form, you take the following steps:

• Write the user exit in Pro*C.

• Precompile the source code.

• Compile the. c file from step 2.

• Use the GENXTB utility to create a database table, IAPXTB.

• Use the GENXTB form in SQL*Forms to insert your user exit information into the table.

• Use the GENXTB utility to read the information from the table and create an IAPXIT source code module. Then compile the source code module.

• Create a new SQL*Forms executable by linking the standard SQL*Forms modules, your user exit object, and the IAPXIT object created in step 6.

• In the form, define a trigger to call the user exit.

• Instruct operators to use the new IAP when running the form. This is unnecessary if the new IAP replaces the standard one. For details, see the Oracle installation or user's guide for your system.

You can use the following kinds of statements to write your SQL*Forms user exit:

• C code

• EXEC SQL

• EXEC ORACLE

• EXEC TOOLS

This section focuses on the EXEC TOOLS statements, which let you pass values between SQL*Forms and a user exit.

EXEC TOOLS statements support the basic Oracle Toolset by providing a generic way to handle get, set, and exception callbacks from user exits. The following discussion focuses on Oracle Forms but the same concepts apply to Oracle Report.

EXEC TOOLS SET form_variable[, ...] 
     VALUES ({:host_variable :indicator | constant}[, ...]); 

char ename[20];
short ename_ind;

...

    strcpy(ename, "MILLER");
    ename_ind = 0;
    EXEC TOOLS SET emp.ename VALUES (:ename :ename_ind);

EXEC TOOLS GET form_variable[, ...] 
    INTO :host_variable:indicator[, ...]; 

...
char     name_buff[20];
VARCHAR  name_fld[20];
 
strcpy(name_fld.arr, "EMP.NAME");
name_fld.len = strlen(name_fld.arr);
EXEC TOOLS GET :name_fld INTO :name_buff;

EXEC TOOLS SET CONTEXT :host_pointer_variable 
    IDENTIFIED BY context_name; 

... 
char  *context_ptr; 
char  context[20]; 
 
strcpy(context, "context1") 
EXEC TOOLS SET CONTEXT :context IDENTIFIED BY application1; 

EXEC TOOLS GET CONTEXT context_name 
    INTO :host_pointer_variable; 

... 
char  *context_ptr; 
 
EXEC TOOLS GET CONTEXT application1 INTO :context_ptr;  

EXEC TOOLS MESSAGE message_text [severity_code]; 

EXEC TOOLS MESSAGE 'Bad field name! Please reenter.'; 

You call a user exit from a SQL*Forms trigger using a packaged procedure named USER_EXIT (supplied with SQL*Forms). The syntax you use is

USER_EXIT(user_exit_string [, error_string]);  

where user_exit_string contains the name of the user exit plus optional parameters and error_string contains an error message issued by SQL*Forms if the user exit fails. For example, the following trigger command calls a user exit named LOOKUP:

USER_EXIT('LOOKUP'); 

Notice that the user exit string is enclosed by single (not double) quotes.

When you call a user exit, SQL*Forms passes it the following parameters automatically:

However, the user exit string provides the ability to pass additional parameters to the user exit. For example, the following trigger command passes two parameters and an error message to the user exit LOOKUP:

Notice that the user exit string is enclosed by single (not double) quotes.

USER_EXIT('LOOKUP 2025 A', 'Lookup failed');
 

You can use this feature to pass field names to the user exit, as the following example shows:

USER_EXIT('CONCAT firstname, lastname, address');
 

However, it is up to the user exit, not SQL*Forms, to parse the user exit string.

When a user exit returns control to SQL*Forms, it must also return a code indicating whether it succeeded, failed, or suffered a fatal error. The return code is an integer constant defined by SQL*Forms (see the next section). The three results have the following meanings:

User exits are precompiled like standalone host programs. For instructions on compiling a user exit, see the Oracle installation or user's guide for your system.

The IAP program table IAPXTB in module IAPXIT contains an entry for each user exit linked into IAP. IAPXTB tells IAP the name, location, and host language of each user exit. When you add a new user exit to IAP, you must add a corresponding entry to IAPXTB. IAPXTB is derived from a database table, also named IAPXTB. You can modify the database table by running the GENXTB form on the operating system command line, as follows:

RUNFORM GENXTB username/password 

A form is displayed for you to enter the following information for each user exit you define:

• Exit name

• C-language code

• Date created

• Date last modified

• Comments

After modifying the IAPXTB database table, use the GENXTB utility to read the table and create an Assembler or C source program that defines the module IAPXIT and the IAPXTB program table it contains. The source language used depends on your operating system. The syntax you use to run the GENXTB utility is

GENXTB username/password outfile 

where outfile is the name you give the Assembler or C source program that GENXTB creates.

Before running a form that calls a user exit, you must link the user exit into IAP, the SQL*Forms component that runs a form. The user exit can be linked into your standard version of IAP or into a special version for those forms that call the exit.

To produce a new executable copy of IAP, link your user exit object module, the standard IAP modules, the IAPXIT module, and any modules needed from the Oracle and C link libraries.

The details of linking are system-dependent. Check the Oracle installation or user's guide for your system.

The guidelines in this section will help you avoid some common problems.