Chapter 8: Libraries

Previous versions of Oracle's Forms Designer tools, SQL*Forms and Oracle Forms, had two ways of sharing procedural code with multiple applications:

• User Exits let designers write procedures in a programming language (C, COBOL, Fortran, etc.) and link the compiled executable directly into the Runform module.
• Copy/Reference allowed designers to define procedures in forms that were saved to the database; new forms could include the procedures by either copying or referencing them.

These two methods are still available, but Form Builder provides a third. Designers of applications can create libraries of procedures which can be attached to any form or menu module. Calls within those forms can reference any procedure in an attached library as if the procedure were in the active form. Procedures are written in PL/SQL and compiled into a runtime module.

There are three file extensions used by the library facility:

• .PLL files are the binary library modules. This file is portable across systems (but be aware of the inclusion or exclusion of path information that may make the module unportable - see a discussion of this later in the chapter). The file is created when you save the library in the Designer. It contains both the source code from the design session and the compiled executable code needed at runtime. This is the file you attach to a form module.
• .PLX files contain only the compiled executable code. When the form is run, Form Builder looks first for the .PLX file; if one is not found, Form Builder looks for the .PLL file with the same name.
• .PLD files are the text form of the binary module. They can be used for source control. They are created with the command line option script=yes when you generate the form module using f45gen.

You'll create a library to handle messages that are produced when the form is run. The library will provide an alternate format for error messages and informative messages, printing some messages in a customized format and suppressing some completely. The object is to give all operators the same messages in the same format across all forms in the application.

Creating a Library

1. Create a new library module using one of these methods:

• Double-Click on the PL/SQL Libraries node of the Object Navigator.
• Select the PL/SQL Libraries node, then click on the Create icon on the Object Navigator toolbar.
• Choose File, New, Library.

As with other objects you create, any of these methods will work.

The new Library is shown on the Object Navigator. It has the usual default name of the object type and a sequence number (your number may be different).

2. Expand the new Library node and select the Program Units node.

3. Choose Navigator, Create, or click on the Create icon, or Double-click on the Program Unit node.

The New Program Unit dialog box opens.

4. Type message_handler in the Name entry field.

Leave the Type as Procedure.

5. Click on OK.

The new Program Unit is shown on the Object Navigator. The PL/SQL Editor opens with the skeleton of a PL/SQL procedure shown.

6. Type the following in the text entry area between the keywords BEGIN and END. Also add the parameters to the PROCEDURE header..

The procedure header has two parameters that are passed into the procedure. First, message_number is the five digit code assigned by Oracle to the message. It's used here to decide if the message is to be suppressed or changed in format. Second, is_error is a variable of type boolean. This means that it takes only the values TRUE or FALSE (without single quotes, as in the if statement eight lines from the bottom). The values are not character strings, which need single quotes around them. Their values are decoded when Form Builder and PL/SQL find the TRUE or FALSE for a boolean parameter.

Both parameters are marked as in, meaning that their values are passed into the procedure, but not out to the calling form. (There are also out and in out types.) The calling form will have triggers written to call the procedure and set up the values to be passed to it. You'll do that later in this chapter.

The procedure starts with the statement begin. The first command starts an if

test that continues through the entire procedure, ending at the next to last line. Once the first if is stated, the statement proceeds with a series of elsif tests that extend the if tests without having to end and restart them.

The next line is a comment. Two dashes (--) in a PL/SQL procedure identify a comment. (You can also use /* to begin and */ to end a comment.) The first comment informs you that messages telling the operator that the last row of the query has been retrieved will be suppressed completely. The action taken is to raise form_trigger_failure, meaning that the trigger is aborted at that point, with no alternate message displayed.

The next set of tests changes the wording of certain messages. The idea is to shelter the operator from Form Builder's standard message output and provide the same messages in all forms.

The next section of code uses the boolean variable is_error. If the variable is TRUE, it's an error message. You'll pass the value through the ON-ERROR trigger in the calling form. The message then includes the word ERROR: to let the operator know that something serious is happening. If the boolean value is FALSE, it's an informative message. It will be passed through the ON-MESSAGE trigger. In either case, the format of the message is changed from the standard to a customized format.

The if statement and the procedure are ended in the last two lines.

7. Click on Compile.

The messages Not Modified and Successfully Compiled should be shown on the bottom line of the editor window.

8. Click on Close.

9. Choose File, Save As...

Make sure the PL/SQL Library module is selected on the Object Navigator. If you choose File, Save, the library will be saved with the default name.

The Save As dialog box opens with the Form Builder name for the library shown.

10. Change the file name to msghndlr.pll.

11. Click on Save.

The name of the saved library is shown on the Object Navigator, replacing the default name.

Attaching a Library

The library must be attached to the active form. When you saved the library module, Form Builder created the binary .PLL file.

12. Expand the ORDENTRY nodes so you can see the node Attached Libraries.

13. Select Attached Libraries.

14. Click on the Create icon, or choose Navigator, Create, or double-click on the Attached Library node.

The Attach Library dialog box opens.

15. Click on Browse...

The PL/SQL Library File dialog box opens.

16. Select the correct drive and directory for the library you just saved.

17. Select msghndlr.pll.

18. Click on Open.

The name and path of the file are transferred to the Attach Library dialog box.

19. Click on Attach.

