Oracle Forms

Forms Case Study

The FORM section consists of several user requirements that we will call Hands-On Oracle Form Builder. In these Hands-On, your client is a company that keeps track of its customers’ orders. You have been assigned to develop a customer order data entry application based on their requirements.

These are designed to challenge your skills in developing user’s friendly applications. Assuming, you as a developer gathered the requirements during prototyping sessions with the client, using the Rapid Application Development (RAD) model.

Introduction to From Builder

Oracle Developer tool

Oracle Developer Tools contain “back-end” and “front-end” development tools.

Back-End development tool

The “Back-End” development tool consists of:

Schema Builder,

Query Builder, and

Procedure Builder.

Front-End development tool

The “Front-End” development tool consists of:

Form Builder,

Report Builder, and

Graphics Builder.

Introduction

In this Hands-on book, we focus on the Form Builder tool, a FRONT-END development tool. You use the Form Builder tool to simplify the creation of data-entry screens or Forms. Forms are the applications that connect to a database, retrieve information requested by the user, present it in a layout specified by the form’s designer, and allow the user to modify or add information. Form Builder allows you to build forms quickly and easily.

Form Builder components

The main Form Builder components are:

FORM modules,

MENU modules,

PL/SQL libraries, and

Object libraries.

Form Module

The Form Module is a binary program code that is generated by Form Builder (fmb).

Menu Module

The Menu Module is a binary menu code that should be compiled and used by the Form Module (mmb).

PL/SQL libraries

The PL/SQL libraries are storage for stored procedure (pll).

Object Libraries

The Object libraries are storage for FORM objects.

The main objects in a Form module are:

Windows,

Blocks,

Items, and

Canvases.

Windows

Window is an empty frame to hold your object and it is the outermost boundary for a form.

Blocks

Block is a logical container that holds form objects such as data items and control buttons. There are two types of block: Data Block and Control Block. A Data Block is a link to your database information and holds database data. A Control Block holds information that has nothing to do with database.

Items

An item could be a database column or none database item. A canvas is a place to hold the objects.

Canvases

The four types of canvases are: Content, Stacked, Tab, and Toolbar. All can coexist within a single window. A content canvas is the basic background for all windows. A stacked canvas lays on top of the others. A tab canvas is the same as stacked canvases with handy “tabs” at the top. It can simply move from one canvas to another. A toolbar canvas contains push buttons giving users quick access such as horizontal canvas or vertical canvas.

Object Navigator

When you open the Forms Builder tool, the first window you will see is the Object Navigator window. The Object Navigator will be used on all development tools. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, and a list of all the built-in functions and procedures. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object.

Property palette

Oracle tools are somehow follow object oriented methodology. Although they are not pure object oriented but there are not far from it either. Each item in the Oracle tools can be identified as an object. To manipulate an object, you use Property Palette. As you can see from now on, you can do much without property palette. A Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse button then select Property Palette. The Property Palette provides complete control over your objects. It contains the property list of an object. Remember that the properties of an object can be changed to control the behavior of the object. The Item properties such as Tooltip, Hint, and Display Hint automatically, will be used to provide item-level assistance for the client and can be changed only from property palette. A text item appearance can be modified by manipulating properties such as Justification, or Format Mask properties. For controlling the data within a text item or display item, you can use properties such as Calculation Mode, and Lowest and Highest value.

Data Block Wizard tool

You can use the Data Block Wizard tool to create and modify data blocks.

Layout Wizard tool

You can also use the Layout Wizard tool in Form Builder to create, and modify forms quickly and efficiently. The Layout Wizard asks you a series of questions and then generates a basic form that fulfills the criteria you have specified. You can later use the Layout Wizard to modify the form if you want to change its fundamental design or contents. You always can modify the layout manually to better suit your applications’ needs.

Form Builder (Hands-On)

Hands-on introduction

You use Form Builder to simplify for the creation of data-entry screens, also known as Forms. Forms are the applications that connect to a database, retrieve information requested by the user, present it in a layout specified by Form designer, and allow the user to modify or add information. Form Builder allows you to build forms quickly and easily.

In this Hands-On, you learn how to: Create a Data block for the “Customer” table, Create a layout, Use “content” canvas, Use “execute query”, Navigate a table, Use next, previous record, Enter query, Manipulate table’s record, Insert, Update, Delete and Save record.

Form Builder Tool

Open the "Form Builder" tool.

Welcome window

You will get the ‘Welcome to the Form Builder’ window. If you don’t want to get this window anymore uncheck mark the ‘Display at startup’ box. You can start your entry with the following option:

· Use the data Block Wizard

· Build a new form manually

· Open an existing form

· Build a form based on a template

The default is ‘Use the data Block Wizard.’ If you want to build a new form manually, click on "Cancel” or checkmark ‘Build a new form manually’ and click ‘OK.’

Connect to database

In the ‘Object Navigator’ window, highlight "Database Objects." Go to the Main menu and choose "File," then "Connect."

In the ‘Connect’ window, login in as “iself” password “schooling,” then click “CONNECT.”

Notice that the box next to ‘Database Objects’ is not empty anymore and it has a ‘+’ sign in it. That will indicate that this item is expandable and you are able to see its entire objects.

Click on the ‘+’ sign next to the ‘Database Objects’ to expand all database schemas.

Create a Module

In the ‘Object Navigator’ window, highlight module1. This is a default name. Go to the Main menu and choose “File,” select “Save as” to store the new object in the “iself” folder and save it as customer data entry. "c:_de." In this example the ‘DE’ abbreviation stands for Data Entry.

Create a Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon. The ‘Create’ icon is in the vertical tool bar in the ‘Object Navigator’ window. It is a green ‘+’ sign. If you drag your cursor on the icon a tooltip will show ‘Create.’

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click on the “NEXT” icon.

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "customer” table; then click "OK."

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window. To choose selected columns, click on the one arrow sign. And then select all columns, and click “next.”

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish." You can also use the Data Block Wizard to modify your existing data block. Simply select the data block in the Object Navigator and click the Data Block Wizard toolbar button, or choose ‘Data Block wizard’ from the ‘Tools’ menu.

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Canvas is a place that you will have your objects such as columns, titles, pictures, etc. If you have already had your canvas, select the canvas and then click on the next. The following are different types of canvases: Content, Stacked, Vertical Toolbar, Horizontal Toolbar, and Tab.

Think of the ‘Content’ canvas as one flat place to have all your objects. In the stacked canvas, you can have multiple layers of objects and it is the same as the tab canvas. You use the vertical or horizontal toolbar canvases for your push buttons. Check the different types of canvases by clicking on the ‘down arrow’ box next to the ‘Type’ field. Select "content," then click “Next.”

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to be displayed on the canvas. Then click “Next.”

Change your objects appearances

Change size or prompt if needed. In this window, you can enter a prompt, width, and height for each item on the canvas. You can change the measurement units. As a default the default units for item width and height are points. You can change it to inch or centimeter. When you change size, click “Next.”

Selecting a layout style

Select a layout style for your frame by clicking a radio button. Select "Form," if you want one record at a time to be displayed. Select “Tabular,” if you want more than one record at a time to be displayed. Select "Forms," and then click “next.”

Record layout

Type the "Frame Title" and click "next." Checkmark the ‘Display Scrollbar’ box when you use multiple records or the ‘Tabular’ option.

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

You will see the output layout screen.

Make some window adjustments and then run the form. To run the form, click on the ‘Run’ icon. The ‘Run’ icon is on the horizontal toolbar in the ‘CUSTOMER_DE’ canvas.

The object module should be compiled successfully before executing the Form.

Execute Query

Click on the "Execute Query" icon below the main menu. If you drag the cursor on the toolbar in the ‘Forms Runtime’ window, a tooltip will be displayed and you see ‘Execute Query.’

So to know all your option, drag your cursor to view all the icon descriptions.

Next Record

Click on the "Next Record" icon to navigate to the next record.

Previous Record

Click on the "Previous Record" icon to navigate to the previous record.

This is an easy way to navigate through the “Customer” table.

Enter Query

Click on the "Enter Query" icon to query selected records.

Conditional query

To query all the customers whom their creditlimits are more than 7000, first click on the ‘Enter Query’ icon on the ‘Forms Runtime’ toolbar. All items will be blanked. Go to the ‘creditlimit’ item and type ‘> 7000.’ Then click on the ‘Execute Query’ icon on the ‘Forms Runtime’ toolbar.

Now, you should get all customers whom their creditlimits are more than 7000 dollars.

To query all customers whom their names start with the letter "E," first click on the ‘Enter Query’ icon on the ‘Forms Runtime’ toolbar. All items will be blanked. Go to the ‘NAME’ item and type ‘E%.’ Then click on the ‘Execute Query’ icon on the ‘Forms Runtime’ toolbar.

Now, you should get all customers whom their names start with the letter "E."

Query all customers whom their names start with the letter "E" and their creditlimits are more than 7000 dollars. Now you should be able to do that.

Insert Record

Click "Insert Record" to add new customer. All items on the forms will be blanked. You can either type all the customer information or duplicate it from pervious record.

Duplicate Record

To duplicate the previous record, go to the main menu and select the ‘Record’ sub-menu. A drop down menu will be displayed. Select the ‘Duplicate’ option in the sub-menu.

Apply the changes. Remember in this stage, your record was inserted but not committed yet.

Next and Previous Record

Click "next record" and "previous record" to navigate through the records and the one was added.

Save transactions

Click "Save" to commit the insert statement.

Delete Record

Click "Remove Record" to delete the record.

Lock a Record

You can also lock the record.

Exit from Form Runtime

Exit the FORM Runtime. If you have not committed any transaction, you will be prompted to save changes. Click “YES” to save changes.

Click “OK” for acknowledgement.

Don’t forget to save the Form.

Tab canvas, Master-Detail relationship

Master-Detail Relationship

A master/detail relationship or parent/child relationship is a relationship between two data blocks. A primary key of a master table is referenced by a foreign key in the detail table.

Tab canvas, Master-Detail relationship (Hands-On)

Hands-on introduction

Having performed Fact Finding, and determine the user's initial requirements, you are ready to create a data entry form based on those requirements. Keeping in mind that this is an iterative process, and the User is closely involved in the design. During your interviews, you have identified that tables Customer, Ord, and Items will be required, and you have an idea of the "Look-and-Feel" of the Form.

Your client asks you to create an easy to use data entry Form for their “Customer Order” data entry application.

See Figure 1, 2, and 3 for screen layout requirements.

Your tasks are:

1- Create a Form that contains three tabs for each Entity. The tabs should be labeled as "Customer", "Orders", and "Items

2- Develop a "Master-Detail" relationship using the "Tab Canvas" to establish a relationship between "Customer to Order" and "Order to Items."

3- Review your iterations with the User as prototypes of the final product.

4- Enhance your prototype. Working with you, the User has requested a more robust application.

5- Run and test all user functional requirements.

FIn this Hands-On, you will learn how to use: tab canvas, “object navigator,” “Data Blocks,” “Layout Editor,” “Property Palette,” “Run Form,” “Execute Query,” “Auto-Join data blocks,” and Master-Detail relationship.

Figure 1

Figure 2

Figure 3

Create a Module

Create a Data Block

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "customer” table; then click "OK."

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window. To choose selected columns, click on the one arrow sign. For this hands-on exercise select all columns, and click “next.”

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Canvas is a place that you will have your objects such as columns, titles, pictures, etc. If you have already have your canvas, select the canvas and then click on the next.

