Oracle Forms Tutorial (www.aboutoracleapps.com)

Forms 5.0 Form Builder Data Blocks, Frames, Text Items and LOV Input and Non-input Items Widows and Canvases Triggers and Functions Flexible code Sharing and Code Multi Form Applications !

Form Builder and Creation of Data Blocks Form Builder Create a form module Create a data block Modify a data block using the data block Wizard Modify the layout using the layout wizard Creating a Master-Detail Form ! !

Form Builder Environment Form Builder executables Components of Form Builder Objects in a Form Module !

Form Builder Key Features With Form Builder you can : Insert, update, delete, query data Present data as text, image etc Control forms across several windows Access graphics and OLE2 applications Use integrated menus Send data to Report Builder ! @

Form Builder Executables Forms Builder Forms Runtime Forms Compiler Definitions Runfiles ! @ @

Form Builder Module MENUS FORMS LIBRARIES Developer /2000 Components Database ! @

Blocks, Items and Canvases Canvas 1 Canvas 2 Items Block A Block B Items ! @

Navigation in a Block Canvas1 Canvas2 Items Items ! @

Data Blocks A B C D X Y Z A B C A B C Master Data Block Detail Data Block Table Table 1 2 3 4 5 6 1. Base Table source 3. Trigger Access 5. Multirecord data block 2. Single-record data block 4. Nonbase table source 6. Record !

Forms and Data Blocks Block 1 Block 2 Block 4 Block 3 Block 1 Block 1 Block 2 Block 1 Form A Form B Form C Open Form Open Form Multiple Form Modules Single Form Module ! @

Creating a New Form Module Set object properties Create a New Module Create datablock items Fine- tune layout Add code Test for module !

Creating a New Module Click on “Forms” Click on the plus sign of the tool palette “Module1” will appear A new module is now ready !

Creating Data Block Items Click on the Module1(last created ) and click the plus sign of the tool palette A window showing the options would appear Select the option of using “wizard” Click “next” on every screen till you reach the “layout editor”(in between you will be asked to enter the table which you can do with the “browse “ button. Proceed further till you reach “layout editor” !

Fine tune layout By now you can see the items on the layout editor screen , which are nothing but the database columns represented on the screen Try to resize the items single click on one of the item and then keeping the mouse on the item’s boundary expand/contract it By going at the tool palette you can fill up the colours also in the items !

Set object properties To alter the properties of any Module/DataBlock/Item ,click on any one of them and then go to the “Tool “ option of the menu Adjust the property sheet as you desire !

Add Code To write a pl/sql code on any Module/DataBlock/Item , go to the “Program” of the menu structure and click it Write the code and click “compile” Click “close”(do not choose the delete option) !

Test for Module Now go to the object navigator and click The runtime screen would appear You can enter the data into the database tables (through the items being displayed on the screen) Open the SQL*PLUS to verify your data entry into the data base tables !

Saving a Form Module Save form module: File --> Save Save Icon Enter Filename Save to one of the following: File System Database ! @

Data Block Functionality Once you create a data block with the wizards, Form Builder automatically creates: Form Module with database functionality Query,insert,update,delete Frame Object Items in the data block (TRIGGERS) Prompt of each item !

Compiling the Form Module Compile Explicitly File-->Administration -->Compile file Form Compiler Component Command line interface Compile implicitly Tools -->Preferences Set the build before running preference ! @

Module Types .FMB .FMX .FMT .MMB .MMX .MMT .PLL .PLX .PLD FORM MODULE MENU MODULE PL/SQL LIBRARY ! @

Form Block Relationships MASTER MASTER MASTER DETAIL DETAIL DETAIL DETAIL DETAIL !

Relation Object New relation object in Object Navigator under master data block node Default name MasterDataBlock_DetailDataBlock Triggers and Program Units generated Automatically ! @

Running A Master-Detail Form Module Automatic Block linking for Querying Inserting Default Deletion rules Cannot delete Master record if detail record exists !

Working with Data Blocks and Frames Identify the components of the property palette Manipulate properties through the property palette. Control the behaviour and appearance of data blocks control frame properties create blocks that do not directly correspond to the database Delete data blocks and their components !

Modifying the Appearance and Behaviour of Data Blocks Reentrant Wizards Layout editor Data Block property palette Frame property palette !

Displaying the Property Palette Toolbar Expand/Collapse Property Name Find field Search Backward Search Forward Property Value !

Visual Attributes Visual attributes : are font,colour,and pattern properties can be set for form and menu objects A visual attribute is a form object with font,colour,and pattern properties Set visual attribute group property to the visual attribute object !

Data Block Property groups Each data block has several properties which may be divided into the following groups: General Navigation Records Database Advanced Database Scrollbar Font and Colour Character Mode International !

Navigation Properties BOOKS Items Previous navigation Data block Same Record Next Record !

Database Properties Type of Block - Data or Control Block Query, insert, update, and delete operations on the data block Data Block’s Data source Query Search criteria and default sort order Maximum Query time Maximum Number of records fetched !

Vertical Record Orientation Number of Records Displayed Current record Item !

Horizontal Record Orientation Items Number of records displayed Current Record !

Scrollbar Properties Item Scroll bar X/Y position Scroll bar height Scroll bar width !

Frame Properties Distance between Records Form Layout Style Member Tabular Layout Style Member Details !

Creating a Control Block Click Data Blocks node Click Create icon or Select Navigator --> Create Select the ‘Build a new data block manually’ option in the New Data Block dialog !

Deleting a Data block Select data block for deletion Click Delete icon or Navigator --> Delete Click Yes in alert box !

Items ..an important attribute Basically there are two types of items INPUT ITEMS (text items,list items,push buttons) NON INPUT ITEMS(LOV’s , display items etc.) Note : The text items are the most often used for the data entry !

Text Items Default item type Interface object for Querying, Inserting, Updating, Deleting Behaviour defined in the property palette !

Modifying the Appearance of a Text Item Font and colour properties: Visual attributes Form name, size, weight, style, colour, pattern Distance between two records No. of items displayed !

Prompts Text label that is associated with an item Several properties are available to arrange and manage prompts Use prompt properties of an item to change the prompt’s display style, justification, alignment, colour and font !

Format Masks Standard SQL formats Dates FXDD-MON-YY Numbers L099G99D99 Non-standard formats Use double quotes for embedded characters “(“099”)”099”-”0999 Allow for the format mask’s embedded characters when defining the width property ! @

Navigational Behaviour of Text Items Established by: Order of entries in Object Navigator Controlled by: keyboard navigable previous navigation item next navigation item !

Database Properties Item’s data source -- Base Table item or control item Query, Insert and Update operations on an item Maximum query length Query Case !

Functional Properties Enabled Justification Multi-line Wrap style Case restriction Conceal data Keep cursor position Automatic skip Pop-up menu !

Conceal Data Property LOG SCREEN Enter Userid : Enter Password : scott ***** !

Keyboard Navigable and Enabled Properties Set both properties to allow or disallow navigation and interaction with text item When enabled is set to YES , keyboard navigable can be set to YES or NO When enabled is set to NO , the icon is always non-navigable !

Hint and ToolTip Properties Tooltip Hint ! @

Creating Additional Input Items Identify the item types that allow input Create a check box create a list item create a radio group !

Other Input Items Item types that accept user input.These Include: Check Boxes List Items Radio Groups Can Allow: Insert, Update, Delete and Query !

Check Boxes Two-State interface Object Checked Unchecked Not Limited to two values ! @

Creating a Check Box Convert an existing item to a check box item Use check Box tool in the layout editor Use create icon in the object navigator Check box Properties: Data Type Label Access Key Value when checked Value when unchecked Mapping of other values Mouse Navigate !

List Items Set of mutually exclusive choices,each representing a different value Three list styles available Space_saving alternative to a radio group Smaller scale alternative to an LOV !

List Item Specific Properties Elements in List List elements List item value List style Mapping of other values Mouse Navigate ! @

List Styles Excellent Excellent Excellent Good Poor Poplist Tlist Combo Box !

Radio Groups Set of mutually exclusive radio buttons,each representing a value Use: To display two or more static choices As an alternative for a list item As an alternative for check box ! @

Creating a Radio Group Convert an existing Item Create a new radio group item in the layout editor Use the create icon in the object navigator Radio group and radio button properties Radio Group Datatype Mapping of other values Mouse Navigate Radio Button Access Key Label Radio Button Value !

Creating Non-Input Items Identify Item types that do not allow input Create a display item Create an image item Create a button Create a calculated Field !

Non-Input Items Item types that do not accept direct user input include: Display Items Image Items Calculated items Buttons ! @

Display Items Display items are similar to text items Display items cannot: Be edited Be queried Be navigated to Accept user input Display Items can: Display date Conserve resources ! @

Image Items Interface Control Used to display bitmapped images From file system-supported file type From database-LONG RAW column !

Image Specific Item Properties Image Format Image Depth Compression Quality Display Quality Show palette Sizing Style Show Horizontal Scrollbar Show Vertical Scrollbar !

Buttons Interface control Cannot display/represent data Use to initiate an action Display as: Text Button Iconic !

Buttons Use Buttons to: Move Input Focus Display an LOV Invoke an editor Invoke another window Commit data Issue a query Perform calculations !

Button Specific Item Properties Label Mouse Navigable Default Button Iconic Icon filename ToolTip ToolTip visual attribute group !

Calculated Fields Accept item values that are based on calculation Are read only Can be expressed as: Formula Summary !

Calculation Modes Formula Calculated item value is the result of a horizontal calculation Involves bind variables Summary Calculated item’s value is a vertical calculation Summary performed on values of a single item over all rows in a block !

Item Properties Specific to the Calculated Field Formula Calculation Mode Formula Summary: Calculation Mode Summary Function (AVG, COUNT,MAX, MIN, STDDEV, SUM, VARIANCE) Summarized block Summarized Item ! @

Items Based on Formula Rules: Must not invoke restricted built-ins Cannot execute any DML statements . Do not terminate PL/SQL expressions with a semicolon Do not enter complete PL/SQL statement in assignment expressions Book id Book name Price Qty Total Cat 1 2 3 4 200 120 50 25 5 4 9 3 1000 480 450 75 Books NVL((:s_books.price * :s_books.qty),0) Formula Item !

Items Based on Summary Rules for summary items Summary Item must reside in: The same block as the summarized item ,or A control block with single record property set to yes Summarized item must reside in: A data block with query all records property or pre-compute summaries property set to YES , or A control block Data type of summary item must be number unless using MAX or MIN !

Items Based on Summary Book id Book name Price No of copies Total 1 2 3 4 200 120 50 25 5 4 9 3 1000 480 450 75 Books Summarised Item Total Price 2,005 Summary Item !

Creating LOVs and Editors Describe LOVs and Editors Design, create, and associate LOVs with text items in a form module Create editors and associate them with text items in a form module !

LOVs and Editors LOVs List of Values for text items Dynamic list or static list Independent of single-text items Flexible and efficient Editors Override default editor Used for special requirements like larger editing window, position, colour, and title System editor available as an option ! @

LOVs Book Name Author Name select bookname, authorname from books ; Books Table Books record group Books LOV !

LOV Queries Avoid very large queries -- use restrictions Use column in LOV to validate user input -- place this column first in SELECT list Define return items later, or use optional INTO clause Use optional WHERE , GROUP BY, and ORDER BY clauses ! @

LOV Properties Column mapping (X,Y) Height Width Return Items Automatic Display Automatic Skip LOV Automatic column width Filter before display Automatic select ! @

LOV Column Mapping Book Name Author Name select bookname, book_id,authorname from books; Books table Books record group Books LOV Book id Book id Tc0001 St0002 Hidden columns Book Name Author Name ! @

Editors Defining an Editor - A user defined or default Form Builder editor associated with text items. Associate one of three types of editors with a text item. Set text items editor property to one of the following: Null (default form builder editor) editor_name (customized editor) SYSTEM_EDITOR (external editor) !

Creating Windows and Canvases Describe Windows and content canvases Describe the relationship between Windows and content canvases Identify window and content canvas properties Display a form Module in multiple windows Display a form module on multiple layouts !

Creating Windows and Canvases Describe the different types of canvases and their relationships to each other Identify the appropriate canvas type for different scenarios Create an overlay effect using stacked canvases Create a toolbar Create a tabbed interface !

Windows and Canvases Window Container for form builder visual objects Canvas Surface on which you ‘paint’ visual objects To see a canvas and its objects display the canvas in a window !

Window, Canvas and Viewport Document Window MDI Parent Window Canvas ! @

Content Canvas ‘Base’ canvas View occupies entire window Default Canvas type Each window should have at least one content canvas. !

Windows WINDOW1 Created by default with each new form module Modeless You can delete,rename,or change its attributes Use additional Windows to: Display two or more content canvases at once Switch between canvases without replacing the initial one Two type of windows: Modal and Modeless !

Window Properties MDI Parent Window X/Y Position Title Document Window Dialog Window Vertical Scrollbar Horizontal Scrollbar !

GUI Hints Recommendations about window appearance and functionality If the Window Manager supports a specific GUI Hint and its property is set to YES , it will be used Functional properties for GUI Hints Close allowed Move and Resize allowed Maximize and Minimize allowed Inherit Menu !

Creating a Content Canvas Implicitly Layout Wizard Layout Editor Explicitly Create icon in Object Navigator !

Content Canvas Properties Viewport X/Y Position on Canvas Canvas Viewport Height Width !

Canvas Types Content Canvas Content Canvas Stacked Canvas Horizontal Toolbar Tab Tab Canvas !

Stacked Canvas Displayed on top of a content canvas Shares a window with a content canvas Size: Usually Smaller than the content canvas in the same window. Determined by viewport size Created in: Object navigator Layout editor ! @

Stacked Canvas Properties Content Canvas Stacked Canvas Viewport X/Y position Viewport Height Viewport Width !

Toolbars Special type of canvas for tool items Three types: Vertical toolbar Horizontal toolbar MDI toolbar Provide: Standard look and feel Alternative menu or function key operation !

Toolbar Related Properties Canvas properties Canvas type Window Width Height Window properties Horizontal toolbar canvas Vertical Toolbar canvas Form Module Properties Form Horizontal toolbar canvas Form Vertical toolbar canvas !

Tab canvas Enables you to organize and display related information on separate tabs Consists of one or more tab pages Provides easy access to data Created in Object navigator Layout editor Items on a tab canvas Place Items on a tab page for user interaction Set item’s canvas,tab page properties !

Tab Canvas Related Properties Viewport X/Y position Tab attachment edge Viewport Height Viewport Width !

Introduction to Triggers Define Triggers Identify the different Trigger types Plan the type and scope of triggers in a form Describe the properties that affect the behaviour of a trigger Write trigger code Explain the use of built-in subprograms in D2K applications Describe the WHEN_BUTTON_PRESSED trigger !

Form Builder Triggers PL/SQL PL/SQL PL/SQL Queries Validation Navigation Interaction Internal Events Errors/Messages Others Event Fire Trigger Types !

Scope of a Trigger Scope Code Type Levels Form Block Item Pre- Post- When- On- Key- Statements PL/SQL User subprograms Built-in subprograms !

Scope of a Trigger Item Level (ON-MESSAGE) Block Level (ON-MESSAGE) Form Level (ON-MESSAGE) ! @

Scope of a Trigger When the cursor is on the Book_name item, a message fires the On-Message trigger of the Book_name item When the cursor is elsewhere in the Books block, a message causes the block-level On-Message trigger to fire because it is outside the scope of the item level trigger When the cursor is outside the Books block, a message causes the form-level On-Message trigger to fire, because the cursor is outside the scope of the other two On-Message triggers !

Trigger Properties General Functional Help !

Trigger PL/SQL Blocks BEGIN END ; !

Variables in Form Builder PL/SQL variables must be declared in a trigger or defined in a package Form Builder variables not formally declared in PL/SQL need a colon prefix in reference !

Form Builder Variables Items: For presentation and user interaction :block_name.item_name Global variables: session wide character variable :GLOBAL.variable_name System variables: form status and control :SYSTEM. variable_name Parameter variables: passing values in and out of a module :PARAMETER.name ! @

Form Builder Built-in Sub-programs Built-ins belong to either the standard extensions package no prefix required other Form Builder packages prefix required Limits of Use Unrestricted built-ins any trigger or sub-program Restricted built-ins only allowed in certain triggers and sub-programs called from such triggers Consult help system !

Useful Built-ins EDIT_TEXTITEM ENTER_QUERY, EXECUTE_QUERY EXIT_FORM GO_BLOCK, GO_ITEM GET_ITEM_PROPERTY, SET_ITEM_PROPERTY MESSAGE SHOW_ALERT, SHOW_EDITOR, SHOW_LOV SHOW_VIEW, HIDE_VIEW ! @

When-Button-Pressed trigger Fires when the operator clicks a button Allows restricted and unrestricted built-ins Used to provide convenient navigation, to display LOVs and many other frequently used functions Example: Show Books GO_BLOCK(‘Books’); EXECUTE_QUERY; !

Functionality used in Triggers Description of the different types of Triggers with some examples Different styles of Messages and Alerts and their built-ins Runtime errors and built-ins A general overview on Query Array Processing Enter Query Mode Use of SYSTEM variables Validation at different levels Navigation !

Item Interaction Triggers When-Button-Pressed When-Checkbox-Changed When-Radio-Changed When-Image-Pressed When-Image-Activated When-List-Changed When-List-Activated !

Coding Item Interaction Triggers Valid commands SELECT items Standard PL/SQL constructs All Built-in Subprograms Use When-Validate-”Object” to trap the operator during validation !

Example of When-Image Activated BEGIN READ_IMAGE_FILE ('C:\orant\tools\doc20\guide21\gifs\bookicon.gif’ , 'GIF’, 'BEGIN.IMAGE’ ); END; !

Runform Messages and Alerts System Alerts Application Messages Informative Error Working Application !

Detecting Runtime Errors FORM_SUCCESS TRUE:Action successful FALSE:Error/Fatal error occurred FORM_FAILURE TRUE:A non-fatal error occurred FALSE:No error/No Fatal error FORM_FATAL TRUE:A fatal error occurred FALSE:No error/No Fatal error !

Errors and Built-ins Built-in failure does NOT cause an exception Test built-in success with FORM_SUCCESS function IF FORM_SUCCESS THEN …. What went wrong? ERROR_CODE,ERROR_TEXT,ERROR_TYPE MESSAGE_CODE,MESSAGE_TEXT, MESSAGE_TYPE !

Message Severity Levels Severity Level Description 0 All Messages 5 Reaffirms an obvious condition 10 Procedural mistake by user 15 Form not designed for action attempted by user 20 Cannot continue due to trigger problem 25 Condition resulting in form performing incorrectly >25 Messages that cannot be suppressed System.MESSAGE_LEVEL := severity level System.SUPPRESS_WORKING := ‘TRUE’ ! @

Error Triggers On-error Fires when a system error message is issued Used to trap form Builder and Oracle Server errors On-Message Fires when an informative system message is issued Used to suppress or customize specific message Built-in functions : MESSAGE_CODE MESSAGE_TEXT MESSAGE_TYPE !

Alerts Title Message Alert Style - (1) Caution Button1 Button2 Alert Styles (2) Stop (3) Informative messages !

Built-ins with Alerts SET_ALERT_PROPERTY SET_ALERT_BUTTON_PROPERTY SHOW_ALERT !

Query Triggers Fire PRE-QUERY trigger Fetch A Row Into A New record Mark Record As valid Fire POST-QUERY trigger Validate any Record Changes Perform Query Construct SELECT….. Abort query on failure Flush record on failure Query Processing !

WHERE Clause Three sources for the WHERE Clause Where clause block Property Example Record Query Where dialog WHERE Clauses are combined by the AND operator !

ORDER Clause Two sources of ORDER Clause ORDER BY clause property Query Where dialog Second source for ORDER BY clause overrides the first one !

Pre_Query, Post_Query Triggers Pre_Query Define at block level Fires once, before query is performed Post_Query Fires for each fetched record (except during array processing) Use to populate non-database items and calculate statistics !

Using SELECT Statements in Triggers Form Builder variables preceded by colon Query must return one row for success Code exception handlers INTO clause mandatory,with a variable for each selected column or expression ORDER BY not relevant ! @

Coding For ENTER-QUERY Mode Some triggers may fire in Enter-Query mode Set to Fire in ENTER-QUERY Mode Test mode during execution with :SYSTEM.MODE NORMAL ENTER-QUERY QUERY Some built-ins are illegal Consult online Help You cannot navigate to another record in current form !

Triggers firing in Enter-Query Mode Key On-Error On-Message When triggers, except When-Database-Record When-Image-Activated When-New-Block-Instance When-New-Form-Instance When-Create-Record When-Remove-Record When-Validate-Record When-Validate-Item !

Overriding Default Query Processing On-Fetch continues to fire until: It fires without executing CREATE_QUERIED_RECORD The query is closed by the user or by ABORT_QUERY It raises FORM_TRIGGER_FAILURE On-Select replaces open cursor,parse and execute phases !

Obtaining Query Information at Runtime SYSTEM.MODE to obtain the form mode SYSTEM.LAST_QUERY to obtain the text of the base table SELECT statement that was last executed by Form Builder Contains bind variables (ORD_ID = :1) before SELECT_RECORDS Contains actual values (ORD_ID=102)after SELECT_RECORDS !

Obtaining Query Information at Runtime GET_BLOCK_PROPERTY SET_BLOCK_PROPERTY Following block properties may be set for obtaining query information Get and Set DEFAULT_WHERE ORDER_BY QUERY_ALLOWED QUERY_HITS Get only QUERY_OPTIONS RECORD_TO_FETCH !

Obtaining Query Information at Runtime GET_ITEM_PROPERTY SET_ITEM_PROPERTY Following block properties may be set for obtaining query information Get and Set: CASE_INSENSITIVE_QUERY QUERYABLE QUERY_ONLY Get only: QUERY_LENGTH !

Validation Form Builder validates at the following levels: Form Level Block Level Record level Item level Item level ! @

Validation Triggers Item level When-Validate-item Block level When-validate-record if :s_issues.due_date < sysdate then message(‘enter due date correctly’); raise FORM_TRIGGER_FAILURE; End if; !

Tracking Validation Status NEW When a record is created Also for Copy Value from item or Initial Value CHANGED When the item is changed by the user or a trigger When any item in a new record is changed VALID When Validation of the item has been successful After records are fetched from database After successful POST or COMMIT Duplicated records inherit status of source !

CLEAR_BLOCK,CLEAR_FORM,EXIT_FORM, EXIT_FORM ENTER SET_FORM_PROPERTY (…..,VALIDATION) (……,VALIDATION_SCOPE) ITEM_IS_VALID item property VALIDATE (VALIDATE_SCOPE) Built-Ins for Validation !

About Navigation What is a navigation unit? A Navigation Unit is an invisible, internal object that determines the navigational state of a form. It keeps track of the object that is currently the focus of a navigational process. Navigation unit can be an object in the hierarchy: Outside the form Form Block Record Item Entering and Leaving Objects What happens if navigation fails !

Navigation Properties Form Module Mouse navigation limit First Navigation data block Block Navigation style Previous navigation data block Next navigation data block Item Enabled Keyboard navigable Mouse navigate Previous navigation item Next navigation item !

Navigation Triggers Pre- and Post- When New object Instance Fire during navigation Fire after navigation Does not fire if validation Does fire when validation unit is larger than trigger unit is larger than the object trigger object Allows unrestricted Allow restricted and built-ins unrestricted built-ins Handle failure by Is not affected by failure returning to initial object ! @

When-New-’object’-Instance triggers When-New-Form-Instance When-New-Block-Instance When-New-record-Instance When-New-Item-Instance !

The Pre- and Post-Triggers Pre/Post-Form Pre/Post-Block Pre/Post-Record Pre/Post-Text-Item !

Using Pre and Post Triggers Trigger Type Use to Pre-Form Pre-Block Validate - User, Time of day Initialize control blocks - Call another form to display messages Post-Form Perform housekeeping - erase global variables Before exit - Display messages to user Authorize access to the block Post-Block Validate the last record that had input focus Test a condition and prevent the user from leaving Pre-Record Set global variables Post-Record Clear global variables - Set a visual attribute for an item as the user scrolls down through a set of records. Perform cross field validation Pre-Text-Item Post-Text-Item Derive a complex default value. Record the previous value of a text item Calculate or change item values ! @

Post-Block Triggers Example Changing the status of the book to LOST If (system.block_status =‘ new’ or system.block_status =‘changed’) and :s_returns.lost = 'Y' then s_copy.status=‘L’ ; !

Navigation in Triggers Built-in routines for Navigation Function GO_FORM Navigates to an open form (in a multiple fprm application) GO_BLOCK Navigates to an indicated block Navigates to an indicated item Navigates to a specific record GO_ITEM GO_RECORD NEXT_BLOCK Navigates to the next enterable block NEXT_ITEM Navigates to the next enterable item NEXT_RECORD Navigates to the first enterable item in the next record NEXT_SET Fetches another set of records from the database and navigates to the first record that the fetch retrieves UP Navigates to the instance of the current item in the previous record DOWN Navigates to the instance of the current item in the next record PREVIOUS_BLOCK Navigates to the previous enterable block PREVIOUS_ITEM Navigates to the previous enterable item PREVIOUS_RECORD Navigates to the previous enterable record SCROLL_UP SCROLL_DOWN Scrolls the block so that the records above the top visible one display Scrolls the block so that the records below the bottom visible one display ! @

Transaction Processing Describe details of commit processing and commit triggers Supplement transaction processing by using triggers Allocate sequence numbers to records as they are applied to tables Implement Array DMLs !

Transaction Processing Transaction processing includes two phases: POST Writes record changes to base tables Fires transactional triggers COMMIT Performs database commit Error result in: Rollback of the database changes Error messages displayed !

The Commit Sequence of Events Validate the form Pre-Commit Validate the block Pre-Delete Delete Row On-Delete Post-Delete 1 2 More records? Contd. !

The Commit Sequence of Events Copy value from item Pre-Update Check uniqueness Insert row On- Insert Post-Insert 1 2 More blocks? INSERT UPDATE More records? Pre-Insert Check uniqueness Update row On-Update Post-Update Post-Forms-Commit Commit changes Post-Database-Commit STOP !

Characteristics of Commit triggers Pre-Commit : Fires once if form changes are made or uncommitted changes are posted Pre/Post-DML : Fires for each record selected for DML operations On-DML : Fires per record,replacing default DML on row Use of DELETE_RECORD , INSERT_RECORD , UPDATE_RECORD Post-forms-Commits : Fires once even if no changes are made Post-Database-Commit : Fires once even if no changes are made NOTE : A commit-Trigger failure causes a rollback to the savepoint !

Testing the result of Trigger DML SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT Example : if SQL%NOTFOUND then message (‘Book not available’); end if; !

DML Statements Issued During Commit Processing Rules DML statements may fire associated database triggers Using and retrieving ROWID Update Changed Columns and Column Security Locking Statements are not issued during default commit processing !

Overriding Default Transaction Additional Transactional Triggers Trigger Do-the-right-thing Built-in On-Check-Unique On-Column-Security On-Commit On-Rollback On-Savepoint On-Sequence-Number CHECK_RECORD_UNIQUENESS ENFORCE_COLUMN_SECURITY COMMIT_FORM ISSUE_ROLLBACK ISSUE_SAVEPOINT GENERATE_SEQUENCE_NUMBER !

Overriding Default Transaction Transactional Triggers for logging on and off Trigger Do-the-right-thing Built-in Pre-Logon Pre-Logout On-Logon On-Logout Post-Logon Post-Logout - - LOGON LOGOUT - - !

Running with non-Oracle Data sources Three ways to run against non-oracle Data sources: Oracle Open Gateways Oracle Open Client Adapter for ODBC Write appropriate transactional triggers Connecting with Open Gateway Cursor and save point mode form module properties Key mode and Locking mode block Properties Using Transactional triggers Call 3GL programs Database data block property !

Getting and Setting Commit Status What is commit Status? Commit Status determines how the record will be processed during the next commit phase . SYSTEM.RECORD_STATUS NEW INSERT (Also caused by control items) QUERY CHANGED ! @

Getting and Setting the Commit Status System variables versus built-ins for commit status Built-ins for getting and setting commit status: GET_BLOCK_PROPERTY GET_RECORD_PROPERTY SET_RECORD_PROPERTY Warnings: Do not confuse commit status with validation status The commit status is updated during during validation !

Implementing Array DML Array Processing, processes groups of records at a time, reducing network traffic and thereby increasing performance. With DML array size greater than 1, the appropriate Pre-Triggers fire for all of the new, changed and deleted rows; all of the DML statements are issued and then all of the Post-Triggers fire. For Array Processing - Enable the array Processing option Specify a DML Array Size of greater than 1 Specify Block Primary Keys ! @

Writing Flexible Code, Sharing Objects and Code and Introduction to Multiple Form Applications Describes flexible code and the built-in subprograms that assist flexible coding Discusses advantages of using system variables Writing code to reference objects directly and indirectly, using internal object id. Describing various methods for reusing objects and code Inheriting properties from property classes Grouping related objects for reuse Reusing objects from an object library Reusing PL/SQL code Calling one form from another form module !

Flexible Code Is reusable code Is generic code Avoids hard-coded object names Makes maintenance easier Increases productivity !

System Variables for current Context Input focus SYSTEM.CURSOR_BLOCK SYSTEM.CURSOR_RECORD SYSTEM.CURSOR_ITEM SYSTEM.CURSOR_VALUE Trigger focus SYSTEM.TIRGGER_BLOCK SYSTEM.TIRGGER_RECORD SYSTEM.TIRGGER_ITEM !

System Status Variables Writing the trigger code for WHEN-BUTTON-PRESSED on SAVE button Example : If :system.block_status=‘CHANGED’ then Commit_form; end if; !

GET_”object”_PROPERTY Built-in subprograms that provide the same type of runtime information as built-in variables GET_APPLICATION_PROPERTY GET_FORM_PROPERTY GET_BLOCK_PROPERTY GET_RELATION_PROPERTY GET_RECORD_PROPERTY GET_ITEM_PROPERTY GET_ITEM_INSTANCE_PROPERTY !

GET_”object”_PROPERTY GET_LOV_PROPERTY GET_RADIO_BUTTON_PROPERTY GET_MENU_ITEM_PROPERTY GET_CANVAS_PROPERTY GET_TAB_PAGE_PROPERTY GET_VIEW_PROPERTY GET_WINDOW_PROPERTY !

SET_”object”_PROPERTY SET_APPLICATION_PROPERTY SET_FORM_PROPERTY SET_BLOCK_PROPERTY SET_REALTION_PROPERTY SET_RECORD_PROPERTY SET_ITEM_PROPERTY SET_ITEM_INSTANCE_PROPERTY !

SET_”object”_PROPERTY SET_LOV_PROPERTY SET_RADIO_BUTTON_PROPERTY SET_MENU_ITEM_PROPERTY SET_CANVASPROPERTY SET_TAB_PAGE_PROPERTY SET_VIEW_PROPERTY SET_WINDOW_PROPERTY !

FIND_Built-Ins The FIND_”object” built-in returns the id of the object being referenced by it. FIND_FORM FIND_BLOCK FIND_ITEM FIND_RELATION FIND_LOV FIND_WINDOW FIND_EDITOR FIND_VIEW FIND_CANVAS FIND_ALERT !

Using Object IDs Declare a PL/SQL variable of the same datatype Use a variable for any later reference to the object Use a variable within the current PL/SQL block only Example: DECLARE item_var item; BEGIN item_var := FIND_ITEM(:SYSTEM>CURSOR_ITEM); SET_ITEM_PROPERTY(item_var,position,30,35); SET_ITEM_PROPERTY(item_var,prompt_text,’Current’); END; !

Referencing Objects Indirectly Item A Item B Item A Item A thornbirds thornbirds Direct Reference Indirect Reference !

Referencing Objects Indirectly NAME_IN Function Returns The contents of the variable Character string USE Conversion functions for NUMBER and DATE COPY Procedure Allows Direct Copy eg: COPY(‘thornbirds’,’S_BOOKS.book_name’); eg: COPY(‘thornbirds’,:GLOBAL.book_name); Indirect Copy eg: COPY(‘thornbirds’,NAME_IN(‘GLOBAL.customer_name’)); Assigns a value to an item whose name is stored in a global variable !

Sharing Code Increases Productivity Decreases Maintenance Increases Modularity Maintains Standards !

Property Classes Canvas View Properties Relation Properties Item Properties LOV Properties Block Properties A property class is a named object that contains a list of properties and their settings ! @

Property Class Icons Add Property Delete Property Property Class Inherit Property !

Inherited and Variant Properties Inherited Property An inherited property is one that takes it’s value from the property class that you associated with the object. An inherited property displays with an arrow to the left of the property name Variant Property A variant property is one that has a modified value although it is inherited from the property class associated with the object. You can override the setting of any inherited property to make that property variant. Variant properties display with a red cross over an arrow ! @

Inherited and Variant properties Property Palette Default property Default property Default property Property Palette Default property Default property Changed property Property Palette Inherited property Inherited property Inherited property Property Class Apply Change Change Inherit Property Palette Inherited property Inherited property Inherited property !

Inheriting Properties To inherit property values from a property class, set the Subclass Information Property To convert a Variant Property to an Inherited Property, change Inherited Property in the Property Palette to Variant Property To convert an Inherited Property to a Variant Property, overtype the Inherited value with a new value !

Object Group An object group is a Logical Container for a set of Form Builder objects Enables you to Group related objects Copy multiple objects in one operation !

Using Object Groups Including a block into an Object Group includes it’s Items Item-level triggers Block-level triggers Relations Other Object Groups cannot be included into an object group Deleting an Object Group does not affect objects in the form module Object from a form module affects the object groups containing it !

Copying Objects Bookname Author name Price Bookname Author name Price Ability to make an exact copy of the object !

What Is Subclassing? Bookname Author name Price Bookname Author name Price Ability to make an exact copy of the object Bookname Author name Price Category Book-id Ability to add properties to an object !

Subclassing Bookname Author name Price Bookname Author name Price Ability to make exact copy of object Bookname Author name Price Category Book-id Ability to add to object Bookname Author name Price Ability to alter properties !

Inheriting Changes Bookname Author name Price Bookname Author name Price Bookname Author name Price Category Book-id Ability to inherit changes Bookname Author name Price Making changes to parent Ability to inherit changes Ability to inherit changes !

What Is an Object Library? Convenient container of objects for reuse Simplifies reuse in complex environment Supports and provides controls for corporate, project, and personal standards Simplifies sharing of reusable components Eliminates the need to maintain multiple referenced forms Saved to ‘.olb’ file or to the database !

SmartClass An object in an Object library that is frequently used as a Class is a Smart Class Can be applied easily and rapidly to existing objects Can be defined in many object libraries Can have many SmartClasses of the given object type !

PL/SQL Libraries A library is a separate module, holding procedures, functions and packages Direct reference to bind variables are not allowed Use sub-program parameters for passing bind variables Use functions, where appropriate, to return values !

Creating Library Program Units and attaching Libraries !

Multiple Form Applications Behaviour Flexible navigation between windows Single or multiple database connections Transactions may span forms, if required Commits in order of opening forms, starting with current form Links Data exchanged by global variables or parameter lists Code shared as required, through libraries and databases !

Multiple Form Session Global Variables Form A Form D Form B Form C Forms Runtime Open Open Open !

Opening Another Form Notes: Control passes immediately to called form - no statements after open_form are processed Activate mode NO_ACTIVATE retains control in current form Transaction continues unless explicitly committed before !

Opening Another Form Example: If book available open ISSUE form, else open DEMAND form if book_status=‘A’ then open_form(‘ISSUE’) ; else open_form(‘DEMAND’); end if; !

Closing a Form with EXIT_FORM Default functionality same as [Exit] key Commit_mode argument defines action on uncommitted changes Example : EXIT_FORM(DO_COMMIT); EXIT_FORM(NO_COMMIT); !

Oracle Forms Tutorial (www.aboutoracleapps.com)

In this document

More Related Content

What's hot

Similar to Oracle Forms Tutorial (www.aboutoracleapps.com)

Recently uploaded

Oracle Forms Tutorial (www.aboutoracleapps.com)