SQL*Forms Technical Tips - Misc...

Reprinted without permission from the Oracle Real-Time Support Bulletin Board System. Several hundred bulletins like this are available on-line, or can be requested from any technical support analyst. I believe they're working on an e-mail server as well.

    FORMS 3.0 QUESTIONS & ANSWERS (#4) Steve Muench, Tools Sup.

       What are the most common problems called in to Oracle Worldwide
       Support relating to conversion from SQL*Forms 2.x to SQL*Forms? 
 


       (*) After converting their forms from 2.x to 3.0, they get the


           impression that all of their triggers have disappeared
           because the trigger text for V2-Style triggers is "missing".
           This is certainly not the case. What they have forgotten is
           to [Zoom-In] on the V2 trigger steps from the Trigger
           Definition screen. This one additional keystroke does make a
           difference.
 


       (*) Non-Displayed fields are no longer visible in the screen


           painter. This occurs because conversion process moves all
           non-displayed fields to page zero.
 


       (*) After converting their forms, some customers expect to see


           their triggers written in PL/SQL. This is not the case. The
           conversion process merely changes the structure of the INP
           file from the particularly cryptic style familiar to Forms
           2.3 users, to the Forms 3.0 format -- structured, well
           formatted, and more readable. Recoding of V2-Style triggers
           is not necessary, since forms will generate and execute
           without modification. Logically, to take advantage of new
           functionality offered by PL/SQL (looping and explicit cursor
           management to name just two) new trigger code will need to be
           written.
 


       (*) The keymappings for VT220 and VT100 have changed from version


           2.3 to 3.0 of Forms. Oracle supplies the additional terminal
           mappings UPGRD220 and UPGRD100 for users who wish to remain
           true to their Forms 2.3 keystrokes with which they have
           become familiar.
 


       (*) Changes made to the CRT definitions will NOT affect SQL*Forms


           3.0 which uses a new technology for providing user-interface
           portability across display devices. Oracle*Terminal resource
           files have completely replaced their CRT analog. Instead of
           modifying tables in the database and generating a '.crt'
           file, now the Oracle*Terminal utility allows modification of
           all keymapping and device attribute information stored in
           these new resource files. Several default resource files are
           provided with Forms 3.0, as well as a utility called CRTTORES
           which converts CRT definitions store in the CRT tables to
           equivalent resource files.
 


       (*) Customers converting directly from Forms 2.0 to 3.0 in


           general have not familiarized themselves with the differences
           in behaviour which exist and are documented between versions
           2.0 and 2.3. These include the ability for Macros to fail and
           a difference in processing the values of right-formatted
           number fields for example. Forms 3.0 offers functional
           compatibility with the Forms 2.3 behaviour, as well as an
           option to convert directly from Forms 2.0 with or without the
           NOFAIL option.
 
       _______________________________________________________________
 
       What is the one event-trigger that allows use of 'restricted'
       procedures, and why aren't there more? 
 
       The ON-NEW-FIELD-INSTANCE trigger allows use of restricted
       packaged procedures.  V2 event triggers do not allow use of
       restricted procedures because they interfere with the internal
       navigation of SQL*Forms and could cause unpredictable results. 
       Most examples of 'restricted' behaviour can be successfully coded
       into KEY-Triggers if the ON-NEW-FIELD-INSTANCE does not satisfy
       the need. Current plans call for the introduction of new triggers
       which will allow for the use of both restricted and unrestricted
       packaged procedures.
 
       _______________________________________________________________
 
       Why isn't there a KEY-CLRFLD trigger?
 
       In some environments (especially Graphical User Interfaces), the
       "clear field" function is handled in a way that is invisible to
       and hence independent from SQL*Forms. It is not possible,
       therefore, to make such a trigger fire in a portable manner
       across platforms. However, since the ON-VALIDATE-FIELD trigger
       will fire even when the operator has changed a field's value to
       NULL, in contrast to the POST-CHANGE trigger, some behaviour
       intended for the hypothetical KEY-CLRFLD can be coded into an
       ON-VALIDATE-FIELD inside an IF...THEN clause that checks if the
       field's values is null.
 
       _______________________________________________________________
 
       What are the advantages/disadvantages to saving SQL*Forms in the
       database, versus simply using .INP files?
 
       Advantages
       ==========


       (*) Only forms stored in the DB can be documented with the


           provided facilities.


       (*) To reference or copy one form's objects (procedures, 


           triggers, blocks, fields, text pages) that form must be
           stored in the DB.


       (*) Forms stored in the DB are automatically backed up when the


           SYSTEM tables are exported.
 
       Disadvantages
       =============


       (*) Storing forms in the database takes up space.


 
       Many sites only store the forms which serve as 'reference
       libraries' in the database. When any form that contains
       references is generated, all of the forms containing the
       referenced objects MUST be present in the database and the
       current Oracle user must either be the owner of those forms or
       have been granted the 'Form Security' permission by the owner of
       the forms.
 
       _______________________________________________________________
 
       Can the default menu-bar in SQL*Forms 3.0 be modified without
       using SQL*Menu?
 
       Without associating a SQL*Menu application to your form, the
       default menu bar can be modified slightly.  For example, the
       default menu choice "Action" contains a "Print" menu selection. 
       If you define a Key-PRINT trigger at Form-Level, then whatever
       description you enter for the Key-PRINT trigger will appear in
       the menu, and if the operator selects the "Print" menu selection,
       the Key-PRINT trigger will be fired.
 
       _______________________________________________________________
 
       How can I write "full" data values to database columns of type
       date (date + time)?
 
       You can use the DATETIME data type. In comparison, the DATE
       datatype only stores the Month,Day,Year information. The default
       format mask for a DATETIME field is DD-MON-YYYY HH:MI[:SS]
 
       _______________________________________________________________
 
       What development considerations should I make in my Forms
       today, to support bit-mapped Forms tomorrow?
 
       The main consideration is that, where possible, stay away from
       using Key- triggers to accomplish things.  Use the ON-NEW-FIELD-
       INSTANCE instead, as this trigger will fire correctly in both
       bitmap and character mode environments.  Also, the Graphical User
       Interface treats called forms as modal windows in bitmap
       environments; if this is undesirable, then try where possible
       (and where it makes sense) to use blocks positioned on pop-up
       pages instead.  Finally, when designing the layout of character
       mode forms, be aware that when you move them to bitmap, that
       bitmap displays can use proportionally spaced fonts.  This means
       that you may want to  leave more "white space" between
       boilerplate text and fields, so that the text does not run into
       the fields when proportionally spaced fonts are used.
 
       _______________________________________________________________
 
       Why aren't there version 3 equivalents to the version 2 macros
       MOVLEFT and MOVRIGHT?
 
       It is not possible to support these functions in all environments
       that SQL*Forms 3.0 runs in.
 
       _______________________________________________________________
 
       Please describe the correct usage of the POST-CHANGE and
       ON-VALIDATE-FIELD triggers?
 
       The ON-VALIDATE-FIELD trigger should be used in all cases when
       the POST-CHANGE trigger used to be used, except during query
       processing. This is because ON-VALIDATE-FIELD will fire whenever
       a field is validated (not just if the field value has changed);
       however, validation does not occur when records are being queried
       and that why the POST-QUERY is needed to cover the fetch-time
       case. Typically, functionality that in Forms 2.3 applications was
       performed by a single POST-CHANGE trigger which had its
       limitations, is now programmed into separate ON-VALIDATE-FIELD
       trigger and POST-QUERY trigger to separate logically different
       events in form processing.
        
       _______________________________________________________________
 
       What is the difference between a "commit" and a "post"?
 
       From a functionality standpoint, POST is a proper subset of
       COMMIT_FORM, as shown below:
       
         +====+-----> (1) Navigating to Form level
         |    |       (2) Looping thru all DB blocks in the form
         |    |           to process Deletes, Inserts, and Updates
         |   POST         (a) Doing PRE-, ON-, and POST- transactional
      COMMIT  |               triggers.
       FORM   |           (b) Doing DML necessary to post changes in the
         |    |               Form to the DB.
         |    |       (3) Setting Rec, Block & Form status variables correctly
         |    +-----> (4) Navigating back to the current field
         |            (5) Performing a Session-wide Commit
         +===========>(5a) Freeing Row-level locks release resources
                          
 
       Any data which is simply POSTed, may be still be rolled back, but
       has essentially been recorded in the database (as seen by the
       current Oracle user). Issuing the final commit makes these posted
       changes visible to all other users of the database and completes
       the current transaction.
 
       _______________________________________________________________
       
       Why do the POST-INSERT and POST-COMMIT triggers fire before
       changes have been comitted to the database?
 
       As SQL*Forms sequentially processes each database block in the
       form as the result of a POST or COMMIT_FORM packaged procedure,
       each record which qualifies for INSERT into the database (i.e.
       new records) will be inserted with an appropriate statement of
       the form:
 
       INSERT INTO basetable(...names of database fields in block...)
                      VALUES(...values of database fields in block...)
 
       you have the chance just before this DML statement is executed
       and just after it is executed to perform any non-restricted
       operations in your PRE-INSERT and POST-INSERT triggers. The same
       holds for each delete or update processed. As the changes in
       SQL*Forms Record Buffers are translated into appropriate DML
       statements against the database the corresponding PRE- and POST-
       triggers fire. Since the PRE-COMMIT and POST-COMMIT "envelop" the
       posting process (see picture above) such that the statements in
       the PRE-COMMIT and POST-COMMIT belong to the same transaction as
       all of the DML operations being performed at POST time. Typically
       functionality that needs to be performed after the data has
       actually been Committed to the database can be written just after
       the invocation of the POST or COMMIT_FORM packaged procedures in
       the triggers that use them.
 
       _______________________________________________________________
       
       What are the most commmon mistakes made in SQL*Forms 3.0?
       


       (*) Failure to link SQL*Menu and SQL*Forms correctly. Both Menu


           and Forms must each be linked by indicating that one share a
           logon with the other. When SQL*Menu shares a logon with
           SQL*Forms, then a standalone menu can call a form in
           linked-in mode without spawning a new O/S process.
 


       (*) Failure to install the base tables. Since Forms 3.0 uses a


           completely distinct set of base tables from its predecessor,
           unless the operator chooses to Initialize the Database Tables
           for SQL*Forms 3.0, they will never actually get created. Many
           indicate that they are performing an Upgrade, so the scripts do
           not touch the base tables.
 


       (*) Failure to locate the Oracle*Terminal resource file on


           startup. The README notes explain how to correctly set the
           logical ORA_SQLFORMS30_OKT to instruct SQL*Forms where to
           find your terminal resource files.
 


       (*) Failure to exploit full functionality of List of Values.


           Automatic List-of-Values provides built-in searching on the
           data in the FIRST column listed in the L-O-V SQL-Text. Many
           people write their SQL such that searching on the most
           interesting column is not possible.
 


       (*) Failure to store forms in the database which will serve 


           as sources of copied or referened objects. If a form includes
           a reference to a particular object, then the form containing
           referenced object must be stored in the DB at load and
           generate time of the form making the reference.
 
       _______________________________________________________________
       
       Will a version 2.x Form, converted to version 3, but still
       retaining version 2-style tiggers, run faster or slower than in
       version 2? 
 
       It truly depends on the application. Typically applications
       converted directly with no V2 to V3 trigger modification will run
       about the same speed, but will require more memory because of
       some new overhead incurred by PLSQL and the Tookit (terminal/
       window manager). For a discussion of performance considerations
       with SQL*Forms 3.0, see the README file that is available with
       SQL*Forms 3.0.16. Some related performance information, based on
       atomic tests conducted by the Forms Development group in
       controlled benchmarking environment are:
 
                                  % vs. 2.3       % vs. 3.0.15
 
          Load and Exit           16% faster      11% faster
          Navigation              21% faster      17% faster
          Field Edit              5% faster       25% faster
          RDBMS                   7% faster       10% faster
          Read INP (terse)        ----------      80% faster (elapsed)
                                                  69% faster (CPU)
          Write FRM (terse)       ----------      86% faster (elapsed)
                                                  46% faster (CPU)
       _______________________________________________________________
       
       Can I access a version 5 database using Forms version 3.0 and
       SQL*Net?
 
       This is not supported; SQL*Forms 3.0 requires ORACLE V6.

• Dan

Daniel Druker
Senior Consultant
Oracle Corporation                    

| Dan Druker                    |  work 415.506.4803                          |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Sat May 23 1992 - 01:32:44 CEST