The following are different types of canvases: Content, Stacked, Vertical Toolbar, Horizontal Toolbar, and Tab. Think of the ‘Content’ canvas as one flat place to have all your objects. In the stacked canvas, you can have multiple layers of objects and it is the same as the tab canvas. You use the vertical or horizontal toolbar canvases for your push buttons. Check the different types of canvases by clicking on the ‘down arrow’ box next to the ‘Type’ field. Select "Tab Canvas," then click “Next.”

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to display them on the canvas. Then click “Next.”

Change your objects appearances

Selecting a layout style

Record layout

Type the Frame Title, Records Displayed, Distance Between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now you should see the form layout.

Create another new tab page for the customer's order.

Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon.

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "ord” table; then click "OK." You can also type ‘ord’ and then click on the "refresh” button.

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window and then click “next.”

Relationships

You may optionally create and delete master-detail relationships to other data blocks in your form. Click on the ‘Create Relationship…’ button to select a master data block. In this Hands-on exercise is the ‘Customer’ table. Notice that your master and detail items could represent your primary and foreign keys. Clicking on the ‘Delete Relationship’ button, will remove the relationship. Make sure that if you didn’t establish the primary and foreign keys, you should unmark the ‘Auto-join data blocks’ box. Then click "Create relationship" and “OK.”

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish."

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Change "tab page" to "new tab page” and click "next."

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns and deselect “CUSTID” since the customer is known and then click “Next.”

Change your objects appearances

Selecting a layout style

Select “Tabular” since there are more than one order for each customer and then click “next.”

Record layout

Type the Frame Title, Records Displayed, Distance between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.” Make sure to change the ‘Records Displayed’ item to be more than 1 and checkmark “Display Scrollbar.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now, there are two canvases: One for customer information and the other for their orders.

Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon.

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "items” table; then click "OK." You can also type ‘items’ and then click on the "refresh” button.

Selecting columns for the Data Block Wizard

To choose all columns, click on the two arrow signs in the ‘Data Block Wizard’ window and then click “next.”

Relationships

You may optionally create and delete master-detail relationships to other data blocks in your form. In this Hands-on exercise the master table is the ‘ORD’ table. Then click "Create relationship" and “OK.”

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, use the default checkmark radio button (Create the data block, then call the Layout Wizard), and click "Finish."

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Change "tab page" to "new tab page” and click "next."

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns and deselect “ORDID” since the order is known and then click “Next.”

Change your objects appearances

Selecting a layout style

Select “Tabular” since there is more than one item for each order and then click “next.”

Record layout

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now, there are three canvases:

Navigate the Tab pages

Navigate through the tab pages. Notice that page3, 5, and 7 don't mean anything. Your page number could be different.

Change the Tab labels

Select page3, right click and open its "property palette." Change its “name” and “label” to customer. Press the enter key. Notice! The name always changes to upper case.

Click on page5 to replace its properties and do the same for “Orders.” Be sure to press the enter key to confirm the changes.

Click on page7 to replace its properties and do the same for “Items.” Then close the window.

Now, the tabs are more meaningful. Navigate through the tab pages.

Collapse the objects to view items easier in the ”object navigator” window.

Select “customer_orders,” and right click to open its "property palette." Change "First Navigation Data Block" to “Customer.” Then close the window.

Run Form

Click on the "Run" icon to compile and execute the module.

Execute Query

Click "execute query" to query the customer information. Navigate through the “tab” options.

On the "orders" tab, select an item by highlighting the item. Then click on the ‘items’ tab. Notice on the “items” tab, all its items correspond to the selected order.

Repeat this.

On the customer tab, position the cursor on “Customer ID” and navigate through the table by clicking on “NEXT Record.”

Do the same for the previous record.

Enter Query

Click on "Enter Query.” The items in the form become blank. Type 106, and click "Execute Query." Navigate through the customer’s orders and its items.

Save the transaction

Close the window. Save the changes.

Help, Hint, format mask, properties

Introduction

To manipulate an object, you use Property Palette. As you can see from now on, you can do much without property palette. A Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse button then select Property Palette. The Property Palette provides complete control over your objects. It contains the property list of an object. Remember that the properties of an object can be changed to control the behavior of the object. The Item properties such as Tooltip, Hint, and Display Hint automatically, will be used to provide item-level assistance for the client and can be changed only from property palette. A text item appearance can be modified by manipulating properties such as Justification, or Format Mask properties. For controlling the data within a text item or display item, you can use properties such as Calculation Mode, and Lowest and Highest value.

Hands-on

During your prototyping iterations, the user thought it would be useful to have a Help option on the Form. A Help function will enable them to view descriptions of Form items as they move from item to item, and see helpful hints for each item.

They also want to have multi-lines on their “comments” item with word wrap; and apply their display format mask requirements.

See Figure 4.

Your tasks are:

1- Build Help functionality on the data entry Form at the items' level. The Figure 4 is an example of tool tip for the “CUSTID” item.

2- Provide “Display Format Mask” for all currency and date items based on your client’s layout requirements and content. For example, date should be keyed in the format MM-DD-YY, or currency amounts displayed with the dollar '$' sign, with format mask $99,999.99.

3- Provide a multi-lines “comments” box for comments and make sure the text is word wrapped.

4- Run and test all user functional requirements.

FIn this Hands-ON, you will learn how to use: Help, Hint, Format mask of properties, tab canvas, “object navigator,” “Data Blocks,” “Layout Editor,” “Property Palette,” “Run Form,” and “Execute Query.”

Figure 4

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders). In the ‘Open’ window, open the “customer_orders” form from the “iself” folder.

Save a module

Click on the “customer_orders” form. The color changes to blue. Then Change the name and save the Form name as version 02 (customer_orders_v02). This way the original form is untouched.

Layout Editor

Go to the Main menu and choose the "Tools" option and select "Layout Editor." Adjust the window if needed.

Open a property palette

In the ‘Layout Editor’ window, right click on the "CUSTID.” A drop down window will be opened. Click on the ‘Property Palette’ option to open its property palette window.

Create a Tooltip

In its Property Palette window, go to the “Tool tip” item and type "Enter customer ID."

In the Customer data block in the ‘Object Navigator’ window, click on the “Name” item.

Notice that the Property Palette will change from the “CUSTID” property palette to the “name” property palette.

In the property palette window, set "Display Hint automatically" to "yes"; then type the hint (Enter your customer name) on the "Hint" Box. Press the entry key and then close the window.

You can do the same process for each item in the canvas.

Resizing an object

In the Layout Editor window, select the “Comments” item. Notice that the ‘Comments’ item will get 8 handlers. By dragging the handler, the item can be resized. Resize the ‘Comments’ item so that we can enter multiple lines of comments in it.

Right click on it and open its "property palette." Set the "multi-line" item to "Yes." Then close the window.

Change an item format mask

Go to the "Orders" tab. Right click on the ‘Orderdate’ item to open its property and change its "format mask" to MM-DD-YY. Press the enter key to apply all the changes then close the window.

Do the same for the "shipdate" item; and press the enter key and close the window.

Right click on the "total" item and open its property palette. Change its format mask to $99,999.00. Change “justification” to right. Then close the window.

Go to the "items" tab. Right click on the "itemtot" item and open its property palette. Change its "justification" and "format mask" properties to $99,999.00

Do the same for the "actualprice" item with “Format mask” value $999.00.

Repeat the same for the quantity item ("Qty"), but don't change it's format mask.

Before to run the application, click on the ‘-‘ sign next to the ‘Data Block’ item to collapse all objects.

Run the Form runtime

Click on the "Run” icon, to compile and run the application. If there are any errors in the compilation, you will get them this time.

Execute Query

Click on the 'Execute Query' icon and navigate through the application and test the modification changes.

Notice! On the “Orders” tab page, the format mask of “Orderdate,” “Shipdate,” and “Total items” were changed.

On the “Items” tap page, the format mask of “Actualprice,” “qty,” and “itemtot” were changed. Try to navigate to see the changes.

Notice! The quantity item (“qty”) doesn't have comma.

Close the ‘Runtime Forms’ window.

Open the “Qty” item property and change it's format mask to 99,999. Close the property window and run it again.

Click “Execute Query” and navigate through its records.

Test Tooltip display

Go to the customer tab and test the help function. Check the tooltip messages and help hint messages.

On the comments box, write multi-lines comments to test the modification and then save the changes.

Navigate through the records and see the changes.

Save a module

Exit and save the Form.

LOVs, Record Group

List of Values (LOV)

A List of Values (LOV) is a modal window that populates a text item based on a selection made by the user from the list. The user can search a List of Values (LOV) for strings that occur anywhere within the values.

Record Group

A Record Group is a query that displays data from the database into the List of Values.

Hands-On

Your client needs more robust customer’s information retrieval. You are going to provide them with a list of customer names by adding a List Of Values (LOV) to the Form. This will help the data entry clerks to find a customer quickly without knowing the customer’s ID. The list should contain at a minimum, the customer's name and ID.

This will provide a fast and quick way to access customer information and increase the accuracy of the data entry.

See Figure 5.

Your tasks are:

1- Add a List Of Values (LOV) to the Form.

2- Retrieve customer ID by using customer name.

3- Query specific customer information using customer name.

4- Run and test all user functional requirements.

FIn this Hands-On, you will learn how to: use the List of Values (LOV), and group records, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 5

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V02)

from the “iself” folder.

Save with different name

Click on the “customer_orders_v02” form. The color changes to blue. Then change the name and save the Form name as version 03 (customer_orders_v03). This way the original form is untouched.

Layout Editor

In the Main menu, choose the "Tools" option; you can see all the Form tools. Select the "Layout Editor" option.

Create a List Of Value

In the ‘Object Navigator’ window, select the LOVs object known as "List of values."

Then click on the green ‘+’ (Create) icon. In the ‘New List Of Value’ window, leave the default radio button which is: ‘Use the LOV Wizard’ and the click ‘OK.’

Record Groups

LOVs get their data from Record Groups, which may be populated using SQL queries, or by entering a static list of values. In the LOV Wizard window, use the default to base your LOV on a new Record Group; and then click ‘Next.’

In the ‘SQL Query Statement’ box, write a ‘SQL’ statement to query customers name and their ID. Then click ‘Next.’

(SQL Query Statement)

SELECT name, custid FROM customer

The LOV may include some or all of the columns in the Record Group. Select the columns that you wish to include in your LOV. In this hands-on exercise, select all the columns and click “Next.”

You can specify the LOV column properties with its return value. Click on the "return value" box which is on the same row of the ‘CUSTID’ item; then click on "Look up return item." In the ‘Return Value’ window, select the "customer.custid" item and click "OK."

Notice that the ‘CUSTOMER.CUSTID’ value will be replaced in the box.

Click “Next” to continue.

Appearance of LOV window

If you want to display a title in your LOV window, type the title. You can change the size of the LOV window (Width and Height). You can also let the Forms Runtime to position your LOV. Apply changes if needed and click “Next.”

Behavior of LOV

To change the behavior of your LOV, change number of rows to be retrieve at a time, refresh record group data before displaying LOV, or Let the user filter records before displaying them. Accept the defaults as they appear and click ‘Next.’

Assign your LOV to ‘CUSTOMER.CUSTID’ by moving ‘CUSTOMER.CUSTID’ to the ‘Assigned item’ column and click “Next” again.

Click ‘Finish’ to create the new LOV. Now, your ‘Object Navigator’ should have an entry for your new LOV.

In the ‘Object Navigator’ window, right click on the new record groups to open its property palette. In the Property Palette, change the name to a more meaningful naming convention (RG_CUST_NAME); and then press the enter key.

Do the same for the new LOV object. Change the ‘Name’ value to LV_CUST_NAME. Notice that the ‘Record Group’ value was changed to RG_CUST_NAME.

In the ‘Object Navigator’ window, expand the ‘CUSTOMER’ data block in the ‘Data Blocks’ item. Expand the ‘Items’ object. Now, you should see all your selected data block items.

