SQL*Forms 3.0 FAQ

$Date: 02-Dec-1994 $
$Revision: 1.5 $
$Author: Frank Naudé $

Topics

• Only Forms stored in the database can be documented with the provided facilities;
• To reference or copy a Form's objects (procedures, triggers, blocks, fields, text pages) that Form must be stored in the database; and
• Forms stored in the database are automatically backed up with the database.

LOV's can be replaced with separate Forms to reduce the number of records transmitted from the database and to save huge amounts of memory.

SELECT SYSDATE FROM DUAL;

statement to be issued against the database. The same apply to USER, UID and USERENV. So, it is much better to use:

          screen_field_a := SYSDATE;
          screen_field_b := screen_field_a;

          screen_field_a := SYSDATE;
          screen_field_b := SYSDATE;

• A form is a data file read by the IAP execution process. A larger form will take longer to read and hence execute; and

• Long SQL statements and triggers will take longer to parse.

• Use short block and field names as well as table aliases to reduce your Form's size.
• Plan the use of 'common' database procedures and Forms triggers and what should be the naming standard of these.
• Modularize as much as possible. Use procedures for repeated code and make full use of parameters for any slight differences in functionality.
• Don't repeat SQL statements. Use form or database procedures (or user named triggers) instead. This prevents re-parsing of the same statements and reduces storage requirements since each cursor uses at least 4K of memory. This will also reduce the form's size and will result in faster load and execution times.

Use database integrity constraints on all your entities (tables) to denote primary and foreign key's, uniqueness and check values. With "DEFAULT BLOCK GENERATION" this information will be used by Forms to generate some of the validation and triggers on your behalf.

Do not use DML (Data Manipulation Language) statements such as INSERT, UPDATE, and DELETE in any triggers apart from commit time transactional triggers (PRE-INSERT, POST-UPDATE...). They can actually be used without causing a syntax error in most triggers, but this is generally to be avoided as it can de-synchronize the state of records in SQL*Forms and rows in the database, and can cause unexpected behaviour. The reason for this is that if you perform any DML the status of the Form does not change as it would, had you performed the operation on a default block. So if you exit the form without performing a commit, all your changes are lost as Forms does not know about them, and will not prompt you to COMMIT/ROLLBACk. Unlike DML commands, DDL (Data Definition Language) and DCL (Data Control Language) are not legal in any SQL*Forms triggers.

DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not supported in SQL*Forms because your Form is not suppose to manipulate the database structure. Don't use them in user exits either as they will force an implicit COMMIT and de-synchronize SQL*Forms.

All triggers can do SELECTs and call unrestricted packaged procedures but only KEY triggers and the ON-NEW-FIELD-INSTANCE trigger can call restricted package procedures. A list of these restricted packaged procedures are provided at the end of chapter 16, 'Packaged Procedures', of the SQL*Forms Designer's Reference.

Try not to issue SQL statements if the goal can be accomplished with SQL*Forms statements (eg. field assignment, DEFAULT_VALUE, etc.). Because SQL*Forms statements executes within Forms, it is much faster and does not use cursors.

Replace KEY-NXTFLD and KEY-PRVFLD with ON-VALIDATE-FIELD, ON-VALIDATE-RECORD and ON-NEW-FIELD-INSTANCE triggers as KEY-NXTFLD and KEY-PRVFLD will neither trigger in a block mode (mainframe) environment nor with mouse movement in a GUI bit-mapped environment.

One should write DATA API's (Application programming interfaces) or packages on the database utilizing stored functions and procedures. This API should then be used to replace all SQL access to the database. It can be build in an Object Oriented fashion based on your Entity Relationship Diagram or Function Hierarchy Diagram.

The names you choose should be readable and reflect functionality. Eg.:

      emp.hire_employee   (constructor)
      emp.fire_employee   (destructor)
      emp.retire_employee (destructor)
      emp.promote         (operation)
      emp.raise_salary    (operation)

• Ensure that code is centrally maintained and not duplicated in more than one Form;
• Different application interfaces can be written in different languages (English, Greek, etc.) utilizing different GUI builders (Forms, C++, SQLWindows, etc.) because the client interface doesn't contain any complicated logic;
• This will hide database complexities from the Forms designers;
• Will always ensure data integrity because tables can't be directly accessed (Insert/Update);
• This method is particularly suited for Client/Server deployment because it minimize network I/O;

To eliminate duplicate rows in a base table block, create a SELECT DISTINCT view on your base table. Be careful for bad performance though.

