slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables PowerPoint Presentation
Download Presentation
Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables

Loading in 2 Seconds...

play fullscreen
1 / 48

Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables - PowerPoint PPT Presentation


  • 62 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Chapter 6 Using Triggers, the PL/SQL Editor, Syntax Palette, and Form Variables' - kendra


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Chapter 6

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

Oracle9i Developer: Developing Web Applications with Forms Builder

slide2

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

slide3

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

slide4

Triggers and Trigger Types

Oracle9i Developer: Developing Web Applications with Forms Builder

slide5

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

slide6

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

slide7

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

slide8

Triggers and Trigger Types

Oracle9i Developer: Developing Web Applications with Forms Builder

slide9

Triggers and Trigger Types

Oracle9i Developer: Developing Web Applications with Forms Builder

slide10

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

slide11

Triggers and Trigger Types

Oracle9i Developer: Developing Web Applications with Forms Builder

slide12

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

slide13

Creating Triggers and Using SmartTriggers

Oracle9i Developer: Developing Web Applications with Forms Builder

slide14

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

slide15

Creating Triggers and Using SmartTriggers

Oracle9i Developer: Developing Web Applications with Forms Builder

slide16

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

slide17

Using the PL/SQL Editor

Oracle9i Developer: Developing Web Applications with Forms Builder

slide18

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

slide19

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

slide20

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

slide21

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

slide22

Miscellaneous PL/SQL Constructs and Built-in Tools

Oracle9i Developer: Developing Web Applications with Forms Builder

slide23

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

slide24

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

slide25

Miscellaneous PL/SQL Constructs and Built-in Tools

Oracle9i Developer: Developing Web Applications with Forms Builder

slide26

Miscellaneous PL/SQL Constructs and Built-in Tools

Oracle9i Developer: Developing Web Applications with Forms Builder

slide27

Miscellaneous PL/SQL Constructs and Built-in Tools

Oracle9i Developer: Developing Web Applications with Forms Builder

slide28

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

slide29

Trapping Errors and Causing Exceptions

Oracle9i Developer: Developing Web Applications with Forms Builder

slide30

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

slide31

Trapping Errors and Causing Exceptions

Oracle9i Developer: Developing Web Applications with Forms Builder

slide32

Trapping Errors and Causing Exceptions

Oracle9i Developer: Developing Web Applications with Forms Builder

slide33

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

slide34

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

slide35

Creating Your Own Stored Procedures or Program Units

Oracle9i Developer: Developing Web Applications with Forms Builder

slide36

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

slide37

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

slide38

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

slide39

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

slide40

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

slide41

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

slide42

Using Form Parameters, Global Variables, and System Variables

Oracle9i Developer: Developing Web Applications with Forms Builder

slide43

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

slide44

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

slide45

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

slide46

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

slide47

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

slide48

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