Assign LOV to an item

Right click on the CUSTID item and open its Property Palette. Go to the ‘List of Values’ item. Check to make sure that the List Of Values was assigned to it and then close the window.

Run the Form Runtime

Click on the "Run” icon (Traffic Green icon), to compile and run the application.

Notice that when the cursor is on “CUSTID,” the "List of Values" message will be displayed.

Enter Query

Click on the "Enter Query" icon to query a specific customer.

In the Main menu, click on “HELP” and select the ‘Keys’ option, to see all the function keys. Notice that the “F9” key is used for the “List of Values” item.

Retrieve LOV (F9)

Press “F9,” while the cursor is in the "CUSTID" item. A list of all the customers will be displayed. Select "Every Mountain" and then click “OK.”

Execute Query

Notice that its “Customer id” was returned.

Click on “Execute Query,” and navigate through the application and test the changes.

Click on “Enter Query,” and Press on the “F9” function.

In the LOV window, type the letter "W." then select the customer.

Click "Execute Query."

Navigate through the application.

Then close the window and save the form.

Radio Buttons

Introduction

A radio button group allows values stored in a data source column to display in a more graphical fashion, by having one radio button assigned to each possible value. Once a radio group is created, each radio button within it must be assigned a unique value.

Hands-On

Your users have indicated that they keep track of their salesmen's commission plans. The company has commission plans "A," "B," "C," and Blank. The “Blank” commission plan means that this is not applicable to salesman. Middle Management would like to stream line their salesmen contract assignment process. The company assigns salesmen, based on their commission performance. Those salesmen with higher than average commission will be rewarded and assigned to lucrative contracts.

To accommodate Management's need for tracking high performing salesmen, they ask you to change the commission plan item to the radio button style.

See Figure 6.

Your tasks are:

1- Change the commission plan column to the Radio Buttons.

2- Change the "order tab page" to implement the Radio Buttons.

3- Run and test all user functional requirements.

Note: The Radio Buttons are useful when there are two or more possible values but only one value can be true.

FIn this Hands-On, you will learn how to: add and use Radio Buttons, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 6

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V03)

from the “iself” folder.

Save a module

Click on the “CUSTOMER_ORDERS_V03” form. The color changes to blue. Then change its name and save the Form name as version 04 (customer_orders_v04). This way the original form is untouched.

Expand ORD data block

In the ‘Object Navigator’ window, expand “Data Blocks.” Then expand the order's data blocks (ORD) and its items.

Change an item in Radio Group

Right click on the “Commission Plan” item (COMMPLAN) to open its Property Palette. In the Property Palette window, change its item type to "Radio Group." Change "Initial Value" to "A," then press the enter key or click on any box to apply the changes. Close the window.

Notice the icon next to the Commission Plan (COMMPLAN) was changed in the ‘Object Navigator’ window and in the Layout Editor the Commission Plan item (COMMPLAN) has disappeared.

In the Object Navigator window, expand commission plan. Notice that there is no radio button associated with the Radio Group item.

Do some layout adjustments before creating the radio button. You need at least four radio buttons. Therefore you need more space to put the objects on the canvas. See above figure.

Moving objects

Select the shipdate, total and scrollbar items and move the cursor inside any handlers and drag the selected objects to the right. To select multiple objects, push and hold the control key and then click on the objects that you wish to be part of group. You can also, go outside of the objects and click and hold the cursor on the canvas and move it until all the objects are in the box. Release the mouse. Now, you selected all your objects. Go anywhere in side of the handlers to move your selected objects.

You can also click on the right, left, up, and down arrow keys to move the objects. Try it number of times. It is fun.

To deselect the objects, just move your cursor outside of the selected objects and click. All your handlers will be disappeared.

Create a Radio Button

In the ‘Layout Editor’ window click on the “radio button” icon. Now, if you move your cursor in side of canvas, your cursor changes to a plus sign.

Move your cursor on a position that you want to have your Radio Button and click. The ‘Radio Groups’ window will appear. In the ‘Radio Groups’ window, select the radio group "COMMPLAN" and click “OK.”

Adjust the size if needed. In the Layout Editor window, right click on the new Radio Button (RADIO_BUTTONxx) to open its Property Palette.

Radio Button 1:

Change a Radio Button Property Sheet

In the Property Palette, change its name to A. Change “Label” to blank. Change "Radio Button Value" to A. Change "Prompt" to A. Change "Prompt Attachment Edge" to top. Then close the window.

Make some layout adjustments if needed.

Copy the object and paste it three times. The copy will be pasted on the top of each other. You need to select them one by one and drag them to be appeared next to each others. Make sure that when you copy the object to place the Radio Button item in the “COMMPLAN” Radio Group.

Open the new Radio Buttons property palette. And change their "name", their "radio button value", and their "prompt" properties.

Radio Button 2:

In the Property Palette, change its name to B. Change “Label” to blank. Change "Radio Button Value" to B. Change "Prompt" to B. Change "Prompt Attachment Edge" to top. Then close the window.

Radio Button 3:

In the Property Palette, change its name to C. Change “Label” to blank. Change "Radio Button Value" to C. Change "Prompt" to C. Change "Prompt Attachment Edge" to top. Then close the window.

Radio Button 4:

In the Property Palette, change its name to ‘BLANK.’ Change “Label” to blank. Change "Radio Button Value" to ‘ ‘. Change "Prompt" to ‘Blank.’ Change "Prompt Attachment Edge" to top. Then close the window. The blank means that the commission plan is not applicable to this transaction. Then close the window.

Do some layout adjustments if needed.

Navigate through the tab pages layout by clicking on them.

Run the Form Runtime

Run the Form.

Execute Query

Click “Execute Query” and navigate through the customers.

Test and check the changes.

When you are done with all the testing, then close the application and save the changes.

Display items, Summary and function columns

Introduction

Display items are read-only items and are only useful for calculated data such as subtotals and unchangeable data.

You can use Summary Columns to calculate average, sum, min, and max of a group of items in a block. It can be read-only or text items. Use Function columns to return calculation of binding variables to your screen. It also can be read-only or text items.

Hands-On

While designing the tables, you have identified a derived field in one of the tables (Item). There should not be a column in a table that is calculated or generated by two other columns. This is the Third Normal form rule. It should be rectified for good database design. Their DBA will remove any columns whose contents are depended on the other columns. In the "Item" table the "itemtot" column is a column depended on quantity * actualprice.

See Figure 7.

Your tasks are:

1- Modify the "Items" tab canvas layout to generate a new item called "itemtotal". This field will be populated from other “Item” table’s column during order entry (quantity * actualprice).

2- Avoid populating the “itemtotal” from the table’s column.

3- Add a display field to calculate the grand total of all calculated field call "itemtotal" for each customer's order.

4- Run and test all user functional requirements.

FYou will learn how to: display fields, summary and formula functions, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 7

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V04)

from the “iself” folder.

Save a module

Click on the “CUSTOMER_ORDERS_V04” form. The color changes to blue. Then change the name and save the Form name as version 05 (customer_orders_v05). This way the original form is untouched.

Move an object

In the Layout Editor window, click on the ‘ITEM’ tab to display its canvas. Select and move the ‘ITEMTOT’ item and Scrollbar to right to reserve a space for a new item.

To select multiple objects, push and hold the control key and then click on the objects that you wish to be part of group. You can also, go outside of the objects and click and hold the cursor on the canvas and move it until all the objects are in the box. Release the mouse. Now, you selected all your objects. Go anywhere in side of the handlers to move your selected objects.

In the Layout Editor window, make sure that the “Block” box shows the "ITEM" block. If not select the ‘ITEM’ data block.

Create a Display item

On the toolbar in the Layout Editor Window, select “Display item” and click the ‘+’ where you wish to have your new item (DISPLAY_ITEMnn). In this hands-on exercise, you should have your item next to the “quantity” item. Adjust its size. And open its property palette.

Formula Column

In its Property Palette, change “name” to itemtotal. Change “Justification” to right. Change “Datatype” to Number. Its “format mask” to $99,999.00. Change "Calculation Mode" to Formula, and type the formula on the “Formula” box (:qty * :actualprice). The colon next to an item, references to a binding variable on the screen such as actualprice and quantity. Change "database item" to no, because this is not a column from the item table. Change "prompt" to "itemtotal.” Change "Prompt attachment edge” to “Top.” Then close the window.

Add another "Display Item" to calculate the totals of “itemtotal.” Notice that the “Block” box on the canvas window points to the “ITEM” block. And the “Number of Items Displayed” on this object is 5.

On the toolbar in the Layout Editor, select “Display item” and click the ‘+’ where you wish to have your new item (DISPLAY_ITEMnn). In this hands-on exercise, you should have your item under the ‘ITEMTOTAL’ item. You get 5 objects but we only need one. Right click on the object and open its property palette.

Summary Column

In its Property Palette, change “name” to ‘ORDERTOTAL.’ Change “Justification” to “Right.” Change “Data Type” to “Number.” Change “Calculation Mode” to “Summary.”

Here, the formula property is not applicable. Change “Summary Function” to “SUM.” Change “Summarized block” to “ITEM.” Change “summarized Item” to ITEMTOTAL. Change “Number of items displayed” to 1. Change “Database item” to “no.” Type the prompt property to ‘Order Totals:.’ Change “Prompt attachment edge” to “Start.” Then, close the window.

Make some window adjustments, if needed.

In the Object Navigator window, right click on the ITEM data blocks to open its Property Palette.

In its Property Palette, change "Query all records" to "yes." Then close the window.

Navigate the application's layout.

Run the Form Runtime

Then run the application.

Execute Query

Click “Execute query” and navigate through the application.

Notice that the ITEMTOTAL column matches with the itemtot column.

There is no “Format mask” for the “order total” item.

Close the Runtime Forms.

Change a Format Mask

In the Layout Editor, right click on the order total (ORDERTOTAL) to open its Property Palette.

In its Property Palette, change its format mask to $999,999.00. Close the window and run the application.

Execute Query

Execute the query and navigate through the application.

When you are done with all your testing, then close the application and save the changes.

Triggers

Introduction

Forms Trigger

A forms trigger is a block of PL/SQL code that adds functionality to your application. Triggers are attached to objects in your application. When a trigger is fired, it executes the code it contains. Each trigger’s name defines what event will fire it; for instance, a WHEN-BUTTON-PRESSED trigger executes its code each time you click on the button to which the trigger is attached. Or, we can say, a forms trigger is a set of PL/SQL actions that happen each time an event such as when-checkbox-changed, when-button-pressed, or when-new-record-instance occurs. You can attach several triggers to a data query. The most popular of them are the PRE-QUERY and POST-QUERY.

PRE-QUERY and POST-QUERY trigger

The PRE-QUERY trigger fires before the select statement is finalized. The POST-QUERY trigger fires before selected records are presented to the user. It fires after records are retrieved but before they are displayed. So, you can use it to enhance a query’s records in a number of ways. Your Post-Query trigger can contain code to calculate or populate control items.

PRE-INSERT and WHEN-NEW-FORM-INSTANCE trigger

Some other useful triggers are: PRE-INSERT and WHEN-NEW-FORM-INSTANCE.

A PRE-INSERT trigger fires once before each new record is inserted in a commit process. The “WHEN-NEW-FORM-INSTANCE” trigger will be used to prepare objects or an action when entering to a new form. It fires when the form is entered.

Hands-On

Now, the user’s DBA removed the “itemtot” column whose content was depended on the other columns. Your client wants you to remove the “itemtot” item from layout screen.

Also, your application only shows products' ID. Your client wants to see product's description since product's ID does not tell them what the product is.