Global variables can be used in DML and SELECT statements but you can't use global variables in a default Where/Order By clause. Also, remember that global variables are always of type char and that implicit conversion might disable the use of indexes (eg. ...where number_field = :global.x).

Global variables that will no longer be used in the application should be deallocated from memory. Use the ERASE command to perform this. Below are some memory requirements for SQL*Forms variables:

     Base Table Field:        +-100 bytes + 2*field length
     Non-Base Table Field:    +-100 bytes + field length
     Global Variable:         +- 20 bytes (internal structure) +
                                255 bytes (data length)

Some considerations:

• Fields require longer set-up time during start up.
• Fields can be used in a DEFAULT WHERE/ORDER BY; unlike Globals.
• Globals are character only. TO_NUMBER and other datatype conversion functions are somewhat time expensive.
• Lookup times for fields (qualified by blockname) are comparable to lookup times for Globals.
• Memory for Global variables gets allocated on an as needed basis.
• Fields are cleared during CLEAR_FORM; Globals are not.

Always use :block.fieldname. The reason for this is that SQL*Forms contains an internal data structure to hold all of the information about blocks and their associated fields. By always specifying the blockname, SQL*Forms will not have to search through all of the blocks to find the correct field. This saves a lot of time especially for forms with many blocks and many fields. In addition, :block.field references are less ambiguous and easier to read.

Use the sequence generator to generate unique primary keys:

SELECT sequence_name.NEXTVAL, ...

You can do a "SELECT * FROM USER_SEQUENCES;" to get a list of sequences for your current Oracle user/schema.

Note that a SELECT MAX(...)+1 can generate duplicate key values. If you add a LOCK statement to prevent this, multiple users will wait for the next value of the primary key.

• Avoid operating system dependencies like C, COBOL or Assembler interfaces, "EXEC CICS..." call's etc.
• Re-write user-exits to PL/SQL procedures and functions because user exits limits portability, increases maintenance and unnecessarily complicates an application.
• Always put a SPACE before and after screen fields.

• NEW_FORM as much as possible versus using CALL. With CALL, the previous form and any other previously CALLed forms are kept in memory.

• Group SELECT ... FROM DUAL;'s. If the value of SYSDATE and USER are to be used, it will save time if you make one trip to the database instead of two. In a networked environment (Client/Server) this could be particularly advantageous.

           SELECT sysdate,user INTO ... FROM dual;

  versus

           SELECT sysdate INTO ... FROM dual;
           SELECT user into ... FROM dual;

  Note that if you access these same values elsewhere, you may wish to store these variables into globals or local variables rather than make another request across the network.
• Avoid using SELECT ... from DUAL's to perform logic or calculations. By using PL/SQL's IF THEN ELSE END IF; constructs, SQL*Forms does not have to access the database.
• Database procedures can be more efficient than the equivalent Forms and SQL code because it executed directly on the server (near the data) and doesn't need to go though the SQL layer.
• Be careful when enforcing uniqueness with the "PRIMARY KEY" column attribute. Oracle has to issue a SELECT statement to verify whether the value already exists. This check might be done with a full table scan!!! Make sure that a unique index or constraint has been defined for this column. An alternative is to use your own well designed query to replace this functionality.
• Retrieve as many columns and do as many computations and validations in a single select statement as possible. This will reduce storage, parsing time and database activity.

message('...'); synchronize;

The SYNCHRONIZE; packaged procedure forces SQL*Forms to display any information that it should write to the screen but hasn't yet.

This can also be used to execute a query while the user is looking at the results of a previous query.

With new and improved capabilities, you should guard against the tendency to create programs that are "works of art" and maintenance nightmares.

     "The ability to simplify means to eliminate the
     unnecessary so that the necessary may speak"
                    Hans Hofmann, Search for the Real, 1967.

This package will read the SQL*Forms V3.0 tables and convert all V2-stype triggers to PL/SQL. Run it after you've upgraded from Forms V2.x or before you upgrade from Forms 3.0 to a higher release.

• SQL*Forms V3 to Developer/2000 Upgrade

The following conversion tools are available in the market:

• GUI*Converter: Kumaran's SQL*Forms 3.0 to Forms 4.5 migration tool

• FormsGenie: Sierra Atlantic's SQL*Forms 3.0 to Forms 4.5 conversion tool

The SQL*Forms V3.0 Tutorial

SQL*Forms in Oracle