Pro*COBOL Precompiler Programmer's Guide Release 8.0 A58232-01 |
Library | Product | Contents | Index

# 13
Writing User Exits

This chapter focuses on writing user exits for your SQL*Forms and Oracle Forms applications. First, you learn the EXEC IAF statements that allow a SQL*Forms application to interface with user exits. Then, you learn how to write and link a SQL*Forms user exit. You also learn how to use EXEC TOOLS statements with Oracle Forms. (SQL*Forms does not support EXEC TOOLS.) That way, you can use EXEC IAF statements to enhance your existing applications and EXEC TOOLS statements to build new applications.

Use EXEC TOOLS rather than the obsolescent EXEC IAF in any new applications.

The following topics are covered:

- What Is a User Exit?
- Why Write a User Exit?
- Developing a User Exit
- Writing a User Exit
- Calling a User Exit
- Passing Parameters to a User Exit
- Returning Values to a Form
- Sample Program 5: Oracle Forms User Exit
- Precompiling and Compiling a User Exit
- Using the GENXTB Utility
- Linking a User Exit into SQL*Forms
- Guidelines for SQL*Forms User Exits
- EXEC TOOLS Statements

This chapter is supplemental. For more information about user exits, see the Oracle Forms Designer's Reference, the Oracle Forms Reference Manual, Vol. 2, and your system-specific Oracle manuals.

## What Is a User Exit?

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

When called by a SQL*Forms trigger, the user exit runs, then returns a status code to SQL*Forms (refer to Figure 13-1). Your user exit can display messages on the SQL*Forms status line, get and put field values, manipulate Oracle8 data, do high-speed computations and table lookups-even log on to different databases.

#### Figure 13-1 SQL*Forms Communicating with a User Exit

## Why Write a User Exit?

SQL*Forms Version 3 allows you 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 performed in third generation languages like C and FORTRAN (for example, numeric integration)
- controlling real time devices or processes (for example, issuing a sequence of instructions to a printer or graphics device)
- data manipulations that need extended procedural capabilities (for example, recursive sorting)
- special file I/O operations

## Developing a User Exit

This section outlines the way to develop a SQL*Forms user exit; later sections go into more detail. For information about EXEC TOOLS statements, which are available with Oracle Forms, see "EXEC TOOLS Statements" on page 13-14.

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

1. Write the user exit in a supported host language.
2. Precompile the source code.
3. Compile the modified source code.
4. Use the GENXTB utility to create a database table, IAPXTB.
5. Use the GENXTB form in SQL*Forms to insert your user exit information into the database table.
6. Use the GENXTB utility to read the information from the table and create an IAPXIT source module. Then, compile the source module.
7. Create a new IAP (the SQL*Forms component that runs a form) by linking the standard IAP object modules, your user exit object module, and the IAPXIT object module created in step 6.
8. In the form, define a trigger to call the user exit.
9. 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 your system-specific Oracle manuals.

## Writing a User Exit

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

- host-language
- EXEC SQL
- EXEC ORACLE
- EXEC IAF GET
- EXEC IAF PUT

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

### Requirements for Variables

The variables used in EXEC IAF statements must correspond to field names used in the form definition. If a field reference is ambiguous because you did not specify a block name, you get an error. An invalid or ambiguous reference to a form field generates an error.

Host variables must be named in the user exit Declare Section and must be prefixed with a colon (:) in EXEC IAF statements.

  Note: Indicator variables are not allowed in EXEC IAF GET and PUT statements.

### The IAF GET Statement

This statement allows your user exit to "get" values from fields on a form and assign them to host variables. The user exit can then use the values in calculations, data manipulations, updates, and so on. The syntax of the GET statement follows:

         EXEC IAF GET field_name1, field_name2, ...
            INTO :host_variable1, :host_variable2, ... END-EXEC.

where field_name can be any of the following SQL*Forms variables:

- field
- block.field
- system variable
- global variable
- host variable (prefixed with a colon) containing the value of a field, block.field, system variable, or global variable

If field_name is not qualified, it must be unique.

The following example shows how a user exit GETs a field value and assigns it to a host variable:

         EXEC IAF GET employee.job INTO :NEW-JOB END-EXEC.

All field values are character strings. If it can, GET converts a field value to the datatype of the corresponding host variable. If an illegal or unsupported datatype conversion is attempted, an error is generated.

In the last example, a constant is used to specify block.field. You can also use a host string to specify block and field names, as follows:

         MOVE "employee.job" TO BLKFLD.
         EXEC IAF GET :BLKFLD INTO :NEW-JOB END-EXEC.

Unless the field is unique, the host string must contain the full block.field reference with intervening period. For example, the following usage is invalid:

         MOVE "employee" TO BLK.
         MOVE "job" TO FLD.
         EXEC IAF GET :BLK.:FLD INTO :NEW-JOB END-EXEC.

You can mix explicit and stored field names in a GET statement field list, but not in a single field reference. For example, the following usage is invalid:

         MOVE "job" TO FLD.
         EXEC IAF GET employee.:FLD INTO :NEW-JOB END-EXEC.

### The IAF PUT Statement

This statement allows your user exit to "put" the values of constants and host variables into fields on a form. Thus, the user exit can display on the SQL*Forms screen any value or message you like. The syntax of the PUT statement follows:

         EXEC IAF PUT field_name1, field_name2, ...
            VALUES (:host_variable1, :host_variable2, ...) END-EXEC.

where field_name can be any of the following SQL*Forms variables:

- field
- block.field
- system variable
- global variable
- host variable (prefixed with a colon) containing the value of a field, block.field, system variable, or global variable

The following example shows how a user exit PUTs the values of a numeric constant, string constant, and host variable into fields on a form:

         EXEC IAF PUT employee.number, employee.name, employee.job
            VALUES (7934, 'MILLER', :NEW-JOB) END-EXEC.

Like GET, PUT lets you use a host string to specify block and field names, as follows:

         MOVE "employee.job" TO BLKFLD.
         EXEC IAF PUT :BLKFLD VALUES (:NEW-JOB) END-EXEC.

On character-mode terminals, a value PUT into a field is displayed when the user exit returns, rather than when the assignment is made, provided the field is on the current display page. On block-mode terminals, the value is displayed the next time a field is read from the device.

If a user exit changes the value of a field several times, only the last change takes effect.

## Calling a User Exit

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.

## Passing Parameters to a User Exit

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

However, the user exit string allows you 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:

    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.

## Returning Values to a Form

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 generated by precompiler (see the next section). The three results have the following meanings:

If a user exit changes the value of a field, then returns a failure or fatal error code, SQL*Forms does not discard the change. Nor does SQL*Forms discard changes when the Reverse Return Code switch is set and a success code is returned.

### The IAP Constants

The precompiler generates three symbolic constants for use as return codes. They are prefixed with IAP. For example, the three constants might be IAPSUCC, IAPFAIL, and IAPFTL.

### Using the SQLIEM Function

By calling the function SQLIEM, your user exit can specify an error message that SQL*Forms will display on the message line if the trigger step fails or on the Display Error screen if the step causes a fatal error. The specified message replaces any message defined for the step.

The syntax of the SQLIEM function call is:

         CALL "SQLIEM" USING ERROR-MESSAGE  ERROR-MESSAGE-LEN.

where ERROR-MESSAGE and ERROR-MESSAGE-LEN are character and integer variables, respectively. The Oracle Precompilers generate the appropriate external function declaration for you. You pass both parameters by reference; that is, you pass their addresses, not their values. SQLIEM is a SQL*Forms function; it cannot be called from other Oracle tools.

### Using WHENEVER

You can use the WHENEVER statement in an exit to detect invalid datatype conversions (SQLERROR), truncated values PUT into form fields (SQLWARNING), and queries that return no rows (NOT FOUND).

## Sample Program 5: Oracle Forms User Exit

This user exit concatenates form fields. To call the user exit from a Oracle Forms trigger, use the syntax

    <user_exit>('CONCAT <field1>, <field2>, ..., <result_field>');

where user_exit is a packaged procedure supplied with Oracle Forms and CONCAT is the name of the user exit. A sample CONCAT form invokes the user exit.

     IDENTIFICATION DIVISION.
     PROGRAM-ID.  CONCAT.
     ENVIRONMENT DIVISION.
     DATA DIVISION.
     WORKING-STORAGE SECTION.

         EXEC SQL BEGIN DECLARE SECTION END-EXEC.
             01  FIELD-NAME             PIC X(80)  VARYING.
             01  FIELD-VALUE            PIC X(80)  VARYING.
             01  RESULT                 PIC X(800) VARYING.
         EXEC SQL END DECLARE SECTION END-EXEC.
         EXEC SQL INCLUDE SQLCA END-EXEC.
             01  EXIT-MESSAGE           PIC X(80).
             01  EXIT-MESSAGE-LEN       PIC S9(9) COMP.
             01  RTN-CODE               PIC S9(9) COMP.
                 77  INDX               PIC S9(4) COMP.
             01  DONE-FLAG              PIC X.
                 88  DONE               VALUE 'Y'.
             01  PTR                    PIC S9(4) COMP.
             01  WS-CMD-LINE.
                 05  WS-CMD-LINE-Y      PIC X(80).
                 05  WS-CMD-LINE-X      REDEFINES WS-CMD-LINE-Y
             01  WS-FIELD-NAME-AREA.
                 05  WS-FIELD-NAME      PIC X(80).
                 05  WS-FIELD-NAME-X    REDEFINES WS-FIELD-NAME
                                        PIC X OCCURS 80.
                 05  WS-FIELD-NAME-LEN  PIC S9(4) COMP.

     LINKAGE SECTION.
             01  CMD-LINE               PIC X(80).
             01  CMD-LINE-LEN           PIC S9(9) COMP.
             01  ERR-MSG                PIC X(80).
             01  ERR-MSG-LEN            PIC S9(9) COMP.
             01  IN-QUERY               PIC S9(9) COMP.
             01  RETURN-VALUE           PIC S9(9) COMP.

     PROCEDURE DIVISION USING CMD-LINE, CMD-LINE-LEN,
                              ERR-MSG, ERR-MSG-LEN,
                              IN-QUERY, RETURN-VALUE.

     MAIN.
         MOVE 1 TO PTR.
         MOVE SPACE TO RESULT-ARR.
         MOVE ZERO TO RESULT-LEN.
         MOVE SPACE TO DONE-FLAG.
         MOVE 7 TO INDX.
         MOVE CMD-LINE TO WS-CMD-LINE-Y.
         PERFORM CMD-LINE-PARSE UNTIL DONE.
         EXEC SQL
             WHENEVER SQLERROR GOTO SQL-ERROR
         END-EXEC.
         MOVE WS-FIELD-NAME TO FIELD-NAME-ARR.
         MOVE WS-FIELD-NAME-LEN TO FIELD-NAME-LEN.
         EXEC IAF
             PUT :FIELD-NAME VALUES(:RESULT)
         END-EXEC.
         MOVE SQL-IAPXIT-SUCCESS TO RTN-CODE.
         EXIT PROGRAM GIVING RTN-CODE.

     CMD-LINE-PARSE.
         MOVE ZERO TO WS-FIELD-NAME-LEN.
         MOVE SPACES TO WS-FIELD-NAME.
         MOVE SPACES TO FIELD-NAME-ARR.
         MOVE ZERO TO FIELD-NAME-LEN.
         PERFORM GET-FIELD-NAME
             UNTIL WS-CMD-LINE-X(INDX) = ',' OR DONE.
         IF WS-CMD-LINE-X(INDX) = ','
         MOVE SPACES TO FIELD-NAME-ARR
         MOVE WS-FIELD-NAME TO FIELD-NAME-ARR
         MOVE WS-FIELD-NAME-LEN TO FIELD-NAME-LEN
         MOVE SPACES TO FIELD-VALUE-ARR
         EXEC IAF
             GET :FIELD-NAME INTO :FIELD-VALUE
         END-EXEC
             STRING FIELD-VALUE-ARR
                 DELIMITED BY SPACE
                 INTO RESULT-ARR
                 WITH POINTER PTR
             ADD FIELD-VALUE-LEN TO RESULT-LEN
             ADD 1 TO INDX.

     GET-FIELD-NAME.
         IF WS-CMD-LINE-X(INDX) NOT EQUAL SPACE
             ADD 1 TO WS-FIELD-NAME-LEN
             MOVE WS-CMD-LINE-X(INDX) TO
                 WS-FIELD-NAME-X(WS-FIELD-NAME-LEN).
         ADD 1 TO INDX.
         IF INDX > CMD-LINE-LEN MOVE 'Y' TO DONE-FLAG.

     SQL-ERROR.
         EXEC SQL
             WHENEVER SQLERROR CONTINUE
         END-EXEC.
         MOVE SQLERRMC TO EXIT-MESSAGE.
         MOVE SQLERRML TO EXIT-MESSAGE-LEN.
         CALL "SQLIEM" USING EXIT-MESSAGE EXIT-MESSAGE-LEN.
         MOVE SQL-IAPXIT-FAILURE TO RTN-CODE.
         EXIT PROGRAM.