They want you to remove duplicate item information from "Items" tab canvas; and add a new item to display product's description in the "Items" tab canvas.

See Figure 8.

Your tasks are:

1- Remove duplicate item information from "Items" tab canvas.

2- Add a new item to display product's description “Product Description” in the "Items" tab canvas.

3- Run and test all user functional requirements.

FIn this Hands-On, you will learn how to: use the post-query trigger, Compile Triggers, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 8

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V05) in the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V05” form. The color changes to blue. Change it and then save the Form name as version 06 (customer_orders_v06). This way the original form is untouched.

Layout Editor

In the Main menu, choose the ‘Tools’ sub-menu and select the Layout Editor option.

Adjust the window, if you need it. In the Layout Editor window, select the "ITEMTOT" item by clicking on it and press the "Delete" function key to remove the object since it is duplicated.

Make some window adjustment to make space for the product description.

Add a Display Item

In the toolbar in the Layout Editor window, select the ‘Display Item’ icon. Drag the ‘+’ sign on the canvas and click it where you wish to add your Product Description item (DISPLAY_ITEMnn). Adjust its size.

Make sure the BLOCK box in the Layout Editor shows ‘ITEM.’

Change Property Palette sheets

Right click on the new item and open its property palette. Change name to “PRODUCT_DESC.” Change "database item" to "no." On “Prompt” type “Product Description.” Change “Prompt Attachment edge” to " Top.” Then close the window.

Do some window adjustments if needed.

Notice that the "Product Description" is not in the item table, therefore you need to create a trigger to query that information.

Create a Trigger

Expand the ITEM data block. Notice that the box next to Trigger is empty. That means the ITEM data block does not have any trigger.

Highlight the Trigger item and click on the green ‘+’ sign on the toolbar in the Object Navigator window.

POST-QUERY Trigger

In the Trigger window, type a letter ‘P’ and you will see all the triggers that start with the letter ‘P.’ Select the ‘POST-QUERY’ trigger.

PL/SQL editor

In the PL/SQL Editor window, write a query to move "product description" into the display item for each "prodid" on the screen.

(PL/SQL Editor)

SELECT descrip INTO :product_desc

FROM product

WHERE prodid = :prodid;

Compile a PL/SQL statement

Compile it.

You should get successfully compiled.

Close the window.

Navigate through the application layout.

Run the Form Runtime

Then run the application.

Execute Query

Execute the query and then navigate through the application.

Check the changes. Test the "product description."

Check if the description is right.

Close the window and save all the changes.

Horizontal Toolbar, Push Button, Trigger

Introduction

A canvas is a place to hold the objects. The four types of canvases are: Content, Stacked, Tab, and Toolbar. All can coexist within a single window. A content canvas is the basic background for all windows. A stacked canvas lays on top of the others. A tab canvas is the same as stacked canvases with handy “tabs” at the top. It can simply move from one canvas to another. A toolbar canvas contains push buttons giving users quick access such as horizontal canvas or vertical canvas.

Horizontal Toolbar

A Horizontal Toolbar canvas contains buttons that give you quick access to your application functions. They would be activated by your defined triggers.

Hands-On

New functional requirements came in from the users. They need a horizontal toolbar to have on their Form. The toolbar should contain all the frequently used options. This will make ease of use of some options.

The following are the list of all the frequently used functions they expect to use:

Move to previous record,

Move to next record,

Add new record,

Delete current record,

Save work, and

Exit the application.

See Figure 9.

Your tasks are:

1- Adding more functionality to the application.

2- Create a horizontal Toolbar.

3- Create push button for above frequently used functions list.

4- Create and compile triggers for each push button created on step 3.

5- Run and test all user functional requirements.

FYou will learn how to: create and use horizontal toolbar, create and use the push button item, create the "WHEN-BUTTON-PRESSED" trigger, write and compile trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 9

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V06) in the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V06” form. The color will change to blue. Now, change the name and then save the Form name as version 07 (customer_orders_v07). This way the original form is untouched.

Create a Canvas

To create more canvas, go to the Object Navigator window and highlight the Canvases item. Click on the green ‘+’ sign in the toolbar in the Object Navigator. A canvas will be created for you (CANVASnn). The nn could be any Oracle assigned number.

Change a property palette sheets

Right click on it and open its Property Palette. Change “name” to "PB_BLOCK.” The PB stand for Push Button Block. Change "Canvas type" to "Horizontal toolbar." Check the icon next to it changed in the Object Navigator. Close the window.

Select a Canvas

In the Layout Editor click on the Down Arrow next to the Canvas box to select new canvas (PB_BLOCK).

Notice that, it is too big to be the "horizontal toolbar" canvas. Change the size. Notice that you will see two boarders one is white and the other block. Let see if you only drag the black boarder. Check the picture to see how your horizontal tool bar is going to be.

Navigate through these two canvases.

Create a New Data block

In the Layout Editor, highlight the “Data Blocks” and click on the green ‘+’ sign to create a new data block.

New Control Data Block

In the ‘New Data Block’ window, don’t choose the default option. Select the “Build a new data block manually” and click "OK."

Change a property palette sheets

Right click on it and open its Property Palette. In the Property Palette window, change the Name value to ‘PB_BLOCK.’ Change "Database Data Block" to “NO.” Then close the window.

Create a Push Button Item

In the Layout Editor, while the Canvas value is PB_BLOCK and the Block value is PB_BLOCK, click on the PUSH BUTTON icon on the toolbar and drag the ‘+’ sign on the PB_BLOCK canvas and click where you wish to have your Push Button.

Copy and paste objects

Copy the object and paste it five times. The copy will be pasted on the top of each other. You need to select them one by one and drag them to be next to each others. Now, you should have six push buttons.

Change a property palette sheets

Right click on the first push button object and open its property palette. Change "name" to ‘PREV_REC.’ Blank the "label." Set “iconic” to "yes." On “Icon Filename,” type "w_prev." This is an oracle reserve word and could be different version to version. Change "Keyboard Navigators" and "Mouse Navigators” to “NO.” Then close the window.

Repeat the process for the rest of the push button items.

Only change the name and the Icon Filename.

Change name to NEXT_REC.

On “Icon Filename,” use "w_next" for next record.

Change name to ADD_REC.

On “Icon Filename,” use "addrow" for adding a record.

Change name to DEL_REC.

On “Icon Filename,” use "delrow" for deleting a record.

Change name to SAVE.

On “Icon Filename,” use "save" for saving the application transactions.

Change name to EXIT.

On “Icon Filename,” use "exit" for exiting the application.

While holding the control key, click on the objects you want to select. And then right click to open their property palette.

Change “height” to 20 and press the enter key.

Close the window.

Make some layout adjustments if needed.

Notice that these push buttons have no functionality.

Expand the PB_BLOCK data block. Expand the ITEMS object. All the items in the PB_BLOCK data block will be displayed. Notice that the box next to Trigger is empty for all the items in the data block. That means they don’t have any triggers.

Create a trigger

For each item, highlight the Trigger item and click on the green ‘+’ sign on the toolbar in the Object Navigator window to create a trigger.

WHEN-BUTTON-PRESSED trigger

In the Trigger window, type a letter ‘W’ and you will see all the triggers that start with the letter ‘W.’ Select the ‘WHEN-BUTTON-PRESSED’ trigger.

In the PL/SQL Editor window, write a query for a record to navigate to a previous record.

Using PL/SQL Editor

(PL/SQL Editor)

PREVIOUS_RECORD;

Compile a PL/SQL block

Compile it.

Repeat this process for all the other triggers.

For NEXT_REC:

(PL/SQL Editor)

NEXT_RECORD;

For ADD_REC:

(PL/SQL Editor)

CREATE_RECORD;

For DEL_REC:

(PL/SQL Editor)

DELETE_RECORD;

For SAVE:

(PL/SQL Editor)

PREVIOUS_RECORD;

For EXIT:

(PL/SQL Editor)

COMMIT_WORK;

Notice on different ways of doing this.

Check the application layout.

Run the Forms Runtime

Then run the application.

Here, the only canvas you can see is: the horizontal toolbar canvas. There is no indication of the other canvas. You should drag the window to see the other canvas.

You can either close the window or drag to resize the window to use the “Customer’s Order” application. Resize the window and execute the query.

Test the push button functionality. After the testing is done, close the window and save the changes.

The canvas should not be small and user will not like to drag the window to see other data information. In the next hands-on exercise, we will learn how to avoid such problem and why we were getting that.

Sizing, Layout appearances

Introduction

Window is an empty frame to hold your object and it is the outermost boundary for a form. Block is a logical container that holds form objects such as data items and control buttons. There are two types of block: Data Block and Control Block. A Data Block is a link to your database information and holds database data. A Control Block holds information that has nothing to do with database. An item could be a database column or none database item. A canvas is a place to hold the objects. The four types of canvases are: Content, Stacked, Tab, and Toolbar. All can coexist within a single window. A content canvas is the basic background for all windows. A stacked canvas lays on top of the others. A tab canvas is the same as stacked canvases with handy “tabs” at the top. It can simply move from one canvas to another. A toolbar canvas contains push buttons giving users quick access such as horizontal canvas or vertical canvas.

Hands-On

On pervious Hands-On, your users only see the horizontal toolbar canvas instead of the Customer, Orders, and Items canvases. They have to drag the window in order to see their “Customer Order” application. They complain that it is cumbersome to drag the window in order to use the application. You have been assigned to fix this problem. The requirement from your client is: to make sure your layout was properly sized so your client doesn't need to drag or expand the window size.

See Figure 10.

Your tasks are:

1- Make sure the canvas was sized properly.

2- Run and test all user functional requirements.

FYou will learn how to: size your canvas, change the horizontal toolbar canvas, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 10

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V07) in the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V07” form. The color will change to blue. Now, change the name and then save the Form name as version 08 (customer_orders_v08). This way the original form is untouched.

Change a Canvas Layout

In the Main menu, choose the ‘Tools’ sub-menu and select the ‘Layout Editor’ option. In the Layout Editor window, make sure that the Canvas box contains ‘PB_BLOCK.’ If not, click on the down arrow next to its box and select PB_BLOCK.

Now, you should have the PB_BLOCK canvas in the Layout Editor window. In the Object Navigator window, expand the Canvases item and select the PB_BLOCK canvas. Notice that in the Layout Editor window, the canvas gets eight handlers. You can use these handlers to expand the size of an object.

Expanding an object using handlers

Click on the middle down handler to expand the size of canvas. Make sure that you drag the black line and let the white line stays. After you reach to a suitable size, release your mouse. The white line is the border of the "horizontal toolbar canvas." And the black line is the border of the content canvas.

Run the Forms Runtime

Run the application

If the window was not sized enough, you can close the Runtime Form window and repeat the above steps until you are happy.

Run the application again and check the appearance of the application.

Trigger (PRE-QUERY), Set dynamically properties

Introduction

PRE-QUERY trigger

Change an object property dynamically

You can also change an object property dynamically. The “SET_objectname_PROPERTY” built-in subprogram will change an object property dynamically.

Hands-On

On the "Orders" tab, your client wants to see the most recent or last customer orders. They ask you to sort the customer orders list based on their order’s date. They like to have two radio buttons to allow them to manipulate the order of displayed records by order date.

You have been assigned to create two radio buttons, Labeled "Ascending" and

"Descending" to sort displayed record order by "order date."

See Figure 11.

Your tasks are:

1- Create a Radio Group.

2- Create two Radio Buttons associated with step 1 (Radio Group).

3- Label the first Radio Button “Ascending.”

4- Label the second Radio Button “Descending.”

5- Run and test all user functional requirements.