A path to the file was included when you selected the library file name. To make the form and the attached library portable across platforms, you should strip the path from the library specification. Form Builder will find the library file on other platforms by first searching the current directory, then the directories pointed to by the environmental variables FORMS50_PATH and ORACLE_PATH. When moving a library to another platform make sure it's in a directory pointed to by one of those variables (or in what would be the current directory). If the library won't be in one of those directories, don't strip the path from the file specification, or you'll be greeted by the error message 40034: Cannot attach library when you run the form.

20. Click on Yes to strip the path (or on No to leave it).

The library is attached to the ORDENTRY form. It appears on the Object Navigator.

Adding Triggers to Use the Library

In order to make use of the program units in the library, triggers in your form must call them. Two triggers will reference MESSAGE_HANDLER in your form. Both trap Form Builder events that display messages.

1. Expand the Triggers node at the Forms level of the Object Navigator.

2. Click on the Create icon on the Object Navigator.

The Triggers LOV opens

3. Select the trigger ON-MESSAGE

This trigger tells Form Builder what to do when it has a message to output to the operator of the form. (Without it, Form Builder uses its default logic to display messages.)

The Designer status line tells you how many choices of triggers there were when the LOV opened. If you use the Find feature, it will tell you how many selections you received from the the criterion you entered in the Find entry field. In this case, after selecting the ON-MESSAGE trigger, it tells you that there's one choice in the list, and 125 choices in the full list of trigger types.

4. Click on OK.

The PL/SQL Editor opens.

This is a Form level trigger whose name is ON-MESSAGE, as shown on the specification area of the editor.

5. Type in the PL/SQL Editor entry area:

--Call the message handler; pass FALSE to IS_ERROR

message_handler(error_code, FALSE);

You're passing two parameters to the procedure: the message number and the FALSE boolean value to tell the procedure that this isn't an error.

6. Click on Compile.

7. Click on New...

The Triggers LOV opens.

8. Select the ON-ERROR trigger.

This trigger tells Form Builder how to output any error messages it has to show to the operator.

9. Click on OK.

10. Type the following in the entry area of the PL/SQL Editor:

--Call the message handler; pass TRUE to IS_ERROR

message_handler(error_code, TRUE);

11. Click on Compile.

12. Click on Close.

Testing the Library Procedure

13. Choose File, Save. Make sure the form is selected.

14. Click on Run. (You may have to log on before the form generates.)

15. Click on Execute Query.

16. Press the up arrow as if you were moving to a previous record.

The message on the message line should read At first record. without any message number, as you defined it in the procedure.

17. Click on Save.

The message on the message line should read ERROR: No changes to save. (FRM-40401). Since this is an error, the logic for displaying an error message is executed, putting the word error before the message text, and the error number at the end.

Try other operations that produce messages. The display should show the text in the format you specified in MESSAGE_HANDLER, with the message number at the end, and the word ERROR at the beginning if it's an error.

You can add other messages to the procedure as you find the need. The library can be opened and edited as a separate module (even while a form module is open).

When you're done, choose File, Save to regenerate the library.

18. Click on Exit.

Maintaining a Library

Once a library is defined, you can add or remove procedures from it.

1. Select the library you want to modify on the Object Navigator under the Libraries node (not the Attached Libraries node under a form definition).

2. Choose Program, PL/SQL Editor...

3. Make any changes you want to the procedure(s) in the library.

You can add or delete procedures, functions, and so forth, or modify ones already in the library.

4. Choose File, Save to regenerate the .PLL file.

The changes are applied to each module (form or menu) to which the library is attached when you save it.

Stripping Source Code from the .PLL File

You can strip the source code to make the library module smaller. This process leaves just the pcode in the .PLL file. A module of this sort is not portable, but is smaller. Specify STRIP_SOURCE=YES on the f45gen command line to produce this type of file.

Object Libraries

The Object Library is another way to standardize objects across forms. Where a PL/SQL Library is used for code standardization, the Object Library is used to make interface objects standard. Objects in the Object Library are available to you when you open a form or menu module. The objects in it can be dragged and dropped into any open module. In addition, objects can be SmartClassed: A change to the original object will be made to the copy in the Object Library automatically. Form Builder opens all Object Libraries that were open at the end of the last Designer session. Object Libraries have a file extension of .OLB.

You'll move the Button_Palette to the Object Library and make it a SmartClass. Doing this will mean that you can use the same set of buttons across the forms of an application and make them into standard objects with the same look and feel. Adding or changing a button in the original, further, will add the button to the Object Libary's copy.

Creating an Object Library

1. Select the Object Libraries node on the Object Navigator.

2. Double-click on the node, or click on the Create button on the toolbar.

A new object library is created. (Your number may be different.)

3. Choose Tools, Object Library.

The Object Library opens with two tabs showing.

4. Expand the Data Blocks node on the Object Navigator.

5. Drag and drop the BUTTON_PALETTE block to the Object Library.

The block is now included in the Object Library.

6. Choose Object, SmartClass

The BUTTON_PALETTE now has a check mark next to it. Any change you make to the BUTTON_PALETTE block in the ORDENTRY form will be reflected in the object in the Object Library. You'll use this object in the next chapter.

Saving the Object Library

1. Click on the Save button on the Object Library toolbar.

The Save As dialog box opens.

2. Change the name to BUTTONS.OLB.

3. Click on Save.

Both the Object Navigator and the Object Library show the new name.