Forms FAQ

Oracle Forms FAQ:

What is Oracle Forms and what is it used for?

Oracle Forms is a 4GL Rapid Application Development (RAD) environment. Forms Builder is used to create applications to enter, access, change, or delete data from Oracle (and other) databases. The Forms Runtime environment is required to execute compiled Forms modules. Forms can also be deployed across the Web using the Oracle [Internet Application Server]] (iAS) Forms Services.

Oracle Forms is part of Oracle's Internet Developer Suite (iDS). Previous versions of it was called SQL*Forms.

Can one convert/reverse engineer a FMX back to a FMB file?

Unfortunately not. One should always backup the FMB files to ensure they are not lost. The best you can do is to open the FMX file in a good text editor and copy most of your SQL out of it. After that, you will have to recreate the form from scratch.

Can an Forms FMX be moved from one operating system to another?

No, FMX files are operating system dependent. On the other hand, FMB's are not. So, you can just copy your FMB files to the new operating system and regenerate them.

PS: You also need to regenerate the FMB files after upgrading to a new Forms version.

What tools can do a 'diff' to discover the differences between 2 versions of an FMB file?

Use an Object List Report. File > Administration > Object List Report This generates a text file of the form. Compare these to find the difference easily.

FORMS API Master -- This will help us to compare two Fmbs. in Tools Developer Compare option will be there you can compare two fmbs.

How does one iterate through items and records in a specified block?

One can use NEXT_FIELD to iterate (loop) through items in a specific block and NEXT_RECORD to iterate through records in a 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

Can one 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-LOGON Trigger
declare
   uname varchar2(10);
   pass  varchar2(10);
begin
   uname := 'username';
   pass  := 'password';
   logon(uname, pass || '@connect_database');
end;

Alternatively, edit the formsweb.cfg file located in ORACLE_HOME/forms/server creating a config like this:

[my_cfg]
userid=username/password@connect_database
form=MY_FORM.fmx

and after that, access the application like this:

http://<<ip_address:port>>/forms/frmservlet?config=my_cfg

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);

What is the difference between CALL_FORM, NEW_FORM and OPEN_FORM?

CALL_FORM: start a new form and pass control to it. The parent form will be suspended until the called form is terminated. user can not navigate to parent form until child form is open.

NEW_FORM: terminate the current form and replace it with the indicated new form. The old form's resources (like cursors and locks) will be released. parent form will be closed.

OPEN_FORM: Opens the indicated new form without suspending or replacing the parent form.user can navigate between parent and child forms.

How does one suppress or customize error messages in Forms?

One can either set the message level using the system variable SYSTEM.MESSAGE_LEVEL or trap errors using the ON-ERROR or ON-MESSAGE triggers.

MESSAGE_LEVEL:

Set to 0, 5, 10, 15, 20, 25 to suppress all messages with severity below this level. The default level is 0. Messages with a level higher than 25 cannot be suppressed. See the "Forms Error Messages Manual" for more details about the various MESSAGE_LEVEL's:

• 0 - Default value. All types of messages from the other levels of severity.
• 5 - Reaffirms an obvious condition.
• 10 - Indicates that the operator has made a procedural mistake.
• 15 - Declares that the operator is attempting to perform a function for which the form is not designed.
• 20 - Indicates a condition where the operator cannot continue an intended action due to a problem with a trigger or another outstanding condition.
• 25 - Indicates a condition that could result in the form performing incorrectly.
• >25 - Indicates a message severity level that you cannot suppress via the SYSTEM.MESSAGE_LEVEL system variable.

Examples:

:SYSTEM.MESSAGE_LEVEL := '25';
COMMIT;
:SYSTEM.MESSAGE_LEVEL := '0';

/* For suppressing FRM-40100: At first record. */
:SYSTEM.MESSAGE_LEVEL := '5';
FIRST_RECORD;
:SYSTEM.MESSAGE_LEVEL := '0';

/* For suppressing FRM-40350: Query caused no records to be retrieved. */
:SYSTEM.MESSAGE_LEVEL := '5';
EXECUTE_QUERY;
:SYSTEM.MESSAGE_LEVEL := '0';

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)');

FORMS_DDL can also be used to create dynamic SQL statements at runtime. The FORMS_SUCCESS built-in can be used to determine if the last executed built-in was successful.

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.

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?).

Can one change the mouse pointer in Forms?

The SET_APPLICATION_PROPERTY build-in in Oracle Forms allows one to change the mouse pointer. Example:

SET_APPLICATION_PROPERTY(CURSOR_STYLE, BUSY);

The following cursor styles are supported:

• BUSY - Specifies a busy symbol
• CROSSHAIR - Specifies a crosshair symbol
• DEFAULT - Specifies an arrow symbol
• HELP - Specifies a help symbol/ Displayed as a hand in WebForms
• INSERTION - Specifies an insertion symbol
• HAND - Hand cursor pointer for Developer 6.0 and above

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.

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).

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).

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:

• Where to Get Compilers and Other Files?

Note: Designer/2000 includes a Help Generator that can generate source files for the Help Compiler.

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;

How can I generate all my forms in a batch?

Look at this DOS Batch file example:

@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 FOR %%F in (*.fmb) DO start /w f45gen32 userid=%1 batch=y module=%%F
FOR %%F in (*.fmb) DO start /w ifcmp90.exe userid=%1 module=%%F
 
@echo.
@echo Done!!! Remember to move the FMX files into your runtime directory.
@echo.
	
:END

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. Forms 6i uses commands f60gen, f60run, etc.