FYou will learn how to: create and use PRE-QUERY trigger, set dynamically block property on a table's data block and execute the query using trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 11

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V08)

from the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V08” form. The color will change to blue. Now, you should be able to change the name and then save the Form name as version 09 (customer_orders_v09). This way the original form is untouched.

Open Layout Editor

In the Main menu, choose the ‘Tools’ sub-menu and select the ‘Layout Editor’ option. In the Layout Editor window, make sure that the Canvas box contains your tab canvas. If not, click on the down arrow next to its box and select it and also make sure in the Layout Editor window, the BLOCK box contains the ORD block.

Create a Control Data Block

In the Object Navigator window, highlight the Data Blocks item and click on the green ‘+’ sign to create a data block. In the ‘New Data Block’ window, select the ‘Build a new data block manually’ option and then click ‘OK.’

Change a property palette sheets

Now an object was created (BLOCKnn). Right click on the object and open its Property Palette. Change "name" to sort_block. And press the enter key. Change "Database Data Block" to "no." Close the window.

Create a Radio Group and Radio Button

In the Layout Editor, on the “BLOCK” box, select the sort_block item and click on the Radio Button icon to create a radio button. Drag the ‘+’ sign on the canvas and click where you wish to have your Radio Button. In the ‘Radio Group’ window, click “new” to create a new “Radio Group” for sorting.

Notice on the "Object Navigator," a radio group and radio button were created.

In the Object Navigator, right click on the new RADIO_GROUPnn item and open its Property Palette.

In its Property Palette, change the "name" property to SORT_SELECT or any name you like and then press the enter key. Change "initial Value" to "orderdate asc." Then press the enter key and click “OK.” Change "Database Item" to "no." Close the window.

Right click on the RADIO_BUTTONnn item and open its property palette. Change the "name" property to RB_SORT_ASC. The RB stands for Radio Button. Change "radio button value" to "orderdate asc." Change "Label" to "Ascending." Close the window.

Copy and Paste a Radio Button

Copy and paste the Radio Button item. Make sure to place it in the “sort_select” Radio Group. Then click “OK.”

Move an object

Move the new "radio button" item similar to user requirement with above figure.

Right click on it and open its Property Palette.

Change a property palette sheets

In the Property Palette window, change the NAME to RB_SORT_DESC. Change the "label’ to Descending. Change "Radio Button Value" to "orderdate desc." Then close the window.

Change an appearance of an object

Click on the "Text" icon; and type 'Sort Order Date.' Then click outside of the text box.

In the Layout Editor, you can change the appearance of the text by clicking on the B, I, U icons on the top horizontal toolbar.

Create a Trigger

In the Object Navigator, highlight the Trigger of the ‘SORT_SELECT’ radio group and click on the green ‘+’ sign to create a trigger.

WHEN-RADIO-CHANGED’ trigger

In the Triggers window, type a letter ‘W’ then ‘R.’ Select the ‘WHEN-RADIO-CHANGED’ trigger and click ‘OK.’

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to go to the order block (ORD) and then execute the query statement.

(PL/SQL Editor)

GO_BLOCK(ord);

EXECUTE_QUERY;

Compile a trigger

Compile the trigger and close the window.

Create a trigger

In the Object Navigator, highlight the Trigger of the ‘ORD’ data blocks and click on the green ‘+’ sign to create a trigger

PRE-QUERY trigger

In the Triggers window, type a letter ‘P’ then ‘Q.’ Select the ‘PRE-QUERY’ trigger and click ‘OK.’

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to change the “order_by” property of the “ORD” data block. Notice that the “sort_select” value is changed based on the radio button you select.

(PL/SQL Editor)

SET_BLOCK_PROPERTY (‘ord’, ORDER_BY, :sort_block.sort_select);

Compile Trigger

Compile the trigger. Close the window.

Run the Form

Run the application.

Execute Query

Click “Execute query.”

Navigate

Navigate through the application and check the sort radio button.

After the testing is done, close the window and save the changes.

Sequence object, Trigger (PRE-INSERT)

Introduction

SEQUENCE

A sequence is a database object that generates a series of integer numbers according to rules at the time you created the object. One of the purpose of creating a sequence object is to generate primary keys automatically. In this Hands-On, you will generate customer IDs.

Forms Trigger

POST-QUERY and PRE-QUERY Trigger

PRE-INSERT and WHEN-NEW-FORM-INSTANCE trigger

Some other useful triggers are: PRE-INSERT and WHEN-NEW-FORM-INSTANCE.

Hands-On

It is very cumbersome for your users to assign a new customer ID to a new customer. It requires finding what the last customer ID was entered into their system; then they increment it by 1 and use that number for a new customer ID. This is not very a professional way of doing business particularly if you have multiple transactions to enter.

You have been assigned to solve this problem and automate this process. They ask you to add a sequence number to customer ID (CUSTID) starting with 200 and incremented by 1. So, your customer ID would start from 200, 201, 202, and so on.

See Figure 12.

Your tasks are:

1- Create a sequence number to generate a unique customer ID.

2- Assign the generated sequence number to each customer each time a new customer is added to a table.

3- Run and test all user functional requirements.

FYou will learn how to: create sequence object, create and use "PRE-INSERT" trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 12

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V10)

from the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V10” form. The color will change to blue. Now, change the name and then save the Form name as version 10 (customer_orders_v10). This way the original form is untouched.

Go to MS-DOS Prompt.

CREATE SEQUENCE …

Create a sequence object that starts from 200 and is incremented by 1.

SQL> CREATE SEQUENCE seq_custid START WITH 200;

Retrieve Sequence

To see how it works: Use the “Next Value” Function to increment the sequence number.

Repeat to run the query

SQL> SELECT seq_custid.nextval FROM dual;

SQL> /

This will give you some idea how a sequence object works.

Close the MS-DOS window or minimize it.

Change a property palette sheets

In the Layout Editor or Object Navigator, you can right click on the "custid" item to open its Property Palette.

In its Property Palette window, change the "Required" property to "NO." Then close the window.

Create a Trigger

In the Object Navigator, highlight the Trigger item under the CUSTOMER data block and click on the green ‘+’ sign to create a trigger.

PRE-INSERT trigger

In the Trigger window, type ‘P’ then ‘R,’ and then select the ‘PRE-INSERT’ trigger.

PL/SQL Editor

In the PL/SQL Editor, write a select statement to assign a new sequence number to “custid.”

(PL/SQL Editor)

SELECT seq_custid.nextval INTO :customer.custid

FROM dual;

Compile a trigger

Compile the trigger and then close the window.

Run the Form

Run the application.

Execute Query

Click “Execute query.”

Navigate

Navigate through the customers information.

Insert a record

Click on the "insert" icon.

Now, the form is on the “insert” mode.

Type the new customer information.

Remember that the “Customer ID” is going to be generated.

Save a transaction

Save the record.

New “Customer ID" was generated.

Add more customers.

Duplicate the previous record.

Notice that even though the Customer ID is copied, the new "Customer ID" will be assigned to it.

Click on "save."

Notice that "custid" is changed to the new number (205).

Navigate

Navigate through the application.

Then, close the application and save the changes.

Call FORMS

Introduction

There are three ways you can execute a form within a form. These three ways are:

OPEN_FORM,

CALL_FORM and

NEW_FORM.

CALL_FORM build-in subprogram

The CALL_FORM built-in subprogram opens a form with the same database connection and can be run also in query mode.

NEW_FORM build-in subprogram

The NEW_FORM built-in subprogram opens a form and closes the calling form.

OPEN_FORM build-in subprogram

The OPEN_FORM built-in subprogram opens a form and its own database connection.

Global Variable

You can use a Global variable to pass a value from one form to other. A Global Variable is a binding variable that can be used by multiple Form Modules.

Hands-on

Your client wants to have another application Form to query, insert, delete, and update their product history prices. They want to open the Form from their “Customer Order” application.

It is their requirement that the window size for product price should be small enough to fit on a small portion of their application. Also, it is part of their requirement to have a Push Button to click in order to open and run the product history prices application from the “Items” tab canvas. You should modify their application to provide such request.

See Figure 13.

Your tasks are:

1- Create the product history prices Form.

2- Test all its user functional requirements, such as insert, delete, and update.

3- Create a Push Button on the “Items” tab canvas to open the Form that created from step 1.

4- Create and compile trigger to call the Form that was created from step 1.

5- Run and test all user functional requirements.

FYou will learn how to: call another Form, create and compile triggers, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 13

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V10) in the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V10” form. The color will change to blue. Then change then name and save the Form name as version 11 (customer_orders_v11). This way the original form is untouched.

Layout Editor

Create a New FORM

Create a new Form to query, insert, delete, and change product history prices as an independent form from this.

Change a Module name and Save it

In the Object Navigator, highlight the "Forms" item and click on the "create" icon. A form (MODULEnn) will be created. Click on the new form and change its color to blue and type PROD_DE.

Go to the Main menu, choose File and select the Save option to save the Form in the iself folder as "prod_de." Make it short for product price data entry.

Create a Data Block

New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Tables

Click on “browse.” In the ‘Tables’ window, highlight the "PRICE” table; then click "OK."

Selecting columns for the Data Block Wizard

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to display them on the canvas. Then click “Next.”

Change your objects appearances

Selecting a layout style

Record layout

Type the Frame Title (Product Data Entry), Records Displayed (5), Distance between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Open and change a property palette sheets

In the Layout Editor, expand the Windows item and right click on the WINDOWnn to open its Property Palette.

In its Property Palette, change the "X” position to 50. Change the "Width" length to 300. Change the "Height" length to 150. Then, close the window. The X and Y positions are where the object is going to be displayed. Close the window.

Save and compile a Form

Save the Form in the iself folder.

Then go to File > Administration > Compile file to compile it.

Now, go back to the CUSTOMER_ORDERS_V11 form.

Create a Push Button

In the Layout Editor, click on the Push Button icon and drag the ‘+’ sign into the ITEMS canvas. Click on any where in the canvas that you wish to have your Push Button. Right click on it and open its Property Palette.

Change a property palette sheets

In its Property Palette, change the name property to PB_PROD_DE; then press the enter key. Change "Label" to “Product Data Entry.” Change the Keyboard and Mouse Navigate to "NO.” Close the window.

Notice that on the Object Navigator if the push button is not in the “item” block, you should move it to the “Item” block.

Now, you should see five Push Button. The reason is the ITEM block display 5 items at a time.

Right click on the new Push Button and open its Property Palette. In the Property Palette window, change "Number of items displayed" to 1. Press the enter key and close the window.

Now, you should see only one push button. Adjust the position of the push button if needed.

Create WHEN-BUTTON-PRESSED trigger

Right Click on the push button again to choose "Smart Triggers," and select "WHEN-BUTTON-PRESSED."

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to call the “Product Price” data entry application.

(PL/SQL Editor)

OPEN_FORM(‘c:_de’);

Compile a trigger

Compile the trigger; and close the window.

Run a Form

Run the application.

Execute Query

Click “Execute Query.”

Navigate

Navigate through the application options.

Click on the “Product Data Entry” push button.

Then click “Execute Query” again.

All products will be displayed.

Click on the "Enter Query" icon to query all product prices for a specific product.

Close the window.

Navigate through the application to check all the changes. When you are done with the testing, close the application and save the changes.

Global Variable, Trigger

Global Variable

A Global Variable is a binding variable that can be used by multiple Form Modules.

Form Trigger

POST-QUERY trigger

WHEN-NEW-FORM-INSTANCE trigger

Some other useful triggers are: PRE-INSERT and WHEN-NEW-FORM-INSTANCE.

Hands-ON

Now, your client desires it would be better if they could get specific history of product price rather than entire product history prices table. They want you to add more functionality to their application to display only current history of product prices information.