## Precompiling and Compiling a User Exit

User exits are precompiled like stand-alone host programs. Refer to Chapter 7, "Running the Pro*COBOL Precompiler".

For instructions on compiling a user exit, see your system-specific Oracle manuals.

## Using the GENXTB Utility

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 that allows you to enter the following information for each user exit you define:

- exit name
- host-language code (COBOL or C)
- 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 source program that GENXTB creates.

## Linking a User Exit into SQL*Forms

Before running a form that calls a user exit, you must link the user exit into IAP. 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 host-language link libraries. The details of linking are system-dependent, so check your system-specific Oracle manuals.

## Guidelines for SQL*Forms User Exits

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

### Naming the Exit

The name of your user exit cannot be an Oracle reserved word. Also avoid using names that conflict with the names of SQL*Forms commands, function codes, and externally defined names used by SQL*Forms.

SQL*Forms converts the name of a user exit to upper case before searching for the exit. Therefore, the exit name must be in upper case in your source code if your host language is case-sensitive.

The name of the user exit entry point in the source code becomes the name of the user exit itself. The exit name must be a valid file name for your host language and operating system.

### Connecting to Oracle

User exits communicate with Oracle8 via the connection made by SQL*Forms. However, a user exit can establish additional connections to any database via SQL*Net. For more information, see "Concurrent Logons" on page 3-46.

### Issuing I/O Calls

SQL*Forms I/O routines might conflict with host-language printer I/O routines. If they do, your user exit will be unable to issue printer I/O calls. File I/O is supported but screen I/O is not.

### Using Host Variables

Restrictions on the use of host variables in a stand-alone program also apply to user exits. Host variables must be named in the user exit Declare Section and must be prefixed with a colon in EXEC SQL and EXEC IAF statements. However, the use of host arrays is not allowed in EXEC IAF statements.

### Updating Tables

Generally, a user exit should not UPDATE database tables associated with a form. For example, suppose an operator updates a record in the SQL*Forms work space, then a user exit UPDATEs the corresponding row in the associated database table. When the transaction is COMMITted, the record in the SQL*Forms work space is applied to the table, overwriting the user exit UPDATE.

### Issuing Commands

