Enhanced Guide to Oracle10gChapter 6:Creating Custom Forms

Data Block and Custom FormsData block formBased on data blocks that are associated with specific database tablesReflect the structure of the databaseCustom formBased on control blocks that process data from multiple tablesReflect business processes rather than the database structure

Creating a Custom FormCreate the formCreate the form window and canvas manuallyCreate a control blockData block that is not associated with a specific tableContains form items that you manually draw on the canvasCreate form triggers to process data

Form TriggersCode that is associated with a form object and an eventCan contain SQL INSERT, UPDATE, DELETE, and SELECT commandsReferencing form text item values in triggers:

:block_name.item_name

Program UnitsSelf-contained programs Can be called from PL/SQL triggersUsed to make triggers more modular, and reuse code within triggers

Practice1Create a custom form that has:Employee NameEmployee SalaryDepartment NameCreate a LOV to retrieve block recordsUse triggers to retrieve block recordsCreate a button that calls a program unit to clear the block.

Referencing System Date and Time Values in Forms

System VariableReturn Value$$DATE$$Current operating system date$$TIME$$Current operating system time$$DATETIME$$Current operating system date & time$$DBDATE$$Current DB server date$$DBTIME$$Current DB server time$$DBDATETIME$$Current DB server date & time

Form TriggersCategoriesBlock processingInterface eventMaster-detail processingMessage handlingNavigationalQuery timeTransactionalValidation

Trigger TimingPRE-Fires just before associated event occursPOST-Fires just after associated event occursON-, WHEN-, KEY-Fires immediately, in response to specific user actions, such as key presses

Trigger ScopeDefines where an event must occur to make the trigger fireTrigger scope includes the object to which the trigger is attached, as well as all objects within that objectForm-level: fires when event occurs within any block or item in the formBlock-level: fires when event occurs within any item in the formItem-level: fires only when event occurs within that item

Trigger Execution HierarchyIf 2 related objects have the same trigger, the lower-level objects trigger fires instead of the higher-level one.Form & block, blocks trigger fires Block & item, items trigger firesYou can specify a custom execution order using the Execution Hierarchy property of the trigger.

Navigational TriggersExternal navigation: occurs when user causes form focus to changeInternal navigation: occurs as a result of internal form triggers that fire in response to external navigation events

User ActionTriggers FiredResult on User Screen DisplayUser starts formPRE-FORMPRE-BLOCK

Form appears, but with no data visible WHEN-NEW-FORM-INSTANCEWHEN-NEW-BLOCK-INSTANCEWHEN-NEW-RECORD-INSTANCEWHEN-NEW-ITEM-INSTANCE Form is available for use Triggers That Fire at Form Startup12345

User ActionTriggers FiredResult on User Screen DisplayUser places the insertion point in a text item WHEN-NEW-ITEM-INSTANCE

Insertion point appears in item WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTANCE Next record appearsUser clicksthe Next Recordbutton Triggers That Fire as a Result Of External Navigation

User ActionTriggers FiredResult on User Screen DisplayUser closes the Forms Runtime window Forms Runtime window closes POST-BLOCKPOST-FORM Triggers That Fire When a Form Closes

Practice2Create a department data block.Create an Update button.Create a Cancel button that uses a procedure.

Directing External NavigationForm tab order is based on item order in Object Navigator block listFormtaborder

Moving to a Specific Form ItemGO_ITEM(block_name.item_name);Do not write colon(:) before the block name in this commnd.E.g:GO_ITEM(emp.sal);Other navigational instructions: GO_BLOCK(block_name)GO_FORM(form_name)

Oracle Error Message Severity Levels5: informative message10: informative message advising user of procedural error20: condition that keeps trigger from working correctly25 condition that keeps form from working correctly>25: extreme severity

Suppressing Lower Level System MessagesSet :SYSTEM.MESSAGE_LEVEL variable to a higher level in PRE-FORM trigger

:SYSTEM.MESSAGE_LEVEL := 25;

Providing User Feedback in FormsMessageText in message line at bottom of formInformational only; user doesn't have to respondAlertDialog box Allows user to choose different ways to proceed

MessagesMessageSyntax:MESSAGE(message text);

AlertsForm-level object Object properties define alert appearanceTitleStyleiconMessageButtons

Code for Displaying an AlertDECLAREalert_button NUMBER;BEGINalert_button := SHOW_ALERT('alert_name');IF alert_button = ALERT_BUTTON1 THENprogram statements for first button;ELSEprogram statements for second button;END IF;END;

Avoiding User ErrorsMake primary and foreign key text items non-navigable (How?)When user moves form focus to primary or foreign key text item, move focus to alternate form item (How?)

How to make nonnavigable item?Set the text item property Keyboard Navigable to NOUse When New Item Instance or When Mouse Up triggers to make internal navigation from the nonnavigable item.

Trapping Runtime ErrorsCreate an ON-ERROR event triggerForm-level triggerExecutes whenever an FRM- or ORA- error occurs-FRM errors: generated by Forms Runtime-ORA errors: generated by database