Note: The current product would be defined by where the position of your cursor is.

You have been assigned to modify their application to query only history of product price information for a product rather than entire table.

See Figure 14.

Your tasks are:

1- Automate the “product history prices” application to query only a history of product prices.

2- Pass product ID as a parameter between these two Form applications.

3- Run and test all user functional requirements.

FYou will learn how to: use and assign global variable, pass global variable from one Form to another, use global variable to execute query base on its parameter, create and use the "WHEN-NEW-FORM-INSTANCE" trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 14

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V11) in the “iself” folder.

Save Modules

Click on the “CUSTOMER_ORDERS_V11” form. The color changes to blue. Then change its name and save the Form name as version 12 (customer_orders_12). This way the original form is untouched.

Also, you should save the “Product History Prices Data Entry” (PROD_DE) Form name as version 2 (PROD_DE_V02).

Now, we want to change the application to query all the product history prices for an item that the cursor is on.

Temporary Holder

In the PROD_DE_V02 form, expand the form, highlight the Data Blocks item and then click on the green ‘+’ sign to create a data block.

New Data Block

In the ‘New Data Block’ window, choose the ‘Build a new data block manually’ option and click "OK."

Change a property palette sheet

Now, you have a new data block (Blockn). Right click on it and open its Property Palette. In its property palette, change its name to CONTROL_BLOCK. Change "Database Data Block" to "NO." Close the window.

Create an Item

In CONTROL_BLOCK, create a new item. Open its property palette. Change the "name" item to “TEMP_VALUE.” Change its datatype to NUMBER with length 7 bytes. Change "database item" to “NO.” Close the window.

Copy Value from Item

Select “Prodid” on the price data block; and open its property palette. For “Copy Value from Item” type "control_block.temp_value." Close the window.

Create a trigger

In the PROD_DE_V02 form, create a "WHEN-NEW-FORM-INSTANCE" trigger at Form level.

WHEN-NEW-FORM-INSTANCE trigger

In the Trigger window, type “W” and “N” and select the WHEN-NEW-FORM-INSTANCE trigger.

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to move the global prodid item value into the temp_value item. Then go to the “price” data block and execute the query function.

(PL/SQL Editor)

:temp_value := :global.prodid;

GO_BLOCK (‘price’);

EXECUTE_QUERY;

Compile a trigger

Compile the trigger and close the window.

Compile a module

Highlight the Form and save it. Then go to File > Administration > Compile file to compile it.

Create a Push Button

On the CUSTOMER_ORDERS_V12 Form, right click on the “Product History Data Entry” push button in the ITEM tap canvas to select “Smart Trigger” and select the "WHEN-BUTTON-PRESSED" trigger.

WHEN-BUTTON-PRESSED trigger

In the PL/SQL Editor, write a PL/SQL procedure to move the PRODID value into the global prodid item and call the Product History Price Data Entry form.

(PL/SQL Editor)

:GLOBAL.prodid := NAME_IN(‘item.prodid’);

OPEN_FORM(‘c:_de_v02’);

Compile the trigger and then close the window.

Run the Form

Run the application.

Execute Query

Click on the “Execute Query” icon.

Navigate the application

Navigate through the application.

Highlight a product id and then click on the “Product History Prices Data Entry” push button.

Now, you should only see information about that specific product id not all the products.

Repeat the process for some other items.

Then, close all the windows and save the changes.

Main Menu

Introduction

Main Menus

Main Menus do have their own module file. They are independent of form modules. It will be very useful to look at the code underlying the default menu used by the Forms Runtime program. This module was created by Oracle and it is used as a default main menu. The source file for this menu is stored in the FORMS directory in a file named “menudefs.mmb.” You can easily customized this file or use it as a basis for a new menu module.

Global Variable

You can use Global Variable to shares objects from one form to another. A Global variable is a binding variable that can be used by multiple Form Modules.

NAME_IN and COPY built-in subprogram

You need to use the NAME_IN built-in subprogram to read a value from a global variable or an indirectly referenced object and the COPY built-in subprogram to place a value into a global variable or an indirectly referenced object.

Always start with the default created menu, instead of creating a main menu from scratch.

Hands-On

Your client loves what you have done for them. But they would like you add the same functionality you did at Hands-On 12 to have it on the main menu of the “Customer Order” application.

You have been assigned to modify the main menu and add functions from Hands-On 12 to their window's main menu.

See Figure 15.

Your tasks are:

1- Modify the default main menu and add a menu item as “Data” and a sub-menu item as “Product Price” on the “Customer Order” Form.

2- Delete some unused menu items.

3- Pass product ID as a parameter between these two Form applications.

4- Run and test all user functional requirements.

FYou will learn how to: create or modify default main menu, use and assign global variable from one form to another, use global variable to execute query base on its parameter, create and use the "WHEN-NEW-FORM-INSTANCE" trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 15

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V12)

from the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V12” form. The color changes to blue. The change its name and save the Form name as version 13 (customer_orders_v13). This way the original form is untouched.

We are going to change the application to invoke the “Product History Prices Data Entry” FORM from the application main menu.

Create a Menu

Highlight the “Menu” item in the "object navigator” and open the predefined “Oracle default menu” from the Forms folder. To save your programming time, you should start with default menu than you start from scratch. All menus have extension ‘mmb.’ You can search for all files that their extension are ‘mmb.’

Default Menu

Your default menu could be on different Oracle sub-directory version to version. On one version you may have it in

c:.mmb or

c:ii.mmb , etc.

Open the default menu.

Save a Menu

Save it as "cust_menu" in the “iself” folder. Highlight the menu name and click again to change the color to blue. Now, change the menu name to "Cust_menu.” And open its property palette. Notice that the name was changed. Then close the window.

Menu Editor

Double click on the "cust_menu" icon. In the Menu Editor, the whole menu structure layout will be displayed. Here you can change the layout and property of each item in the menu. You can add, remove, and change the sub-menus.

Menu Editor’s Toolbar

In the toolbar of the Menu Editor, you can find very useful icons. There are the ‘Create Right’ icon, ‘Create Down’ icon, and ‘Delete’ icon. If you move your cursor on them, a tooltip will display their functions.

Next to sub-menus, you will find the Down Arrow, and Up Arrow icon. Clicking on the "Up Arrow" icon collapses each menu list item. Clicking on the "Down Arrow" icon expands each menu list item.

Create a sub-menu

Select the “Field” sub-menu, and then click on the "Create Right" icon. That will create a new sub-menu next to the Field sub-menu. Change its name to "Data."

Create an item in a sub-menu

Click on the "Create Down" icon to create an item under new sub-menu (Data). Change "new item" to "Product History Prices," then right click on it and open its property palette.

This is another way to change the item name and other properties. Close the window.

PL/SQL Editor

Right click on the ‘Product History Prices’ and open the "PL/SQL Editor."

In the PL/SQL Editor, write a PL/SQL block to store the “product id” item into the “global product id” item and then call the new version of the “Product Price Data Entry” Form from the iself folder.

(PL/SQL Editor)

:GLOBAL.prodid := NAME_IN(‘item.prodid’);

OPEN_FORM(‘c:_de_v02’);

Compile a PL/SQL block

Compile and close the window.

Delete an item from a Main Menu

To delete an item, select the item and then click on the ‘Delete’ icon in the toolbar. When you delete an item on the menu, you should confirm your action.

Delete the “Edit” sub-menu item.

Try to delete some other sub-menu items if not needed.

Exit Menu Editor

Close the menu editor.

Highlight "cust_menu," save the changes.

Compile a Menu

Then go to File > Administration > Compile File to compile the menu. The compile module is saved in the same folder of its source module.

Open a Form’s property palette

Now, go back to the CUSTOMER_ORDERS_V13 form.

Right click on the “Customer Orders” Form and open its Property Palette to assign the new menu.

In the Form Property Palette, change the ‘Menu Module’ value to c:_menu. Close the window.

Run the application

Run the Form.

Notice that the Main menu was changed. The “Data” sub-menu was added.

Click on “Execute query.” Navigate through the application Form. Highlight a product id, and then click on the "Product Data Entry” button.

Close the window.

Do the same by using the Main menu option.

You should get the same result as push button.

Now, close the windows and save the changes.

Pop up menu

Introduction

Pop-up menus are "mini-menus" that appear when you right click on an object. They are intended to include only items relevant to the object they are attached to. Therefore an application may have many pop-up menus. Pop-up menus do not have module files. They are owned by the FORM modules.

Hands-On

Your client wants to have capability to cut, paste, and copy words or lines when they type their comments in the "comments" item at the "Customer" tab canvas.

You have been assigned to add the above functions into the "comments" item in their application.

See Figure 16.

Your tasks are:

1- Add more functionality into the “comments” text item.

2- Create pop-up menu to have cut, past, and copy functions.

3- Associate pop-up menu to the “comments” item.

4- Run and test all user functional requirements.

FYou will learn how to: create "pop-up" menu, with following functionality "cut," "paste," and "copy,” use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 16

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V13)

from the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V13” form. The color changes to blue. Then change the name and save the Form name as version 14 (customer_orders_v14). This way the original form is untouched.

Layout Editor

Go to the Main menu and choose the "Tools" option and select "Layout Editor." Adjust the window if needed.

In the Layout Editor, make sure that the Canvas box contains Canvas Tab name (CANVASn) and the Block box contains Customer.

Create a Popup Menu

In the Object Navigator, select the "Popup Menus" item and click on the "Create" icon (Green ‘+’). A popup menu will be created for you (MENUnn).

Change a popup menu’s name

Highlight the object (MENUnn) and click again to change its color to blue. Then type its new name EDITOR_TOOLS.

Open Menu Editor for a popup menu

Double click on the EDITOR_TOOLS icon, the Menu Editor window will be displayed.

In the Menu Editor, you can change the layout and property of each item in the menu. You can add, remove, change the Menu items.

Add items into a popup menu

Change "new_itemnn” to "Cut" and press the enter key.

Click on the "Create Down" icon, change " new_itemnn " to "Copy," and press the enter key.

Click on the "Create Down" icon again, change " new_itemnn " to "Paste," and press the enter key.

Right click on the Cut item and open its Property Palette. Change “Menu item Type" to "Magic" and "Magic item" to "Cut." Then close the window.

Right click on the Copy item and open its Property Palette. Change “Menu item Type" to "Magic" and "Magic item" to "Copy." Then close the window.

Right click on the Paste item and open its Property Palette. Change “Menu item Type" to "Magic" and "Magic item" to "Paste." Then close the window.

Exit Menu Editor

Now, close the Menu Editor windown.

Assign a popup menu to an item

In the Layout Editor, right click on the COMMENTS item and open its Property Palette.

In its Property palette, go to the ‘Popup Menu’ item and click on the Down Arrow to display all existing Popup Menus. You should see EDITOR_TOOLS. Assign "editor_tools" to "Popup menu." Then, close the window

Compile and run the Form

Compile and run the application Form.

Execute Query

Click on “Execute query.”

Move the cursor to the "Comments" item and right click on it. The "Popup Menu" will be displayed. You should see all the options such as CUT, COPY, and PASTE.

Select a portion of the text, right click on it, then copy and paste it.

Select a portion of the text, right click on it and cut the selected text.

Save the changes.

Copy the whole text and paste it to the “NEXT” record.

Save the transaction.

Navigate through the application Form.

After testing the application, close the window and save the changes.

Move Objects, Layout Appearances

Introduction

