Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Note: This chapter is supplemental. For more information about user exits, see the Oracle Forms Reference Manual, Vol 2.
Called from a SQL*Forms trigger, the user exit runs, then returns a status code to SQL*Forms. See Figure 10 - 1. Your user exit can display messages on the SQL*Forms status line, get and put field values, manipulate Oracle data, do high-speed computations and table lookups, and even connect to different databases.
Figure 10 - 1. A User Exit Called by SQL*Forms
User exits are harder to write and implement than SQL, PL/SQL, or SQL*Forms commands. You will probably use them only to do processing that is beyond the scope of SQL, PL/SQL, and SQL*Forms. Some common uses are for user exits are:
SQL*Forms Version 3.0 allows you to use PL/SQL blocks in triggers. 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.
Additional Information: Consult your platform-specific Oracle documentation for additional information on user exits.
Additional Information: Consult your platform-specific Oracle documentation for additional instructions about compiling a user exit.
Host variables must be prefixed with a colon (:) in EXEC IAF statements.
Note: Indicator variables are not allowed in EXEC IAF GET and PUT statements.
EXEC IAF GET field_name1, field_name2, ...
INTO :HOST_VARIABLE1, :HOST_VARIABLE2, ...;
where field_name can be any of the following SQL*Forms variables:
EXEC IAF GET employee.job INTO :NEW_JOB;
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:
BLOCK_FIELD := "employee.job";
EXEC IAF GET :BLOCK_FIELD INTO :NEW_JOB;
Unless the field is in the context block, the Ada host string must contain the fully-qualified block.field reference with intervening period. For example, the following usage is invalid:
BLK := "employee";
FLD := "job";
EXEC IAF GET :BLK.:FLD INTO :NEW_JOB;
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:
FLD := "job";
EXEC IAF GET employee.:FLD INTO :NEW_JOB;
EXEC IAF PUT field_name1, field_name2, ...
VALUES (:HOST_VARIABLE1, :HOST_VARIABLE2, ...);
where field_name can be any of the following SQL*Forms variables:
EXEC IAF PUT employee.number, employee.name, employee.job
VALUES (7934, 'MILLER', :NEW_JOB);
Like GET, PUT lets you use a host string to specify block and field names, as follows:
BLOCK_FIELD := "employee.job";
EXEC IAF PUT :BLOCK_FIELD VALUES (:NEW_JOB);
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.
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. The three results have the following meanings:
success
The user exit encountered no errors. SQL*Forms proceeds to the success label or the next step, unless the Reverse Return Code switch is set by the calling trigger step.
failure
The user exit detected an error, such as an invalid value in a field. An optional message passed by the exit appears on the message line at the bottom of the SQL*Forms screen and on the Display Error screen. SQL*Forms responds as it does to a SQL statement that affects no rows.
fatal error
The user exit detected a condition that makes further processing impossible, such as an execution error in a SQL statement. An optional error message passed by the exit appears on the SQL*Forms Display Error screen. SQL*Forms responds as it does to a fatal SQL error.
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.
SQLIEM (error_message, message_length);
where error_message and message_length are character and integer variables, respectively. Pro*Ada generates the appropriate external function declaration for you.
-- -- $Header: concat.pad.d,v 1.3.710.1 93/11/28 20:09:34 losborne: Needtomrg_7_1 $ concat.pad -- -- ================================================================== -- User Exit routine designed to concatenate the character images of -- multiple fields and move the result to a specified field. -- An exit routine is gererally faster than the equivalent SQL stmt. -- -- Calling syntax within a SQL*Forms trigger is: -- -- #CONCAT field1,field2,...,fieldn,result_field -- -- A sample form, concat.inp, which invokes this user exit resides -- ORA_PRECOMP. -- ================================================================== with system; use system; function CONCAT (CMD : ADDRESS; CMDL : INTEGER; ERR : ADDRESS; ERRL : INTEGER; INQRY : INTEGER) return INTEGER is CMD_STR : STRING(1..CMDL); COL_NAME : STRING(1..CMDL); COL_START : INTEGER; COL_FIN : INTEGER; TEMP : ORACLE.VARCHAR(240); CONCAT_STR : ORACLE.VARCHAR(240); SQL_ERROR : exception; function POS_NEXT_COMMA(POS : INTEGER) return INTEGER is TEMP_POS : INTEGER := POS; begin if TEMP_POS >= CMDL then return(-1); end if; while (TEMP_POS < CMDL) and (CMD_STR(TEMP_POS) /= ',') loop TEMP_POS := TEMP_POS + 1; end loop; return(TEMP_POS); end POS_NEXT_COMMA; function "&"(left,right : ORACLE.VARCHAR) return ORACLE.VARCHAR is TEMP_VAR : ORACLE.VARCHAR(240); begin TEMP_VAR.LENGTH := ORACLE_operations."+"(left.LENGTH,right.LENGTH); TEMP_VAR.BUFFER(1..INTEGER(TEMP_VAR.LENGTH)) := left.BUFFER(1..INTEGER(left.LENGTH)) & right.BUFFER(1..INTEGER(right.LENGTH)); return(TEMP_VAR); end "&"; begin EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; ORACLE.FORMS.COPY_TO_STRING(CMD_STR,CMD,CMDL); CONCAT_STR.LENGTH := 0; -- skip over the characters "#concat " in the command line. COL_START := 8; loop COL_FIN := POS_NEXT_COMMA(COL_START) - 1; exit when COL_FIN = CMDL; EXEC IAF GET :CMD_STR(COL_START..COL_FIN) INTO TEMP; CONCAT_STR := CONCAT_STR & TEMP; COL_START := COL_FIN + 2; end loop; EXEC IAF PUT :CMD_STR(COL_START..CMDL) VALUES (:CONCAT_STR); return(ORACLE.FORMS.SUCCESS); exception when SQL_ERROR => ORACLE_FORMS.MESSAGE(SQLCA); return(ORACLE.FORMS.FAIL); when others => ORACLE_FORMS.MESSAGE("Processing error in CONCAT"); return(ORACLE.FORMS.FAIL); end CONCAT; pragma export_function (internal => CONCAT, external => CONCAT, parameter_types=> (ADDRESS, INTEGER, ADDRESS, INTEGER, INTEGER), result_type => INTEGER;
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:
GENXTB username/password outfile
where outfile is the name you give the Assembler or Ada source program that GENXTB creates.
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, SQL, and host-language link libraries.
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.
Command Line
Is the user exit string.
Command Line Length
Is the length (in characters) of the user exit string.
Error Message
Is the error string (failure message) if one is defined.
Error Message Length
Is the length of the error string.
In-Query
Is a boolean value indicating whether the exit was called in normal or query mode.
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.
SQL*Forms converts the name of a user exit to uppercase before searching for the exit. Therefore, the exit name must be in uppercase 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 filename for your host language and operating system.
EXEC TOOLS SET form_variable[, ...]
VALUES ({:host_variable :indicator | constant}[, ...]);
where form_variable is an Oracle Forms field, block.field, system variable, or global variable, or a host variable (prefixed with a colon) containing the value of one of the foregoing items. In the following example, a user exit passes an employee name (with optional indicator) to Oracle Forms:
...
ind_var : oracle.indicator := 0;
name_buff : string(1..10);
name_buff(1..10) := "MILLER ";
EXEC TOOLS SET emp.name VALUES (:name_buff:ind_var);
In this example, emp.ename is an Oracle Forms block.field.
EXEC TOOLS GET form_variable[, ...]
INTO :host_variable:indicator[, ...];
where form_variable is an Oracle Forms field, block.field, parameter, system variable, or global variable, or a host variable (prefixed with a colon) containing the value 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:
...
ind_var : oracle.indicator;
name_buff : string(1..8);
EXEC TOOLS GET emp.name INTO :name_buff:ind_var;
EXEC TOOLS SET CONTEXT :host_pointer_variable BY context_name;
where context_name is an undeclared identifier or a character host variable (prefixed with a colon) that names the context area.
EXEC TOOLS GET CONTEXT context_name INTO :host_pointer_variable;
where context_name is an undeclared identifier or a character host variable (prefixed with a colon) that names the context area.
EXEC TOOLS MESSAGE message_text [severity_code];
where message_text is a quoted string or a character host variable (prefixed with a colon), and the optional severity_code is an integer constant or an integer host variable (prefixed with a colon). The MESSAGE statement does not accept indicator variables. In the following example, your user exit passes an error message to Oracle Forms:
EXEC TOOLS MESSAGE 'Bad field name; please reenter.';
The above example could also be coded as follows:
s_code : integer := 0; msg_text : string(1..31) := "Bad field name; please reenter."; ... EXEC TOOLS MESSAGE :msg_text :s_code;
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |