Introduction To Application Development

Transcript

APPENDIX D INTRODUCTION TO APPLICATION DEVELOPMENT WITH ORACLE9i FORMS BUILDER A s you begin to learn the PL/SQL language, it is helpful to view an example of creating an application screen to envision how PL/SQL is used in the application development process. This appendix demonstrates developing screens using the Forms Builder software that is part of the Oracle9i Developer Suite. Two screens will be developed to support the shopper login process on the Brewbean’s Web site. Note that the user login process is discussed in Chapter 5 and the code presented in this appendix will parallel the code previously developed. 1 2 Appendix D REBUILD YOUR DATABASE As with all the chapters in the text, your first step is to rebuild the Brewbean’ database. To rebuild the database: 1. Open SQL*Plus. 2. Enter the following command, which will run all the statements contained in the appdDbcreate.sql file. Messages verifying the creation and data insertion steps will scroll on the SQL*Plus screen. @\appendix.d\appdDbcreate This script file creates and populates only one table, BB_SHOPPER. Note CREATE APPLICATION SCREENS FOR THE BREWBEAN’S WEB SITE The Brewbean’s application allows shoppers to store a profile in the database using the BB_SHOPPER table. To retrieve this information, the shopper must log in using a user name and password.This section is a step-by-step example for creating the login portion of the Brewbean’s application with Forms Builder. To achieve this task we will create a data block, canvases, a stored procedure, and a trigger. Of course, before we can do anything, we must start the Forms Builder, which we will do next. Starting Forms Builder To start Forms Builder: 1. Go to the Windows Start menu. 2. Locate the entry named Oracle9i Developer Suite – Oracle9iDS. Select the Forms Developer item. 3. Click the Forms Builder subentry to start the software. 4. The initial screen as shown in Figure D-1 will appear. Note that the Object Navigator pane shown operates similar to Windows Explorer. Introduction to Application Development with Oracle9i Forms Builder 3 D Figure D-1 Forms Builder initial window 5. To be able to test run any application forms created in Developer9i, the Oracle components for Java need to be available. Under the Forms Developer submenu, click the Start OC4J Instance entry. Now that we can start up Forms Builder, in the next section we will explore how to create the login application screens within this tool. Creating a Control Data Block Data blocks are used to manage data from the database or manage items such as text boxes and push buttons placed on the screens or canvases. First, we need to create a data block to hold the items we will place on the canvases to allow the user to enter login information. To create a data block: 1. Click the Data Blocks node in the Object Navigator and then click the Create button on the toolbar on the left, as shown in Figure D-2. 2. A dialog box will appear, as shown in Figure D-3. Select Build a new data block manually and click OK. 4 Appendix D Create button Data Blocks node Figure D-2 Add a data block Figure D-3 New Data Block dialog box 3. A data block will now appear in the Object Navigator, as shown in Figure D-4. To set the data block properties, right-click the data block node and click Property Palette. A property palette as shown in Figure D-5 is displayed. Note that the names of items created may be different than those shown here. 4. The left side of the property palette lists the property names and the right side contains the settings. Find the property named Database Data Blocks, which can be set to either Yes or No. Set this property to No. 5. Close the Property Palette. Now that we have a data block to hold the items placed on the canvases, we will now create the canvases or the user interface screens. Introduction to Application Development with Oracle9i Forms Builder 5 D Figure D-4 Data Blocks node Figure D-5 Property Palette 6 Appendix D Creating Canvases The login portion of the application will require the development of two canvases.The first canvas will contain text boxes that allow the user or shopper to enter a login id and password. The second canvas will appear if the login is successful. To create the canvases: 1. Click the Canvases node in the Object Navigator and click the Create button, as shown in Figure D-6. Create button Canvases node Figure D-6 Create a canvas 2. Right-click the new canvas node and click Property Palette. 3. Set the Name property to LOGIN. 4. Close the Property Palette. Notice the canvas name of LOGIN appears as a node under the Canvases node, as shown in Figure D-7. 5. Double-click the icon located immediately next to the LOGIN canvas node in the Object Navigator, as shown in Figure D-8. The Canvas Layout Editor will appear. Introduction to Application Development with Oracle9i Forms Builder 7 D LOGIN canvas node Figure D-7 Set canvas properties Canvas Layout Editor Double-click the icon next to the LOGIN canvas node Figure D-8 Canvas Layout Editor 8 Appendix D 6. Now we will place a text box on the canvas. Click the Text Item button on the left-side toolbar of the Canvas Layout Editor, as shown in Figure D-9. Then move over the canvas and click where to place the text box. Place on canvas Text Item button Figure D-9 Adding items to the canvas 7. Repeat Step 6 to add the second text box onto the canvas. 8. Double-click the first text box placed on the canvas to open the Property Palette, as shown in Figure D-10. 9. Set Name to USER and Database Item to No. 10. Close the Property Palette pane. 11. Double-click the second text box placed on the canvas to open the Property Palette. 12. Set Name to PASS and Database Item to No. 13. Close the Property Palette pane. 14. Click the Text button on the Canvas Layout Editor’s left-side toolbar and then click the canvas above the text boxes, as shown in Figure D-11. Introduction to Application Development with Oracle9i Forms Builder 9 D Figure D-10 Set text box properties Place on canvas Text button Figure D-11 Adding text on the canvas 15. Enter the text of LOGIN. 16. Click the Button item on the Canvas Layout Editor’s left-side toolbar and then click the canvas below the text boxes, as shown in Figure D-12. 10 Appendix D Button button Add to canvas Figure D-12 Add a push button on the canvas 17. Double-click the push button placed on the canvas to open the Property Palette, as shown in Figure D-13. Figure D-13 Setting push button properties 18. Set the Name to LOGIN and the Label to Login. Close the Property Palette. Introduction to Application Development with Oracle9i Forms Builder 11 19. The last item we will place on this canvas will allow us to place a message on the screen if the user enters an invalid login. Click the Display Item button on the Canvas Layout Editor’s left-side toolbar and then click the canvas next to the LOGIN text, as shown in Figure D-14. D Display Item button Figure D-14 Adding a display item 20. Double-click the display item placed on the canvas to open the Property Palette and set the properties as follows: Name = LOGIN_REJECT, Maximum Length = 50, Initial Value = Login incorrect, please try again, Database Item = No, and Visible = No. Close the Property Palette. 21. Close the Canvas Layout Editor. 22. Click the Canvases node in the Object Navigator and the Create button to create the second canvas. 23. Right-click the new canvas node and click Property Palette. 24. Set Name = WELCOME and confirm that Window = WINDOW1. Close the Property Palette. 25. Double-click the icon next to the new canvas node to open the Canvas Layout Editor. 26. Add a display item, as shown in Figure D-15. 27. Double-click the display item to open the Property Palette. 28. Set Name = WELCOME and Database Item = No. 29. Close the Property Palette pane. 30. Add a push button to the canvas, as shown in Figure D-16. 12 Appendix D Figure D-15 Adding a display item on the second canvas Figure D-16 Add a push button to the second canvas 31. Double-click the push button to open the Property Palette and set Name = TEST_BUT and Label = Test. 32. Close the Property Palette pane. Introduction to Application Development with Oracle9i Forms Builder 13 Connecting to the Database and Create a Stored Procedure We need to create a stored program unit that will query the BB_SHOPPER table and determine if a valid login has been entered into the login screen. To access and work with any database objects, we will need to connect to the database. To connect to the database: 1. From the main menu in Forms Builder, click File and then Connect. A login dialog box will appear, as shown in Figure D-17. Figure D-17 Login dialog box 2. Enter your Oracle login (such as Scott,Tiger) and a database connection name. The database connection is defined in the TNSNAMES.ORA file.The default is orcl for Personal Oracle9i. Click the Connect button. 3. In the Object Navigator, locate the Database Objects node and expand the node by clicking the plus sign next to the node, as shown in Figure D-18. 4. Locate and expand your schema node, and then click the PL/SQL Stored Program Units node below your schema. 5. Click the Create button to create a new stored program unit. A dialog box will appear requesting the name and type for the program unit, as shown in Figure D-19. D 14 Appendix D Schema node Figure D-18 Expand the Database Objects node Figure D-19 New Program Unit dialog box 6. Type login_sp, click the Procedure option button (if necessary), and then click OK. The PL/SQL Editor pane will appear with a basic statement outline, as shown in Figure D-20. 7. Highlight the code already placed in the editor and press the Delete key to erase it. Introduction to Application Development with Oracle9i Forms Builder 15 D Figure D-20 PL/SQL Editor pane 8. Enter the procedure code shown in Figure D-21. Procedure queries the database to check the login information Figure D-21 Login procedure code 9. Click the Save button in the editor to compile the code. Make corrections if error messages appear. 10. Click the Close button to close the editor. 16 Appendix D Now we have two screens and a stored procedure to verify the login information entered by a user.The last step is to add code to the Login button to fire off the login procedure and change the screens appropriately. If the login is valid, the second screen that welcomes the user should be displayed. However, if the login is invalid, the login incorrect message needs to be displayed on the login screen. Adding Triggers to Application Screens For an application to be useful, it will need to respond to user events such as clicking a button. In Forms Builder, we create triggers that contain PL/SQL code to respond to specific user events. In this application, we will add a trigger on the Login button. To add a trigger: 1. Expand the nodes in the Object Navigator until the Triggers node under the LOGIN button is visible, as shown in Figure D-22. Triggers node Figure D-22 Triggers node 2. Click the Triggers node and then click the Create button. A dialog box as shown in Figure D-23 will appear. This displays all the possible trigger events for a button. Introduction to Application Development with Oracle9i Forms Builder 17 D Figure D-23 Trigger events list 3. Type W to quickly advance to the events that start with the letter W, as shown in Figure D-24. Figure D-24 Button trigger events starting with W 4. Click WHEN-BUTTON-PRESSED, if necessary, and click OK. A blank PL/SQL Editor pane appears, as shown in Figure D-25. Review and enter the trigger code shown in Figure D-26. 18 Appendix D Figure D-25 PL/SQL Editor pane Trigger code calls the stored procedure If login valid, put user’s name in the welcome text and move to the second canvas If login is invalid, display the login incorrect message and remain on the login canvas Trigger added Figure D-26 WHEN-BUTTON-PRESSED trigger code 5. Click the Compile button and make corrections if needed. 6. Close the PL/SQL editor. TESTING THE APPLICATION The last step in our demonstration will be running the application and testing it by entering login values and clicking the Login button.We will want to test both scenarios: a valid and invalid login. Introduction to Application Development with Oracle9i Forms Builder 19 To run and test the application: 1. Click the Run Form button on the Forms Builder toolbar, as identified in Figure D-27. The initial login form will appear in a browser window, as shown in Figure D-28. D Run Form button Figure D-27 Running the application 2. First we need to test an invalid login. Enter charlie and dog, as shown in Figure D-29. After clicking the Login button, the display message should appear noting that this is an incorrect login. Note Notice that the Login incorrect message is not completely visible. You can return to the Layout Editor and increase the size of this item by clicking it and dragging a sizing handle to make it longer. You can also go to the Property Palette of this item and set the Width property to an appropriate value. 20 Appendix D Figure D-28 Initial login form Login incorrect message Figure D-29 Creating an incorrect login Introduction to Application Development with Oracle9i Forms Builder 21 3. Now enter a valid login of charlie and doghouse and click Login. The Welcome canvas should appear, indicating a successful login, as shown in Figure D-30. D Figure D-30 Testing a valid login 4. Close the browser window. 5. In Forms Builder, select File, Disconnect from the main menu. 6. Close Forms Builder. This appendix introduced the process of creating application screens with Oracle Forms Builder. Most importantly, this highlights the role of PL/SQL in providing all the processing logic to support an application.