Window is an empty frame to hold your object and it is the outermost boundary for a form. Block is a logical container that holds form objects such as data items and control buttons. There are two types of block: Data Block and Control Block. A Data Block is a link to your database information and holds database data. A Control Block holds information that has nothing to do with database. A item could be a database column or none database item. A canvas is a place to hold the objects. The four types of canvases are: Content, Stacked, Tab, and Toolbar. All can coexist within a single window. A content canvas is the basic background for all windows. A stacked canvas lays on top of the others. A tab canvas is the same as stacked canvases with handy “tabs” at the top. It can simply move from one canvas to another. A toolbar canvas contains push buttons giving users quick access such as horizontal canvas or vertical canvas.

Property Palette

Hands-On

All your client functional requirements have been met. Now, your user focuses on the appearance of the application. They would like you to move and add some objects. They want their logo be displayed on each of their application tab canvas.

See Figure 17.

Your tasks are:

Perform some cosmetic layout changes.

Add a logo to their application.

Copy the logo to each tab canvas.

Run and test all user functional requirements.

FYou will learn how to: move objects around, create, copy, and paste logo, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 17

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V14) in the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V14” form. The color changes to blue. Then change the name and then save the Form name as version 15 (customer_orders_v15). This way the original form is untouched.

Change appearance of a form

To change an appearance of your application, it is a good idea to view it in the full screen. This way, you will have free space to move around.

Resize an object

To change a size of an object, either you can select the object and drag its handlers or you can open its property palette and change its width and/or height.

Move an object or objects

To move an object, you should select the object and when you see the handlers, position your cursor inside of the handlers and drag it to anywhere you wish to be. You can also press on the up, down, right and left arrow keys to move the selected object.

To select multiple objects, hold on the control key (Ctrl) and select one object at a time. You will see for each object you select there are 8 handlers. Or, you can move your cursor outside of objects, click and drag it until all the objects be inside of the box. Then release the mouse. All the objects in the box will be selected and will contain handlers.

Select an item or items

To select multiple items that are next to each other, select the first one and then hold the shift key and select the last one, all the objects in between will be selected including the first and last.

Deselect an item or items

To deselect an object or objects, just click anywhere on the canvas that it is outside of the selected area.

Graphic Boiler Plate Objects

In the Layout Editor, you can use the Graphic Boiler Plate objects on the toolbar. They are on the shape of square, circle, etc. You can select them, then drop and resize them in your canvas.

TEXT and Color

Notice that in the Layout Editor on the vertical toolbar, there are two boxes that have ‘T’ in them. One is bigger that the other. The smaller T (text) is for entry text. The big one T is for changing the text, background, and boarder colors. There are Fill Color, Line Color, and Text Color. If you select an object and then choose a color by clicking on the Fill Color icon, the object color will change. If you select a text and then choose a color by clicking on the Text Color icon, the text color will change. And it is the same for the Line Color icon. Try them. It is fun!

With all above functionalities and your creativities, you have lots of options to make the appearances of your application easy to understand.

See figure 17 and create the same picture on all your canvas tabs. You can create once and then copy and paste them.

Compile and run the Form

Once you finish. Compile and run the application Form.

Execute Query

Click on “Execute query.”

Navigate the Form

Navigate through the application Form.

After testing the application, close the window and save the changes.

Run Application from desktop

Hands-on Introduction

Your clients ask you that they would like to run their application from the desktop. They want to use Forms “Runtime” to execute their application.

See Figure 18.

Your tasks are:

1- Create an icon on the client PC desktop.

2- Use Forms “Runtime” to run their application.

3- Associate their “Customer Order” application to the Forms “Runtime.”

4- Run and login to application from icon.

5- Run and test all user functional requirements.

FYou will learn how to: run your application from desktop.

Figure 18

Create a new Shortcut

Go to the desktop screen, right click on an empty area, choose the New item, and select the Shortcut option.

Command Line box

In the Create Shortcut window, enter the Runtime Form program with your compiled form name (For example: ifrun60 c:_orders_v15) into the Command Line box. Then click ‘Next.’

Select a Title

In the ‘Select a Title for the Program’ window, enter your application name (Customer Orders Application) into the box. Then click ‘Finish.’

Now, your shortcut was created and you can execute your application from desktop.

Test a form from desktop

To test the functionality of your work, double click on the icon to run the application Form.

In the Logon window, enter the username and password, and then click “Connect.”

Click on “Execute query.”

Navigate through the application Form.

Test all the features and options, and then close it.

Viewing a picture using BLOB datatype

Introduction

Since you had inserted the employee pictures into the EMP table successfully (from PL/SQL hands-on), now the organization wants to view the pictures that were inserted.

You have been assigned to develop an application form to display all of the employee information plus their pictures.

Open a Module

In the ‘Object Navigator’ window, highlight the default Form name (MODULEnn). Highlight the name and click on the form name and change the color to blue. Then type "EMPLOYEE_DATA" as the form name.

Save a Module

Go to the Main menu and choose “File,” then select “Save as.” In the ‘Save As’ window, save the form in the ISELF folder.

Create a Data Block

Choose a New Data Block

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window, click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Selecting Table

Click on “browse.” In the ‘Tables’ window, highlight the EMP table; then click the "OK" push button.

Selecting columns for the Data Block Wizard

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

Welcome screen

In the ‘Welcome to the Layout Wizard’ window, click ”Next.”

Selecting canvas

Select "content," then click “Next.”

Selecting Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to display them on the canvas. Then click “Next.”

Change your objects appearances

Selecting a layout style

Record layout

Type the ‘Frame Title’ (Employee Data Information), ‘Records Displayed’, ‘Distance between Records’ values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Resize an objectYou can resize the photo image as needed. You know how big your original picture is. If the box is too small, you may only see a part of the picture and not the whole.

Run the FormRun the application form and maximize the "Forms Runtime" window.

Execute QueryClick on the "Execute Query" icon to display an employee record. Navigate through your application. Notice that we only added pictures to the first two employees in the employee table. You added picture in the PL/SQL hands-on.

Exit the formExit the Runtime form.

Go to the "Object Navigator" windows and highlight the "EMPLOYEE_DATA" Form to save it. Go to Main menu, choose the File sub-menu, and select the Save option.

Save the "EMPLOYEE_DATA" form in the ISLEF folder.

REF CURSOR

Introduction

You have been assigned to complete the process for basing a block on a stored procedure for “single block operations.”

What is a REF Cursor?

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings. This is an added feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to be opened on the server and passed to the client as a unit, rather than one row at a time. One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units. Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.

What is a table of records?

A table of records is a new feature added in PL/SQL v2.3. It is the equivalent of a database table in memory. If you structure the PL/SQL table of records with a primary key (an index) you can have array-like access to the rows.

Why base a block on a PL/SQL Table versus a Ref Cursor?

A table of records fetches all the rows from the table. A reference cursor fetches only those rows that match your query criteria. If you are planning to filter the rows with a where clause or your query returns only few records out of many, you can choose the ref cursor rather than the table of records. Note the block properties for the number of records set and the buffered affected blocks, based on stored procedures.

Assignments

Your assignments are:

Create a table

Create a package spec at the database level

Create the package body

Create the Form Block

Create following procedure

· query,

· insert,

· query,

· lock,

· update,

· delete, and

· count procedures.

These are required steps to complete the process for basing a block on a stored procedure for single block operations. You have been assigned to complete the process for basing a block on a stored procedure for “single block operations.”

Hands-On

Connect to SQLPLUS using ISELF/SCHOOLING user.

SQL> CONNECT iself/schooling

Create a TableLet's, first create a table that contains all the manager names.

SQL> CREATE TABLE managers (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50))
/

Create a Package Specification

Create a package specification at the database level. Read the following procedures and functions very carefully. At this level, we assume that you know how to write a PACKAGE SPECIFICATION and BODY.

(Procedure Builder)
CREATE OR REPLACE PACKAGE managers_pkg IS
TYPE managers_rec IS

RECORD(
empno managers.empno%TYPE,
ename managers.ename%TYPE);

TYPE c_managers IS REF CURSOR RETURN managers_rec;

TYPE t_mgrtab IS TABLE OF managers_rec

INDEX BY BINARY_INTEGER;

PROCEDURE managers_refcur(managers_data IN OUT c_managers);

PROCEDURE managers_query(managers_data IN OUT t_mgrtab);

PROCEDURE managers_insert(r IN managers_rec);

PROCEDURE managers_lock(s IN managers.empno%TYPE);

PROCEDURE managers_update(t IN managers_rec);

PROCEDURE managers_delete(t IN managers_rec);

FUNCTION count_query_ RETURN number;

END managers_pkg;
/

Note that you can use either a Ref Cursor or a Table of Records on the FORM Builder to perform the query operation.

Create a Package Body

(Procedure Builder)
/*
The next page is a package body that contains the source code
of the procedures and function in the package.

You are encouraged to the movie and take notes about
the package body.

In the next Hands-On you will learn how to use the FORM Builder
tool to call the package and use its procedures and function to
insert, delete, update, lock and count the managers table.

You may use the managers_refcur or managers_query procedures
in the FORM Builder tool to perform the query operation.
*/

CREATE OR REPLACE PACKAGE BODY managerS_pkg

IS

PROCEDURE managers_query(managers_data IN OUT t_mgrtab)

IS
ii NUMBER;
CURSOR manager_select IS
SELECT empno, ename from managers;

BEGIN
for v_managers_select in manager_select loop
ii := 1;
managers_data( ii ).empno := v_managers_select.empno;
managers_data( ii ).ename := v_managers_select.ename;
ii := ii + 1;
END LOOP;
END managers_query;

PROCEDURE managers_refcur(managers_data IN OUT c_managers)

IS
BEGIN
OPEN managers_data FOR SELECT empno, ename
FROM managers;
END managers_refcur;

PROCEDURE managers_insert(r IN managers_rec)

IS
BEGIN
INSERT INTO managers VALUES(r.empno, r.ename);
END managers_insert;

PROCEDURE managers_lock(s IN managers.empno%TYPE)

IS
v_rownum NUMBER;
BEGIN
SELECT empno INTO v_rownum FROM managers
WHERE empno=s FOR UPDATE OF ename;
END managers_lock;

PROCEDURE managers_update(t IN managers_rec)

IS
BEGIN
UPDATE managers SET ename=t.ename
WHERE empno=t.empno;
END managers_update;

PROCEDURE managers_delete(t IN managers_rec)

IS
BEGIN
DELETE FROM managers WHERE empno=t.empno;
END managers_delete;

FUNCTION count_query_ RETURN NUMBER

IS
r NUMBER;
BEGIN
SELECT COUNT(*) INTO r FROM managers;
RETURN r;
END count_query_;

END managers_pkg;
/
Now, you are ready to create the FORM Block along with the Transactional Triggers. Let’s go to the Procedure Builder tool to view the package specification and body.

FORM BLOCK and TRANSACTIONAL TRIGGERS

Introduction

Your organization wants to use the “managers_pkg” package that you created to insert, delete, update, lock, and count a record.

You have been assigned to develop an application form to manipulate the managers table using transactional triggers.

Database Objects

Open the FORMS builder. In the FORMS builder window, click on the "+" sign next to "Database objects" to see its entire object. If the database is not open, you will be prompted a login window to log in to the database.

After successful login, you will see a ‘+’ sign next to the Database Objects. Click on the ‘+’ sign to expand it. All the schemas in the database will be listed.

Check your created packageExpand the ISELF schema. Then, expand the "Stored Program Units" item. You should at least see the last package (MANAGERS_PKG) that you created. There must be two items. One for package specification and the other for package body. There should not be any ‘*’ next to them. An ‘*’ indicates that your package was not compile successfully.

Open the "Managers_PKG" package body. Browse through the package body that had been created during the previous hands-on exercise. We are going to use the delete, update, and insert procedures to manipulate the "FORM BLOCK" object. Then close "WINDOWS."

