Forms Is Elf Schooling

Lesson 01 “Only the educated are free.” Epictetus (55 AD - 135 AD), Discourses Read first then play the video: FRM001(VIDEO)-Introduction to complete Hands-On 01 to 06 FRM002(VIDEO)-Dasic Intro about FORM Builder Tool

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. “Real success is finding your lifework in the work that you love.” David McCullough (1933)

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 theForm 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. Acontent 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. TheObject 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. AProperty 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 Paletteprovides complete control over your objects. It contains the property list of an object. Remember that theproperties 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.

“All you need in this life is ignorance and confidence; then success is sure.” Mark Twain (1835 1910), Letter to Mrs Foote, Dec. 2, 1887

Questions: Q: What are the Oracle Developer Tool components? Q: The Back-End development tool consists of ______ Q: The Front-End development tool consists of ______

Q: The Form Builder components are _____ Q: What is a Form Module in the Form Builder? Q: What is a menu module in the Form Builder? Q: What do you store in a PL/SQL library? Q: What do you store in an Object Library? Q: What are the main objects of a Form Module? Q: Describe a window object in a Form Module? Q: What is a block in a Form Module? Q: What is an item in a From Module? Q: Describe a canvas in a Form Module? Q: What are the types of canvases in a Form Module? Q: Describe the Object Navigator in a Form Module? Q: Describe property palette in a Form Module? Q: What does a property palette for an object contain in a Form Module? Q: Describe the Data Block Wizard tool? Q: Describe the Layout Wizard tool?

Lesson 02 “They say dreams are the windows of the soul--take a peek and you can see the inner workings, the nuts and bolts.” Henry Bromel, Northern Exposure, The Big Kiss, 1991 Read first then play the video: FRM001(VIDEO)-Introduction to complete Hands-On 01 to 06 FRM002(VIDEO)-Dasic Intro about FORM Builder Tool

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.

“A strong positive mental attitude will create more miracles than any wonder drug.” Patricia Neal

Questions Q: Use the Form Builder tool to create a Data block for the “Customer” table.

Q: How to you connect to the database objects using the Form Builder tool? Q: How can you create a "Datablock" for a form application using the "Form Builder" tool? Q: How can you create a "layout" for a form application using the "Form Builder" tool? Q: How can you use a "Content canvas" for a form application using the "Form Builder" tool? Q: Use the "Execute Query" option to query customer's information. Q: How can you navigate a table using the "Form Builder" tool? Q: What does the "NEXT RECORD" option do? Q: What does the "PREVIOUS RECORD" option do? Q: What does the "Enter Query" option do? Q: How can you manipulate table's record? Q: Insert, delete and update a record. Q: How can you save a transaction using the "Form Builder" tool?

Lesson 03 “You cannot dream yourself into a character; you must hammer and forge yourself one.” James A. Froude (1818 - 1894) Read first then play the video: FRM003(VIDEO)-(Hands-On 01) How to use Tab Canvas, Master-detail relationship

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. In 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 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 order. "c:_orders." 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. 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 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 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 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, 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 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 “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 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 “Tabular” since there is more than one item for each order 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 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.

“The meeting of two personalities is like the contact of two chemical substances: if there is any reaction, both are transformed.” Carl Jung (1875 - 1961)

Questions: Q: How can you create a "Datablock" for a form application using the "Form Builder" tool? Q: How can you create a "layout" for a form application using the "Form Builder" tool? Q: How can you use a "Content canvas" for a form application using the "Form Builder" tool? Q: Use the "Execute Query" option to query customer's information. Q: How can you navigate a table using the "Form Builder" tool? Q: What does the "NEXT RECORD" option do? Q: What does the "PREVIOUS RECORD" option do? Q: What does the "Enter Query" option do? Q: How can you manipulate table's record? Q: Insert, delete and update a record. Q: How can you save a transaction using the "Form Builder" tool? Q: What is a Master/Detail relationship between two data blocks in the Form Module? Q: Can a Form Module contain more than one canvas? Q: 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.

Lesson 04 “The life which is unexamined is not worth living.” Plato (427 BC - 347 BC), Dialogues, Apology Read first then play the video: FRM004(VIDEO)-(Hands-On 02) How to use Tooltip, Hint, Format Mask, Properties

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

“To love and be loved is to feel the sun from both sides.” David Viscott

Questions: Q: Describe a property palette for an object in the Form Module. Q: What does a property palette provide for you to modify an object? Q: How do you manipulate an object in the Form Builder tool? Q: 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. 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. Q: How can you create a help for an item in the From Module? Q: How can you create a hint for an item in the Form Module? Q: What are the differences between a help object and hint object? Q: How can you change a format mask of an item in the Form Module? Q: How can you execute a query?