Avoid issuing a COMMIT or ROLLBACK command from your user exit because Oracle8 will commit or roll back work begun by the SQL*Forms operator, not just work done by the user exit. Instead, issue the COMMIT or ROLLBACK from the SQL*Forms trigger. This also applies to data definition commands (such as ALTER and CREATE) because they issue an implicit COMMIT before and after executing.

## EXEC TOOLS Statements

EXEC TOOLS statements support the basic Oracle Toolset (Oracle Forms, Oracle Reports, and Oracle Graphics) by providing a generic way to handle get, set, and exception call-backs from user exits. The following discussion focuses on Oracle Forms but the same concepts apply to Oracle Reports and Oracle Graphics.

Besides EXEC SQL, EXEC ORACLE, and host language statements, you can use the following EXEC TOOLS statements to write an Oracle Forms user exit:

- SET
- GET
- MESSAGE

The EXEC TOOLS GET and SET statements replace the EXEC IAF GET and PUT statements used with SQL*Forms. Unlike IAF GET and PUT, TOOLS GET and SET accept indicator variables. The EXEC TOOLS MESSAGE statement replaces the message-handling function SQLIEM. The EXEC TOOLS SET CONTEXT and GET CONTEXT statements are new and not available with SQL*Forms, Version 3.

  Note: COBOL does not have a pointer datatype, so you cannot use the SET CONTEXT and GET CONTEXT statements in a Pro*COBOL program.

### EXEC TOOLS SET

The EXEC TOOLS SET statement passes values from your user exit to Oracle Forms. Specifically, it assigns the values of host variables and constants to Oracle Forms variables and items. The values are displayed after the user exit returns control to the form.

To code the EXEC TOOLS SET statement, you use the syntax

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

where form_variable is an Oracle Forms field, parameter, system variable, or global variable, or a host variable (prefixed with a colon) containing the name of one of the foregoing items.

In the following Pro*COBOL example, your user exit passes an employee name (with optional indicator) to Oracle Forms:

         EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         ...
             01  ENAME      PIC X(20) VARYING.
             01  ENAME-IND  PIC S9(4) COMP.
         EXEC SQL END DECLARE SECTION END-EXEC.
         ...
         MOVE "MILLER" TO ENAME-ARR.
         MOVE 6 TO ENAME-LEN.
         MOVE ZERO TO ENAME-IND.
         EXEC TOOLS SET emp.ename VALUES (:ENAME:ENAME-IND) END-EXEC.

In this example, emp.ename is an Oracle Forms block.field.

### EXEC TOOLS GET

The EXEC TOOLS GET statement passes values from Oracle Forms to your user exit. Specifically, it assigns the values of Oracle Forms variables and items to host variables. As soon as the values are passed, the user exit can use them for any purpose.

To code the EXEC TOOLS GET statement, you use the syntax

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

where form_variable is an Oracle Forms field, parameter, system variable, or global variable, or a host variable containing the name of one of the foregoing items.

In the following example, Oracle Forms passes an employee name from the block.field emp.ename to your user exit:

         EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         ...
             01  ENAME      PIC X(20) VARYING.
         EXEC SQL END DECLARE SECTION END-EXEC.
         ...
         EXEC TOOLS GET emp.ename INTO :ENAME END-EXEC.
         ...

### EXEC TOOLS MESSAGE

The EXEC TOOLS MESSAGE statement passes a message from your user exit to Oracle Forms. The message is displayed on the Oracle Forms message line after the user exit returns control to the form.

To code the EXEC TOOLS MESSAGE statement, you use the syntax

          EXEC TOOLS MESSAGE message_text [severity_code] END-EXEC.

where message_text is a quoted string or a character host variable, and the optional severity_code is an integer constant or host variable. The MESSAGE statement does not accept indicator variables.

In the following Pro*COBOL example, your user exit passes an error message and severity code to Oracle Forms:

         EXEC TOOLS MESSAGE "Bad field name! Please reenter." 15
         END-EXEC.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index