Browse through the package specification and then close the window.

Save a ModuleIn the Object Navigator, change the "MODULEnn" name to "MANAGERS_INFORMATION."

Create a Data Block

In the ‘Object Navigator’ window, highlight "Data Blocks,” and click on the "create” icon.

Data Block Wizard

In the ‘New Data Block’ window, choose the default option “Data Block Wizard” and click "OK."

Welcome Data Block

In the ‘Welcome Data Block Wizard’ window click “NEXT.”

Type of Data Block

Select the type of data block you would like to create by clicking on a radio button. Select the default option ‘Table or View’ and then click “NEXT” again.

Select Table

Click on “browse.” In the ‘Tables’ window, highlight the "MANAGERS” table; then click "OK."

Select columns for the Data Block Wizard

Layout Wizard

End of the Data Block Wizard and beginning of the Layout Wizard

In the ‘Congratulations’ screen, select the "Just Create the data block" option and click "FINISH." You can also use the Data Block Wizard to modify your existing data block. Simply select the data block in the Object Navigator and click the Data Block Wizard toolbar button, or choose ‘Data Block wizard’ from the ‘Tools’ menu.

Open and change a property palette sheet

In the Object Navigator, right click on the “MANAGERS" data block item to open its Property Palette.

In the its property palette, change the "Query Data Source Type" item to "Procedure," change the "Source Name" item to "MANAGERS_PKG.MANAGERS_REFCUR,” change the "Source Arguments" item, in the ‘Query Data Source Arguments’ window, type "MANAGERS_DATA” in the ‘Argument Name’ box, change the Type item to "REFCURSOR,” type "MANAGERS_PKG.C_MANAGERS” in the Type Name box, set the "Mode" to "IN OUT,” and click ‘OK’ to close the window. Back to property palette (Data Block: MANAGERS), change the "DML Data Target Type" item to "Transactional Triggers." Then close the window.

Create a triggerIn the Object Navigator, highlight "Triggers" for the MANAGERS data block and click on the "Create" icon.

ON-INSERT triggerIn the Trigger window, type the letter "O," then "I," to select the ON-INSERT trigger, and click "OK."

PL/SQL EditorIn the PL/SQL window, write a stored procedure to insert a record into the "MANAGERS" table.

(PL/SQL Editor)

DECLARE

r managers_pkg.managers_rec;

BEGIN

r.empno := :managers.empno;

r.ename := :managers.ename;

managers_pkg.managers_insert ( r );

END;

Notice that the "MANAGERS_INSERT" procedure was already created during the previous Hands-On.

Compile ON-INSERTCompile the trigger.

Create ON-LOCK triggerIn the PL/SQL Editor, click on "New" and in the Trigger window, type the letter "O" and "L” to get the ON-LOCK trigger. Then click on "OK."

PL/SQL for ON-LOCK triggerIn the PL/SQL Editor, write a stored procedure to lock a record. Use "MANAGERS_LOCK" that was created from the previous Hands-On.

(PL/SQL Editor)

managers_pkg.managers_lock (:managers.empno);

Compile ON-LOCK trigger

Compile the trigger.

Create ON-UPDATE triggerIn the PL/SQL Editor, click on "New" and in the Trigger window, type the letter "O" and "U" to get the ON-UPDATE trigger. Then click "OK."

In the PL/SQL Editor, write a stored procedure to update a record. Use the "UPDATE" procedure from your created package.

(PL/SQL Editor)

DECLARE

r managers_pkg.managers_rec;

BEGIN

r.empno := :managers.empno;

r.ename := :managers.ename;

managers_pkg.managers_update ( r );

END;

Compile then trigger.

Create ON-DELETE trigger

In the PL/SQL Editor, click on "New" and in the Trigger window, type the letter "O" and "D," select the ON-DELETE trigger. Then click "OK."

In the PL/SQL Editor, write a stored procedure to update a record. Use the "Delete" procedure from your created package.

(PL/SQL Editor)

DECLARE

r managers_pkg.managers_rec;

BEGIN

r.empno := :managers.empno;

r.ename := :managers.ename;

managers_pkg.managers_delete ( r );

END;

Compile it.

Create ON-COUNT trigger

In the PL/SQL Editor, click on "New" and in the Trigger window, type the letter "O" and "C," select the ON-COUNT trigger. Then click "OK."

In the PL/SQL Editor, write a stored procedure to update a record. Use the "COUNT_QUERY_" procedure from your created package.

(PL/SQL Editor)

DECLARE

r NUMBER;

BEGIN

r := managers_pkg.count_query_;

SET_BLOCK_PROPERTY (‘managers’, query_hits, r);

END;

Compile the trigger and close the window.

Open Layout Wizard

Go to the Main menu, choose "Tools" sub-menu and select the "Layout Wizard" option.

In the Welcome window, click on "next" to continue.

Select canvas

In the ‘Layout Wizard’ window, select the "new canvas" option. Select "content," then click “Next.”

Select Columns for the Layout Wizard

In the ‘Layout Wizard’ window, select all the columns. These are the columns that you want to display them on the canvas. Then click “Next.”

Change your objects appearances

Select a layout style

Record layout

Type the Frame Title (Managers Name), Records Displayed, Distance Between Records values and checkmark the ‘Display Scrollbar’ box, when you use multiple records or the ‘Tabular’ option. Then click “Next.”

Congratulation Screen

In the ‘Congratulations’ window, click "Finish."

Now, run the program to test it. Insert records into the Managers table. Delete and/or update a record. Then save the transactions.

Close the application and save the FORM.

Parameters between FORMS

Introduction

Let’s suppose that you have two forms named EMPLOYEES and DEPARTMENTS. In the EMPLOYEES form, if your cursor is pointing at the “deptno” item when you click on a push button (For example: Show me department information…) you would want it to open a window to display all information about that department.

Before reading the following hands-on exercise, you should have all ready completed at least the ‘Introduction to Form Builder’ hands-on. To avoid repetition, we assume that, you are at least familiar with where the icons and sub-menus are.

Step 1:

Create DEPARTMENT form

Create the “DEPARTMENT” form as you create any other form. Create a parameter and name it p_deptno. Open the parameter’s property palette and change the Parameter Data Type field to “Number,” the Maximum Length field to “2,” and the Parameter Initial Value field to “10.” Open the data block properties (DEPT) and in the “WHERE Clause” property type deptno = :parameter.p_deptno. Then create the “WHEN-NEW-FORM-INSTANCE” triggers on form level and type “execute_query;” then compile the procedure.

Step 2:

Create EMPLOYEES form

Create the “EMPLOYEES” form as you create any other form including the tabular, and 10 records at a time options, and check mark “Display Scrollbar” option.

Create a control block and then create a push button in that block. Open the push button’s property palette and change the “Name,” and “Label” fields. The “label” value should be “Show me department information…” Create the “WHEN-BUTTON-PRESSED” trigger and type the following code:

(Form Builder)

DECLARE

p_id paramList; -- This is a variable that contains id of the parameter list

p_name VARCHAR2(20); -- A variable that keeps your parameter name

BEGIN

p_name := 'myparameter';

p_id := GET_PARAMETER_LIST('p_name'); -- find it if exists.

IF NOT ID_NULL (p_id) THEN

-- If exit then destroy it.

DESTROY_PARAMETER_LIST(p_id);

END IF;

--Now create a parameter list and add your p_deptno parameter.

p_id := CREATE_PARAMETER_LIST('p_name');

ADD_PARAMETER(p_id,'P_DEPTNO',TEXT_PARAMETER,to_char(:deptno));

--- You can call the called program either by default or specific like c:.

OPEN_FORM('c:',ACTIVATE,SESSION,p_id);

-- Error messages in case the called program was not able

-- to open called program.

IF NOT FORM_SUCCESS THEN

MESSAGE('ERROR: Unable to open the DEPARTMENT form.');

RAISE FORM_TRIGGER_FAILURE;

END IF;

EXCEPTION

WHEN others THEN

MESSAGE ('Error: unable to create parameter list…');

RAISE FORM_TRIGGER_FAILURE;

END;

Step 3:

Compile the DEPARTMENT form

Make sure that you have compiled the DEPARTMENT form.

Step 4:

Test the application

Now, you should be able to test. Execute the query on the EMPLOYEES table and move your cursor to any record that you need to know more information about its department. Then click on the “Show me department information” push button. You should see complete information about that department.

Parameter List

Introduction

With the previous versions of Forms, the way to pass values between different forms was using Global variables. This technique you should have already known by previous topics in this book. Now there is a flexible way to pass values between forms. You can create your own parameter list programmatically and pass it as an argument in a CALL_FORM or OPEN_FORM.

You should use the following built-ins functions and procedures to manipulate parameter lists:

CREATE_PARAMETER_LIST: creates an empty parameter list.

ADD_PARAMETER: adds a parameter to an existing parameter list.

GET_PARAMETER_LIST: determines if there is already a parameter list with the same name as the one you are trying to create.

DESTROY_PARAMETER_LIST: destroys a parameter list.

DELETE_PARAMETER: deletes a parameter in the parameter list.

Hands-on

Let’s illustrate the use of these built-ins functions and procedures. Suppose that you need to pass the customer ID from one form (CUSTOMERS) to another form (PORTFOLIO); you use the CUSTOMER ID to query all customers’ stocks portfolio in the portfolio table in the called form. You can execute the following code from a “WHEN-BUTTON-PRESSED” trigger or even from a menu item:

DECLARE

param_list_id ParamList; -- Define an object of type paramlist

BEGIN

param_list_id := GET_PARAMETER_LIST(‘my_parameter');

-- Test if the parameter list already exists.

IF NOT ID_NULL(param_list_id) THEN

DESTROY_PARAMETER_LIST(param_list_id);

END IF;

param_list_id := CREATE_PARAMETER_LIST('my_parameter');

ADD_PARAMETER(param_list_id, 'p_customer_id', TEXT_PARAMETER, :ID);

CALL_FORM('c:', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, param_list_id);

END;

It is important to remember that you must declare an object of type ParamList. Also, all parameters that you define in the Object Navigator belong to the default parameter list. You can also pass the default parameter list to another form if you need. For example:

WHEN-BUTTON-PRESSED

BEGIN

CALL_FORM('employee', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, 'default');

END;

When passing the default parameter list as well as any other parameter list, make sure that every parameter exists with the same name in the called form.

Now let us see how we can access the value of parameter in a called form? To access the value of a parameter in a called form, you must create the following triggers in the employee form: WHEN-NEW-FORM-INSTANCE at the form level

In addition, create a parameter with the same name as the parameter that you are passing in the parameter list. If you fail to do this, the application returns an error message that the parameter does not exist.

The following is an example of a trigger needed to do a query based on the

value passed in the parameter list when the form 'portfolio’ is called:

WHEN-NEW-FORM-INSTANCE

BEGIN

-- Obtain the name of the calling form

form_name := GET_APPLICATION_PROPERTY(CALLING_FORM);

IF form_name IS NOT NULL THEN

-- Execute a query if the form is a called form

EXECUTE_QUERY;

ELSE

:parameter.p_customer_id := 10;

END IF;

END;

Also you can create a parameter by executing the following steps:

1) In the Object Navigator, select the Parameters node and choose

NAVIGATOR->CREATE.

2) Bring up the properties of the parameter and set the properties as needed.

For example, Set the Datatype, Default Value, and Name of the parameter.

3) To access the value of the parameter, add the reserved word PARAMETER as a prefix to the parameter name. If you need to assign a value to a parameter, use a regular assignment statement such as:

temp_test := :parameter.test; -- assigns the value of the parameter to test

:parameter.test := 'value'; -- assigns a value to the parameter test