Lesson 05 “There is always some madness in love. But there is also always some reason in madness.” Friedrich Nietzsche (1844 - 1900), "On Reading and Writing" Read first then play the video: FRM005(VIDEO)-(Hands-On 03) How to use LOVs and Record Group

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

“Happiness depends upon ourselves.” Aristotle (384 BC - 322 BC)

Questions: Q: What is a List of Values (LOV) in a Form Module? Q: How can you search a List of Values for a string? Q: What is a Record Group in the Form Module? Q: What is a Tab Canvas? Q: How does a Record Group work with a List of Values? Q: 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.

Lesson 06 “America believes in education: the average professor earns more money in a year than a professional athlete earns in a whole week.” Evan Esar (1899 - 1995) Read first then play the video: FRM006(VIDEO)-(Hands-On 04) How to use Radio Buttons

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. In 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.”

Lesson 07 “People fail forward to success.” Mary Kay Ash Read first then play the video: FRM007(VIDEO)-(Hands-On 05) How to use Display items, Summary and function columns

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

“Whenever I hear, 'It can't be done,' I know I'm close to success.” Michael Flatley (Lord of the Dance) quoted by Eric Celeste

Questions: Q: Describe a Display Item in the Form Module. Q: What are the Summary Columns in the Form Module? Q: What are the Function Columns in the Form Module? Q: What are the differences between Summary Columns and Function Columns? Q: How do you calculate a SUM of a group of items in a block? Q: 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.

Lesson 08 “Go confidently in the direction of your dreams. Live the life you have imagined.” Henry David Thoreau (1817 - 1862) Read first then play the video: FRM008(VIDEO)-(Hands-On 06) How to use FORMS Triggers (POST-QUERY)

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 POSTQUERY 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. In 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.

“Human beings, by changing the inner attitudes of their minds, can change the outer aspects of their lives.” William James (1842 - 1910)

Questions: Q: What is a Forms Trigger? Q: Describe the WHEN-BUTTON-PRESSED trigger. Q: Describe the PRE-QUERY trigger. Q: Describe the POST-QUERY trigger. Q: Describe the PRE-INSERT trigger. Q: Describe the WHEN-NEW-FORM-INSTANCE trigger. Q: 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.

Lesson 09 “When the character of a man is not clear to you, look at his friends.” Japanese Proverb Read first then play the video: FRM009(VIDEO)-Introduction to complete Hands-On 07 to 12 FRM010(VIDEO)-(Hands-On 07) How to use Horizontal Toolbar, Push Button, Trigger (WHEN-BUTTON-PRESSED)

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 canvaslays 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. You 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.

“Friendship makes prosperity more shining and lessens adversity by dividing and sharing it.” Cicero (106 BC - 43 BC), On Friendship, 44 B.C.

Questions:

Q: Describe a canvas in the Form Module. Q: The four types of canvases are: ________ Q: Describe all four types of canvases. Q: How do you create a horizontal toolbar? Q: How do you create a push button item? Q: 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.

Lesson 10 “Life is something that everyone should try at least once.” Henry J. Tillman Read first then play the video: FRM011(VIDEO)-(Hands-On 08) How to do sizing and layout appearances

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. 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. AProperty 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 Paletteprovides complete control over your objects. It contains the property list of an object. Remember that theproperties 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 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. You 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.

“Happiness is that state of consciousness which proceeds from the achievement of one's values.” Ayn Rand (1905 - 1982)

Questions: Q: Describe a Window in the Form Module. Q: The properties of an object can be changed to control the behavior of the _____. Q: What is a Data Block in the Form Module? Q: How do you resize an object in the Layout Editor? Q: How do you move an object or a group of objects in the Layout Editor? Q: What are the handlers in a selected object? Q: 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.

Lesson 11 “"Life is pleasant. Death is peaceful. It's the transition that's troublesome." - Isaac Asimov Read first then play the video: FRM012(VIDEO)-(Hands-On 09) How to use Trigger (PRE-QUERY) and set dynamically properties

Trigger (PRE-QUERY), Set dynamically properties Introduction 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-checkboxchanged, 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 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. 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. You 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.

“Education... has produced a vast population able to read but unable to distinguish what is worth reading.” G. M. Trevelyan (1876 - 1962), English Social History (1942)

Questions: Q: Describe the PRE-QUERY trigger. Q: How do you set a block property dynamically? Q: Can you attach several triggers to an object in the Form Module? Q: 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.

Lesson 12 “Good teaching is one-fourth preparation and three-fourths theater.” Gail Godwin Read first then play the video: FRM013(VIDEO)-(Hands-On 10) How to use Sequence object and Trigger (PRE-INSERT)

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

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. POST-QUERY and PRE-QUERY Trigger The PRE-QUERY trigger fires before the select statement is finalized. The POSTQUERY 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 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. You 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. Login to “sqlplus” as “iself” password “schooling.” 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> / 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.

“Aim for success, not perfection. Never give up your right to be wrong, because then you will lose the ability to learn new things and move forward with your life.” Dr. David M. Burns

Questions: Q: Describe a Sequence object in a Form Module. Q: How do you create a sequence object in a Form Module? Q: Describe the PRE-INSERT trigger. Q: How do you create and use a PRE-INSERT trigger in a Form module? Q: How do you navigator through tab canvases? Q: What for do you use an object’s property palette? Q: How do you read and test a sequence object? Q: 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.

Lesson 13 “Dreams surely are difficult, confusing, and not everything in them is brought to pass for mankind. For fleeting dreams have two gates: one is fashioned of horn and one of ivory. Those which pass through the one of sawn ivory are deceptive, bringing tidings which come to nought, but those which issue from the one of polished horn bring true results when a mortal sees them.” Homer (800 BC - 700 BC), The Odyssey Read first then play the video: FRM014(VIDEO)-(Hands-On 11) How to use Call FORM

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. You 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 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 ITEM block. Or you can click on the ITEMS tap. 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 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. 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 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 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. 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

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 "Tabular," and then click “next.” 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 "WHENBUTTON-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.

“The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.” William James (1842 - 1910)

Questions: Q: What are the ways you can execute a form in a form? Q: What are the differences between the OPEN_FORM, CALL_FORM, and NEW_FORM build-in subprograms? Q: What is a Global variable? Q: How can you create a form to call another form?

Q: How do you compile a trigger in a form module? Q: 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.

Lesson 14 “Men show their characters in nothing more clearly than in what they think laughable.” Johann Wolfgang von Goethe (1749 - 1832) Read first then play the video: FRM015(VIDEO)-(Hands-On 12) How to use Global Variable and Trigger (WHEN-NEW-FORM-INSTANCE)

Global Variable, Trigger Global Variable A Global Variable is a binding variable that can be used by multiple Form Modules. Form 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. POST-QUERY trigger The PRE-QUERY trigger fires before the select statement is finalized. The POSTQUERY 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.

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, 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. You 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-FORMINSTANCE 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.

“We all carry around so much pain in our hearts. Love and pain and beauty. They all seem to go together like one little tidy confusing package. It's a messy business, life. It's hard to figure--full of surprises. Some good. Some bad.” Henry Bromel, Northern Exposure, The Big Kiss, 1991

Questions: Q: How do you define a binding variable in a multiple forms? Q: How do you assign a value to a global variable? Q: How do you pass a global variable value from one form to another? Q: How do you use a global variable to execute a query based on its parameter? Q: Describe the "WHEN-NEW-FORM-INSTANCE" trigger. Q: How do you create the "WHEN-NEW-FORM-INSTANCE" trigger? Q: 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.

Lesson 15 “She knows what is the best purpose of education: not to be frightened by the best but to treat it as part of daily life.” John Mason Brown (1900 - 1969) Read first then play the video: FRM016(VIDEO)-Introduction to complete Hands-On 13 to 16 FRM017(VIDEO)-(Hands-On 13) How to use Main Menu

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

“Fathers send their sons to college either because they went to college or because they didn't.” L. L. Henderson

Questions: Q: What is a main menu in the Form Builder? Q: Are the main menus independent of form? How? Q: What is the default main menu name? Q: How do you use NAME_IN and COPY build-in subprogram? Q: How do you create a default main menu? Q: How do you modify a default main menu? Q: 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.

Lesson 16 “Nothing changes your opinion of a friend so surely as success - yours or his.” Franklin P. Jones, Saturday Evening Post, November 29, 1953 Read first then play the video: FRM018(VIDEO)-(Hands-On 14) How to use Pop up menu

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

“Now, I say to you today my friends, even though we face the difficulties of today and tomorrow, I still have a dream. It is a dream deeply rooted in the American dream. I have a dream that one day this nation will rise up and live out the true meaning of its creed: - 'We hold these truths to be self-evident, that all men are created equal.'” Martin Luther King Jr. (1929 - 1968), Speech at Civil Rights March on Washington, August 28, 1963

Questions: Q: Describe a Pop-Up menu in a Form Module. Q: How do you create a Pop-Up menu? Q: 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.

Lesson 17 “I happen to feel that the degree of a person's intelligence is directly reflected by the number of conflicting attitudes she can bring to bear on the same topic.” Lisa Alther, Kinflicks, 1975 Read first then play the video: FRM019(VIDEO)-(Hands-On 15) How to move objects and manage the layout appearances

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 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. AProperty 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 Paletteprovides complete control over your objects. It contains the property list of an object. Remember that theproperties 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 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. You 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.

“Hard work spotlights the character of people: some turn up their sleeves, some turn up their noses, and some don't turn up at all.” Sam Ewing

Questions: Q: How do you use a window in the Form Module? Q: Describe a Data Block in the Form Module. Q: Describe a Control Block in the Form Module. Q: What are the differences between a control block and a data block? Q: How do you move an object or objects around in the Layout Editor? Q: How do you copy and paste an object or objects in the Layout Editor and in the Object Navigator? Q: How do you select a object or objects in the Layout Editor and in the Object Navigator? Q: 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.

Lesson 18 “The secret of a good life is to have the right loyalties and hold then in the right scale of values.” Norman Thomas (1884 - 1968) Read first then play the video: FRM020(VIDEO)-(Hands-On 16) How to run application from desktop

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

“When love is in excess it brings a man nor honor nor any worthiness.” Euripides (484 BC - 406 BC), Medea, 431 B.C.

Questions: Q: How can you run an application from desktop? Q: They 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.

Lesson 19 “The discovery of a new dish does more for human happiness than the discovery of a new star.” Anthelme Brillat-Savarin (1755 - 1826), Physiologie du Gout, 1825 Read first then play the video: FRM021(VIDEO)-Inserting employee's picture to a table FRM022(VIDEO)-Viewing a picture using BLOB datatype in a FORM

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

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.’ 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 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 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 have your canvas, select the canvas and then click on the Next push button. 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

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’ (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 object You 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 Form Run the application form and maximize the "Forms Runtime" window. Execute Query Click 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 form Exit 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.

“All I can say about life is, Oh God, enjoy it!” Bob Newhart (1929)

Questions: Q: Describe the BLOB datatype. Q: How do you create an item containing picture in a canvas? Q: 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.

Lesson 20 “The pursuit of happiness is a most ridiculous phrase; if you pursue happiness you'll never find it.” C. P. Snow (1905 - 1980) Read first then play the video: FRM023(VIDEO)-What is a REF CURSOR FRM024(VIDEO)-FORM BLOCK and TRANSACTIONAL TRIGGERS

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 Table Let'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.

"The optimist proclaims that we live in the best of all possible worlds, and the pessimist fears this is true." - James Branch Cabell

Questions: Q: What is a REF Cursor? Q: What is a table of records?

Lesson 21 "A friendship founded on business is better than a business founded on friendship." - John D. Rockefeller (1874-1960) Read first then play the video: FRM023(VIDEO)-What is a REF CURSOR FRM024(VIDEO)-FORM BLOCK and TRANSACTIONAL TRIGGERS

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 package Expand 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 Module In 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 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 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 trigger In the Object Navigator, highlight "Triggers" for the MANAGERS data block and click on the "Create" icon. ON-INSERT trigger In the Trigger window, type the letter "O," then "I," to select the ON-INSERT trigger, and click "OK." PL/SQL Editor In 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-INSERT Compile the trigger. Create ON-LOCK trigger In 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 trigger In 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 trigger In 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 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. When you change size, click “Next.” Select 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 (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.

"Be nice to people on your way up because you meet them on your way down." - Jimmy Durante

Questions: Q: What is a transactional trigger? Q: How do you use a stored procedure in stead of tables to create a form? Q: When do you use a transactional trigger?

Lesson 22 "The true measure of a man is how he treats someone who can do him absolutely no good." - Samuel Johnson (1709-1784)

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-FORMINSTANCE” 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.

Lesson 23 "A people that values its privileges above its principles soon loses both." - Dwight D. Eisenhower (1890-1969), - Inaugural Address, January 20, 1953

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-BUTTONPRESSED” 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

"I'll sleep when I'm dead." - Warren Zevon (1947-2003)

Questions: Q: How do you pass a parameter from one form to another? Q: Describe the following build-in procedures and functions: CREATE_PARAMETER_LIST ADD_PARAMETER GET_PARAMETER_LIST DESTROY_PARAMETER_LIST DELETE_PARAMETER