Week 2 February 1

1. Week 2February 1 PL/SQL Creating a Form

2. Coding Standards • Element names (functions, objects, variables, etc.): 30-character maximum length • Uppercase letters for keywords • Lowercase letters for user-defined elements • 80 characters per line • One command per line • Comments • Header comments: /* comment */ • Inline comments: -- comment • Prefix object names

3. PL/SQLProcessing Language/SQL • Procedural processing language for various Oracle tools: • Forms • Reports • Graphics • Character set • Alphabetic, numeric and special characters • Arithmetic and relational operators • Others (;, ., :=, | |, --, /* */)

4. PL/SQL Block Declares variables and constants (optional). Declaration Section Executable Section Procedural programming Defines the exception handlers invoked for both predefined and user-defined exceptions (optional). Exception Section

5. PL/SQL Block Declare section Executable section Exception section

6. PL/SQLDeclaration Section • Declare variable and constant names and types • Data types • Character (varchar2) • Numeric • Date • Boolean

7. Variable and Constant Declaration • Defines the existence of all variables used in the procedure...declarevariable-namedata-type;constant-name := value; • For example...declareline_count number;product_request varchar2(10);max_count := 20; Data types Constant

8. For Example... declare product_request varchar2(3); product_descrip varchar(30); price real; cursor product is select product_description, product_msrp from products where manufacturer_code = product_request; ... begin; open product; loop fetch product into product_descrip, price;...close product; Query assigned to “product” Order of variables must match the order in the select

9. Executable Section • Types of statements • Assignment • Flow-of-control • SQL • Cursor • All statements must be terminated by a semi-colon (;)

10. Exception Section • Instructs PL/SQL how to handle particular exceptionsexception when exception-name thenPL/SQL statements; when exception-name thenPL/SQL statements;end;

11. Exception Names • Predefined - Defined in Oracle • DUP_VAL_ON_INDEX (duplicate value on a unique index) • INVALID_NUMBER • NO_DATA_FOUND (no rows returned) • TOO_MANY_ROWS (multiple rows returned) • VALUE_ERROR • User-defined - Defined in the declarations as an exception (data type).

12. Oracle Developer • Forms (interactive) • Presenting information and entering data online • Reports (reporting) • Page-oriented display of information • Graphics (charts) • Graphic representation of data

14. Forms Designer • Four major components • Object Navigator • Layout editor • Property palette (sheet) • PL/SQL editor

15. Object Navigator  Forms module with various objects Modules • Modules: • Forms • Menu • Libraries • Built-in Packages • Database Objects

16. Menus Object type Hierarchy Specifies the order in which objects and items are executed Toolbar

17. Window and Canvas Objects Window Canvas-View Canvas-View Four types of canvas-views: Content, Stacked, Horizontal Toolbar, and Vertical Toolbar Interface Item Boilerplate objects (lines, images, boxes, etc.)

18. Window-Canvas-Block-Items Relationship Menu object Items PL/SQL Block Canvas-View Window

19. Layout Editor Tool bar Rulers Tool palette Layout work area

20. Property Palette Set an object’s attributes Properties of the canvas

21. Triggers Trigger

22. PL/SQL Editor Trigger PL/SQL code List of triggers

23. Inheriting Properties Stereos to Go! Enter Stereos to Go! Exit View Report Graph Exit Class Properties

24. Creating a Property Class • Create a Property Class object in the parent module • Add the property and its values • Select the object in the child • Under Subclass Information,specify the property class

25. Create the Property Class in the Parent

26. Add the Properties to the Class Added to the class

27. Inherit the Property Class in the Child  Select the object and its Property Palette  Select Subclass Information  Specify the object or property class name and its form module Property class Parent module

28. Menus Menus Menu items File Menu

29. Menu Module  Menu module  Menu object Menu items Five Simple Steps: Create the menu module Add the menu objects Add the menu items to the menu objects Program the items in PL/SQL Attach the menu module to the form module

30. Why Multiple Forms? • Build more functional units (forms) • Design the form to support a narrow scope of functions • Reduce the complexity of a single form • Reduce the time to develop and implement an application • Enhance maintainability of the application(plug-and-play!) • Promotes reusability or cloning

31. Calling and Called Forms • The calling form module transfers control (calls, opens)to the called form CALL_FORM(form-module-name,display,switch-menu) • Calling form remains presentbehind the called form • Called module assumes the calling module’s menu module  Calling Called

32. Calling and Called Forms • Upon exit of the called form,control returns to the calling formmodule EXIT_FORM  Calling Called

33. Calling a Form Module • Create or select an item (i.e., push button) on the canvas or in the menu module • Program the item in the PL/SQL Editor • Select a trigger that will activate the procedure • Enter the CALL_FORM statement • Compile the code • Test the form module

34. In the PL/SQL Editor Causes Oracle Forms to keep the default menu application of the calling form active for the called form. (REPLACE) Form module name Causes Oracle Forms to clear the calling form from the screen before drawing the called form. HIDE is the default parameter. (NO_HIDE)

35. NEW_FORM • NEW_FORM closes the calling form Greeting Screen NEW_FORM EXIT_FORM Main Menu Menu CALL_FORM EXIT_FORM Query