Why should I save my Forms in the database?
Forms should always be saved in the database because:
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.
· Back to top of file
Why is a List of Values so slow?
Unlike a normal SQL*Forms query, which performs buffering, a List of Values (LOV) will read all of the records queried from the database before displaying them.
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.
· Back to top of file
Why shouldn't I overuse SYSDATE?
Remember "form_field_x := SYSDATE;" will translate into a
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;
instead of:
screen_field_a := SYSDATE;
screen_field_b := SYSDATE;
· Back to top of file
Why should I try to keep my Forms as small as possible?
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.
General rules:
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.
· Back to top of file
Why should I use database integrity constraints?
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.
· Back to top of file
In what triggers can't I use DML statements?
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.
· Back to top of file
Why can't one use DDL statements in a Form?
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.
· Back to top of file
In what triggers can I use restricted package procedures?
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.
· Back to top of file
Should I use SQL or SQL*Forms statements?
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.
· Back to top of file
Why shouldn't I use KEY-NXTFLD and KEY-PRVFLD triggers?
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.
· Back to top of file
Why and how should I use database packages, procedures and functions?
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)
Some of the advantages of this approach is:
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;
· Back to top of file
How can I eliminate duplicate rows in a base table block?
To eliminate duplicate rows in a base table block, create a SELECT DISTINCT view on your base table. Be careful for bad performance though.
· Back to top of file
Should I use GLOBAL variables or HIDDEN fields?
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)
Therefore, Non-Base Table Fields will take up less room than a global variable when it holds less than 175 bytes or characters. For database fields, global variables will use less space when dealing with more than about 88 bytes.
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.
In general, control fields can offer lower memory requirements and faster performance for datatypes other than character. Globals are a more convenient data structure since they do not deal with screen position, attributes to be set and triggers to be fired.
· Back to top of file
Is it better to use :block.fieldname or just :fieldname?
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.
· Back to top of file
How can I generate unique database key values?
Use the sequence generator to generate unique primary keys:
SELECT sequence_name.NEXTVAL, ...
Issue this statement only as the last step of the PRE-INSERT after the validations. This will prevent the generation of numbers that won't be used.
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.
· Back to top of file
How can I make my Forms more portable?
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.
· Back to top of file
How can I make my code more readable?
Avoid the use of the NXTBLK, PRVBLK, NXTFLD, PRVFLD macros. Use GOBLK, GOFLD instead because it is easier to read and to maintain.
· Back to top of file
How can I make my Forms more memory efficient?
NEW_FORM as much as possible versus using CALL. With CALL, the previous form and any other previously CALLed forms are kept in memory.
· Back to top of file
How can I make my Forms perform better?
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.
· Back to top of file
Why doesn't my messages show on the screen?
Regardless of whether you call MESSAGE(), your message may or may not be displayed. This is because messages are displayed asynchronously. To display messages immediately, use the SYNCHRONIZE packaged procedure:
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.
· Back to top of file
Should I always use the latest and greatest functionality available?
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.
· Back to top of file
How can I get rid of V2-stype triggers?
Download and run this V2-style to PL/SQL Forms Converter.
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.
· Back to top of file

How does one iterate through items in a specified block?
Code example:
OriPos := TO_NUMBER(:System.Trigger_Record);
First_Record;

