1 / 48

Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables

Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables. In this chapter you will: Learn how to use triggers to add your own programming logic to a form Understand the various types of triggers Create and use SmartTriggers Use the PL/SQL Editor

kendra
Download Presentation

Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables Oracle9i Developer: Developing Web Applications with Forms Builder

  2. In this chapter you will: • Learn how to use triggers to add your own programming logic to a form • Understand the various types of triggers • Create and use SmartTriggers • Use the PL/SQL Editor • Use built-in subprograms when appropriate • Use miscellaneous PL/SQL constructs and built-in tools • Trap form errors • Create your own stored procedures • Use form parameters, global variables, and system variables • Modify the action of function keys Oracle9i Developer: Developing Web Applications with Forms Builder

  3. Triggers and Trigger Types • Oracle forms are event driven applications. This means the programs are executed as a result of an event such as pressing a button. • Events are called triggering events or triggers and can have PL/SQL code associated with them. • Triggers are also form objects that can be attached to the form, the data block, or the data block item. • There are triggers for every event that can happen to the form. Oracle9i Developer: Developing Web Applications with Forms Builder

  4. Triggers and Trigger Types Oracle9i Developer: Developing Web Applications with Forms Builder

  5. Triggers and Trigger Types • Triggers contain PL/SQL code and can also execute: • Stored procedures • Oracle-developed built-in subprograms • SELECT statements • DML statements • DDL statements Oracle9i Developer: Developing Web Applications with Forms Builder

  6. Triggers and Trigger Types • There are five types of triggers. The trigger name is preceded by the type designation. The types are: • Key triggers are fired as a result of a key being pressed. • Pre triggers are fired before an event occurs. • On triggers replace the action that would be performed in an event. • Post triggers are fired after an event occurs. • When triggers are fired during the event and supplement the event action. Oracle9i Developer: Developing Web Applications with Forms Builder

  7. Triggers and Trigger Types • Triggers have a specific scope. • Form level triggers are fired for events that happen anywhere on the form and have the broadest scope. • Data block triggers are fired for events that happen on the data block. • Data block item triggers are fired for events that happen to a data block item and have the smallest scope. • The same named trigger can be assigned to the form, the data block, and the data block simultaneously. However, they will fire at different times. Oracle9i Developer: Developing Web Applications with Forms Builder

  8. Triggers and Trigger Types Oracle9i Developer: Developing Web Applications with Forms Builder

  9. Triggers and Trigger Types Oracle9i Developer: Developing Web Applications with Forms Builder

  10. Triggers and Trigger Types • Triggers have three characteristics: • Legal commands - Some triggers are restricted in the type of SQL statements and built-ins that can be executed.Triggers that are fired as a result of navigation generally have some type of restriction. • Enter Query mode - Some triggers cannot be fired in the Enter Query mode. • On-Failure - Triggers can differ in the action that will occur when the triggers fails. In some cases nothing will happen. In other cases the trigger actions will be rolled back. Oracle9i Developer: Developing Web Applications with Forms Builder

  11. Triggers and Trigger Types Oracle9i Developer: Developing Web Applications with Forms Builder

  12. Creating Triggers and Using SmartTriggers • Before creating a trigger, identify the needed scope and the associated form object. • Select the Triggers node under the object and press the Create tool. • The Triggers dialog box will open allowing the developer to select the appropriate trigger. • The PL/SQL editor will open after the trigger is selected. Oracle9i Developer: Developing Web Applications with Forms Builder

  13. Creating Triggers and Using SmartTriggers Oracle9i Developer: Developing Web Applications with Forms Builder

  14. Creating Triggers and Using SmartTriggers • Right-clicking the mouse on the Object Navigator will open a popup menu. • One of the options on the menu is SmartTriggers. SmartTriggers is a submenu option that displays triggers that are applicable for the selected Triggers node. • Some triggers can be assigned to any of the nodes. Some triggers can only be assigned to specific nodes. SmartTriggers options will be different dependent upon the selected Triggers node. Oracle9i Developer: Developing Web Applications with Forms Builder

  15. Creating Triggers and Using SmartTriggers Oracle9i Developer: Developing Web Applications with Forms Builder

  16. Using the PL/SQL Editor • The PL/SQL editor also called the Program Unit editor is used to create and maintain PL/SQL code. • The Source Code pane appears in the upper half of the editor. It is a text area used to enter and modify PL/SQL. • The Error Code pane appears in the lower half of the editor. It displays syntax errors identified during the compilation process. This pane only appears after compilation occurs. • Double-clicking an error message will move the input focus in the Source Code pane to the error location. Oracle9i Developer: Developing Web Applications with Forms Builder

  17. Using the PL/SQL Editor Oracle9i Developer: Developing Web Applications with Forms Builder

  18. Using the PL/SQL Editor • The PL/SQL Editor has tools to: • Indent lines • Undo work • Size the panes • Compile the script • It is highly recommended that the script be compiled using the Compile PL/SQL Code tool before closing the editor. Oracle9i Developer: Developing Web Applications with Forms Builder

  19. Using the PL/SQL Editor • A typical trigger PL/SQL code block has three sections: • The Declaration section that begins with the Declare keyword. • The Executable section that begins with the Begin keyword • The Exception section that begins with the Exceptions keyword. • The Begin and End keywords are implicit and are only needed if the script has a Declaration or Exception section. Oracle9i Developer: Developing Web Applications with Forms Builder

  20. Using the PL/SQL Editor Declare cursor a is select dname from dept where dept.deptno = :emp.deptno; Begin open a; -- Opens the cursor or executes a Select statement fetch a into :emp.department_name; --Places the retrieved value into the form item close a; -- Closes the cursor Exception When others then message (‘A database error has occurred’); end; Oracle9i Developer: Developing Web Applications with Forms Builder

  21. Miscellaneous PL/SQL Constructs and Built-in Tools • Built-ins are Oracle developed procedures and functions that perform tasks in Forms Builder. • Built-ins perform tasks such as placing a data block into the Enter Query mode, sending a message to the status line, or setting a property at runtime. • The Syntax Palette is available to help identify the PL/SQL construct or built-in used in the trigger. Oracle9i Developer: Developing Web Applications with Forms Builder

  22. Miscellaneous PL/SQL Constructs and Built-in Tools Oracle9i Developer: Developing Web Applications with Forms Builder

  23. Miscellaneous PL/SQL Constructs and Built-in Tools • Built-ins have two restrictions: • Enter Query mode - Some built-ins cannot be executed when the data block is in the Enter Query mode. The Next_block built-in is an example. • Restricted/Unrestricted - Some built-ins cannot be executed in a trigger that is fired as a result of navigation. These built-ins are Restricted. Unrestricted built-ins can be fired in any trigger. Find_canvas is an example of an unrestricted built-in. Oracle9i Developer: Developing Web Applications with Forms Builder

  24. Miscellaneous PL/SQL Constructs and Built-in Tools • Built-ins are often overloaded. • Overloaded means multiple built-ins exist with the same name. The built-ins are differentiated by the built-in’s parameter list. • Overloaded built-ins allow developers to call the same basic built-in using different parameter values. Oracle9i Developer: Developing Web Applications with Forms Builder

  25. Miscellaneous PL/SQL Constructs and Built-in Tools Oracle9i Developer: Developing Web Applications with Forms Builder

  26. Miscellaneous PL/SQL Constructs and Built-in Tools Oracle9i Developer: Developing Web Applications with Forms Builder

  27. Miscellaneous PL/SQL Constructs and Built-in Tools Oracle9i Developer: Developing Web Applications with Forms Builder

  28. Trapping Errors and Causing Exceptions • PL/SQL code blocks that do not have exception handlers will terminate abnormally when an error occurs. • Unhandled exceptions cause the developer to lose control of the application and it gives it an unprofessional look. • It is recommended that all PL/SQL code blocks used in a form have exception handlers. Oracle9i Developer: Developing Web Applications with Forms Builder

  29. Trapping Errors and Causing Exceptions Oracle9i Developer: Developing Web Applications with Forms Builder

  30. Trapping Errors and Causing Exceptions • A developer can terminate a trigger using the Raise Form_trigger_failure statement. • Triggers can also be terminated using the Raise keyword and user defined exceptions. Oracle9i Developer: Developing Web Applications with Forms Builder

  31. Trapping Errors and Causing Exceptions Oracle9i Developer: Developing Web Applications with Forms Builder

  32. Trapping Errors and Causing Exceptions Oracle9i Developer: Developing Web Applications with Forms Builder

  33. Creating Your Own Stored Procedures or Program Units • Forms Builder allows the developer to create PL/SQL objects (procedures, functions, packages) from the IDE. These objects are: • Stored procedures that reside in the database • Program units that reside in the form • The difference between the two is scope. A program unit can only be used in a single form. A stored procedure can be used in many forms and other objects that can access the database. Oracle9i Developer: Developing Web Applications with Forms Builder

  34. Creating Your Own Stored Procedures or Program Units • Program units are very useful when a form employs the same code script in multiple locations. • It is recommended that the code be written in one place and called from multiple locations. • To create a program unit, select the Program Units node on the Object Navigator and press the Create tool. • A New Program Unit dialog box will appear allowing you to select the type of object. Pressing the OK button will launch the PL/SQL Editor. Oracle9i Developer: Developing Web Applications with Forms Builder

  35. Creating Your Own Stored Procedures or Program Units Oracle9i Developer: Developing Web Applications with Forms Builder

  36. Creating Your Own Stored Procedures or Program Units • Stored procedures are created in the same manner except that they are created under the Database Objects node. Oracle9i Developer: Developing Web Applications with Forms Builder

  37. Using Form Parameters, Global Variables, and System Variables • Forms have three different types of variables that hold values: • Data block items • Parameters • Global variables • Parameters are form objects that have properties for the data type, length, and initial value. • Parameters are best used to receive values from a source outside the form Oracle9i Developer: Developing Web Applications with Forms Builder

  38. Using Form Parameters, Global Variables, and System Variables • Parameters are the only tool that can accept values from the operating system when the session is launched. They can be used to accept user id’s, passwords, and search values. • Parameters require a parameter list to be created in order to pass the parameter to another form. Oracle9i Developer: Developing Web Applications with Forms Builder

  39. Using Form Parameters, Global Variables, and System Variables • Global variables are in an area of memory that can retain values. • Global variables are not form objects and receive the data type designation based on the first assigned value. Oracle9i Developer: Developing Web Applications with Forms Builder

  40. Using Form Parameters, Global Variables, and System Variables • Global variables are always created in a trigger. • Any application in the session can access a global variable. • Global variables are eliminated with the Erase built-in. Oracle9i Developer: Developing Web Applications with Forms Builder

  41. Using Form Parameters, Global Variables, and System Variables • Another type of form variable is a system variable. They contain values that describe the state of a form characteristic or control a form characteristic. • System variables can be used to answer questions about states such as: • Is the current record the last record in the data block? • Is the current record the first record in the data block? • Has the data block been modified? • What is the name of the current data block item? • What is the name of the current data block? Oracle9i Developer: Developing Web Applications with Forms Builder

  42. Using Form Parameters, Global Variables, and System Variables Oracle9i Developer: Developing Web Applications with Forms Builder

  43. Using Form Parameters, Global Variables, and System Variables • Some system variables control the behavior of a form. • The Message_level system variable is representative of a variable that controls a form feature. It controls the messages that are issued. • All non-fatal form messages have a value from 0 - 25. The Message_level variable suppresses messages below the variable value. Oracle9i Developer: Developing Web Applications with Forms Builder

  44. Using Form Parameters, Global Variables, and System Variables • System variables are always preceded by the “system” qualifier (:system.message_level). • The colon (:) is used because PL/SQL requires variables declared outside a code block to be qualified with the colon. Oracle9i Developer: Developing Web Applications with Forms Builder

  45. Modifying Function Key Functionality • All Oracle Forms applications have function keys defined. • Function keys perform tasks such as placing the form into the Enter Query mode or committing changes. • The default behavior of the function key can be overridden using Key triggers. • Key triggers replace the default functionality of the key. • If the overridden key is to retain its default functionality, a built-in causing the action must be entered into the trigger. Oracle9i Developer: Developing Web Applications with Forms Builder

  46. Modifying Function Key Functionality • An example Key trigger is a Key-Entqry trigger used to move the insertion point to the master block whenever the key is used to place the form in the Enter Query mode. • A Key-Others trigger overrides all default function keys. • A Key trigger must be added to the form to re-enable a function key after the Key-Others trigger has been used. • The Do_key built-in simulates a function key and is useful when a form has function keys, toolbar tools, and menu options that perform the same tasks. Oracle9i Developer: Developing Web Applications with Forms Builder

  47. Where You Are and Where You’re Going • You have seen: • All of the major Forms Builder tools: the Object Navigator, the Property Palette, the Layout Editor, and the PL/SQL editor • How to add your own custom programming to the form using triggers • Different types of triggers and built-ins • Restrictions of triggers and built-ins • How to create stored procedures and program units • When and how to use global variables, parameters, and system variables • How to redefine function keys Oracle9i Developer: Developing Web Applications with Forms Builder

  48. Where You Are and Where You’re Going • In the next chapter you will see: • How to change a text item into a different item type such as a radio button, check box, display item, or a number of other item types. Oracle9i Developer: Developing Web Applications with Forms Builder

More Related