Form Procedures That Return System Error InformationDBMS_ERROR_CODE: The most recent -ORA error number. (Negative Number)DBMS_ERROR_TEXT: -ORA error message and number.ERROR_CODE: The most recent FRM error number. (Positive Number)ERROR_TEXT: FRM error message and number.

Structure of ON-ERROR TriggerBEGIN --trap FRM errorsIF ERROR_CODE = FRM_error_code1 THENerror handler;ELSIF ERROR_CODE = FRM_error_code2 THENerror handler;ELSE--trap ORA errorsIF DBMS_ERROR_CODE = -ORA_error_code1 THENerror handlerELSIF DBMS_ERROR_CODE = -ORA_error_code2 THENerror handlerEND IFEND IF;END;

Code totrap FRM errorsCode totrap ORA errors

Form ValidationEnsures that form data meets preset requirements so erroneous data is not sent to databaseUsing validation properties or using validation triggers.

Form Validation CategoriesDataSpecifies data types, lengths, and maximum and minimum valuesDatabaseSpecifies which operations a user can perform on a text itemList of ValuesSpecifies whether a data value must be validated against the text items LOV (Validate From List text item property)

Validation PropertiesValidation unit form property: specifies the largest data chunk that the user can enter before validation occursCan be performed at the form, block, record, or item levelSpecified in the Validation Unit property on the form Property PaletteBy default, it is set to item level.

Cont.You should use the item level with the custom forms, and the record or block level with the data block forms.The validation properties that are checked always and before checking other properties and triggers are: format mask, required, datatype, range, and validate from list

Text Item Validation PropertiesData TypeMinimum ValueMaximum ValueValidate From ListInsert AllowedQuery AllowedRequired

Why do not we use validation properties instead of validation triggers?Question

We use validation triggers for more complex validations. (like what?)Answer

Validation TriggersWhen Validate ItemIt should be Item level trigger.Executes when the item validation occurs depending on Validation Unit property.E.g. : IF NOT :student.s_class IN (FR,SO) THEN Message(Legal Values are FR and SO);RAISE FORM_TRIGGER_FAILURE;END IF;

How To Disable ButtonsSET_ITEM_PROPERTYE.g. :SET_ITEM_PROPERTY(Control.Delete , ENABLED, PROPERTY_FALSE);

Data Blocks vs. Control BlocksData blockEasy to create and useIs associated with a single table, and reflects the tables structureControl blockRequires a lot of custom programmingCan contain items from many different tablesYou can link data and control blocks to take advantages of the strengths of each

Linking Data Blocks and Control BlocksCreate the control block as the master blockCreate the data block as the detail block, but do not create a master-detail relationshipCreate a master-detail relationship manually in the WHERE Clause property of the detail block:

data_block_field := control_block.text_item

Displaying and Refreshing the Data Block ValuesCreate a trigger to:Place the insertion point in the data blockGO_BLOCK(block_name);Flush the data block to make its data consistent with the master block and the database:EXECUTE_QUERY;

Converting a Data Block to a Control BlockCreate a data block and layout that contains most of the required text itemsConvert the data block to a control block by changing the following block properties:Database Data Block = NoRequired = No

Creating a Form with Multiple CanvasesUsers should be able to see all canvas text items without scrollingFor complex applications with many text items, divide application into multiple canvases

Block Navigation OrderFirst block in Object Navigator Data Blocks list determines block items that first appear when form opensUsers can use the Tab key to navigate among different block itemsCanvas that contains block items automatically appears

Block Navigation OrderBlock orderCanvas order doesnt matter

Controlling Block NavigationBlock Navigation Style propertySame Record: navigation cycles through items on same blockChange Data Block: navigation moves to next data block in list

CanvasIt is the surface that has the form items on it.It has many types:Content (The default)TabStackedTo display the canvas at runtime, use the canvas property Window.

Tab CanvasesMultiple-page canvases that allow users to move among different canvas surfaces by clicking tabs

Tab Canvas ComponentsTab canvasCollection of related tab pagesTab pagesSurfaces that display form itemsTab labelsIdentifier at top of tab pageA tab canvas lies on top of a content canvas

Creating a Tab CanvasUse the Tab Canvas tool on the Layout Editor tool palette to draw a tab canvas on an existing content canvasBy default, a new tab canvas has 2 tab pagesCreate new tab pages as neededAdjust tab page properties

Important Tab Page PropertiesName: how the pageIs referenced in the formLabel: Caption that appears on the associated tab

Adjusting the Tab Page OrderTab page that appears first is tab page whose block items appear first in the Object NavigatorBlock orderTab page order doesnt matter

Stacked CanvasesCanvas that appears on an existing content canvas, and can be displayed or hidden as neededAllows user to configure canvas items

Stacked Canvas ExampleStackedcanvasContentcanvas

Creating a Stacked CanvasUse the Stacked Canvas tool on the Layout Editor tool palette to draw a stacked canvas on an existing content canvasCreate block items on the stacked canvasCreate buttons and triggers to display and hide the stacked canvas

Displaying and Hiding a Stacked CanvasDisplaying a stacked canvas:GO_BLOCK(stacked_canvas_block);SHOW_VIEW(stacked_canvas);

Hiding a stacked canvas:GO_BLOCK(content_canvas_block);HIDE_VIEW(stacked_canvas);