LOOP
-- do processing
IF (:System.Last_Record = 'TRUE') THEN
Go_Record(OriPos);
EXIT;
ELSE
Next_Record;
END IF;
END LOOP
· Back to top of file
Can on bypass the Oracle login screen?
The first thing that the user sees when using runform is the Oracle logon prompt asking them for their username, password, and database to connect to. You can bypass this screen or customise it by displaying your own logon screen. Eg:
ON-LOGIN
declare
uname varchar2(10);
pass varchar2(10);
begin
uname := 'username';
pass :='password';
logon(uname, pass'@connect_database');
end;
· Back to top of file
Can one Maximize/ Minimize a Window in Forms?
On MS-Windows, Forms run inside a Windows Multiple-Document Interface (MDI) window. You can use SET_WINDOW_PROPERTY on the window called FORMS_MDI_WINDOW to resize this MDI (or any other named) window. Examples:
set_window_property(FORMS_MDI_WINDOW, WINDOW_STATE, MINIMIZE);
set_window_property(FORMS_MDI_WINDOW, POSITION, 7, 15);
set_window_property('my_window_name', WINDOW_STATE, MAXIMIZE);
· Back to top of file
Can one issue DDL statements from Forms?
DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not directly supported from Forms because your Forms are not suppose to manipulate the database structure.
A statement like CREATE TABLE X (A DATE); will result in error:
Encountered the symbol "CREATE" which is a reserved word.
However, you can use the FORMS_DDL built-in to execute DDL statements. Eg:
FORMS_DDL('CREATE TABLE X (A DATE)');
· Back to top of file
Can one execute dynamic SQL from Forms?
Yes, use the FORMS_DDL built-in or call the DBMS_SQL database package from Forms. Eg:
FORMS_DDL('INSERT INTO X VALUES (' col_list ')');
Just note that FORMS_DDL will force an implicit COMMIT and may de-synchronize the Oracle Forms COMMIT mechanism.
· Back to top of file
Forms won't allow me to use restricted built-in's. What should I do?
How to get around the "can't use a restricted built-in in built-in XXX" message:
1. Create a TIMER at the point where you want the navigation to occur. Eg. create_timer('TIMER_X', 5, NO_REPEAT);
2. Code a WHEN-TIMER-EXPIRED trigger to handle the navigation
DECLARE
tm_name VARCHAR2(20);
BEGIN
tm_name := Get_Application_Property(TIMER_NAME);
IF tm_name = 'TIMER_X' THEN
Go_Item('ITEM_X');
END IF;
END;
Dirty but effective (didn't Oracle promise to fix this feature?).
· Back to top of file
Can one change the mouse pointer in Forms?
The SET_APPLICATION_PROPERTY build-in in Oracle Forms allow one to change the mouse pointer. Eg:
SET_APPLICATION_PROPERTY(CURSOR_STYLE, BUSY);
· Back to top of file
Why doesn't my messages show on the screen?
Regardless of whether you call the MESSAGE() built-in with ACKNOWLEDGE, NO_ACKNOWLEDGE, or with no mode specification at all, your message may or may not be displayed. This is because messages are displayed asynchronously. To display messages immediately, use the SYNCHRONIZE build-in:
message('...'); synchronize;
This can also be used to execute a query while the user is looking at the results of a previous query.
· Back to top of file
What happened to SQL*Menu?
From Forms V4.5, SQL*Menu is fully integrated into Oracle Forms. Application menus can be added to your application by creating Menu Modules (*.MMB) and generate it to Menu Module Executables (*.MMX).
· Back to top of file
How does one create a custom toolbar?
Create a new block, let's name it "TOOLBAR" and a canvas named "C_TOOLBAR" (for ilustration purposes). Put some iconic buttons on your canvas. Use the following properties for these buttons:
· Enabled: True
· Navigable: False
· Mouse Navigate: False
Now set the "Canvas Type" in the canvas property palette to "Horizontal Toolbar" and the "Form Horizontal Toolbar Canvas" in the module property palette to your canvas name (C_TOOLBAR in our case).
· Back to top of file
How does one compile MS Help files?
The Microsoft Help Compiler does not ship with Designer/2000 or Developer/2000, but you can download it from here:
Help Compiler - FTP Sites
Note: Designer/2000 includes a Help Generator that can generate source files for the Help Compiler.
· Back to top of file
How can I read/write OS Files from Forms?
OS files can be read/written from Forms using the TEXT_IO package in Forms. The TEXT_IO package has a datatype FILE_HANDLE. It also has procedures FCLOSE, GET_LINE, NEW_LINE, PUT, PUT_LINE & PUTF and a function FOPEN. Example:
DECLARE
file1 TEXT_IO.FILE_TYPE;
file2 TEXT_IO.FILE_TYPE;
str VARCHAR2(80);
BEGIN
file1 := TEXT_IO.FOPEN( 'input.txt','r' );
file2 := TEXT_IO.FOPEN( 'output.txt', 'w' );
TEXT_IO.GET_LINE( file1, str );
TEXT_IO.PUT_LINE( file2, str );
TEXT_IO.FCLOSE( file1 );
TEXT_IO.FCLOSE( file2 );
END;
· Back to top of file
How can I generate all my forms in a batch?
@echo off
@echo. +----------------------------------------------------------
@echo. FMXGNALL.BAT
@echo. +----------------------------------------------------------
@echo.
@echo. Create runtime FMXs from source FMBs
@echo. Will convert ALL of the fmbs in the current direcotry
@echo. Usage : FMXALL.BAT username/password@connect string
@echo.
@echo. +----------------------------------------------------------
@echo.
@echo. Username/Password@connect_string = %1
@echo.

IF %1 == "" GOTO END

@echo Removing old FMX files
del *.fmx

@echo Creating the new FMX files
rem Change f45gen32 to f45gen if in 16 bit environment.
FOR %%F in (*.fmb) DO start /w f45gen32 userid=%1 batch=y module=%%F

@echo.
@echo Done!!! Remember to move the FMX files into your runtime directory.
@echo.

:END
· Back to top of file
How does one get a form to run on Unix?
You need to design your form on your workstation. FTP or copy the Forms's FMB file to the Unix box. If you generate for a terminal environment (character based), the syntax is:
f45gen USERID=userid/passwd@db_name MODULE_TYPE=FORM MODULE=module_name
If you want to generate a Library file, replace FORM with LIBRARY. Use f45genm to generate your form in a Motif environment.
Use the "f45run" command to run your form.
· Back to top of file
Why do terminal users hate Forms?
Most Unix, MVS and VMS users do not like Forms 4.5/ 5.0 for a couple of reasons:
You need to design on a PC and frequently get compatibility problems (font scaling, etc);
Forms 4.5 is no improvement for Forms 3.0 terminal users at all, rather it is a step backwards;
Forms 4.5 uses too much memory and executables are about 400% larger than for its 3.0 counter part;
The largest Oracle Forms customers still runs on Forms V3.0 and will rather throw out Oracle than to convert to Forms 4.5.
I think Oracle should bring SQL*Forms v3.0 back for terminal users. They could rename the product to Oracle Forms for Terminals, or something.













REPORTS

· What exactly does the "Print Condition" do?
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
As an example, assume we have created a field inside a repeating frame with Print Condition Object set to 'anchoring object', and Print Condition Type set to 'All But First'. On every instance of that repeating frame which is printed entirely within a single page, our object will not print. However, if an instance of that frame spans more than one page then our object will print on the second and every subsequent page that this instance of the repeating frame spans.
For most objects you will not have to play with this print condition setting as the default setting is pretty good at determining what pages to print on, even though it only chooses between 'first' and 'last'. Only such things as heading objects you want reprinted on multiple pages are normally candidates for fooling around with this setting.
· Back to top of file
How do I create a truly dynamic 'where' condition which the user can input on the parameter form for my select statement
While setting a simple parameter for use in defining the select statement, such as a date, bill_period_id etc. is simple, there are times when you may wish to allow a user to add any "where" statement they wish. However, if you create a varchar user variable and try to reference it as an SQL condition ( e.g. Select * from account where :usercondition) you will get an error. The secret is that the variable must be initialized to a valid SQL condition before the Data Model will accept it. This is done in the "Initial Value" spot on the variable's properties form. The usual default is "1 = 1" which simply means all rows meeting whatever other conditions are included in the select statement will pass this condition if the user does not change it in the parameter form.
· Back to top of file
How do I change a user parameter at runtime from a layout object trigger?
Quite simply, you can't. Once the BeforeReport trigger has fired, Reports locks down the user parameters until the report is finished. Oh, I know you can put a statement into a layout trigger at design time and the compiler will accept it, but the moment you run the report you will get a nasty error and the report will die. Why they couldn't catch those problems at compile time I have no idea, except that it probably uses the same PL/SQL compiler as Forms which uses that same syntax for the perfectly acceptable function of changing field values.
That being said, there is valid technique to mimic having a user variable which can be changed over the course of the report execution. What you have to do is create a PL/SQL package that contains a variable as well as the functions to read and write to that variable. Since variables inside a package are both local to that package and persistent over the duration of the run, you use this to save and change your variable value. I know that this seems like overkill, but it is the most efficient way of handling an issue that is very rarely encountered. As you can probably guess, this technique is a last resort to finding an SQL work around if one exists.
· Back to top of file
How do I set the initial values of parameters for the parameter form at runtime?
This is what the BeforeForm trigger is primarily used for. Even if you have used a select statement to create a lookup list for the parameter, this statement is fully parsed before the parameter form is opened. Simply setting the parameter to a given value in the BeforeForm trigger will select that option as the default value displayed to the user. For example, assume you have a parameter called p_input_date which is intended to hold an invoice date. The following example will select the most recent invoice date as the default, and note that it properly handles exceptions to ensure that the report does not arbitrarily die if this default setting fails. Note also that like all report triggers, it must return a true or false value.
function BeforePForm return boolean is
begin
select max(bill_period_end_date + 1)
into :p_input_date
from billing_period
where bill_period_end_date <= (select trunc(sysdate)
from dual);
return (TRUE);
exception
when others then
:p_input_date := null;
return true;
end;
· Back to top of file
Why can't I highlight a bunch of fields and change all their format masks or print conditions at once?
You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
· Back to top of file
How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value. That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure. The syntax is SRW.Set_Field_char(0,) and the output of the object that the current trigger is attached to will be replaced by . There are also SRW.set_fileld_num, and SRW.set_field_date for numeric or date fields.
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals.