developer/2000: build forms ii -...

Developer/2000: Build Forms II

...........................................................................................Volume 2 • Student Guide

44111GC10Production 1.0January 1998M06179

Author

Pascal Gibert

TechnicalContributors andReviewers

Louise Barnfield

Gunnar Boehrs

Jacquelyn Bruce

Phyllis Chan

Bulent Cinarkaya

Laurent Dereac

Mark Doran

Gillian Elias

Tushar Gadhia

Stephen Gramann

Nancy Greenberg

Ursula Hovy

Uwe Jentzsch

Leta A. Johnson

Jan Keuben

Steve Lirette

Jayne Marlow

Rita Morin

Daphne Nougier

Christin Nowakowski

Bryan Roberts

Bert Salyga

Helene Schwann

Hiroyuki Sugiyama

Pal Tarcsay

Bart Van der Laar

Jack Walsh

Publishers

Stephanie Jones

Kelly Lee

Renee Voss

Copyright Oracle Corporation, 1998. All rights reserved.

This documentation contains proprietary information of Oracle Corporation. It isprovided under a license agreement containing restrictions on use and disclosureand is also protected by copyright law. Reverse engineering of the software isprohibited. If this documentation is delivered to a U.S. Government Agency of theDepartment of Defense, then it is delivered with Restricted Rights and thefollowing legend is applicable:

Restricted Rights LegendUse, duplication or disclosure by the Government is subject to restrictions forcommercial computer software and shall be deemed to be Restricted Rightssoftware under Federal law, as set forth in subparagraph (c) (1) (ii) of DFARS252.227-7013, Rights in Technical Data and Computer Software (October 1988).

This material or any portion of it may not be copied in any form or by any meanswithout the express prior written permission of the Worldwide Education Servicesgroup of Oracle Corporation. Any other copying is a violation of copyright law andmay result in civil and/or criminal penalties.

If this documentation is delivered to a U.S. Government Agency not within theDepartment of Defense, then it is delivered with “Restricted Right,” as defined inFAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).

The information in this document is subject to change without notice. If you findany problems in the documentation, please report them in writing to EducationProducts, Oracle Corporation, 500 Oracle Parkway, Box 659806, RedwoodShores, CA 94065. Oracle Corporation does not warrant that this document iserror-free.

Developer/2000, Oracle Server, and PL/SQL are trademarks or registeredtrademarks of Oracle Corporation.

All other products or company names are used for identification purposes only,and may be trademarks of their respective owners.

Developer/2000: Build Forms II iii......................................................................................................................................................

...........................................................................................................................................................Contents

PrefaceProfile xiRelated Publications xiiTypographic Conventions xiii

Introduction I-1Course Objectives I-3Course Content I-5

Lesson 1: Managing Projects with Project BuilderIntroduction 1-3Introducing Project Builder 1-5Project Builder Terminology 1-7Project Builder Benefits 1-9Project Builder User Interface 1-11Creating Projects and Subprojects 1-13Adding Project Files 1-15Working with Project Files 1-17Establishing Dependencies 1-19Building Projects 1-21Delivering Projects 1-23Practice Session Overview: Lesson 1-1 1-25Practice Session: Lesson 1 1-26Inheritance in Project Builder 1-29Defining New Types 1-31Actions 1-33Modifying Types Using Actions 1-35Customizing Actions Using Macros 1-37Customizing the Launcher 1-41Summary 1-43Practice Session Overview: Lesson 1-2 1-45Practice Session: Lesson 1 1-46

Lesson 2: Creating a Menu ModuleIntroduction 2-3Components of the Menu Module 2-5Menu Styles 2-7The Default Menu 2-9The Menu Editor 2-11Creating a Menu Module 2-13Module Properties 2-15Menu Properties 2-17Menu Item Properties 2-19Menu Item Types 2-21Menu Item Command Types 2-23Implementing Menu Toolbars 2-25

iv Developer/2000: Build Forms II......................................................................................................................................................

...................................................................................................................................................... Contents

Storing the Menu Module 2-27Attaching the Menu Module 2-29Pop-Up Menus 2-31Summary 2-33Practice Session Overview: Lesson 2 2-35Practice Session: Lesson 2 2-36

Lesson 3: Managing Menu ModulesIntroduction 3-3Using PL/SQL in Menu Item Code 3-5Useful Built-in Menu Subprograms 3-7Showing and Hiding the Current Menu 3-9Substitution Parameters 3-11Defining a User-Named Substitution Parameter 3-13Substitution Parameter Built-ins 3-15Validating a Substitution Parameter Value 3-17Managing Menu Security 3-19Defining Security Roles 3-21Assigning Access to Menu Items 3-23Summary 3-25Practice Session Overview: Lesson 3 3-27Practice Session: Lesson 3 3-28

Lesson 4: Programming Function KeysIntroduction 4-3Key Triggers 4-5Defining Key Triggers 4-7Characteristics of Key Triggers 4-9Classification of Key Triggers 4-11Using Key Triggers 4-15Association with Other Interface Controls 4-17Summary 4-19Practice Session Overview: Lesson 4 4-21Practice Session: Lesson 4 4-22

Lesson 5: Responding to Mouse EventsIntroduction 5-3What Are Mouse Events? 5-5Responding to Mouse Movement 5-7Responding to Mouse Button Actions 5-11Summary 5-17Practice Session Overview: Lesson 5 5-19Practice Session: Lesson 5 5-20

Developer/2000: Build Forms II v......................................................................................................................................................

...........................................................................................................................................................Contents

Lesson 6: Controlling Windows and Canvases ProgrammaticallyIntroduction 6-3Using Window-Interaction Triggers 6-5Built-ins for Manipulating WINDOWS 6-7Built-ins for Manipulating Canvases 6-9Working with Tab-Style Canvases 6-11Windows and Blocks 6-15Manipulating Window Properties Programmatically 6-17Showing Multiple Windows 6-19Closing Windows 6-21Using Large Blocks 6-23Summary 6-25Practice Session Overview: Lesson 6 6-27Practice Session: Lesson 6 6-28

Lesson 7: Controlling Data Block RelationshipsIntroduction 7-3Creating a Relation 7-5Modifying a Relation 7-7Block Coordination 7-11Characteristics of Relation-Handling Triggers 7-13Principles of Relation-Handling Code 7-15Obtaining Relation-Handling Information 7-17Implementing a Coordination-Type Toggle 7-19Forcing a Commit Per Master 7-21Summary 7-23Practice Session Overview: Lesson 7 7-25Practice Session: Lesson 7 7-26

Lesson 8: Building Multiple Form ApplicationsIntroduction 8-3OPEN_FORM to Invoke Additional Forms 8-5Closing Forms 8-7Navigating Between Forms 8-9Transaction Processing for Opened Forms 8-11CALL_FORM to Invoke Additional Forms 8-13Transaction Processing for Called Forms 8-15NEW_FORM to Invoke Additional Forms 8-19Controlling Open Forms and Called Forms Together 8-21Different Ways of Invoking Forms 8-23Using Form Parameters 8-25Parameter Lists 8-29Creating and Manipulating Parameter Lists 8-31Passing Data Between Forms 8-33Summary 8-35

vi Developer/2000: Build Forms II......................................................................................................................................................

...................................................................................................................................................... Contents

Practice Session Overview: Lesson 8 8-37Practice Session: Lesson 8 8-38

Lesson 9: Defining Data SourcesIntroduction 9-3Data Sources Types 9-5Basing a Data Block on a From Clause Query 9-7Basing a Data Block on a Stored Procedure 9-9Example of Query Using a Ref Cursor Procedure 9-11Example of Query Using a Table of Records Procedure 9-13Example of DML Using a Stored Procedure 9-15Deciding Whether to Use a Ref Cursor or a Table of Records 9-17Data Block Wizard 9-19Data Block Properties 9-21Data Source Guidelines 9-23Summary 9-27Practice Session Overview: Lesson 9 9-29Practice Session: Lesson 9 9-30

Lesson 10: Working with Record GroupsIntroduction 10-3Record Groups 10-5Using Record Groups 10-7Defining Record Groups at Design Time 10-9Built-in Functions for Controlling Record Groups 10-11Defining Query Record Groups Programmatically 10-17Defining Non-Query Record Groups Programmatically 10-19Manipulating Record Group Rows 10-21Manipulating Selected Record Group Rows 10-23Defining Global Record Groups 10-25Manipulating List Items Programmatically 10-27Implementing Dynamic List Items 10-29Adding Values to Combo Boxes 10-31Summary 10-33Practice Session Overview: Lesson 10 10-35Practice Session: Lesson 10 10-36

Lesson 11: Including Charts and ReportsIntroduction 11-3Including Charts Using the Chart Wizard 11-5Chart Item Properties 11-11Reporting Within Form Builder 11-13Report Object Properties 11-15Working with Reports 11-17Summary 11-21Practice Session Overview: Lesson 11 11-23

Developer/2000: Build Forms II vii......................................................................................................................................................

...........................................................................................................................................................Contents

Practice Session: Lesson 11 11-24

Lesson 12: Applying TimersIntroduction 12-3Timers 12-5Handling Timer Expiration 12-7Creating a Timer 12-9Modifying a Timer 12-11Deleting a Timer 12-13Summary 12-15Practice Session Overview: Lesson 12 12-17Practice Session: Lesson 12 12-18

Lesson 13: Using Reusable ComponentsIntroduction 13-3Reusable Components 13-5Reusable Components List 13-7The Calendar Class 13-11Calendar Object Group Content 13-13Built-in for Manipulating the Calendar 13-15Summary 13-17Practice Session Overview: Lesson 13 13-19Practice Session: Lesson 13 13-20

Lesson 14: Using Server Features in Form BuilderIntroduction 14-3Using Oracle Server Functionality in Forms 14-5Dealing with PL/SQL Code 14-7Handling Errors Raised by the Oracle Server 14-9Obtaining the Cause of Declarative-Constraint Violations 14-13Customizing Oracle Server Error Messages 14-15Example Procedure for Handling Oracle Server Errors 14-17Performing DDL with FORMS_DDL 14-21Summary 14-25Practice Session Overview: Lesson 14 14-27Practice Session: Lesson 14 14-28

Appendix A: Practice SolutionsPractice 1-1 Solution A-2Practice 1-2 Solution A-5Practice 2 Solution A-7Practice 3 Solution A-13Practice 4 Solution A-19Practice 5 Solution A-23Practice 6 Solution A-26Practice 7 Solution A-29

viii Developer/2000: Build Forms II......................................................................................................................................................

...................................................................................................................................................... Contents

Practice 8 Solution A-33Practice 9 Solution A-35Practice 10 Solution A-39Practice 11 Solution A-47Practice 12 Solution A-50Practice 13 Solution A-54Practice 14 Solution A-57

Appendix B: Table Descriptions and DataSummit Sporting Goods Database Diagram B-2S_CUSTOMER Description B-3S_CUSTOMER Data B-4S_DEPT Description and Data B-8S_EMP Description B-9S_EMP Data B-10S_IMAGE Description and Data B-13S_INVENTORY Description B-14S_INVENTORY Data B-15S_ITEM Description B-19S_ITEM Data B-20S_ORD Description and Data B-22S_PRODUCT Description B-23S_PRODUCT Data B-24S_REGION Description and Data B-28S_TITLE Description and Data B-29

Appendix C: Project Builder AddendumIntroduction C-3Creating Connections C-5Team Development with Project Builder C-7Generating and Printing Project Reports C-13Summary C-15

Appendix D: Oracle Terminal 2 and Developer/2000 Forms

Appendix E: Using Oracle Server Roles at Run TimeUsing Oracle Server Roles at Run Time E-3

Appendix F: EMP_PKG PackagePackage Specification F-2Package Body F-4

Appendix G: Implementing Online Help Reusable ComponentIntroduction G-3The Online Help Class G-5Creating the Database Objects G-7Creating Online Help Content G-9

Developer/2000: Build Forms II ix......................................................................................................................................................

...........................................................................................................................................................Contents

Displaying Online Help G-15Summary G-17

Appendix H: Working with ActiveX ControlsIntroduction H-3What Are VBX, OCX, and ActiveX Controls? H-5Comparison of VBX, OCX, and ActiveX Controls H-7ActiveX in Form Builder H-9Implementing an ActiveX Control H-11Inserting an ActiveX Control in an ActiveX Control Item H-13Importing ActiveX Control Methods and Events Packages H-15Setting and Getting ActiveX Control Properties H-17Invoking ActiveX Control Methods H-21Responding to ActiveX Control Events H-23Handling Exceptions H-25Registering an ActiveX Control H-27Summary H-29

Appendix I: Oracle Rdb OverviewWhat Is Oracle Rdb? I-2Oracle Rdb Features I-3Standard User Interfaces I-4Other Information I-5

x Developer/2000: Build Forms II......................................................................................................................................................

...................................................................................................................................................... Contents

................................

11

Including Charts andReports

11-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Lesson 11: Including Charts and Reports

11-2 Copyright Oracle Corporation, 1998. All rights reserved.

Objectives

After completing this lesson, you shouldbe able to do the following:• Embed charts in a form module using

the Chart Wizard

• Incorporate existing graphic displays ina form module

• Create and invoke reports in a formusing the Report Wizard

• Control a report programmatically in aform

Developer/2000: Build Forms II 11-3......................................................................................................................................................

......................................................................................................................................................Introduction

Introduction

OverviewIn this lesson, you will learn how to include other document types in yourapplication, such as charts built using the Graphic Builder, and reports builtusing the Report Builder.

ObjectivesAfter completing this lesson, you should be able to do the following:

• Embedded charts in a form module using the Chart wizard

• Incorporate existing graphic displays in a form module

• Create and invoke reports in a form using the Report Wizard

• Control a report programmatically in a form




Including Charts Using theChart Wizard

To invoke the Chart Wizard:

• Use theChart toolto drag achart areaontothe canvas.

• Choose Tools—>Chart Wizard.


......................................................................................................................................................Including Charts Using the Chart Wizard

Including Charts Using the Chart WizardYou are able to create charts and reports within the Form Builder withoutlaunching separate chart or reports engines.

The Chart Wizard leads you step-by-step through the process of creating achart from within a form or a report.

Once the chart is created, you can modify or enhance the chart by using theGraphics Builder.

Invoking the Chart WizardTo invoke the Chart Wizard:

• From the Layout Editor, use the Chart tool to drag a chart area onto thecanvas.

• Choose Tools—>Chart Wizard.

Incorporating a ChartThe steps to create a chart are:

1 Specify a chart type and subtype.

2 Specify the data block that contains the data you want to assign to chartcolumns.

3 Specify the data that will appear on the Category (X) axis of the chart.

4 Specify the data that will appear on the Value (Y) axis of the chart.

Follow the prompts on each page of the wizard to specify the settings for thechart you want to create.

Re-entering the Chart WizardA powerful quality of the Chart Wizard is its ability to operate in re-entrantmode. This means you can use the Chart Wizard to modify an existing chartcreated with the wizard.

To invoke the Chart Wizard in re-entrant mode, select the chart object in theObject Navigator, and then choose Tools—>Chart Wizard from the menu.





Chart type



Arrow indicates that data from the block is represented in the chart.



Chart TypeSelect the style of chart that you require. The picture on the left of thewizard displays the relevant chart style.

The chart subtype options are context-sensitive; the number and style ofoptions change depending on the chart type that you choose. Choose thechart subtype that you require, and then click Next to continue to the nextwizard page.

Data SourceThe next step in the Chart Wizard is to select the block that contains the datayou want to represent in the chart. The data block you select determines thedata that is available for assignment to the Category and Value axes of yourchart.

Chart Type Description

Column Data is plotted as columns.

Bar Data is plotted as horizontal bars.

Pie Data is plotted as individual “slices” showing the relationship of parts ofthe whole.

Line Data is plotted as points along a line.

Mixed Data is charted using multiple chart types, such as bar and line.





Arrow pointsto the Categoryaxis



Arrow points to the Value axis



Category AxisTo use the Chart Wizard to map data to your chart’s Category (X) axis:

1 In the Available Fields list, select the field that you want to map to theCategory (X) axis of the chart.

2 Transfer the selected field from the Available Fields list to the CategoryAxis list.

Note:

Value AxisTo use the Chart Wizard to map data to your chart’s Value (Y) axis:

1 In the Available Fields list, select the field that you want to map to theValue (Y) axis of the chart.

2 Transfer the selected field from the Available Fields list to the ValueAxis list.

Note:

Note: You must transfer at least one field from the Available Fields list tothe Category Value list before navigating to the next page.


Column, Line, Mixed chart Categories appear along the x-axis.

Bar chart Categories appear down the y-axis.

Pie chart Each category represents one segment of the pie.


Column, Line, Mixed chart Values are represented by the height of the column againstthe y-axis.

Bar chart Values are represented as the length of the column againstthe x-axis.

Pie chart Values are represented by the size of each segment.




Chart Item Properties

Filename

Execution Mode

Communication Mode

Data Source Data Block

Query Name

Data Source X Axis

Data Source Y Axis

Update on Query

Update on Commit


......................................................................................................................................................Chart Item Properties

Chart Item PropertiesThe chart item properties are the following:

Property Description

Filename Specifies the name of the file where the chart is stored

Execution Mode Specifies the execution version of Graphics Builder to use:BATCH or RUNTIME

Communication Mode The communication mode to be used when running GraphicsBuilder: SYNCHRONOUS or ASYNCHRONOUS

Data Source Data Block Specifies the block that contains the data to represent in the chart

Query Name Specifies the name of the query used to display the chart

Data Source X Axis Specifies the item name used to map data to the chart Category(X) axis

Data Source Y Axis Specifies the item name used to map data to the chart Value (Y)axis

Update on Query Specifies that the chart is refreshed each time you perform aquery

Update on Commit Specifies that the chart is refreshed each time you perform acommit



Technical NotePassing data is only applicable if running against a local server.


Reporting Within Form Builder

• Create report objects in ObjectNavigator

• Run a report against a local or remoteserver

• Base a report ona data block


......................................................................................................................................................Reporting Within Form Builder

Reporting Within Form BuilderDeveloper/2000 Form Builder provides the ability to run a report against alocal or a remote server with considerable ease. A report object is availablethat allows you to specify all the commands necessary for running a report.

Using the Report WizardUsing the Report Wizard within Form Builder, you can:

• Create a new report document based on one data block in your form

• Create a new report document

• Use an existing report document

Note: You can modify or enhance the report document by using the ReportBuilder.

Creating a Report Object Based on a Data BlockThe ability to base a report on a data block represents an efficient way ofpassing data from a form module to a report. Select the Report node in theObject Navigator.

1 Create a new report object, by clicking the Create icon.

Form Builder displays the New Report dialog box.

2 Specify a filename and a block name.

3 Click OK.

Form Builder runs Report Builder and displays the Report Wizard.

4 Choose the report style that most closely resembles the report you wouldlike to create.

5 Select the fields you would like to display in your report.

6 Select the fields you would like to total by choosing from a list ofpossible calculations mode. (This step is optional.)

7 Enter labels and widths for your fields and totals. (This step is optional.)

8 Select a template for your report and then click the Finish button.

The Live Previewer is displayed.




Report Object Properties

Filename

Execution Mode

Communication Mode

Data Source Data Block

Query Name

Dev/2000 Integration

Properties



Report Destination Type

Report Destination Name

Report Destination Format

Report Server

Other Report Parameters

Reports Properties


......................................................................................................................................................Report Object Properties


Dev/2000 Integration Properties

Reports Properties

Property Name Description

Filename Specifies the name of the report document to be executed byReport Builder.

Execution Mode Specifies the execution mode to be used when running ReportBuilder. Valid numeric constants for this property are BATCHand RUNTIME.

Communication Mode Specifies the communication mode to be used when runningReport Builder. Valid numeric constants for this parameter are:

SYNCHRONOUS specifies that control returns to FormBuilder only after Report Builder has been exited. The end usercannot work in the form while Report Builder is running.

ASYNCHRONOUS specifies that control returns to the callingapplication immediately, even if Report Builder has not com-pleted its display.

Data Source Data Block Block name used by Report Builder as data source.

Query Name Query name used by Report Builder to retrieve the data. Whenthe report is based on data from a Form Builder block, thequery name property value is the block name.


Report Destination Type Specifies the type of device that will receive the reportoutput.

Report Destination Name Specifies the name of the file, printer, or Oracle Officeusername (or distribution list) to which the report output willbe sent.

Report Destination Format Specifies the format of your report. Possible values are PDF,HTML, HTMLCSS, or the printer driver to be used whenDESTYPE is File.

Report Server Name of the remote server on which the report is run.

Other Report Parameters Declare additional parameters.




Working with Reports

Built-ins for interacting with reports:

• RUN_REPORT_OBJECT• FIND_REPORT_OBJECT

• CANCEL_REPORT_OBJECT• REPORT_OBJECT_STATUS

• COPY_REPORT_OUTPUT• GET_REPORT_OBJECT_PROPERTY

• SET_REPORT_OBJECT_PROPERTY• RUN_PRODUCT


......................................................................................................................................................Working with Reports

Working with ReportsYou can control reports in the Report Server dynamically when running aform by including the following new built-ins in your trigger code:

Built-in Description

RUN_REPORT_OBJECT Runs a specified report on a specified server

FIND_REPORT_OBJECT Returns the internal ID of a report object

CANCEL_REPORT_OBJECT Cancels a report in the server queue

REPORT_OBJECT_STATUS Returns the current status of a report (The statuscodes can be: FINISHED, RUNNING,CANCELED, OPENING_REPORT,ENQUEUED, INVALID_JOB,TERMINATED_WITH_ERROR, orCRASHED.)

COPY_REPORT_OUTPUT Copies the report output back to the client

GET_REPORT_OBJECT_PROPERTY Dynamically gets report object properties atrun-time

SET_REPORT_OBJECT_PROPERTY Dynamically sets report object properties atrun-time

RUN_PRODUCT Invokes one of the supported Oracle toolsproducts and specifies the name of the module ormodule to be run.




Running a Report Against aLocal Server

DECLARE

v_rep VARCHAR2(100);

repid REPORT_OBJECT;

BEGIN

repid := FIND_REPORT_OBJECT( ' deptrpt ' );

v_rep := RUN_REPORT_OBJECT(repid);

end;


Running a Report Against aRemote Server

. . .

repid := FIND_REPORT_OBJECT( ' barcode ' );


rep_status := REPORT_OBJECT_STATUS(v_rep);

IF rep_status = ' FINISHED ' THEN

COPY_REPORT_OBJECT_OUTPUT(v_rep,' c:\local.pdf ' );

HOST( ' netscape c:\local.pdf ' );

END IF;

. . .


......................................................................................................................................................Working with Reports

Running a Report Against a Local ServerThis example allows the user to run a report against a local server.

Running a Report Against a Remote ServerThis example allows the user to run a report against a remote server.RUN_REPORT_OBJECT returns a string that uniquely identifies the reporton the server. This string could be used to get the report status or copy theoutput across or terminate the report in case the report is being runasynchronously.

DECLARE



BEGIN

repid := FIND_REPORT_OBJECT(’deptrpt’);


end;

DECLARE



rep_status VARCHAR2(20);

BEGIN

repid := FIND_REPORT_OBJECT(’barcode’);


rep_status := REPORT_OBJECT_STATUS(v_rep);

IF rep_status = ’FINISHED’ THEN

MESSAGE(’Report Completed’);

COPY_REPORT_OBJECT_OUTPUT(v_rep,’c:\local.pdf’);

/* Once the report output is copied across successfully

depending on the type of the output the file could

be displayed using a Web Browser or an OCX control */

HOST(’netscape c:\local.pdf’);

ELSE

MESSAGE(’Error when running report.’);

END IF;

END;




Summary

• Invoking the Chart Wizard

• Reporting features in Form Builder– Create report objects

– Run against local or remote server– Base reports on data blocks


Summary

• Interacting with reports

– RUN_REPORT_OBJECT

– FIND_REPORT_OBJECT

– CANCEL_REPORT_OBJECT

– REPORT_OBJECT _STATUS

– COPY_REPORT_OUTPUT

– GET_REPORT_OBJECT_PROPERTY

– SET_REPORT_OBJECT_PROPERTY


......................................................................................................................................................Summary

SummaryIn this lesson, you have learned how to include charts and reports in yourapplication.

Invoking the Chart Wizard

Reporting Features in Form Builder• Create report objects

• Run against local or remote server

• Base reports on data blocks

Interacting with reports• RUN_REPORT_OBJECT

• FIND_REPORT_OBJECT

• CANCEL_REPORT_OBJECT

• REPORT_OBJECT_STATUS

• COPY_REPORT_OUTPUT

• GET_REPORT_OBJECT_PROPERTY

• SET_REPORT_OBJECT_PROPERTY




Practice 11 Overview

• Creating a chart to display the totalorders for each customer

• Creating a report based on thes_customer block

• Displaying different report styles


......................................................................................................................................................Practice Session Overview: Lesson 11

Practice Session Overview: Lesson 11This practice guides you to incorporate chart into your application, andcreate report using the Form Builder report object.

Practice Contents• Create a chart to display the total orders for each customer.

• Create a report based on the s_customer block.

• Display different report styles.



Practice Session: Lesson 111 Create a chart object using the chart wizard. This chart should display

the total orders for each customer.

a Open the CUSTOMERS form module.

b Create a new tab page on the TAB_CUSTOMER canvas. Name thispage CHART and change the label name.

c Open theFormBuilder_II.olb object library. Copy the OBJ_CHARTobject group into the CUSTOMERS form. This object groupcontains a block named S_ORD. Only one item from this block isvisible: TITLE. This item displays a title for the chart on the Charttab page.

d From the Layout Editor, use the Chart tool to drag a chart area ontothe chart tab page. Select Use the Chart Wizard from the New ChartObject dialog box.

e Select Column as chart type, Plain as chart subtype.

f Specify the S_ORD block as the data block that contains the datayou want to assign to chart columns.

g Select CUSTOMER_NAME to appear on the Category (X) axis, andTOTAL to appear on the Value (Y) axis of the chart.

h Click the Finish button. Save, run, and test your form.

2 Create a report object based on the s_customer block. This reportdisplays a customer list using a tabular layout. Display only the ID,NAME, COUNTRY, and REGION_ID fields.

a Open the CUSTOMERS Form.

b Create a new report object.

- Choose the report style Tabular.

- Select the ID, NAME, COUNTRY, and CREDIT_RATING fields.

- Do not select fields to total.

- Change labels and widths for your fields.

- Select a template for your report.

c Save your report and exit Report Builder.

d Modify the Execution Mode and Report Destination Type reportobject properties.

e Create a button named CUST_REP_BUTTON into the controlblock. Display this button on the CV_CUSTOMER canvas.


......................................................................................................................................................Practice Session: Lesson 11

f Create a When-Button-Pressed trigger on theCUST_REP_BUTTON button to run the report.

g Change the Query All Record property value to Yes for thes_customer block.

h Save, run, and test your form.

If you have time...

3 Create a new tab page from which the user can run different reportlayouts.

a Open theForm_Builder_II.olb object library.

b From the Report tab page, select all the report objects and copy themto the CUSTOMERS form.

c From the Report tab page, select the REPORT block and copy thisblock to the CUSTOMERS form. Organize this block so it is the lastblock in sequence.

d From the Report tab page, select the REPORT tab page object, andcopy this tab page object to the CUSTOMERS form. Organize thistab page so it is the last tab page in sequence.

e Save, run, and test your form.

................................

12

Applying Timers


......................................................................................................................................................Lesson 12: Applying Timers


Objectives

After completing this lesson, you shouldbe able to do the following:

• Describe timers

• Create a timer

• Modify a timer

• Delete a timer

• Handle timer expiration


......................................................................................................................................................Introduction

Introduction

OverviewThis lesson covers time-initiated processing; that is, processing that occursafter a certain amount of time has elapsed. The mechanism you will use todo this is called a timer and it is created, modified, and deleted at run-time.


• Describe timers

• Create a timer

• Modify a timer

• Delete a timer

• Handle timer expiration



Technical NoteTimers are not suitable means of shutting down an application. It is the jobof the operating system to recognize idle processes and shut them down.


Timers

• What is a timer?

• Built-ins for timers:

– FIND_TIMER

– CREATE_TIMER

– SET_TIMER

– DELETE_TIMER

– GET_APPLICATION_PROPERTY(TIMER_NAME)


Timers

• When-Timer-Expired trigger

• Using timers:

– Poll database

– Periodically query or commit orrollback

– “ About… ” information at startup


......................................................................................................................................................Timers

TimersTypically, Forms processes events that are (originally) initiated by the user.You can use timers when you want Forms to perform a set of actions after aperiod of time has elapsed.

What Is a Timer?A timer is a programmatic construct similar to an “internal alarm clock.”You can create, modify, or delete timers by means of built-ins. When youcreate or modify a timer, you can specify the period of time that elapsesbefore the timer “expires.” Using a trigger, you can specify what actionsmust be performed at that time.

Built-in Functions for Timers

When-Timer-Expired TriggerThis trigger fires when a timer expires; that is, when the specified timeinterval of the timer has elapsed.

Uses of Timers• Polling the database to check if a certain event has occurred

• Performing an automatic query at regular intervals

• Showing “About this...” information at form startup

• Performing an automatic commit or rollback after a specific amount ofidle time

Built-in DescriptionFIND_TIMER Returns the internal timer ID (of datatype TIMER)

of a timer with the given nameCREATE_TIMER Creates a timer with the given name (You can

specify the time interval and whether the timershould repeat on expiration.)

SET_TIMER Changes the settings for the given timer (You canmodify the time interval and the repeat behavior.)

DELETE_TIMER Deletes the given timerGET_APPLICATION_PROPERTY The TIMER_NAME property returns the name of

the most recently expired timer.




Handling Timer ExpirationTimer Acreated

Timer Bcreated

Start

Timer Aexpired

Repeat

A

When-Timer-Expired (A)Query issued

Timer Bexpired B

Timer Aexpired

Repeat

B,A

Querycomplete

When-Timer-Expired (B)

When-Timer-Expired (A)


......................................................................................................................................................Handling Timer Expiration

Handling Timer ExpirationWhen a timer expires, the When-Timer-Expired trigger fires and executesthe desired actions. If you define more than one timer, you need to knowwhich timer expired and how expired timers are handled.

Timer QueueWhen a timer expires, it is put in a queue of expired timers. Forms servicesthis timer queue on a first-in-first-out basis, but only while it is waiting foruser actions. After an expired timer is handled, it is removed from the queue.

Note: A repeating timer will not begin the next iteration while it is still inthe timer queue.

When-Timer-Expired TriggerWhen using the When-Timer-Expired trigger remember that it

• Fires once for each timer that expires, but only after Form Builder hascompleted any current processing of triggers and built-in functions

• Fires after the specified time interval, rather than exactly on the momentof expiration

• Must be defined at the form level

• Should include the GET_APPLICATION_PROPERTY built-in functionto find out which timer has expired

ExampleHandle the expiration of two timers named HOUR_ALARM andABOUT_STARTUP.

DECLAREv_timer_name VARCHAR2(30);

BEGINv_timer_name := GET_APPLICATION_PROPERTY(timer_name);IF v_timer_name = ’HOUR_ALARM’ THEN

MESSAGE(’One hour has passed again.’);ELSIF v_timer_name = ’ABOUT_STARTUP’ THEN

DELETE_TIMER(’ABOUT_STARTUP’);END IF;

END;




Creating a Timer

Syntax:

Example:

CREATE_TIMER (timer_name, milliseconds, iterate)

v_timer_id := CREATE_TIMER (’hour_alarm’, cst_hour);


......................................................................................................................................................Creating a Timer

Creating a TimerYou can create a timer by using the CREATE_TIMER built-in function,which returns type TIMER.

Syntax

ExampleAt form startup, create a timer named HOUR_ALARM that expires everyhour.

CREATE_TIMER(timer_name, milliseconds, iterate)

Parameter Descriptiontimer_name The Timer namemilliseconds The duration of the timer in milliseconds (Value must be

between 1 and 2147483648 (approximately 25 days).)iterate Specifies whether the timer should repeat upon expiration

(Valid values are REPEAT (default) and NO_REPEAT.)

DECLAREcst_hour constant NUMBER(7) := 3600000;--3600000 is one hour in millisecondsv_timer_id TIMER;

BEGINv_timer_id := CREATE_TIMER(’hour_alarm’, cst_hour);

END;




Modifying a Timer

Syntax:

Example:

SET_TIMER (timer_name, milliseconds, iterate)

SET_TIMER (timer_id, milliseconds, iterate )

SET_TIMER (’hour_alarm’, no_change, no_repeat);


......................................................................................................................................................Modifying a Timer

Modifying a TimerYou can modify a timer by using the SET_TIMER built-in procedure.

Syntax

ExampleSet the repeat behavior of a timer named HOUR_ALARM without changingthe time interval. The trigger name depends on the situation.

SET_TIMER(timer_name, milliseconds, iterate)SET_TIMER(timer_id, milliseconds, iterate)

Parameter Description

timer_name The timer name

timer_id The internal timer ID

milliseconds The duration of the timer in milliseconds (Value must be between 1and 2147483648 (approximately 25 days) or must beNO_CHANGE.)

iterate Specifies whether the timer should repeat upon expiration (Validvalues are REPEAT (default), NO_REPEAT, and NO_CHANGE.)

BEGINSET_TIMER(’hour_alarm’, no_change, no_repeat);

END;




Deleting a Timer

Syntax:

Example:

DELETE_TIMER (timer_name)

DELETE_TIMER (timer_id)

...

IF NOT ID_NULL (FIND_TIMER (’hour_alarm’)) THEN

DELETE_TIMER (’hour_alarm’);

END IF;

...


......................................................................................................................................................Deleting a Timer

Deleting a TimerYou can delete a timer by using the DELETE_TIMER built-in procedure.

Syntax

Note: Forms generates an error if you attempt to delete a nonexistant timer.

ExampleDelete a timer named HOUR_ALARM after first checking that it exists. Thetrigger name depends on the situation.

DELETE_TIMER(timer_name)DELETE_TIMER(timer_id)

Parameter Description

timer_name The timer name

timer_id The internal timer ID

BEGINIF NOT ID_NULL(FIND_TIMER(’hour_alarm’)) THEN

DELETE_TIMER(’hour_alarm’);END IF;

END;




Summary

• A timer is a programmatic construct

• Built-in functions for timers:

– FIND_TIMER

– CREATE_TIMER

– SET_TIMER

– DELETE_TIMER

– GET_APPLICATION_PROPERTY(TIMER_NAME)


Summary

• Using timers

• Handling timer expiration:

– Timer queue

– When-Timer-Expired trigger


......................................................................................................................................................Summary

SummaryA timer is a programmatic construct much like an “internal alarm clock.”

Built-ins for Timers• FIND_TIMER

• CREATE_TIMER

• SET_TIMER

• DELETE_TIMER

• GET_APPLICATION_PROPERTY(TIMER_NAME)

Uses of Timers• Polling the database to check if a certain event has occurred

• Performing an automatic query at regular intervals

• Showing “About this...” information at form startup

• Performing an automatic commit or rollback after a specific amount ofidle time

Handling Timer Expiration• When a timer expires, it is put in a first-in-first-out timer queue.

• The When-Timer-Expired trigger fires once for each timer that expires,but only after Forms has completed any current processing.





• Showing “ About... ” information at formstartup

• Periodically checking to see if there arelocked records and asking the user tocommit or rollback



Practice Session Overview: Lesson 12This practice guides you through the implementation of time-initiatedprocessing. You will use the appropriate timer built-ins and triggers to createtimers and handle timer expiration.

Practice Contents• Show “About...” information at form startup.

• If table rows are locked, display an alert that asks the user to commit orrollback changes after a set period of time.



Practice Session: Lesson 121 Show «About...» information at the startup of the form.


b Add a window and a canvas to the form that are used to display thetwo control items. This window could be considered an «About...»window and should be a modal dialog window. Set the Hide on Exitproperty to Yes for this window.Both the window and its canvas are called ABOUT. An «About...»window should be a modal dialog window.

c Create a new control block manually, called ABOUT. Create twoitems in the ABOUT block that are used to display the user name andthe current date and time.The control block is called ABOUT and the items are calledUSER_NAME and CLIENT_DATE.

d Show the «About...» window for a short period of time at the startupof the form. You can import thepr12_1d1sqlandpr12_1d2.txtfiles.

If you have time...

2 Automatically ask the users if they want to commit after a set period oftime.


b At the startup of the form, create a global variable calledGLOBAL.LOCKS_PENDING, which indicates at all times whetherrows of the S_CUSTOMER table are locked.

c Create the On-Lock trigger to implement the default lock processing,update the global variable, and create the timer. You can import thepr12_2c.txtfile.

d Write a When-Timer-Expired trigger to display an alert if locks arestill pending after a certain period of time elapses. This alert shouldask the user to commit or rollback the changes. You can import thepr12_2d.txtfile.

Note: You need to create an alert called ASK_SAVE.

Define an alert (called ASK_SAVE) of style Caution with a Yes, and aNo button. Define an appropriate message.

e Create the Post-Database-Commit trigger to give the NULL value tothe global variable.You can import thepr12_2e.txtfile.



f Create the On-Rollback trigger to implement the default rollbackprocessing and give the NULL value to the global variable. You canimport thepr12_2f.txtfile.

................................

13

Using ReusableComponents


......................................................................................................................................................Lesson 13: Using Reusable Components


Objectives


• List the reusable components

• Include the calendar class in anapplication


......................................................................................................................................................Introduction

Introduction

OverviewIn this lesson, you will learn about the reusable components that areavailable with Developer/2000. You will learn how these components can fitinto any application.


• List the reusable components

• Include the calendar class in an application




Reusable Components

I’m the Calendar

I’m the Navigator

My Form


......................................................................................................................................................Reusable Components

Reusable ComponentsDeveloper/2000 contains a complete set of demonstrations that illustrate thepower and productivity of the product. These demonstrations contain manytips and techniques that you can copy and use in your own applications.

Reusable ComponentA reusable component is a generic object, (object group, block, PL/SQLlibrary, and so on), that you can reuse in all your applications. You can createyour own reusable components (code and object) using the genericprogramming characteristics.

This release also include a number of reusable components that allow you toeasily and rapidly build applications that provide power and performance,while conforming to GUI standards. These components are customizable.

Technical NoteSome of the demonstrations and reusable components require PL/SQLfeatures that are only supported in Oracle7 release 7.3 or later. If yourdatabase does not support these features, certain packages will be createdbut marked as “Invalid.” If you try to run a demonstration that requires theseobjects, you will see a warning that the packages are invalid.




Reusable Components List

• ActiveX controls

• Calendar class

• Navigator class

• Online Help class

• Picklist class

• Wizard class

• Drag & Drop PL/SQL Library

• Form Builder Utilities PL/SQL Library


......................................................................................................................................................Reusable Components List

Reusable Components ListThe reusable components include:

Reusable Component Description

ActiveX Controls An ActiveX control is a standalone software component thatyou can include in your Form Builder application. FiveActiveX controls are shipped with Developer/2000: Image,Sound, Spread Table, Tabsheet, and a Video object.

Calendar Class This component allows you to add a calendar or date list ofvalues to your application.

Navigator Class This component allows you to add an Explorer-styleinterface to your application. This component is very similarto the Object Navigator.

Online Help Class This component enables you to easily add a Help system toan application, and to provide functionality that mimics theWindows 95 Help system. It allows you to provide context-sensitive Help to the end users. The Help text is stored in thedatabase so that it can be easily shared among all users,making updates and changes immediately available toeverybody.

Picklist Class This component enables you to easily add a picklist orchooser-style interface to an application, and to providefunctionality that mimics the picklists of wizards and otherdialogs.

Wizard Class This component enables you to easily add a wizard-styleinterface to an application, and to provide functionality thatmimics the wizards of the Developer/2000 builders and otherWindows 95 products.

Drag & Drop PL/SQLLibrary

This PL/SQL library provides simple procedures to helpimplement drag and drop, or direct manipulation interfaces.

Form Builder UtilitiesPL/SQL Library

This PL/SQL library contains a procedure to write out thecontents of a block.




Reusable Components List

• Conversion PL/SQL Library

• Window System Interface PL/SQLLibrary

• Standard Object Library

• Oracle Applications Object Library

• Standard Menus

• Sample Icons


......................................................................................................................................................Reusable Components List

Reusable Component Description

Conversion PL/SQLLibrary

This PL/SQL library contains procedures to performcomplex conversions.

Window System InterfacePL/SQL Library

This PL/SQL library contains many commonly used routinesspecific to the Windows environment. The utility consists ofa PL/SQL library and a Windows DLL and can be used fromany of the Developer/2000 builders.

The PL/SQL library uses the ORA_FFI package and can beused as a model for creating your own DLL calls. The utilityFFI_GEN is also available to help you generate a PL/SQLinterface to DLLs.

Standard Object Library The standard object library, STNDRD20.OLB, containsstandard classes and visual attribute groups to help you buildapplications that conform to a standard look and feel. Thestandards can be used as provided, or you may choose toextend or modify them for your own requirements. Thesestandards are designed to provide a Windows-95-style lookand feel. Where appropriate, the objects in the library are setas SmartClasses so that they can be readily applied to theobjects that you create in your forms.

Oracle Applications ObjectLibrary

This object library, APPSSTDS.OLB, contains propertyclasses and visual attribute groups that enable you to buildmodules that have the same look and feel as Oracleapplications. These standards have a Windows-95 look andfeel, but they have also been designed for maximum portabil-ity.

Standard Menus These demonstrations include a number of standard menusthat you may wish to reuse and customize.

Sample Icons Sample icons can be found inORACLE_HOME\TOOLS\DEVDEM20\DEMO\BIN. Thisdirectory includes all of the icons used in the demonstrationsand sample menus.




The Calendar Class


Creating a Calendar

To create a calendar:

1. Copy or make the Calendar Object Group a subclass.

2. Attach the PL/SQL library CALENDAR.PLL.

3. Create a Key-Listval trigger.

4. Call Date_LOV.Get_Date.


......................................................................................................................................................The Calendar Class

The Calendar ClassThis component enables you to easily add a calendar or date list of values toan application. The Calendar window will automatically display thecalendar according to the NLS settings that are currently in effect, adjustingthe start of the week (for example, Sunday in the United States, Monday inWestern Europe) and the month and day names accordingly.

Creating a CalendarTo create a calendar, follow the steps below:

1 From the Components page of the STNDRD20.OLB object library, copyor subclass the Calendar object group.

2 Attach the PL/SQL library CALENDAR.PLL, which contains theDate_LOV package, to your module.

3 Create a Key-Listval trigger on the date item for which you would like touse the Date List of Values window. Add code to display the calendarusing the Date_LOV package.

4 If you want the end user to be able to close the Date List of Valueswindow by clicking the window close button in the title bar, create aform-level When-Window-Closed trigger.

The calendar will appear whenever the end user invokes a list of values forthe date item. A single calendar can be reused on many different date items.Simply create a Key-Listval trigger for each item.




Calendar Object Group Content


......................................................................................................................................................Calendar Object Group Content

Calendar Object Group ContentThe component contains many different Form Builder objects. You cancustomize those objects for your specific application.

The BlocksThe calendar object contains two blocks:

• Date_Control_Block

This block contains the buttons that control which month and year isdisplayed.

• Date_Button_Block

This block contains the buttons that represent each day of the month.

The CanvasThe calendar class contains a content canvas named Date_Lov_Canvas. Thiscanvas contains all the items from both blocks.

The WindowThe calendar class contains a window named Date_Lov_Window. Thiswindow is used to display the Date_Lov_Canvas.

The Visual AttributesThe calendar class contains three visual attributes:

• Date_Normal_VA

This visual attribute contains the attributes for a date button that isdisplayed normally.

• Date_Selected_VA

This visual attribute contains the attributes for a date button that isdisplayed as selected.

• Date_Weekend_VA

This visual attribute contains the attributes for a date button that isdisplayed as a weekend day.




Built-in for Manipulatingthe Calendar

Use Date_LOV.Get_Date to display thecalendar

• Syntax

• Exampledate_lov.get_date(sysdate, ' s_ord.date_ordered ' ,240,

60, ' Order Date ' , ' OK' , ' Cancel ' ,TRUE,FALSE,FALSE);

date_lov.get_date( display_date,return_item,

window_x_pos,window_y_pos,window_title,ok_label,

cancel_label,highlight,autoconfirm,autoskip );


......................................................................................................................................................Built-in for Manipulating the Calendar

Built-in for Manipulating the CalendarTo use the calendar class, you have to attach a PL/SQL library calledcalendar.pll. This library contains a package called DATE_LOV. Thispackage contains routines that the developer uses to interact with thecomponent.

Date_LOV.Get_Date ProcedureThis procedure displays calendar with the month and year specified in theDisplay_Date argument. The day of the month is displayed with boldnumbers. The date chosen by the end user is returned to the item specifiedby the Return_Item argument.

. . .PROCEDURE Date_LOV.Get_Date(display_date DATE, return_item VARCHAR2,window_x_pos NUMBER := 0, window_y_pos NUMBER := 0,window_title VARCHAR2 := ’Date List of Values’,ok_label VARCHAR2 := ’OK’,cancel_label VARCHAR2 := ’Cancel’, highlight BOOLEAN := TRUE,autoconfirm BOOLEAN := TRUE, autoskip BOOLEAN := FALSE );. . .

Argument Description

display_date The date to display when the Date List of Values window first appears

return_item The name of the block and item to return the chosen date to

window_x_pos Specifies the X coordinate of the Date List of Values window

window_y_pos Specifies the Y coordinate of the Date List of Values window

window_title Title to display in the Date List of Values window

ok_label Label for the OK button

cancel_label Label for the Cancel button

highlight Specifies that weekend days will appear in a different color

autoconfirm Specifies that the date will immediately be returned when the end userclicks a day

autoskip Specifies that the input focus moves to the next item in sequence




Summary

• Reusable components

• Calendar class:– Create a calendar class

– Built-in to manipulate the calendarclass


......................................................................................................................................................Summary

Summary

Reusable Components• The purpose of a reusable component

• Reusable components list

The Calendar Class• Create a calendar

• Calendar Object Group content

• Built-in for manipulating the calendar





• Creating a picklist to display and selectthe products available

• Adding a calendar on items:

– s_ord.date_ordered

– s_ord.date_shipped



Practice Session Overview: Lesson 13This practice guides you through some useful reusable components.

Practice Contents• Create a picklist to display and select the products available.

• Add a calendar on the s_ord.date_ordered and s_ord.date_shipped items.



Practice Session: Lesson 131 Create a picklist using the picklist class. This picklist allows the end user

to make selections using two lists, one showing the products availableand the other showing the objects selected.

Note: To reuse the code you wrote in practice 10 question 2, we haveprovided most of the code by way of an object library.

a Create a new form module.

b Open theForm_Builder_II.olb object library.

c From the Picklist Basic tab page, drag and copy thePICKLIST_BASIC object group to the Object Groups node in yourmodule and release it. This object group contains a canvas, awindow, and a block. The block contains two buttons, with the codeto return the products selected in the picklist to the orders form.

d From thePicklist Class tab page, copy the PICKLIST object groupinto your new form module. This object group contains all theobjects to implement the picklist. Organize the CONTROL block soit is the last block in sequence.

e Attach thepicklist.pll library to your form.

f Create a When-New-Form-Instance trigger to populate the List_In.Create an instance of the Picklist with theCreate_Picklistprocedure.Populate the List_in, usingpopulate_picklist_with_query function,with the results of a query that returns the Ids, Names and suggestedprices from the S_PRODUCT table (Retrieve the list of productsexcluding the products that exists in the S_ITEM block. You shoulduse the global variable from practice 11). Select the first element inthe list using theSet_picklist_selection procedure, and display bothlists usingdisplay_picklistprocedure. You can import thepr13_1f.txtfile.

g Save, and compile your form.

h Open the ORDERS form module.

i Modify the When-Button-Pressed trigger for theCONTROL.PRODUCT_LOV_BUTTON so that it calls your newform module.

j Save, run, and test your form.



If you have time...

2 In the ORDERS form, add a calendar on the S_ORD.DATE_ORDEREDitem and S_ORD.DATE_SHIPPED item.

a From theCalendar page of the Form_Builder_II.olb object library,subclass theCalendar object group.

b Attach the PL/SQL librarycalendar.pll, which contains theDate_LOV package, to your module.

c Create a Key-Listval trigger on the date ordered item. Add code todisplay the calendar using the Date_LOV package. You can importthepr13_2c.txt file.

d Create a Key-Listval trigger on the date shipped item. Add code todisplay the calendar using the Date_LOV package.You can importthepr13_2d.txt file.


................................

14

Using Server Features inForm Builder


......................................................................................................................................................Lesson 14: Using Server Features in Form Builder


Objectives

After completing this lesson, you shouldbe able to do the following:• Describe Oracle Server features in Form

Builder• Partition PL/SQL program units• Handle errors raised by the Oracle

Server• Obtain the cause of declarative-

constraint violations• Perform DDL commands using the

FORM_DDL built-in subprogram


......................................................................................................................................................Introduction

Introduction

OverviewThis lesson covers the use of Oracle Server features in Form Builderapplications. You will learn about storing and calling PL/SQL code,handling Oracle Server errors, and issuing DDL commands from withinforms.

ObjectivesAt the end of this lesson, you should be able to:

• Describe Oracle server features in Form Builder

• Partition PL/SQL program units

• Handle errors raised by the Oracle server

• Obtain the cause of declarative-constraint violations

• Perform DDL commands using the FORMS_DDL built-in subprogram




Using Oracle ServerFunctionality in Forms

• Useful Oracle Server Features:

– Declarative constraints

– Stored program units

– Database triggers

– DDL


......................................................................................................................................................Using Oracle Server Functionality in Forms

Using Oracle Server Functionality in FormsOracle database offers several powerful features to implement functionalityin the Oracle Server. You can call that functionality in your forms to handlepossible errors.

Oracle Server Features Useful for FormsThe following useful features are available to application developers:

• Declarative integrity constraints

• Stored program units: procedures, functions, and packages

• Built-in database packages

• Database triggers

• Database roles

• DDL

• SQL Optimizer; shared SQL

• Locking

• Sequences

Some of these features are discussed in more depth later in this lesson.




Dealing with PL/SQL Code

• Where to store PL/SQL code:

– On the Oracle Server

– In the attached PL/SQL libraries

– In the form itself

• Where to call PL/SQL code:

– From database triggers

– From forms triggers

• Application partitioning


......................................................................................................................................................Dealing with PL/SQL Code

Dealing with PL/SQL CodeYou can store and call your code in two places: the Oracle Server or theapplication.

Where to Store PL/SQL Code?To maintain your code, you should not store the code in more locations thannecessary. The places to store PL/SQL code, in order of preference, are asfollows:

1 The Oracle Server

2 Attached PL/SQL libraries

3 The form itself

Note: PL/SQL code cannot be stored in the Oracle Server if it containsvariables or built-ins of Forms.

Where to Call PL/SQL Code?PL/SQL code is eventually called from triggers. Also, for reasons of bettermaintainability, the places to call PL/SQL code, in order of preference, areas follows:

1 Database triggers

2 Forms triggers

Note: Database triggers fire only at commit time. If you want to giveimmediate feedback to the user, you must use forms triggers.

Application PartitioningFor enhanced performance, you may want to call or store PL/SQL code onthe client side. In Forms, you can drag and drop PL/SQL program unitsbetween the Oracle Server and the application. Therefore, you can divideapplications over the available resources in an optimal way. This is calledapplication partitioning.




Causes of Oracle Server Errors

Base table block

Implicit DML

Trigger/PU

Explicit DML

Stored PU call

Declarativeconstraint

Databasetrigger

Storedprogram unit

Form Oracle Server


......................................................................................................................................................Handling Errors Raised by the Oracle Server

Handling Errors Raised by the Oracle ServerOracle Server errors can have different causes. For example, a declarativeconstraint or a stored program unit can cause them. You should know how tohandle errors that may occur in different situations.

Causes of Oracle Server Errors

Types of DML StatementsDeclarative-constraint violations and firing of database triggers are in turncaused by DML statements. For error-handling purposes, you need to beable to distinguish between the following two types of DML statements:

Cause Error MessageDeclarative constraint Causes predefined error message.Database trigger Error message is specified in

RAISE_APPLICATION_ERROR.Stored program unit Error message is specified in

RAISE_APPLICATION_ERROR.

Type DescriptionImplicit DML DML statements that are associated with base table blocks.

Implicit DML is also calledbase table DML. By default,Forms constructs and issues these DML statements.

Explicit DML DML statements that a developer explicitly codes in triggersor program units.




Trapping Server Errors

Base table block

On-Error:DBMS_ERROR_CODEDBMS_ERROR_TEXT

Explicit DML/PU call

When Others:SQLCODESQLERRM

Constraint

DB trigger

Stored PU

Predefinedmessage

RAISE_APPLICATION_

ERROR

RAISE_APPLICATION_

ERROR

Form Oracle Server


......................................................................................................................................................Handling Errors Raised by the Oracle Server

How to Trap Different Types of Oracle Server Errors

Note: Declarative-constraint violations and database triggers may becaused by both implicit DML and explicit DML. Stored program units arealways called explicitly from a trigger or program unit.

Technical NoteThe values of DBMS_ERROR_CODE and DBMS_ERROR_TEXT are notautomatically reset following successful execution.

FRM-Error Messages Caused by Implicit DML ErrorsIf an implicit DML statement causes an Oracle Server error, Forms displaysone of these FRM-error messages:

• FRM-40508: ORACLE error: unable to INSERT record.

• FRM-40509: ORACLE error: unable to UPDATE record.

• FRM-40510: ORACLE error: unable to DELETE record.

You can use ERROR_CODE to trap these errors in an On-Error trigger andthen use DBMS_ERROR_CODE and DBMS_ERROR_TEXT to determinethe ORA-error code and message.

Note: The errors reported by the DBMS_ERROR_CODE andDBMS_ERROR_TEXT built-ins are the same as a user would see afterpressing [Display Error].

Type Error HandlingImplicit DML Use the Forms built-ins DBMS_ERROR_CODE and

DBMS_ERROR_TEXT in an On-Error trigger.Explicit DML Use the PL/SQL functions SQLCODE and SQLERRM in a

WHEN OTHERS exception handler of the trigger or programunit that issued the DML statements.

Stored program unit Use the PL/SQL functions SQLCODE and SQLERRM in aWHEN OTHERS exception handler of the trigger or programunit that called the stored program unit.




Obtaining the Cause ofDeclarative-Constraint Violation• Predefined error messages for

declarative constraints

– Primary/unique key; foreign key;check

– ORA-02291: integrity constraint(schema.constraint) violated—parentkey not found

• Obtaining the name of violatedconstraint

– Function STRIP_CONSTRAINT_NAME


......................................................................................................................................................Obtaining the Cause of Declarative-Constraint Violations

Obtaining the Cause of Declarative-Constraint ViolationsTo handle declarative-constraint violations effectively, you must use thepredefined error message to determine the constraint that was violated.

Predefined Error Messages for Declarative Constraints

Obtaining the Name of the Violated ConstraintYou can use string functions, like the one following, to strip out the name ofthe violated constraint from the Oracle Server error message.

Constraint Error MessagePrimary Key ORA-00001: unique constraint (schema.constraint) violated

Unique KeyForeign Key ORA-02291: integrity constraint (schema.constraint) violated—parent

key not foundORA-02292: integrity constraint (schema.constraint) violated—child record found

Check ORA-02290: check constraint (schema.constraint) violated

FUNCTION strip_constraint_name (p_error_text in VARCHAR2)RETURN VARCHAR2IS

v_start_pos NUMBER(4);v_end_pos NUMBER(4);

BEGINv_start_pos := INSTR(p_error_text, ’.’, INSTR(p_error_text, ’(’));v_end_pos := INSTR(p_error_text, ’)’, v_start_pos);RETURN(SUBSTR(p_error_text, v_start_pos + 1,v_end_pos -

v_start_pos - 1));END;



Technical NoteA foreign-key constraint violation can have two causes: “parent key notfound” or “child record found.” Therefore, two error messages areassociated with one foreign-key constraint name.


Customizing Oracle Server ErrorMessages

• Replacing declarative-constraint errormessages:

– Strip the constraint name fromDBMS_ERROR_TEXT or SQLERRM

– Retrieve the customized messagefrom the messages table


Customizing Oracle Server ErrorMessages

• Replacing database-trigger errormessages:– Strip the application error messages

from DBMS_ERROR_TEXT orSQLERRM

– FunctionSTRIP_APPLICATION_ERROR

• Replacing stored-program-unit errormessages– Same as for database triggers, except

SQLERRM only


......................................................................................................................................................Customizing Oracle Server Error Messages

Customizing Oracle Server Error Messages

Replacing Error Messages Caused by Declarative-Constraint ViolationsThe error is reported in DBMS_ERROR_TEXT (implicit DML) orSQLERRM (explicit DML). Once you have obtained the name of theviolated constraint from the Oracle Server error message, you can use thisname to specify an appropriate customized message. A flexible approach forthis is to maintain a table of messages. You can then use the constraint nameto retrieve the corresponding message from this table.

Replacing Error Messages Caused by Database TriggersThe error is reported in DBMS_ERROR_TEXT (implicit DML) orSQLERRM (explicit DML). The first part of the error message is themessage specified in RAISE_APPLICATION_ERROR in the databasetrigger, which is already a customized message. You can strip out thisapplication-error message from the Oracle Server error message using thefollowing function:

Replacing Error Messages Caused by Stored Program UnitsThe error is reported in SQLERRM only. The customized message isobtained in the same way as for database triggers.

FUNCTION strip_application_error (p_error_text in VARCHAR2)RETURN VARCHAR2IS

v_end_pos NUMBER(4);BEGIN

v_end_pos := INSTR(p_error_text, ’ORA-’, 5) - 2;IF v_end_po s > 0 THEN

RETURN(SUBSTR(p_error_text, 12, v_end_pos - 11));ELSE

RETURN(SUBSTR(p_error_text, 12));END IF;

END;




Example Procedure for HandlingOracle Server Errors

• If the Oracle Server error is adeclarative-constraint error:

– Strip the constraint name from theOracle Server error text.

– Adjust the name of the foreign-keyconstraints.

– Retrieve message from the messagestable.



• If the Oracle Server error is anapplication error:

– Strip the application error from theOracle Server error text.

• Raise FORM_TRIGGER_FAILURE.


......................................................................................................................................................Example Procedure for Handling Oracle Server Errors

Example Procedure for Handling Oracle Server Errors

Note: The STRIP_CONSTRAINT_NAME andSTRIP_APPLICATION_ERROR functions have already been shown earlierin this lesson. The RETRIEVE_MESSAGE function retrieves a messagewith a given ID from a messages table.

Call the HANDLE_SERVER_ERROR procedure from the appropriatetriggers and program units.

PROCEDURE handle_server_error (p_server_error_code IN NUMBER,p_server_error_text IN VARCHAR2)

ISv_message_id VARCHAR2(255);

BEGIN -- handle_server_errorIF p_server_error_code IN (-1, -2290, -2291, -2292) THEN

/* PK/UK/FK/CK-constraint violation error. */v_message_id := strip_constraint_name(p_server_error_text);/* Foreign-key constraint has two associated error

messages.*/IF p_server_error_code = -02291 THEN

/* Foreign-key violation - parent key not found. */v_message_id := v_message_id || ’_PARENT’;

ELSIF p_server_error_code = -02292 THEN/* Foreign-key violation - child record found. */v_message_id := v_message_id || ’_CHILD’;

END IF;MESSAGE(retrieve_message(v_message_id));RAISE form_trigger_failure;

ELSIF p_server_error_code between -20999 and -20000 THEN/* Application error caused by database trigger or stored

program unit. */MESSAGE(strip_application_error(p_server_error_text));RAISE form_trigger_failure;

END IF;END handle_server_error;



Technical NoteError code 40508 is insert failed. Error code 40509 is update failed. Errorcode 40510 is delete failed.


Handle errors caused by implicit DMLOn-Error trigger on form:

Handle errors caused by explicit DML orstored PUs:

EXCEPTIONWHEN OTHERS THEN

handle_server_error(SQLCODE, SQLERRM);


IF ERROR_TYPE = ’FRM’ ANDERROR_CODE IN (40508, 40509, 40510) THENhandle_server_error(DBMS_ERROR_CODE,

DBMS_ERROR_TEXT);END IF;


......................................................................................................................................................Example Procedure for Handling Oracle Server Errors

Handling Oracle Server Errors Caused by Implicit DMLAlways define an On-Error trigger to handle Oracle Server errors caused bybase table DML.

On-Error Trigger at Form Level

Handling Oracle Server Errors Caused by Explicit DML or StoredProgram Unit CallsDefine a WHEN OTHERS exception handler in all triggers and programunits that execute explicit DML or call stored program units.

BEGINIF ERROR_TYPE = ’FRM’ AND ERROR_CODE IN (40508, 40509, 40510) THEN

handle_server_error(DBMS_ERROR_CODE,DBMS_ERROR_TEXT);END IF;/* Show default FRM-error message. */MESSAGE(ERROR_TYPE || TO_CHAR(-ERROR_CODE) || ’: ’ || ERROR_TEXT);RAISE form_trigger_failure;

END;

BEGIN/* DML statements or calls to stored program units. */EXCEPTION

WHEN others THENhandle_server_error(SQLCODE, SQLERRM);

END;




Performing DDL withFORMS_DDL

• Syntax: FORMS_DDL (statement);

• Parameter (<=32 K):PL/SQL block, DMLor DDL statement

• Characteristics:

– Unrestricted; valid in Enter Querymode

– Statement must not contain bind-variable references



• Characteristics (continued):

– Statement cannot return resultsdirectly

– Use FORM_SUCCESS to checksuccess of statement

• Hints:

– Query record group instead ofFORMS_DDL with SELECT

– Call stored program unit instead ofFORMS_DDL


......................................................................................................................................................Performing DDL with FORMS_DDL

Performing DDL with FORMS_DDLForms is primarily intended to perform DML operations. However, in somecases you may find it useful to issue DDL statements from a form. You can dothis using the FORMS_DDL built-in function.

Syntax

DescriptionFORMS_DDL issues dynamic SQL statements at run-time, including server-sidePL/SQL and DDL.

ParameterThe specified statement can be any string expression up to 32K representing a:

• PL/SQL block

• DML statement

• DDL statement

Do not end the PL/SQL block with a slash or the DML or DDL statement with asemicolon.

CharacteristicsFORMS_DDL is an unrestricted procedure that is also valid in Enter Querymode.

• The specified statement must not contain bind-variable references. However,you can concatenate the values of bind variables into the specified string.

• The statement executed using FORMS_DDL cannot return results to Formsdirectly.

• Use the FORM_SUCCESS built-in to check whether the statement issuedusing FORMS_DDL executed correctly.

Note: Consider using a query record group with a SELECT statement or callinga stored program unit, instead of executing FORMS_DDL.

FORMS_DDL(statement);




• Example: Create temporary table

• Example: Execute procedure with givennameFORMS_DDL (‘BEGIN ‘ || p_proc_name ||‘; END; ‘);IF not FORM_SUCCESS THEN

handle_server_error(DBMS_ERROR_CODE, DBMS_ERROR_TEXT);

END IF;


FORMS_DDL (‘CREATE table temp (n_col number)‘);IF not FORM_SUCCESS THEN

MESSAGE (‘Table creation failed.‘);RAISE form_trigger_failure;

END IF;


......................................................................................................................................................Performing DDL with FORMS_DDL

The following examples show how you can use the FORMS_DDL built-infunction.

ExampleCreate a temporary table at the start of a post.

Pre-Commit Trigger at Form Level

ExampleExecute a procedure with a given name. This is useful if you want todetermine dynamically which procedure should be executed in a certainsituation.

Note: If the FORMS_DDL built-in fails, Forms sets theDBMS_ERROR_CODE and DBMS_ERROR_TEXT built-ins. Therefore,you can handle Oracle Server errors using the HANDLE_SERVER_ERRORprocedure discussed earlier.

Always test the SYSTEM.FORM_STATUS before calling theFORMS_DDL built-in.

BEGINFORMS_DDL(’CREATE table temp(n_col number)’);IF not FORM_SUCCESS THEN

MESSAGE (’Table creation failed.’);RAISE form_trigger_failure;

END IF;END;

PROCEDURE exec_proc (p_proc_name IN VARCHAR2)ISBEGIN

FORMS_DDL(’BEGIN ’|| p_proc_name ||’; END;’);IF not FORMS_SUCCESS THEN

handle_server_error(DBMS_ERROR_CODE,DBMS_ERROR_TEXT);END IF;

END;




Summary

• Oracle Server features useful for FormBuilder

• Dealing with PL/SQL code

• Trap Errors raised by the Oracle Server

– Implicit DML:DBMS_ERROR_CODE/TEXT in On-Error

– Explicit DML or stored program units:SQLCODE/ERRM in WHEN OTHERS


Summary

• Replacing Oracle Server error messageswith customized messages

– Constraint: strip constraint name andretrieve message

– DB triggers and stored programunits: strip application error

• Performing DDL with FORMS_DDL


......................................................................................................................................................Summary

Summary

Oracle Server Features Useful for Form Builder• Declarative integrity constraints,

• Stored program units

• Database triggers

• DDL

Dealing with PL/SQL Code• Store PL/SQL code in

- Oracle Server

- Library

- Form

• Call PL/SQL code from

- Database trigger

- Form trigger

• Application partitioning

Handling Errors Raised by the Oracle Server• Causes: declarative constraints, database triggers, stored program units

• Trap implicit-DML errors by using DBMS_ERROR_CODE andDBMS_ERROR_TEXT in an On-Error trigger

• Trap explicit DML errors and stored program unit errors by usingSQLCODE and SQLERRM in a WHEN OTHERS exception handler

Replacing Oracle Server Error Messages with Customized Messages• Declarative constraint: Strip the constraint name from the Oracle Server

error message and retrieve the message from the messages table

• Database triggers and stored program units: Strip the application errormessage from the Oracle Server error message

Performing DDL with FORMS_DDL• Parameter is a string (less than or equal to 32K) representing PL/SQL

block or DML or DDL statement





• Handling errors caused by declarative-constraint violation

• Handling errors caused by storedprogram units



Practice Session Overview: Lesson 14This practice guides through handling Oracle server errors.

Practice Contents• Handle errors caused by declarative-constraint violations.

• Handle errors caused by stored program units.



Practice Session: Lesson 141 Handle errors caused by declarative constraints violations.

a Open the CUSTOMERS form module. This form is based on theS_CUSTOMER table, and one constraint is declared for this table.

CONSTRAINT s_customer_id_pk PRIMARY KEY(id).

b Allow the user to change the customer id. Set the Insert Allowedproperty to Yes.

c Run the CUSTOMERS form and try to add a new customer with anexisting customer number. What FRM-error message do you get?What Oracle Server error message do you get (press [Display Error]to see the message)?

d Which trigger must you use to trap error messages caused byviolation of this constraint?

e Which built-in functions must you use to get error messages causedby violations of this constraint?

f Trap and replace the default constraint-violation message with yourown message. Use the function STRIP_CONSTRAINT_NAME todetect which constraint was violated. You can import thepr14_1f1.txtfile to create the function and thepr14_1f2.txtfile tocreate the trigger.

Note: Remove the existing code from the trigger before you import the file.

If you have time...

2 Handle errors caused by stored program units.

a Open the CUSTOMERS form module. This form is based on theS_CUSTOMER table, and one constraint is declared for this tables.CONSTRAINT s_customer_id_pk PRIMARY KEY(id).




c Implement immediate primary-key checking in the CUSTOMERSform by creating a procedure called CHECK_PK_CUST. Call thisprocedure from an appropriate trigger. You can import thepr14_2c1.txtfile to create the procedure and thepr14_2c2.txtfile tocreate the trigger.

d Run the form and try to add a new customer with an existingcustomer number. What FRM-error do you get?

e Drag the CHECK_PK_CUST procedure to the database under youruser account.

f Explain the errors that are shown in the Stored Program Unit Editorfor this procedure. Correct the errors by usingRAISE_APPLICATION_ERROR.

g Delete or rename the local CHECK_PK_CUST procedure in yourform. Run the form and try to add a new customer with an existingcustomer number. What FRM-error messages do you get? WhatOracle Server error message do you get (press [Display Error] to seethe message)?

h Which trigger must you use to trap error messages caused by storedprocedures?

i Which built-in functions must you use to get error messages causedby stored procedures?

j Trap and replace the default error message with your own message.Use the function STRIP_APPLICATION_ERROR to get theapplication-error text. You can import thepr14_2j1.txtfile to createthe function and thepr14_2j2.txtfile to create the trigger.

................................

A

Practice Solutions

A-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix A: Practice Solutions

Practice 1-1 Solution1 Create a connection to the database.

a Create a new connection.

Choose File––>New––>Connection.b Name the connection “SUMMIT” and specify the connect string.

Your instructor should provide this information.

Enter Title, Username, Password, and Database.2 Create an initial project.

a Create a new project (use the Project Wizard).

Choose Tools––>Project Wizard.b Name the project “Developer/2000: Build Forms II”. Assign to this

project the connection created earlier, and save it assummit.upd inthe Labs directory.

No formal solution.Note: Do not add files to this project.

3 Create a subproject and add files to it.

a Using the Project Wizard, create a subproject named “SummitApplication” that belongs to “Developer/2000: Build Forms II”.

Choose Tools––>Project Wizard. Choose to create a SubprojectUnder an Existing Project. Enter “Summit Application” in theTitle field. Choose “Developer/2000: Build Forms II” from theParent Project list item. Enter the labs directory used for theclass in the Project Item’s Default Directory field.

b From the labs directory, add:

- All the .tif files

- All the .txt files

- All the .txt files

- All the .ico files

- All the .pll files

- all the.rdf files

- The.olb file

- Thecustomers.fmb, orders.fmb, andemployees.fmb files.

No formal solution.

Developer/2000: Build Forms II A-3......................................................................................................................................................

......................................................................................................................................................Practice 1-1 Solution

c Give some of the file items more meaningful titles.

In the Project Navigator, select and edit the file item’s name.4 Explore the Project Navigator.

a Examine the state of the Project Navigator. Note that Project Builderhas added executable versions of the Oracle source files that youadded to the project.

No formal solution.b Turn off the “Show implicit items” option. Note what happens to the

Project Navigator display.

Choose Tools––>Preferences––>Display and deselect the ShowImplicit Items check box.

c Experiment with Project Builder’s different views.

Select the different views under the Navigator menu.5 Deliver the project.

a Select all your files (except the source files like.fmb, .pll, .txt, and soon), and make sure that the “Deliver file” property is set to Yes.

Select all your files in the Project Navigator. Display the propertypalette and set the “Deliver file” property.

b Select the Summit Application subproject, and define thePACKFILE macro.

In the Project Navigator, select Summit Application node.Display the Property Palette. Under the Macros node, select thePACKFILE macro, and enter a name of a zip file; for example:summit.zip.

c Select the Summit Application subproject, and define the “Deliverthe selected files(s)” action.

In the Project Navigator, select Summit Application node.Display the Property Palette. Under the Action node, select the“Deliver the selected files(s)” action, and enter the following:

d Deliver the project.

Choose File––>Administration––>Deliver.

{Packfile ? pkzip -mrp {Packfile} {1}}



If you have time...

6 Create a subproject and add files to it.

a Using the Project Wizard, create a subproject named “PracticeSolutions” that belongs to “Developer/2000: Build Forms II”.

See previous solution.b From the labs directory, add thecustwkNN.fmb, ordwkNN.fmb, and

empwkNN.fmb files.

See previous solution.c Set the “Deliver file” property to No for those files.

See previous solution.


......................................................................................................................................................Practice 1-2 Solution

Practice 1-2 Solution1 Create new types.

a Add an Icon file type to Project Builder. Derive the type informationfrom thelist.ico file. This type is deliverable.

Choose File––>New––>Type.Select the “Derive type information from file” button.Selectlist.ico file.Select an icon to represent this new type in the Project Navigator.Select the “Deliverable” check box.

b Add a Tif file type to Project Builder. Derive the type informationfrom theAceboot.tif file. This type is deliverable.

See solution for a.c Add a SQL file type to Project Builder. Derive the type information

from theSetup1.sql file. This type is not deliverable.

See solution for a.d Add an OLB file type to Project Builder. Derive the type information

from theForm_Builder_II.olb file. This type is deliverable.

See solution for a.2 Create a new action.

a Add a “Run script” action for the SQL file type. Enter SQL*PLUSinto the Name Field and add an appropriate description (for example:Run Script). The action definition is:

Under User Registry, select the SQL file type.Choose File––>New––>Action.Enter SQL*PLUS in the Name field.Enter Run Script in the Description field.Enter the following Action definition:

ORACONNECT is a macro containing your default connectstring. n is a macro containing the name of the current file.

Plus33w.exe {ORACONNECT} @{n}

Plus33w.exe {ORACONNECT} @{n}



b Select theSetup1.sqlfile, and initiate the action “Run Script”.

In the Project Navigator, select theSetup1.txtfile. Press the rightmouse button. Choose Run Script from the pop-up menu.

3 Customize the Launcher.

a Add the Notepad application to the Launcher. Use the Notepad editoras the launch string (command). Label it Notepad. Add an icon bytyping Notepad editor string in the Icon Field.

Select Tools––>Preferences––>Launcher Tab. Choose Add.Enter Notepad.exe in the Command field. Enter Notepad.exe inthe Icon field. Enter Notepad in the Label field.

b Change the orientation of the Launcher from horizontal to vertical(or from vertical to horizontal, depending on the default orientation).

Select Docked at Left.c Rename the Notepad shortcut icon to “Notes”.

Select the Notepad icon. Choose Edit. Change the label to Notes.


......................................................................................................................................................Practice 2 Solution

Practice 2 Solution1 Create a new menu module.

a Using the following screenshot as a guideline, create a new menumodule called M_SUMMIT.

• Select the Menus node in the Object Navigator and click the Createtool.

• Invoke the Menu Editor. This automatically creates the main menu.• Rename the menu item, New Item to File.

- Click the New Item menu item twice to obtain the text cursor.- Erase the old name.- Enter the name: File.

• Create new menu items under File.- Click the File menu item.- Click the Create Down tool.- Change the name.- Set the properties as follows:



• Create a new menu item to the right of File and rename it Edit.• Click the File menu item.• Click the Create Right tool.

- Name it Edit

LabelMenuItem Type

CommandType Menu Item Code

Visiblein Menu

Visible inHoriz. MenuToolbar

IconFilename

Save Plain PL/SQL DO_KEY(’COMMIT_FORM’);

Yes Yes save

Clear Plain PL/SQL DO_KEY(’CLEAR_FORM’);

Yes Yes abort

Null Separator Null Yes Yes

Exit Plain PL/SQL DO_KEY(’EXIT_FORM’);

Yes Yes exit

Null Separator Null No Yes



• Create new menu items under Edit.- Click the Edit menu item.- Click the Create Down tool.- Change the name.- For each of the items, set the properties as follows:

LabelMenuItem Type

MagicItem

CommandType

Visiblein Menu


IconFilename

Cut Magic Cut Null Yes Yes cut

Copy Magic Copy Null Yes Yes copy

Paste Magic Paste Null Yes Yes paste

Null Separator Null No Yes



• Create a new menu item to the right of Edit and rename it Sort By.- Click the Edit menu item.- Click the Create Right tool.- Rename it: Sort By.

• Create three new menu items under Sort By.- Click the Sort By menu item.- Click the Create Down tool.- Change the name.- Set additional properties as follows:

• For each of the items, set the Menu Item Radio Group property toOrder_By.

• Create a new menu to the right of Sort By and rename it Query.- See previous solution.

• Create two new menu items under Query.- See previous solution.- Set the properties as follows:

Menu LabelMenuItem Type Command Type Menu Item Code

Id Radio PL/SQL SET_BLOCK_PROPERTY(’ s_ord’, order_by,’ id’ ) ;

Customer Id Radio PL/SQL SET_BLOCK_PROPERTY(’ s_ord’, order_by,’ customer_id’ ) ;

Sales Rep Id Radio PL/SQL SET_BLOCK_PROPERTY(’ s_ord’, order_by,’ sales_rep_id’ ) ;



b Save as M_SUMMIT and compile the menu module.

c Attach the menu module to the ORDERS form.

Open the ORDERS form and set the Menu Module property toM_SUMMIT for the form module.

d Save and compile the form module.

e Run the CUSTOMERS form and press the Orders button.

LabelMenuItem Type

CommandType Menu Item Code


IconFilename

Enter-Query

Plain PL/SQL DO_KEY(’ENTER_QUERY’);

Yes query

eXe-cuteQuery

Plain PL/SQL DO_KEY(’EXECUTE_QUERY’);

Yes execute



2 Add pop-up menus to the CUSTOMERS form.


b Copy the EDIT_MENU menu from the M_SUMMIT menu moduleto the CUSTOMERS form.

• Select the EDIT_MENU from the M_SUMMIT menu module.• Click the mouse right button and select Copy.• Select the Pop-up menus node from the CUSTOMERS form

module.• Click the mouse right button and select Paste.• Attach this Pop-up menu to theComments item.

c Save and compile the form module.

d Run the CUSTOMERS form and click the right mouse button on thecomment item.



Practice 3 Solution1 Defining a Check Menu Item.

a Open the M_SUMMIT menu module and create a new menu item tothe right of Query and rename it Image.

See previous solutionb Create a new menu item under Image and name it Image Activate.

See previous solution.Set the properties as follows:

c Import the text for the Image Activate menu item using thepr3_1c.txtfile. The menu item name must be IMAGE_ACTIVATE.

Menu Label Menu Item Type Command Type

Image Activate Check PL/SQL



In the PL/SQL Editor, select File->Import Text. Import thepr3_1c.txtfile.

d Save and compile the menu module.

e Run the CUSTOMERS form and press the Orders button.

2 Defining a User-Named Substitution Parameter.

a Create a user-named substitution parameter called AA in yourM_SUMMIT menu module.

Click the Parameter node and click the Create button.b Import thepr3_2b.txtfile into a procedure called

SALES_REP_INFORMATION. This procedure checks if the salesrep id item is empty. In this case, it uses the user-named substitutionparameter to prompt the user for a sales rep id. Then, the procedure

DECLARE

product_image_id ITEM := FIND_ITEM(’s_item.product_image’ );

image_description_id ITEM :=FIND_ITEM(’s_item.image_description’);

image_button_id ITEM := FIND_ITEM(’control.image_button’ );

BEGIN

IF GET_ITEM_PROPERTY(product_image_id,displayed) = ’TRUE’ THEN

SET_ITEM_PROPERTY( product_image_id, DISPLAYED,PROPERTY_FALSE);

SET_ITEM_PROPERTY( image_description_id, DISPLAYED,PROPERTY_FALSE );

SET_ITEM_PROPERTY( image_button_id, LABEL, ’Image On’);SET_MENU_ITEM_PROPERTY( ’image_menu.image_activate’, CHECKED,

PROPERTY_FALSE );

ELSE

SET_ITEM_PROPERTY(product_image_id, DISPLAYED, PROPERTY_TRUE );

SET_ITEM_PROPERTY(image_description_id, DISPLAYED,PROPERTY_TRUE );

SET_ITEM_PROPERTY(image_button_id, LABEL, ’Image Off’);SET_MENU_ITEM_PROPERTY(’image_menu.image_activate’, CHECKED,

PROPERTY_TRUE );

END IF;

END;



opens the EMPLOYEES form and passes the information by way ofa global variable.

PROCEDURE SALES_REP_INFORMATION IS

Status BOOLEAN := TRUE;

BEGIN

IF NAME_IN(’:s_ord.sales_rep_id’) IS NULL

THEN

WHILE Status

LOOP

QUERY_PARAMETER(’&AA’);

IF Menu_Success

THEN

BEGIN

SELECT id, last_name

INTO :GLOBAL.sales_rep_id, :GLOBAL.sales_rep_name

FROM s_emp

WHERE id = TO_NUMBER(:AA);

IF NOT SQL%NOTFOUND

THEN

Status := FALSE;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

MESSAGE(’This employee does not exist.’);

END;

ELSE

Status := FALSE;

END IF;

END LOOP;

OPEN_FORM(’employees’);

ELSE

:GLOBAL.sales_rep_id := NAME_IN(’:s_ord.sales_rep_id’);

:GLOBAL.sales_rep_name := NAME_IN(’:s_ord.sales_rep_name’);

OPEN_FORM(’employees’);

END IF;

END;



c Create a new menu item to the right of the Image menu and use it tocall the SALES_REP_INFORMATION procedure.

• Click the Image menu item.• Click the Create Right tool.• Rename it: Sales Rep.• Set the properties as follows:

d Save and compile the module.

e Open the EMPLOYEES form module and attach the menu module toit.

Open the EMPLOYEES form and set the Menu Moduleproperty to M_SUMMIT for the form module. Some options ofthe M_SUMMIT menu module are not suitable for this form andwill be discussed later.

f Save and compile the form module.

g Run the CUSTOMERS form, calling the ORDERS form and test theSales Rep menu item.

If you have time...3 Write startup code to enhance the functions.

a Write Startup code for the M_SUMMIT menu module thatsynchronizes the Image Activate menu item with the Image button.You can import thepr3_3a1.txtfile. Modify theWhen-Button-Pressed trigger on the image button in the controlblock of the ORDERS form to obtain the same result. You canimport thepr3_3a2.txtfile. The other code needs to be deleted beforeimporting.Save and compile your module.

Menu LabelMenuItem Type Command Type Menu Item Code

Sales Rep Plain PL/SQL sales_rep_information;



Use the Startup Code menu module property to invoke the PL/SQL Editor.

4 Run the CUSTOMERS form and test the application.The menu items Sort, Image Activate, and Sales Rep Information are notrelevant to the EMPLOYEES form and therefore should have no effect.

IF GET_ITEM_PROPERTY(’CONTROL.IMAGE_BUTTON’, LABEL) =’Image Off’

THEN

SET_MENU_ITEM_PROPERTY(’IMAGE_MENU.IMAGE_ACTIVATE’, CHECKED,PROPERTY_TRUE );

END IF;

DECLARE

product_image_id ITEM := FIND_ITEM(’s_item.product_image’);

image_description_id ITEM :=FIND_ITEM(’s_item.image_description’);

image_button_id ITEM := FIND_ITEM(’control.image_button’);

BEGIN

IF GET_ITEM_PROPERTY(product_image_id,displayed) ='TRUE' THENSET_ITEM_PROPERTY(product_image_id,DISPLAYED,

PROPERTY_FALSE);SET_ITEM_PROPERTY(image_description_id,DISPLAYED,

PROPERTY_FALSE);

SET_ITEM_PROPERTY(image_button_id,LABEL,’Image On’);

SET_MENU_ITEM_PROPERTY(’IMAGE_MENU.IMAGE_ACTIVATE’,

CHECKED, PROPERTY_FALSE );

ELSE

SET_ITEM_PROPERTY(product_image_id,DISPLAYED,PROPERTY_TRUE);

SET_ITEM_PROPERTY(image_description_id,DISPLAYED,PROPERTY_TRUE);

SET_ITEM_PROPERTY(image_button_id,LABEL,’Image Off’);

SET_MENU_ITEM_PROPERTY(’IMAGE_MENU.IMAGE_ACTIVATE’,

CHECKED, PROPERTY_TRUE );

END IF;

END;



a Modify the Startup Code of the M_SUMMIT menu module todisable these options when the EMPLOYEES form is opened. Youcan import thepr3_4a.txtfile, which contains the necessary code.

Use the SYSTEM.CURRENT_FORM variable to get the nameof the current form, and use theSET_MENU_ITEM_PROPERTY built-in procedure to disablethese options.

b Save and compile the module.

c Run the CUSTOMERS form and test your application.

IF :SYSTEM.CURRENT_FORM = 'EMPLOYEES'

THEN

SET_MENU_ITEM_PROPERTY(’MAIN_MENU.SALES_REP_INFORMATION’,ENABLED, PROPERTY_FALSE);

SET_MENU_ITEM_PROPERTY(’SORT_BY_MENU.ORDER_ID’, ENABLED,PROPERTY_FALSE);

SET_MENU_ITEM_PROPERTY(’SORT_BY_MENU.CUSTOMER_ID’, ENABLED,PROPERTY_FALSE);

SET_MENU_ITEM_PROPERTY(’SORT_BY_MENU.SALES_REP_ID’, ENABLED,PROPERTY_FALSE);

SET_MENU_ITEM_PROPERTY(’IMAGE_MENU.IMAGE_ACTIVATE’, ENABLED,PROPERTY_FALSE);

ELSIF GET_ITEM_PROPERTY(’CONTROL.IMAGE_BUTTON’, LABEL) =’Image Off’

THEN

SET_MENU_ITEM_PROPERTY(’IMAGE_MENU.IMAGE_ACTIVATE’, CHECKED,PROPERTY_TRUE );

END IF;



Practice 4 Solution1 Create and modify a Key trigger.

a Open the ORDERS form module and replace the M_SUMMIT menumodule with the DEFAULT&SMARTBAR menu.

b Run the form and make sure that you understand the default behaviorof [Duplicate Record], by invoking it once while the cursor is on aqueried record in the S_ORD block and again when the cursor is on anew record.

c In the S_ORD block, redefine [Duplicate Record] in such a way thata new record is created before duplication takes place, and theprimary key items are emptied after duplication.

Key-Duprec on the S_ORD Block

d Duplicate your trigger at the form level and at the S_ITEM blocklevel.

See f. for solution.e For the S_ITEM block, empty the item_id, product_id, description,

and price items after duplication.

See f. for solution.f Avoid duplicating the code by using the Execution Hierarchy trigger

property.

Key-Duprec at Form Level

CREATE_RECORD ;DUPLICATE_RECORD ;:s_ord.id := NULL ;

CREATE_RECORD ;DUPLICATE_RECORD ;



Key-Duprec on the S_ORD block. Execution Hierarchy = After

Key-Duprec on the S_ITEM block Execution Hierarchy = After

If you have time...

2 Disable function keys.

a Make sure that only the function keys [Duplicate Record],[Execute Query], [Enter Query], [Create Record], [Clear Block],[Up], and [Down] can be used for the S_ORD master block. Allfunctions keys should be available for the S_ITEM detail block.

Key-Duprec on the S_ORD Block

Key-Exeqry on the S_ORD Block

Key-Entqry on the S_ORD Block

Key-CreRec on the S_ORD Block

Key-ClrBlk on the S_ORD Block

:s_ord.id := NULL ;

:s_item.item_id := NULL;:s_item.product_id := NULL;:s_item.description := NULL;:s_item.price := NULL;

:s_ord.id := NULL;

EXECUTE_QUERY;

ENTER_QUERY;

CREATE_RECORD;

CLEAR_BLOCK;



Key-Up on the S_ORD Block

Key-Down on the S_ORD Block

Key-Others on the S_ORD Block

b Test whether you can exit from the form by pressing [Exit] key in themaster block.

You cannot exit the form by pressing [Exit] key in the masterblock.

c Add a Form-Level Key-Exit trigger performing the exit functionalityof the CONTROL.EXIT_BUTTON item.

Key-Exit at Form Level

d Test again whether you can exit from the form by pressing [Exit] inthe master block. Explain this behavior.

If there is no Key trigger defined for a function key at the samelevel as Key-Others, Forms searches at a higher level for such aKey trigger.

UP;

DOWN;

MESSAGE(’Function Not Allowed’);

SET_ALERT_PROPERTY(’question_alert’, ALERT_MESSAGE_TEXT, ’Do

you really want to leave the form?’);

IF SHOW_ALERT(’question_alert’) = ALERT_BUTTON1

THEN

EXIT_FORM ;

END IF;



e Modify the When-Button-Pressed trigger of theCONTROL.EXIT_BUTTON item to activate the same functionalitywhen you press the [Exit] function key.

When-Button-Pressed on the CONTROL.EXIT_BUTTON Item

3 Create and modify a Key trigger.

a Create an Alert called DELETE_ALERT.

b For the S_ORD and S_ITEM blocks, redefine [Delete Record] insuch a way that the alert is displayed when a record is deleted.Modify the message displayed, depending on the block where thecursor is.

Key-Delrec on the S_ORD Block

Key-Delrec on the S_ITEM Block

c Specify a new text that will be displayed in the runtime Show Keyshelp screen.

Modify the “Keyboard Help” Text trigger property and set theDisplay in “Keyboard Help” property to TRUE.

DO_KEY(’exit_form’);

SET_ALERT_PROPERTY(’DELETE_ALERT’, ALERT_MESSAGE_TEXT, ’Do you wantto delete this order?’);

IF SHOW_ALERT(’DELETE_ALERT’) = ALERT_BUTTON1

THEN

DELETE_RECORD;

END IF;

SET_ALERT_PROPERTY(’DELETE_ALERT’, ALERT_MESSAGE_TEXT, ’Do you wantto delete this line order?’);

IF SHOW_ALERT(’DELETE_ALERT’) = ALERT_BUTTON1

THEN

DELETE_RECORD;

END IF;



Practice 5 SolutionIn this workshop, you provide additional functionality in your forms bydefining mouse-event triggers.

1 In the CUSTOMERS form, define a mouse-event trigger onS_CUSTOMER.COMMENTS that invokes the item’s editor when theuser double-clicks on the item.

WHEN-MOUSE-DOUBLECLICK on s_customer.comments

2 In the ORDERS form convert the CONTROL.SHOW_HELP_BUTTONbutton into a display item. This item should be capable of displaying asingle large character. Change the item and choose a font that allowslarge character sizes (such as Times New Roman). Set the font size to24. Set Bevel to none. Adjust the item size so that one character is visibleinside it. The item’s background color should match the toolbar, and itstext color should be green. This item appears as ‘S’.

No formal solution.3 In the ORDERS form, define a When-Mouse-Enter trigger at form level

that assigns a value of ’?’ to the display itemCONTROL.SHOW_HELP_BUTTON.

WHEN-MOUSE-ENTER at Form Level

4 Alter the When-Mouse-Enter trigger so that it fires only when enteringCV_ORDER canvas (you may wish to include Toolbar also).

WHEN-MOUSE-ENTER at Form Level

EDIT_TEXTITEM;

:control.show_help_button := ’?’;

IF :SYSTEM.MOUSE_CANVAS = ’CV_ORDER’THEN

:control.show_help_button := ’?’;END IF;



5 Create a When-Mouse-Enter trigger onCONTROL.SHOW_HELP_BUTTON that uses the SHOW_VIEWbuilt-in to display the CV_HELP. Remove the When-Button-Pressedtrigger.

WHEN-MOUSE-ENTER trigger on control.show_help_button

6 Create a When-Mouse-Leave trigger onCONTROL.SHOW_HELP_BUTTON that hides the CV_HELP. Use theHIDE_VIEW built-in to achieve this. Delete theCONTROL.HIDE_HELP_BUTTON button.

WHEN-MOUSE-LEAVE trigger on control.show_help_button

7 Save, compile, and run the ORDERS form to test.The stacked canvas, CV_HELP, displays only if the current item will notbe obscured. Ensure, at least, that the first entered item in the form is onethat will not be obscured by CV_HELP.

You might decide to advertize Help only while the cursor is incertain items, or move the stacked canvas to a position that does notoverlay enterable items.The CV_HELP canvas, of course, could also be shown in its ownwindow, if appropriate.

SHOW_VIEW(’cv_help’);

HIDE_VIEW(’cv_help’);



If you have time...

8 Modify the When-Mouse-Enter and When-Mouse-Leave triggers todisplay the CV_HELP canvas, even if the current item is obscured. Youcan import thepr5_8_1.txtandpr5_8_2.txtfiles.

WHEN-MOUSE-ENTER trigger on control.show_help_button

WHEN-MOUSE-LEAVE trigger on control.show_help_button

:GLOBAL.save_item_name := :SYSTEM.CURSOR_ITEM;

GO_ITEM(’s_ord.id’);

SHOW_VIEW(’cv_help’);

HIDE_VIEW(’cv_help’);

GO_ITEM(:GLOBAL.save_item_name);



Practice 6 Solution1 Manipulate a multiple-window form.

a Open the ORDERS form module.

b Make sure that when a user closes the orders window by way of thesystem-menu box, the form is exited. You can import thepr6_1b.txtfile.

See solution forc.c Make sure that when a user closes the inventory window by way of

the system-menu box, the cursor always navigates to the orderswindow. If cursor navigation succeeds, the inventory window mustbe closed. You can import thepr6_1c.txtfile.

When-Window-Closed Trigger at Form Level

d Make sure that a user cannot make any window larger than it was atthe startup of the form. You should save the size of the windows atthe startup of the form, and create a procedure calledCHECK_WINDOW_SIZE which checks for the current window ifthe new width and height are not larger than the initial one. You canimport thepr6_1d1.txtandpr6_1d2.txtfiles.

The following solution assumes that the window sizes at formstartup are stored in global variables with names of the generalform :GLOBAL.WIDTH_< window_name> andGLOBAL.HEIGHT_< window_name>

Note: In multiple-form applications, it is better to use form parameters thanglobal variables, because form parameters are not visible to other forms.

IF :SYSTEM.EVENT_WINDOW = ’WIN_ORDER’

THEN

DO_KEY(’exit_form’);

ELSIF :SYSTEM.EVENT_WINDOW = ’WIN_INVENTORY’

THEN

GO_BLOCK(’s_ord’);

END IF;



When-New-Form-Instance Trigger at Form Level

When-Window-Resized Trigger at Form Level

EXECUTE_QUERY;

:GLOBAL.width_win_order :=GET_WINDOW_PROPERTY(’win_order’,WIDTH);

:GLOBAL.height_win_order :=GET_WINDOW_PROPERTY(’win_order’, HEIGHT);

:GLOBAL.width_win_inventory :=GET_WINDOW_PROPERTY(’win_inventory’, WIDTH);

:GLOBAL.height_win_inventory :=GET_WINDOW_PROPERTY(’win_inventory’, HEIGHT);

PROCEDURE check_window_size( p_window IN VARCHAR2 )

IS

v_window_max_widthNUMBER;

v_window_max_heightNUMBER;

BEGIN

v_window_max_width := NAME_IN(’GLOBAL.width_’ || p_window );

v_window_max_height := NAME_IN(’GLOBAL.height_’ || p_window );

IF v_window_max_width < TO_NUMBER(GET_WINDOW_PROPERTY(p_window, WIDTH) )

THEN

SET_WINDOW_PROPERTY(p_window, WIDTH, v_window_max_width );

END IF;

IF v_window_max_height < TO_NUMBER(GET_WINDOW_PROPERTY(p_window, HEIGHT) )

THEN

SET_WINDOW_PROPERTY(p_window, HEIGHT, v_window_max_height );

END IF;

END check_window_size;

check_window_size( :SYSTEM.EVENT_WINDOW );



2 Manipulate the MDI application window.


b At the startup of the form, maximize the MDI application window,and display an appropriate title. You can import thepr6_2b.txtfile.


If you have time...

3 Open the EMPLOYEES form, and use the CONTROL.HELP_TAB textitem to display a hint or description for the current tab page.

a Create a When-Tab-Page-Changed trigger to populate this item withan appropriate message for each page. You can import the pr6_3a.txtfile.

b Test and verify your document.

c Modify the When-Tab-Page-Changed trigger to change the label onthe Comment tab page: when Comment is topmost page, change itslabel to ‘Employee XX’, where XX is the employee’s ID number(emp.id). When any other tab page is topmost, change the label backto ‘Comment’. You can import thepr6_3c.txt file.

SET_WINDOW_PROPERTY( forms_mdi_window, WINDOW_STATE, MAXIMIZE );

SET_WINDOW_PROPERTY( forms_mdi_window, TITLE, ’Summit SportingGoods Application’);

:control.help_tab := ’Displays ’|| Initcap(:SYSTEM.TAB_NEW_PAGE)

|| ’ information about ------------------>’ ;

:control.help_tab := ’Displays ’|| Initcap(:SYSTEM.TAB_NEW_PAGE)

|| ’ information about ------------------>’ ;

IF :SYSTEM.TAB_NEW_PAGE = ’COMMENT’ THEN

SET_TAB_PAGE_PROPERTY(’CANVAS2.COMMENT’, LABEL, ’Employee ’ ||:EMP.ID);

ELSE

SET_TAB_PAGE_PROPERTY(’CANVAS2.COMMENT’, LABEL, ’Comment’);

END IF;



Practice 7 Solution

1 Open the ORDERS form and examine the properties of the relationcalled S_ORD_S_ITEM.

a Note the deletion and coordination property values.

b Run the ORDERS form and test the way deletes are handled.

2 Create a relation called S_Item_S_Inventory explicitly between theS_Item and S_Inventory blocks.

a Ensure that line item records can be deleted independently of anyrelated Inventory.

b Set the coordination so that the Inventory block is not queried untilyou explicitly execute a query.

3 Implement a query coordination-type toggle.

a Add two check boxes to the control block with the followingproperties:

Property Check Box 1 Check Box 2

Name IMMEDIATE AUTO_QUERY

Enabled Yes No

Label Immediate Auto Query

Value When Checked Y Y

Value When Unchecked N N

Check Box Mapping ofOther Value

CHECKED UNCHECKED

Keyboard Navigable No No

Mouse Navigate No No

DataType CHAR CHAR

Initial Value Y Y

DataBase Item No No

Canvas TOOLBAR TOOLBAR



b Make sure that the first check box enables a user to toggle betweenimmediate coordination and deferred coordination. You can importthepr7_3b.txtfile.

When-Checkbox-Changed on CONTROL.IMMEDIATE

c Make sure that the second check box enables a user to togglebetween automatic query and no automatic query for the detailblock. This check box should be disabled if the other check boxindicates immediate coordination. You can import thepr7_3c.txtfile.

When-Checkbox-Changed on CONTROL.AUTO_QUERY

IF GET_RELATION_PROPERTY( ’s_ord_s_item’, DEFERRED_COORDINATION )= ’FALSE’

THEN

SET_RELATION_PROPERTY(’s_ord_s_item’, DEFERRED_COORDINATION,PROPERTY_TRUE);

SET_ITEM_PROPERTY(’control.auto_query’, ENABLED,PROPERTY_TRUE );

ELSE

SET_RELATION_PROPERTY(’s_ord_s_item’, DEFERRED_COORDINATION,PROPERTY_FALSE);

SET_ITEM_PROPERTY(’control.auto_query’, ENABLED,PROPERTY_FALSE );

END IF;

IF GET_RELATION_PROPERTY(’s_ord_s_item’, AUTOQUER Y ) = ’ FALSE’

THEN

SET_RELATION_PROPERTY(’s_ord_s_item’, AUTOQUERY, PROPERTY_TRUE );

ELSE

SET_RELATION_PROPERTY(’s_ord_s_item’, AUTOQUERY, PROPERTY_FALSE);

END IF;



If you have time...

4 Synchronize the check boxes at form startup.

a Open the ORDERS form module.

b Create a procedure called INIT_RELATION_CHECK_BOXES.This procedure sychronizes the IMMEDIATE and AUTO_QUERYcheck boxes with the current default value. You can import thepr7_4b.txtfile.

c Call this procedure at form startup.

PROCEDURE init_relation_check_boxes

IS

BEGIN

IF GET_RELATION_PROPERTY(’s_ord_s_item’', DEFERRED_COORDINATION) ='FALSE'

THEN

:control.immediate := ’Y’;

SET_ITEM_PROPERTY(’control.auto_query’, ENABLED, PROPERTY_FALSE);

ELSE

:control.immediate := ’N’;

SET_ITEM_PROPERTY(’control.auto_query’, ENABLED, PROPERTY_TRUE );

END IF;

IF GET_RELATION_PROPERTY(’s_ord_s_item’, AUTOQUERY) = ’TRUE’

THEN

:control.auto_query := ’Y’;

ELSE

:control.auto_query := ’N’;

END IF;

END;

init_relation_check_boxes;

EXECUTE_QUERY;

:GLOBAL.width_win_order := GET_WINDOW_PROPERTY(’win_order’, WIDTH);

:GLOBAL.height_win_order := GET_WINDOW_PROPERTY(’win_order’,HEIGHT);

:GLOBAL.width_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,WIDTH);

:GLOBAL.height_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,HEIGHT);



5 Implement foreign-key delete rules.


b Create a procedure called CHECK_DEL_CUS. This proceduredisplays an error message as soon as a user tries to delete a customerfor which matching orders exist. You can import thepr7_5b.txtfile.

c Call the procedure when a user presses the delete function-key.

Key-Delrec trigger on S_CUSTOMER Block

PROCEDURE check_del_cus ( p_cus_id IN NUMBER )

IS

CURSOR c_ord IS

SELECT ’X’

FROM s_ord

WHERE customer_id = p_cus_id;

v_dummy VARCHAR2(1);

ord_exists EXCEPTION;

BEGIN

OPEN c_ord;

FETCH c_ord INTO v_dummy;

IF c_ord%FOUND

THEN

CLOSE c_ord;

RAISE ord_exists;

ELSE

CLOSE c_ord;

END IF;

EXCEPTION

WHEN ord_exists THEN

MESSAGE(’Cannot delete customer while matching ordersexist.’);

RAISE FORM_TRIGGER_FAILURE;

END check_del_cus;

check_del_cus(:s_customer.id);

DELETE_RECORD;



Practice 8 Solution1 Produce a multiple form application by linking the CUSTOMERS and

the EMPLOYEES forms.

a In the control block of the CUSTOMERS form, create a buttoncalled EMPLOYEE_BUTTON.

b Define a trigger for CONTROL.EMPLOYEE_BUTTON that callsthe EMPLOYEES form with the current sales representative ís ID.

- Create a parameter list called SALES.

- Add the SALES_ID parameter to the parameter list. The type of thisparameter is TEXT_PARAMETER, and it is initialized with thevalue of the SALES_REP_ID item.

- Invoke the EMPLOYEES form using the call_form built-in.

You can import thepr8_1b.txt file.

When-Button-Pressed on the CONTROL.EMPLOYEE_BUTTON Item

c Open the EMPLOYEES form module.

d In the Object Navigator, create a parameter called SALES_ID.

DECLARE

p_list_id PARAMLIST;

BEGIN

IF NOT ID_NULL( GET_PARAMETER_LIST( ’sales’ ) )

THEN

DESTROY_PARAMETER_LIST(’sales’);

END IF;

p_list_id := CREATE_PARAMETER_LIST(’sales’);

ADD_PARAMETER( p_list_id, ’sales_id’, TEXT_PARAMETER,

TO_CHAR( :s_customer.sales_rep_id ));

CALL_FORM(’employees’, NO_HIDE, DO_REPLACE, NO_QUERY_ONLY,p_list_id );

END ;



e Add a trigger to ensure that queries on the employee block arerestricted by the value of SALES_ID parameter. You can import thepr8_1e.txtfile.

Pre-Query Trigger on the EMP Block

f Save and compile each form, then run the CUSTOMERS formmodule and test your application.

If you have time...2 Transaction processing for called forms.

a Make sure that any changes in the EMPLOYEES form that areposted by a user are not rolled back by Forms upon exit. You canimport thepr8_2a.txtfile.

Key-Exit Trigger at Form Level : EMPLOYEES Form

b Make sure that when the CUSTOMERS form calls theEMPLOYEES form, [Commit] performs a post. You can import thepr8_2b.txtfile.

Key-Commit Trigger at Form Level : EMPLOYEES Form

c Save and compile each form, then run the CUSTOMERS formmodule and test your application.

DEFAULT_VALUE(’’, ’GLOBAL.sales_rep_id’);

IF :GLOBAL.sales_rep_id IS NOT NULL AND :PARAMETER.sales_id IS NULL

THEN

:emp.num := :GLOBAL.sales_rep_id;

ELSE

:emp.num := :PARAMETER.sales_id;

END IF;

EXIT_FORM( ASK_COMMIT, NO_ROLLBACK );

IF GET_APPLICATION_PROPERTY( CALLING_FORM ) IS NULL

THEN

COMMIT_FORM;

ELSE

POST;

END IF;



Practice 9 Solution1 Base the EMP block on a sub-query.

a Open the EMPLOYEES form module.

b Set theQuery Data Source TypeEMP block property toFrom ClauseQuery.

In the EMP block property palette, under the Database node,change Query Data Source Type to From Clause Query.

c In the property Query Data Source Name enter the SELECTstatement. Your query should return all the columns from the S_EMPtable joined with the columns from the S_DEPT table. Remember toenclose your select SELECT statement in parentheses. You can Copyand Paste the content of thepr9_1c.txt file.

The SELECT statement is:

d Set the block KeyMode property to Updateable.

e Define the ID item as a primary key for the block.

f Save, run and test your module.

(SELECT e.ID,

USERID,

LAST_NAME,

FIRST_NAME,

START_DATE,

TITLE,

MANAGER_ID,

DEPT_ID,

SALARY,

COMMISSION_PCT,

COMMENTS,

d.ID NUM,

REGION_ID,

NAME

FROM s_emp e, s_dept d

WHERE e.dept_id = d.id)



2 Enable DML on the EMP block without using transactional triggers.

a Set the block DML Target Name.

In the EMP block property palette, under the AdvancedDatabase node, change DML Data Target Name to S_EMP.

b Set the Query Only, Update Allowed, Insert Allowed properties forthe S_DEPT items (NUM, NAME, REGION_ID).

Make the NUM, NAME, and REGION_ID items QueryOnly=Yes, Update Allowed = No, Insert Allowed = No. This isnecessary to prevent Forms from attempting to update the namecolumn, which does not exist in the S_Emp table.

c Remove the appropriate Transactional triggers.

Remove the On-Insert, On-Update, and On-Delete triggers.d Save, run and test your module.

If you have time...

3 Create a package containing a stored procedure to return a Ref Cursor.

a Using the code in the filepr9_3a.sqlin your labs directory, create theOrders package body and package specification.

Use either SQL*Plus to run the file, or use the Stored Procedureeditor in Forms, and copy and paste the code from the file.

4 Modify the Ord_Sum block in moduleord_sum.fmb to base it on theStored Procedure.

a Open moduleord_sum.fmb.Change the Query Data Source Type ofthe Ord_Sum block to Procedure. Change the DML Data TargetType to None (this block does not allow any inserts, updates ordeletes).

In the Ord_Sum block property palette, under the Databasenode, change Query Data Source Type to Procedure.Under the Advanced Database node, change DML Data TargetType to None.

b Set the Query Data Source Name to the name of the stored procedurein the package.

Under the Database node, change Query Source Name toORDERS_PKG.QUERY_PROC. You can find the procedurename by double-clicking the package body name under theStored Program Units node in the Object Navigator.



c Specify the Query Data Source Columns to match the items in theOrd_Sum block.

The column names and types are:

ID, NumberNAME, Varchar2(255)TOTAL, Number

d Specify the procedure arguments. The first argument is the RefCursor that will be used by Forms to populate the block. The secondargument is used to pass the value of the list box(:Choose.View_Type) to the procedure to determine which SELECTstatement is used. These arguments should match the arguments tothe package procedure.

The arguments are:

e Add a trigger so that the data in the Ord_Sum block is updated whenthe list box value changes.

Hint: You need to execute a query in the Ord_Sum block.

Add a When-List-Changed trigger to Choose.View_Type as follows:

Alternative Method: in Choose.View_Type property palette, changeMouse Navigate to False. Now the cursor remains in the Ord_Sumblock and there is no need to code the Go_Block statement.

Argument Type Mode Value

Resultset RefCursor In Out (No Value)

P_View Varchar2 In :Choose.View_Type

GO_BLOCK(’ord_sum’);EXECUTE_QUERY;



5 Save and run your form. Observe the behavior of the record count in theconsole and the scrollbar as you scroll through the records. What do youdeduce from this?

The record count increments as Forms fetches more records, andthe scrollbar button changes size as the number of records in the listbox increases. From this we can deduce that a block based on a RefCursor stored procedure benefits from Array Fetches, just like ablock based directly on a table.



Practice 10 Solution1 Create a dynamic list item. This functionality avoids having to create a

new item to display the department name.

a Open the EMPLOYEES form module.

b Create a design time query record group that contains IDs and namesof departments. Name your record group DEPT_ID.

In the Object Navigator, create a record group; for example, based on thequery below:

c Convert the text item DEPT_ID into a list item, Combo Box Style.Resize it in the Layout Editor.

d Create an element in the list. Label: Dummy and Value: 0

e Create a procedure called LIST_FROM_DESIGNTIME_GROUP.This procedure accepts the list item name as an argument andpopulates the list item dynamically at runtime, using the design-timequery record group. You can import thepr10_1e.txtfile.

Note: In the following solution, it is assumed that the record group isnamed after the list_item (DEPT_ID in this case).

SELECT name || ’ in region ’ || TO_CHAR(region_id) Name,TO_CHAR(id) Id

FROM s_dept

ORDER BY 1

PROCEDURE List_From_DesignTime_Group (p_list_item IN VARCHAR2)

IS

cst_rg_nom CONSTANT VARCHAR2( 30 ) :=

GET_ITEM_PROPERTY(p_list_item, ITEM_NAME );

BEGIN

IF POPULATE_GROUP( cst_rg_no m ) = 0

THEN

POPULATE_LIST( p_list_item, cst_rg_nom );

END IF;

COPY( GET_LIST_ELEMENT_VALUE( p_list_item,1 ), p_list_item);

END List_From_DesignTime_Group;



f Call the procedure each time a new record is created. You can importthepr10_1f.txtfile.

When-Create-Record Trigger on the EMP Block

If you have time...

2 Create a multi-record select list form. This practice shows how to createa list of values where the user can select multi-values. This lab uses aglobal record group.

a Exit Form Builder.

b Run Form Builder and create a new form based on a template namedlov_prod_template.fmb.

c Examine this new module and save it aslov_product.fmb.

This new form contains a multi-record block based on theS_PRODUCT table. Only the ID, NAME, andSUGGESTED_WHLSL_PRICE columns are displayed.This form also contains an OK button and a Cancel buttonwithout any code.This form contains two visual attributes called DESELECTEDand SELECTED.

CLEAR_LIST(’EMP.DEPT_ID’);

LIST_FROM_DESIGNTIME_GROUP(’EMP.DEPT_ID’);



d Create a When-Mouse-Click trigger at the PROD_LOV_BLK blocklevel that selects or deselects a record when a user presses Ctrl+Clickfor a product.Write the code to create a record group if it does not already existand add columns to the group for each item in the PROD_LOV_BLKblock. If the record group exists write the code to retrieve theinternal ID for each column in the record group.Write the code to check whether the record selected is already in therecord group. If so, loop through each item in the record to keep thevisual attribute from being highlighted, and then remove the recordfrom the record group.If the user selects a record that was not already in the group, write thecode to add the record to the record group and loop through eachitem in the block to keep the color highlighted.Use the SELECTED and DESELECTED visual attributes in yourcode to change the color of a selected or deselected record.You can import thepr10_2d.txt file.



When-Mouse-Click Trigger on the PROD_LOV_BLK Block

DECLARE

rg_id RECORDGROUP := FIND_GROUP(’sel_records’);

recno_id GROUPCOLUMN;

prodno_id GROUPCOLUMN;

prodname_id GROUPCOLUMN;

prodprice_id GROUPCOLUMN;

no_rows NUMBER(7);

desel VARCHAR2(5) := ’no’;

itm VARCHAR2(80);

lst VARCHAR2(100);

BEGIN

--

IF :SYSTEM.MOUSE_BUTTON_MODIFIERS = ’Control+’ THEN

-- If record group does not exist

-- create it and add columns to the

-- group for each item in the PROD_LOV_BLK

IF ID_NULL(rg_id) THEN

rg_id := CREATE_GROUP(’sel_records’, GLOBAL_SCOPE);

recno_id := ADD_GROUP_COLUMN(rg_id,’recno’,NUMBER_COLUMN);

prodno_id := ADD_GROUP_COLUMN(rg_id,’prodno’,NUMBER_COLUMN);

prodname_id := ADD_GROUP_COLUMN(rg_id,’prodname’, CHAR_COLUMN,80);

prodprice_id := ADD_GROUP_COLUMN(rg_id,’prodprice’,NUMBER_COLUMN);



-- if the record group does exist

-- retrieve the internal id for each

-- column in the record group

ELSE

recno_id := FIND_COLUMN(’sel_records.recno’);

prodno_id := FIND_COLUMN(’sel_records.prodno’);

prodname_id := FIND_COLUMN(’sel_records.prodname’);

prodprice_id := FIND_COLUMN(’sel_records.prodprice’);

END IF;

-- check to see if record CNTL

-- clicked on is already in the record

-- group - if so loop thru each item

-- in the block to set the visual

-- attribute back from being highlighted,

-- and then remove record from

-- record group

no_rows := GET_GROUP_ROW_COUNT(rg_id);

FOR i IN 1..no_rows LOOP

IF TO_NUMBER(:SYSTEM.MOUSE_RECORD) =GET_GROUP_NUMBER_CELL(recno_id, i) THEN

desel := ’yes’;

itm := GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK, FIRST_ITEM);

itm := :SYSTEM.CURSOR_BLOCK || ’.’ || itm;

LOOP

DISPLAY_ITEM(itm, ’DESELECTED’);

itm := GET_ITEM_PROPERTY(itm, NEXTITEM);

EXIT WHEN itm IS NULL;


END LOOP;

DELETE_GROUP_ROW(rg_id, i);

EXIT;

END IF;

END LOOP;



e Create the code for the OK button. This button enables the user toexit the form.

-- if CNTL click on a record that

-- was not already in the group

-- add the record to the record group

-- and loop through each

-- item in the block to set the color

-- to be highlighted for each.

IF desel != 'yes' THEN

ADD_GROUP_ROW(rg_id,end_of_group);

SET_GROUP_NUMBER_CELL(recno_id, no_rows+1,TO_NUMBER(:SYSTEM.MOUSE_RECORD));

SET_GROUP_NUMBER_CELL(prodno_id, no_rows+1, :prod_lov_blk.id);

SET_GROUP_CHAR_CELL(prodname_id, no_rows+1,:prod_lov_blk.name);

SET_GROUP_NUMBER_CELL(prodprice_id, no_rows+1,:prod_lov_blk.suggested_whlsl_price);

itm := GET_BLOCK_PROPERTY( :SYSTEM.CURSOR_BLOCK , FIRST_ITEM );


LOOP

DISPLAY_ITEM(itm, ’SELECTED’);

itm := GET_ITEM_PROPERTY(itm, NEXTITEM);

EXIT WHEN itm IS NULL;


END LOOP;

END IF;

END IF;

END;

DO_KEY(’EXIT_FORM’);



f Create the code for the CANCEL button in the PROD_LOV_BLKblock.Create a When-Button-Pressed trigger that deletes the record groupbefore returning to the ITEM block. You can import thepr10_2f.txtfile.

When-Button-Pressed Trigger at PROD_LOB_BLK.CANCEL_PB Item

g In the ORDERS form, modify the When-Button-Pressed trigger forthe CONTROL.PRODUCT_LOV_BUTTON. The new code shouldcheck the existing products in the S_ITEM block, so that productsalready ordered do not show up in the list. It then calls thePROD_LOV_BLK form, passing the list of existing products usinga GLOBAL variable. You can import thepr10_2g.txtfile.

IF NOT ID_NULL(FIND_GROUP(’sel_records’)) THEN

DELETE_GROUP(’sel_records’);

END IF;

DO_KEY(’EXIT_FORM’);

:GLOBAL.where_cls := ’ id NOT IN (’;

GO_BLOCK(’item’);

FIRST_RECORD;

IF:SYSTEM.RECORD_STATUS != ’NEW’ THEN

LOOP

:GLOBAL.where_cls := :GLOBAL.where_cls ||TO_CHAR(:item.product_id);

NEXT_RECORD;

EXIT WHEN :SYSTEM.LAST_RECORD = ’TRUE’;

:GLOBAL.where_cls := :GLOBAL.where_cls || ’,’';

END LOOP;

:GLOBAL.where_cls := :GLOBAL.where_cls || ’,’ ||TO_CHAR(:item.product_id) || ’)’;

END IF;

CALL_FORM(’lov_product’, NO_HIDE);



h In the LOV_PRODUCT form, create a When-New-Form-Instancetrigger to retrieve the list of products excluding the products thatexist in the S_ITEM block. You should use the global variablecreated earlier. You can import thepr10_2h.txtfile.


i In the ORDERS form, modify the When-Button-Pressed trigger forthe CONTROL.PRODUCT_LOV_BUTTON. After you call theform, the code creates records in the S_ITEM block for each rowselected in the record group. The code then deletes the recordgroup.You can import thepr10_2i.txt file.

j Save, run, and test your forms.

DEFAULT_VALUE(’’, ’'GLOBAL.where_cls’);

SET_BLOCK_PROPERTY(’prod_lov_blk’, DEFAULT_WHERE,

:GLOBAL.where_cls);

EXECUTE_QUERY;



Practice 11 Solution1 Create a chart object using the chart wizard. This chart should display

the total orders for each customer.


No formal solution.b Create a new tab page on the TAB_CUSTOMER canvas. Name this

page CHART and change the label name.

No formal solution.c Open theFormBuilder_II.olb object library. Copy the OBJ_CHART

object group into the CUSTOMERS form. This object groupcontains a block named S_ORD. Only one item from this block isvisible: TITLE. This item displays a title for the chart on the Charttab page.

In the Object Navigator, double-click the icon next to theFORM_BUILDER_II object library node.From the Chart tab, select the OBJ_CHART object group anddrag it to the object group node in the CUSTOMERS form.Select Copy.

d From the Layout Editor, use the Chart tool to drag a chart area ontothe chart tab page. Select Use the Chart Wizard from the New ChartObject dialog box.

No formal solution.e Select Column as chart type, Plain as chart subtype.

No formal solution.f Specify the S_ORD block as the data block that contains the data

you want to assign to chart columns.

No formal solution.g Select CUSTOMER_NAME to appear on the Category (X) axis, and

TOTAL to appear on the Value (Y) axis of the chart.

No formal solution.h Click the Finish button. Save, run, and test your form.



2 Create a report object based on the s_customer block. This reportdisplays a customer list using a tabular layout. Display only the ID,NAME, COUNTRY, and REGION_ID fields.

a Open the CUSTOMERS Form.

b Create a new report object.

- Select the Report Object node in the navigator.- Click the Create icon.- Specify a filename and a block name.- Click OK.This will invoke the Report Builder. Within the Report Wizard:

- Choose the report style Tabular.

- Select the ID, NAME, COUNTRY, and CREDIT_RATING fields.

- Do not select fields to total.

- Change labels and widths for your fields.

- Select a template for your report.

c Save your report and exit Report Builder.

d Modify the Execution Mode and Report Destination Type reportobject properties.

Display the Property Palette for the report object.Select Execution Mode and change the value to RUNTIME.Select Report Destination Type and change the value toSCREEN.

e Create a button named CUST_REP_BUTTON into the controlblock. Display this button on the CV_CUSTOMER canvas.

f Create a When-Button-Pressed trigger on theCUST_REP_BUTTON button to run the report.

g Change the Query All Record property value to Yes for thes_customer block.

h Save, run, and test your form.

DECLARE

myvar VARCHAR2(50);

BEGIN

myvar := RUN_REPORT_OBJECT(’my_report’);

END ;



If you have time...

3 Create a new tab page from which the user can run different reportlayouts.

a Open theForm_Builder_II.olb object library.

In the Object Navigator, double-click the icon next to theFORM_BUILDER_II object library node.

b From the Report tab page, select all the report objects and copy themto the CUSTOMERS form.

c From the Report tab page, select the REPORT block and copy thisblock to the CUSTOMERS form. Organize this block so it is the lastblock in sequence.

Do this in the Object Navigator.d From the Report tab page, select the REPORT tab page object, and

copy this tab page object to the CUSTOMERS form. Organize thistab page so it is the last tab page in sequence.

Do this in the Object Navigator.e Save, run, and test your form.



Practice 12 Solution1 Show «About...» information at the startup of the form.


b Add a window and a canvas to the form that are used to display thetwo control items. This window could be considered an «About...»window and should be a modal dialog window. Set the Hide on Exitproperty to Yes for this window.

In this solution, both the window and its canvas are calledABOUT. An «About...» window should be a modal dialogwindow.

c Create a new control block manually, called ABOUT. Create twoitems in the ABOUT block that are used to display the user name andthe current date and time.

In this solution, the control block is called ABOUT and the itemsare called USER_NAME and CLIENT_DATE.

d Show the «About...» window for a short period of time at the startupof the form. You can import thepr12_1d1.sqlandpr12_1d2.txtfiles.


When-Timer-Expired Trigger at Form Level

. . .

DECLARE

cst_show_time CONSTANT NUMBER(4) := 4000;

v_timer_id TIMER;

BEGIN

:about.user_name := USER;

:about.client_date := :SYSTEM.CURRENT_DATETIME;

v_timer_id := CREATE_TIMER('ABOUT',cst_show_time,NO_REPEAT );

GO_ITEM(’about.user_name’);

END;

IF GET_APPLICATION_PROPERTY( TIMER_NAME ) = ’ ABOUT’

THEN

GO_ITEM( ’s_customer.id’);

END IF;



If you have time...

2 Automatically ask the users if they want to commit after a set period oftime.


b At the startup of the form, create a global variable calledGLOBAL.LOCKS_PENDING, which indicates at all times whetherrows of the S_CUSTOMER table are locked.


c Create the On-Lock trigger to implement the default lock processing,update the global variable, and create the timer. You can import thepr12_2c.txtfile.

On-Lock Trigger at Form Level

. . .

DEFAULT_VALUE( NULL, ’GLOBAL.locks_pending’);

DECLARE

timer_id TIMER;

BEGIN

LOCK_RECORD;

IF FORM_SUCCESS

THEN

IF :GLOBAL.locks_pending IS NULL

THEN

:GLOBAL.locks_pending := ’TRUE’;

timer_id := CREATE_TIMER( ’locks’, 5000 );

END IF;

ELSE


END IF;

END;



d Write a When-Timer-Expired trigger to display an alert if locks arestill pending after a certain period of time elapses. This alert shouldask the user to commit or rollback the changes. You can import thepr12_2d.txtfile.

Note: You need to create an alert called ASK_SAVE.

Define an alert (called ASK_SAVE) of style Caution with a Yes, and aNo button. Define an appropriate message.

When-Timer-Expired Trigger at Form Level

e Create the Post-Database-Commit trigger to give the NULL value tothe global variable.You can import thepr12_2e.txtfile.

Post-Database-Commit Trigger at Form Level

IF GET_APPLICATION_PROPERTY( TIMER_NAME ) = ’ ABOUT’'

THEN

GO_ITEM('s_customer.id');

ELSIF GET_APPLICATION_PROPERTY( TIMER_NAME ) = ’ LOCKS’

THEN

IF :GLOBAL.locks_pending IS NOT NULL

THEN

IF SHOW_ALERT( ’ask_save’ ) = ALERT_BUTTON1

THEN

COMMIT_FORM;

ELSE

CLEAR_FORM( no_validate );

END IF;

IF :SYSTEM.FORM_STATUS <> ’CHANGED’

THEN

DELETE_TIMER(’locks’);

END IF;

END IF;

END IF;

:GLOBAL.locks_pending := NULL;



f Create the On-Rollback trigger to implement the default rollbackprocessing and give the NULL value to the global variable. You canimport thepr12_2f.txtfile.

On-Rollback Trigger at Form Level

ISSUE_ROLLBACK( GET_APPLICATION_PROPERTY( savepoint_name ) );

IF FORM_SUCCESS

THEN

:GLOBAL.locks_pending := NULL;

END IF;



Practice 13 Solution1 Create a picklist using the picklist class. This picklist allows the end user

to make selections using two lists, one showing the products availableand the other showing the objects selected.

Note: To reuse the code you wrote in practice 10 question 2, we haveprovided most of the code by way of an object library.

a Create a new form module.

b Open theForm_Builder_II.olb object library.

c From the Picklist Basic tab page, drag and copy thePICKLIST_BASIC object group to the Object Groups node in yourmodule and release it. This object group contains a canvas, awindow, and a block. The block contains two buttons, with the codeto return the products selected in the picklist to the orders form.

d From thePicklist Class tab page, copy the PICKLIST object groupinto your new form module. This object group contains all theobjects to implement the picklist. Organize the CONTROL block soit is the last block in sequence.

Do this in the Object Navigator.e Attach thepicklist.pll library to your form.



f Create a When-New-Form-Instance trigger to populate the List_In.Create an instance of the Picklist with theCreate_Picklistprocedure.Populate the List_in, usingpopulate_picklist_with_query function,with the results of a query that returns the Ids, Names and suggestedprices from the S_PRODUCT table (Retrieve the list of productsexcluding the products that exists in the S_ITEM block. You shoulduse the global variable from practice 11). Select the first element inthe list using theSet_picklist_selection procedure, and display bothlists usingdisplay_picklistprocedure. You can import thepr13_1f.txtfile.

g Save, and compile your form.

h Open the ORDERS form module.

i Modify the When-Button-Pressed trigger for theCONTROL.PRODUCT_LOV_BUTTON so that it calls your newform module.

j Save, run, and test your form.

DECLARE

dummy NUMBER;

BEGIN

DEFAULT_VALUE(’’, ’GLOBAL.where_cls’);

pick_list.create_picklist;

dummy :=pick_list.populate_picklist_with_query(pick_list.list_in,’SELECT name, id FROM s_product WHERE ’ || :GLOBAL.where_cls

|| ’ ORDER BY 1’);

pick_list.set_picklist_selection(pick_list.list_in, 1, NULL);

pick_list.display_picklist(pick_list.list_in);

pick_list.display_picklist(pick_list.list_out);

END;

. . .

CALL_FORM(’my_picklist_form’, NO_HIDE );

. . .



If you have time...

2 In the ORDERS form, add a calendar on the S_ORD.DATE_ORDEREDitem and S_ORD.DATE_SHIPPED item.

a From theCalendar page of the Form_Builder_II.olb object library,subclass theCalendar object group.

b Attach the PL/SQL librarycalendar.pll, which contains theDate_LOV package, to your module.

c Create a Key-Listval trigger on the date ordered item. Add code todisplay the calendar using the Date_LOV package. You can importthepr13_2c.txt file.

d Create a Key-Listval trigger on the date shipped item. Add code todisplay the calendar using the Date_LOV package.You can importthepr13_2d.txt file.


date_lov.get_date( sysdate, -- initial date

’s_ord.date_ordered’, -- return block.item

240, -- window x position

60, -- window y position

’Order Date’, -- window title

’OK’, -- ok button label

’Cancel’, -- cancel button label

TRUE, -- highlight weekend days

FALSE, -- autoconfirm selection

FALSE); -- autoskip after selection

date_lov.get_date( sysdate + 7, -- initial date

’s_ord.date_shipped’, -- return block.item

240, -- window x position

60, -- window y position

’Order Date’, -- window title

’OK’, -- ok button label

’Cancel’, -- cancel button label

TRUE, -- highlight weekend days

FALSE, -- autoconfirm selection

FALSE); -- autoskip after selection



Practice 14 Solution1 Handle errors caused by declarative constraints violations.

a Open the CUSTOMERS form module. This form is based on theS_CUSTOMER table, and one constraint is declared for this table.



c Run the CUSTOMERS form and try to add a new customer with anexisting customer number. What FRM-error message do you get?What Oracle Server error message do you get (press [Display Error]to see the message)?

FRM-40508: ORACLE error : unable to INSERT record.ORA-00001: unique constraint(schema.S_CUSTOMER_ID_PK) violated.

d Which trigger must you use to trap error messages caused byviolation of this constraint?

The On-Error trigger.e Which built-in functions must you use to get error messages caused

by violations of this constraint?

The DBMS_ERROR_CODE and DBMS_ERROR_TEXT built-in functions.



f Trap and replace the default constraint-violation message with yourown message. Use the function STRIP_CONSTRAINT_NAME todetect which constraint was violated. You can import thepr14_1f1.txtfile to create the function and thepr14_1f2.txtfile tocreate the trigger.

Note: Remove the existing code from the trigger before you import the file.

FUNCTION strip_constraint_name

/* Strips constraint name (without schema prefix) from server-error

message. */

( p_error_text IN VARCHAR2

)

RETURN VARCHAR2

IS

v_start_pos NUMBER(4);

v_end_pos NUMBER(4);

BEGIN -- strip_constraint_name

/* Get position of dot, starting from position of left

parenthesis. */

v_start_pos := INSTR( p_error_text, ’.’, INSTR( p_error_text,

’(’ ) );

IF v_start_po s > 0 THEN

/* Get position of right parenthesis, starting from

position of dot. */

v_end_pos := INSTR( p_error_text, ’)’, v_start_pos );

/* Return constraint name between dot and right

parenthesis. */

RETURN( SUBSTR( p_error_text, v_start_pos + 1, v_end_pos -v_

start_po s - 1 ) );

ELSE

RETURN( null );

END IF;

END strip_constraint_name;



On-Error Trigger at Form Level

If you have time...2 Handle errors caused by stored program units.

a Open the CUSTOMERS form module. This form is based on theS_CUSTOMER table, and one constraint is declared for this tables.



DECLARE

v_constraint_name VARCHAR2(255);

BEGIN

IF error_type = ’FRM’ AND error_code IN ( 40508, 40509,40510) THEN

IF DBMS_ERROR_CODE in ( -1, -2290, -2291, -2292 ) THEN

/* PK/UK/FK/CK-constraint violation error. */

v_constraint_name := strip_constraint_name(

DBMS_ERROR_TEXT );

IF v_constraint_name = ’S_CUSTOMER_ID_PK’ THEN

MESSAGE( ’This customer already exists.’ );

END IF;


END IF;

END IF;

/* Show default FRM-error message. */

MESSAGE( ERROR_TYPE || TO_CHAR( -ERROR_CODE ) || ’: ’ ||

ERROR_TEXT );


END;



c Implement immediate primary-key checking in the CUSTOMERSform by creating a procedure called CHECK_PK_CUST. Call thisprocedure from an appropriate trigger. You can import thepr14_2c1.txtfile to create the procedure and thepr14_2c2.txtfile tocreate the trigger.

When-Validate-Item Trigger on the S_CUSTOMER.ID Item

PROCEDURE check_pk_cust

( p_cust_id IN NUMBER

, p_rowid IN VARCHAR2

)

IS

CURSOR c_cust is

SELECT 0

FROM s_customer d

WHERE d.id = p_cust_id

AND ( d.rowid <> p_rowid OR p_rowid IS NULL );

r_cust c_cust%ROWTYPE;

cust_exists EXCEPTION;

BEGIN -- check_pk_cust

OPEN c_cust;

FETCH c_cust INTO r_cust;

IF c_cust%FOUND THEN

CLOSE c_cust;

RAISE cust_exists;

ELSE

CLOSE c_cust;

END IF;

EXCEPTION

WHEN cust_exists THEN

MESSAGE( ’This customer already exists.’ );


END check_pk_cust;

:global.customer_id := :s_customer.id;

CHECK_PK_CUST( :s_customer.id, :s_customer.rowid);



d Run the form and try to add a new customer with an existingcustomer number. What FRM-error do you get?

This customer already exists.This is the error message specified by the Forms Designer inprocedure CHECK_PK_CUST. It is not an FRM-error messageor Oracle Server error.

e Drag the CHECK_PK_CUST procedure to the database under youruser account.

f Explain the errors that are shown in the Stored Program Unit Editorfor this procedure. Correct the errors by usingRAISE_APPLICATION_ERROR.

The errors are caused because MESSAGE is a built-in procedureand FORM_TRIGGER_FAILURE is a built-in exception toOracle Forms. It is therefore not known in the Oracle Server.Correct this by replacing the built-in procedure and the built-inexception with a single statement, for example:

g Delete or rename the local CHECK_PK_CUST procedure in yourform. Run the form and try to add a new customer with an existingcustomer number. What FRM-error messages do you get? WhatOracle Server error message do you get (press [Display Error] to seethe message)?

FRM-40735: WHEN-VALIDATE-ITEM trigger raisedunhandled exception ORA-20000.After pressing [Display Error] you get the following message:FRM-42100: No errors encountered recently.

h Which trigger must you use to trap error messages caused by storedprocedures?

You must trap the error messages in the trigger in which you callthe stored procedure, in this case the When-Validate-Itemtrigger.

i Which built-in functions must you use to get error messages causedby stored procedures?

The SQLCODE and SQLERRM functions.

RAISE_APPLICATION_ERROR( -20000, ’This customer already exists.’);



j Trap and replace the default error message with your own message.Use the function STRIP_APPLICATION_ERROR to get theapplication-error text. You can import thepr14_2j1.txtfile to createthe function and thepr14_2j2.txt.txtfile to create the trigger.

FUNCTION strip_application_error

/* Strips application-error message, without error number, from

server-error message.

This is the first error of the full server-error message. */

( p_error_text in varchar2

)

RETURN VARCHAR2

IS

v_end_pos NUMBER(4);

BEGIN -- strip_application_error

/* Get position of (second) ORA-error, starting from

position 5. */

v_end_pos := INSTR( p_error_text, ’ORA-’ , 5 ) - 2;

IF v_end_po s > 0 THEN

/* Return application error message (without ORA-error

number) before next ORA-error. */

RETURN( SUBSTR( p_error_text, 12, v_end_pos - 11 ) );

else

/* Server error only contains application error; remove

ORA-error in front. */

RETURN( SUBSTR( p_error_text, 12 ) );

END IF;

END strip_application_error;



When-Validate-Item Trigger on the S_CUSTOMER.ID Item

:GLOBAL.customer_id := :s_customer.id;

check_pk_cust( :s_customer.id, :s_customer.rowid) ;

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE BETWEEN -20999 AND -20000 THEN

/* Application error caused by database trigger or

stored program unit. */

MESSAGE( strip_application_error( DBMS_ERROR_TEXT ) );


END IF;

................................

B

Table Descriptionsand Data

B-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix B: Table Descriptions and Data

Summit Sporting Goods Database Diagram

*Unique occurrences are identified by PRODUCT_ID and WAREHOUSE_ID.

S_ITEM

S_INVENTORY

S_ORD

S_CUSTOMER

S_EMP

S_DEPT

S_IMAGE

S_PRODUCT

ORD_IDID

SALES_REP_ID

PRODUCT_ID

CUSTOMER_ID

SALES_REP_ID

DEPT_ID

IMAGE_ID

PRODUCT_ID

ID

ID

ID

IDID

ID

*

ID

Developer/2000: Build Forms II B-3......................................................................................................................................................

......................................................................................................................................................S_CUSTOMER Description

S_CUSTOMER Description

Column Name Null? Datatype----------------------------------------- -------------------- -------------------------ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(50)PHONE VARCHAR2(25)ADDRESS VARCHAR2(400)CITY VARCHAR2(30)STATE VARCHAR2(20)COUNTRY VARCHAR2(30)ZIP_CODE VARCHAR2(75)CREDIT_RATING VARCHAR2(9)SALES_REP_ID NUMBER(7)REGION_ID NUMBER(7)COMMENTS VARCHAR2(255)



S_CUSTOMER Data

Continued on Next Page

SQL> SELECT * FROM s_customer;

Id Name Phone Address------- ------------------ ------------ -----------City State Country-------------------- ---------------- ------------Zip_code Credit_ra Sales_rep_id Region_id---------- --------- ------------ ---------Comments--------------------------------------------------

201 Unisports 55-2066101 72 Via BahiaSao Paolo Brazil

Excellent 12 2Customer Usually Orders Large Amounts And Has A HighOrder Total. This Is Okay As Long As The Credit RatingRemains Excellent.

202 Oj Atheletics 81-20101 6741 TakashiBlvd.Osaka Japan

Poor 14 4Customer Should Always Pay By Cash Until His CreditRating Improves.

203 Delhi Sports 91-10351 11368ChanakyaNew Delhi India

Good 14 4Customer Specializes In Baseball Equipment And Is TheLargest Retailer In India.


......................................................................................................................................................S_CUSTOMER Data (continued)

S_CUSTOMER Data (continued)



204 Womansport 1-206-104-0103 281 KingStreetSeattle Washington USA98101 EXCELLENT 11 1

205 Kam’s Sporting Goods 852-3692888 15 HenesseyRoadHong Kong EXCELLENT 15 4

206 Sportique 33-2257201 172 Rue deRivoliCannes France

EXCELLENT 15 5Customer specializes in Soccer. Likes to orderaccessories in bright colors.

207 Sweet Rock Sports 234-603620 6 SaintAntoineLagos Nigeria

GOOD 3






208 Muench Sports 49-527454 435GruenestrasseStuttgart Germany

GOOD 15 5Customer usually pays small orders by cash and largeorders on credit.

209 Beisbol Si! 809-352689 789 Playa DelMarSan Pedro de Macon’s DominicanEXCELLENT 11 1 Republic

210 Futbol Sonora 52-404562 3 ViaSaguaro Nogales

EXCELLENT 12 2Customer is difficult to reach by phone. Try mail.

211 Kuhn’s Sports 42-111292 7 ModranyPrague Czechoslova-kia

EXCELLENT 15 5


......................................................................................................................................................S_CUSTOMER Data (continued)


Note: The above display has been formatted.


212 Hamada Sport 20-1209211 57A CornicheAlexandria Egypt

EXCELLENT 13 3Customer orders sea and water equipment.

213 Big John’s Sports 1-415-555-62814783 18thStreet EmporiumSan Francisco CA USA94117 EXCELLENT 11 1Customer has a dependable credit record.

214 Ojibway Retail 1-716-555-7171415 Main StreetBuffalo NY USA14202 POOR 11 1

215 Sporta Russia 7-3892456 6000YekataminaSt. Petersburg Russia

POOR 15 5This customer is very friendly, but has difficultypaying bills. Insist upon cash.

15 rows selected.



S_DEPT Description and DataColumn Name Null? Datatype--------------------------------------------------------------------------------------ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(25)REGION_ID NUMBER(7)

SQL> SELECT * FROM s_dept;

ID NAME REGION_ID------- --------------------- ---------

10 Finance 131 Sales 132 Sales 233 Sales 334 Sales 435 Sales 541 Operations 142 Operations 243 Operations 344 Operations 445 Operations 550 Administration 1

12 rows selected.


......................................................................................................................................................S_EMP Description

S_EMP DescriptionColumn Name Null? Datatype----------------------------------------- -------------------- -------------------------ID NOT NULL NUMBER(7)LAST_NAME NOT NULL VARCHAR2(25)FIRST_NAME VARCHAR2(25)USERID VARCHAR2(8)START_DATE DATECOMMENT VARCHAR2(255)MANAGER_ID NUMBER(7)TITLE VARCHAR2(25)DEPT_ID NUMBER(7)SALARY NUMBER(11,2)COMMISSION_PCT NUMBER(4,2)



S_EMP Data


SQL> SELECT * FROM s_emp;

ID LAST_NAME FIRST_NAME USERID START_DAT---- ----------------- ----------- -------- ---------COMMENTS---------------------------------------------------MANAGER_ID TITLE DEPT_ID SALARY---------- -------------------- ------- ------COMMISSION_PCT---------------

1 Velasquez Carmen cvelasqu 03-MAR-90President 50 2500

2 Ngao LaDoris lngao 08-MAR-901 VP, Operations 41 1450

3 Nagayama Midori mnagayam 17-JUN-911 VP, Sales 31 1400

4 Quick-To-See Mark mquickto 07-APR-901 VP, Finance 10 1450

5 Ropeburn Audry aropebur 04-MAR-901 VP, Administration 50 1550

6 Urguhart Molly murguhar 18-JAN-912 Warehouse Manager 41 1200

7 Menchu Roberta rmenchu 14-MAY-902 Warehouse Manager 42 1250

8 Biri Ben bbiri 07-APR-902 Warehouse Manager 43 1100

9 Catchpole Antoinette acatchpo 09-FEB-922 Warehouse Manager 44 1300


......................................................................................................................................................S_EMP Data (continued)

S_EMP Data (continued)



10 Havel Marta mhavel 27-FEB-912 Warehouse Manager 45 1307

11 Magee Colin cmagee 14-MAY-903 Sales Representative 31 1400

10

12 Giljum Henry hgiljum 18-JAN-923 Sales Representative 32 1490

12.5

13 Sedeghi Yasmin ysedeghi 18-FEB-913 Sales Representative 33 1515

10

14 Nguyen Mai mnguyen 22-JAN-923 Sales Representative 34 1525

15

15 Dumas Andre adumas 09-OCT-913 Sales Representative 35 1450

17.5

16 Maduro Elena emaduro 07-FEB-926 Stock Clerk 41 1400



S_EMP Data (continued)



17 Smith George gsmith 08-MAR-906 Stock Clerk 41 940

18 Nozaki Akira anozaki 09-FEB-917 Stock Clerk 42 1200

19 Patel Vikram vpatel 06-AUG-917 Stock Clerk 42 795

20 Newman Chad cnewman 21-JUL-918 Stock Clerk 43 750

21 Markarian Alexander amarkari 26-MAY-918 Stock Clerk 43 850

22 Chang Eddie echang 30-NOV-909 Stock Clerk 44 800

23 Patel Radha rpatel 17-OCT-909 Stock Clerk 34 795

24 Dancs Bela bdancs 17-MAR-9110 Stock Clerk 45 860

25 Schwartz Sylvie sschwart 09-MAY-9110 Stock Clerk 45 1100

25 rows selected.


......................................................................................................................................................S_IMAGE Description and Data

S_IMAGE Description and DataName Null? TypeID NOT NULL NUMBER(7)FORMAT VARCHAR2(25)USE_FILENAME VARCHAR2(25)FILENAME VARCHAR2(25)IMAGE LONG RAW

SQL> SELECT * FROM s_image;



S_INVENTORY DescriptionName Null? Type

PRODUCT_ID NOT NULL NUMBER(7)WAREHOUSE_ID NOT NULL NUMBER(7)AMOUNT_IN_STOCK NUMBER(9)REORDER_POINT NUMBER(9)MAX_IN_STOCK NUMBER(9)OUT_OF_STOCK_EXPLANATION VARCHAR2(255)RESTOCK_DATE DATE


......................................................................................................................................................S_INVENTORY Data

S_INVENTORY Data


SQL> SELECT * FROM s_inventory;



S_INVENTORY Data (continued)



......................................................................................................................................................S_INVENTORY Data (continued)




......................................................................................................................................................S_ITEM Description

S_ITEM DescriptionColumn Name Null? Datatype

--------------------------------------------------------------------------------------ORD_ID NOT NULL NUMBER(7)ITEM_ID NOT NULL NUMBER(7)PRODUCT_ID NOT NULL NUMBER(7)PRICE NUMBER(11,2)QUANTITY NUMBER(9)QUANTITY_SHIPPED NUMBER(9)



S_ITEM Data


SQL> SELECT * FROM s_item;

ORD_ID ITEM_ID PRODUCT_ID PRICE QUANTITY QUANTITY_SHIPPED------ ------- ---------- ----- -------- ----------------

100 1 10011 135 500 500100 2 10013 380 400 400100 3 10021 14 500 500100 5 30326 582 600 600100 7 41010 8 250 250100 6 30433 20 450 450100 4 10023 36 400 400101 1 30421 16 15 15101 3 41010 8 20 20101 5 50169 4.29 40 40101 6 50417 80 27 27101 7 50530 45 50 50101 4 41100 45 35 35101 2 40422 50 30 30102 1 20108 28 100 100102 2 20201 123 45 45103 1 30433 20 15 15103 2 32779 7 11 11104 1 20510 9 7 7104 4 30421 16 35 35104 2 20512 8 12 12104 3 30321 1669 19 19105 1 50273 22.8 16 16105 3 50532 47 28 28105 2 50419 80 13 13


......................................................................................................................................................S_ITEM Data (continued)

S_ITEM Data (continued)


ORD_ID ITEM_ID PRODUCT_ID PRICE QUANTITYQUANTITY_SHIPPED-------------------------- ----- -------- ----------- ------------------

106 1 20108 28 46 46106 4 50273 22.89 75 75106 5 50418 75 98 98106 6 50419 80 27 27106 2 20201 123 21 21106 3 50169 4.29 125 125107 1 20106 11 50 50107 3 20201 115 130 130107 5 30421 16 55 55107 4 30321 1669 75 75107 2 20108 28 22 22108 1 20510 9 9 9108 6 41080 35 50 50108 7 41100 45 42 42108 5 32861 60 57 57108 2 20512 8 18 18108 4 32779 7 60 60108 3 30321 1669 85 85109 1 10011 140 150 150109 5 30426 18.25 500 500109 7 50418 75 43 43109 6 32861 60 50 50109 4 30326 582 1500 1500109 2 10012 175 600 600109 3 10022 21.95 300 300110 1 50273 22.89 17 17110 2 50536 50 23 23111 1 40421 65 27 27111 2 41080 35 29 29

97 1 20106 9 1000 100097 2 30321 1500 50 5098 1 40421 85 7 799 1 20510 9 18 1899 2 20512 8 25 2599 3 50417 80 53 5399 4 50530 45 69 69

112 1 20106 11 50 50

62 rows selected.



S_ORD Description and DataColumn Name Null? Datatype

--------------------------------------------------------------------------------------ID NOT NULL NUMBER(7)CUSTOMER_ID NOT NULL NUMBER(7)DATE_ORDERED DATEDATE_SHIPPED DATESALES_REP_ID NUMBER(7)TOTAL NUMBER(11,2)PAYMENT_TYPE VARCHAR2(6)ORDER_FILLED VARCHAR2(1)

SQL> SELECT * FROM s_ord;

ID CUSTOMER_ID DATE_ORDE DATE_SHIP SALES_REP_ID TOTAL PAYMEN ORDER_F

--- ----------- --------- --------- ------------ ------- ------ -------

100 204 31-AUG92 10-SEP-92 11 601100 CREDIT Y

101 205 31-AUG-92 15-SEP-92 14 8056.6 CREDIT Y

102 206 01-SEP-92 08-SEP-92 15 8335 CREDIT Y

103 208 02-SEP-92 22-SEP-92 15 377 CASH Y

104 208 03-SEP-92 23-SEP-92 15 32430 CREDIT Y

105 209 04-SEP-92 18-SEP-92 11 2722.24 CREDIT Y

106 210 07-SEP-92 15-SEP-92 12 15634 CREDIT Y

107 211 07-SEP-92 21-SEP-92 15 142171 CREDIT Y

108 212 07-SEP-92 10-SEP-92 13 149570 CREDIT Y

109 213 08-SEP-92 28-SEP-92 11 1020935 CREDIT Y

110 214 09-SEP-92 21-SEP-92 11 1539.13 CASH Y

111 204 09-SEP-92 21-SEP-92 11 2770 CASH Y

97 201 28-AUG-92 17-SEP-92 12 84000 CREDIT Y

98 202 31-AUG-92 10-SEP-92 14 595 CASH Y

99 203 31-AUG-92 18-SEP-92 14 7707 CREDIT Y

112 210 31-AUG-92 10-SEP-92 12 550 CREDIT Y

16 rows selected.


......................................................................................................................................................S_PRODUCT Description

S_PRODUCT DescriptionColumn Name Null? Datatype

----------------------------------------- -------------------- -------------------------ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(50)SHORT_DESC VARCHAR2(255)LONGTEXT_ID NUMBER(7)IMAGE_ID NUMBER(7)SUGGESTED_WHLSL_PRICE‘ NUMBER(11,2)WHLSL_UNITS VARCHAR2(25)



S_PRODUCT Data


SQL> SELECT * FROM s_product;

ID NAME SHORT_DESC LONGTEXT_ID---- --------------- --------------- -----------

IMAGE_ID SUGGESTED_WHLSL_PRICE WHLSL_UNITS-------- --------------------- -----------

10011 Boot Beginner’s ski boot 5181001 150

10012 Ace Ski Boot Intermediate ski boot 5191002 200

10013 Pro Ski Boot Advanced ski boot 5201003 410

10021 Bunny Ski Pole Beginner’s ski pole 5281011 16.25

10022 Ace Ski Pole Intermediate ski pole 5291012 21.95

10023 Pro Ski Pole Advanced ski pole 5301013 40.95

20106 Junior Soccer BallJunior soccer ball61311

20108 World Cup Soccer Ball World cup soccer ball61528

20201 World Cup Net World cup net 708123


......................................................................................................................................................S_PRODUCT Data (continued)

S_PRODUCT Data (continued)




20510 Black Hawk Knee pads, pair 1017Knee Pads 9

20512 Black Hawk Elbow pads, pair 1019Elbow Pads 8

30321 Grand Prix Bicycle Road bicycle 828 1291 1669

30326 Himalaya BicycleMountain bicycle 8331296 582

30421 Grand Prix Road bicycle tires 927Bicycle Tires 16

30426 Himalaya Tires Mountain bicycle tires 93318.25

30433 New Air Pump Tire pump 94020

32779 Slaker Water Water bottle 1286Bottle 7

32861 Safe-T Helmet Bicycle helmet 13681829 60

40421 Alexeyer Pro Straight bar 928Lifting Bar

1381 65

40422 Pro Curling Bar Curling bar 9291382 50







40421 Alexeyer Pro Straight bar 928Lifting Bar

1381 65

40422 Pro Curling Bar Curling bar 9291382 50

41010 Prostar 10 Ten pound weight 517Pound Weight

8

41020 Prostar 20 Twenty pound weight 527Pound Weight

12

41050 Prostar 50 Fifty pound weight 557Pound Weight

25

41080 Prostar 80 Eighty pound weight 587Pound Weight

35

41100 Prostar 100 One hundred pound 607Pound Weight weight

45

50169 Major League Baseball 676Baseball

1119 4.29


......................................................................................................................................................S_PRODUCT Data (continued)





50273 Chapman Helmet Batting helmet 7801223 22.89

50417 Griffey Glove Outfielder’s glove 9241367 80

50418 Alomar Glove Infielder’s glove 9251368 75

50419 Steinbach Glove Catcher’s glove 9261369 80

50530 Cabrera Bat Thirty inch bat 10371480 45

50532 Puckett Bat Thirty-two inch bat 10391482 47

50536 Winfield Bat Thirty-six inch bat 10431486 50

33 rows selected.



S_REGION Description and DataColumn Name Null? Datatype----------------------------------------- -------------------- -------------------------ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(50)

SQL> SELECT * FROM s_region;

ID NAME--- ---------------------------

1 North America2 South America3 Africa / Middle East4 Asia5 Europe


......................................................................................................................................................S_TITLE Description and Data

S_TITLE Description and DataColumn Name Null? Datatype--------------------------------------------------------------------------------------TITLE NOT NULL VARCHAR2(25)

SQL> SELECT * FROM s_title;

TITLE------------------------PresidentSales RepresentativeStock ClerkVP, AdministrationVP, FinanceVP, OperationsVP, SalesWarehouse Manager

8 rows selected.

................................

C

Project BuilderAddendum

C-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix C: Project Builder Addendum

C-2 Copyright Oracle Corporation, 1998. All rights reserved.

Objectives


• Create connections

• Implement the team development withProject Builder

Developer/2000: Build Forms II C-3......................................................................................................................................................

......................................................................................................................................................Introduction

Introduction

OverviewYou have already seen how to use Project Builder. This lesson explains howto use Developer/2000 Project Builder in a team environment, so that manydevelopers can be working together on the many files that make up a project.


• Customize Project Builder environment.

- Create connections

• Team development with Project Builder

- Provide a Global Registry of common types.

- Import/Export projects.

- Print and distribute project reports.




Creating Connections


Assigning a Connect String


......................................................................................................................................................Creating Connections

Creating ConnectionsIn a typical project the developer has a number of databases that he worksagainst. For example, there might be a small “scratch” database where thedeveloper can create any test data he needs; a large test database thatcontains the data for a formal test suite; and perhaps databases runningdifferent versions of Oracle to test backward compatibility or new features.

Project Builder allows the developer to define and save the connect stringsassociated with these various databases, and then easily associate thoseconnections with the project’s files, simply by dragging a connection anddropping it on the entry for the file. When the Form module is next openedfrom Project Builder, Form Builder will automatically connect to thedatabase before opening the form.

Define a Connect String1 Select the Connections node in the Project Navigator and choose New

Connection from the pop-up menu to display the Add Connection dialogbox.

2 Type a title in the Title text box. The title provides the label for theconnect string in the Project Navigator.

3 Type a username, password, and connect string in the corresponding textboxes.

4 (Optional) Add comments in the Comment text field.

5 Click OK to accept the dialog and add the connection to your list ofpossible connections.

Assign a Connect String to a Project or a Project Item1 In the Project Navigator, expand the Connections node. Select the

connection you want to associate with the project or project item.

2 Drag and drop the connection on the project or project item.

The new connect string will be inserted into the appropriate actions forall items in the project (if you assigned it to a project) or the project item.



Technical NoteIn some cases, the project administrator may decide to share a project andallow more than one user to update it. If a developer attempts to change aproject, adding an entry for example, Project Builder first checks whetherthe project has changed since it was last read; if it has changed, ProjectBuilder notifies the developer that the changes cannot be saved, and rereadsthe latest version of the project. The developer can then attempt to make thechanges again. Project Builder never overwrites one developer’s changeswith another’s, thus avoiding “last write wins” problems.


Team Development with ProjectBuilder

Projectadministrator

Teammembers


......................................................................................................................................................Team Development with Project Builder

Team Development with Project BuilderIn many cases you will want to use Project Builder in a team environment,so that many developers can be working together on the many files that makeup a project.

If one developer modifies a library that another depends on, you will wantProject Builder’s dependency management to track that dependency, just asit would for a single user.

The Project AdministratorA project administrator is charged with creating projects and making themavailable to developers or team members. The project administratormaintains the Global Registry and modifies it as necessary, exporting thechanges to the developers on the team.




The Role of the Global Registry

• Shared by all developers

• Placed on a shared network drive

• Modified by the project administrator

• ORACLE_HOME\PJ10\types nn.upd

– nn indicates the national language


The Role of the User Registry

• Private to each user

• Placed on a local drive or on a networkdrive

• Used to add private types

• Used to override the definitions of typesin the Global Registry



The Role of the Global RegistryCommonly, the Global Registry will be shared by all developers. It containsthe type definitions that are shared across the team. To make it accessible toeverybody, the project administrator may place it on a shared network drive.Because this registry is shared by the whole team, to avoid conflictingchanges only the project administrator should make changes to it.

The recommended way to ensure this is to use the network file system’s filepermissions and access control to ensure that, although the whole team canread this file, only the project administrator can modify it. Protecting theGlobal Registry in this way also prevents accidental damage, such as a userinadvertently moving, deleting or overwriting the registry file.

The Global Registry file name istypesnn.updwherenn indicates the nationallanguage. By default, this file is installed to ORACLE_HOME\PJ10.

This file may be globally writable on your platform by default.

The Role of the User RegistryAs its name suggests, the User Registry is private to each individual user,and it will normally be placed either on a local drive on each user’s PC, or ona private directory on a network drive. (Under Unix it is placed under theuser’s home directory).

Because each user has their own User Registry, they can edit it freely, forexample to add private types or to override the definitions of types in theGlobal Registry.




Importing and ExportingProjects

1. Choose File—>Export or Import Project

2. Enter details

Saved toORACLE_HOME\PJ10\pjuser nn.upd

nn indicates the national language



Importing and Exporting ProjectsProjects are each stored each in their own Project file. Projects may beprivate to an individual developer, or shared among several.

If a project is shared, the Project Administrator may decide to provide eachdeveloper with a private copy of the Project file, or they may share a singlecopy via a shared network drive. The best choice depends on whether all oronly one developer is allowed to modify the project.

If only one developer is allowed to modify a particular project, the simplestapproach is to place the Project file on a file server, and protect the fileagainst update in the same way as for the Global Registry.

If more than one user is allowed to modify a project, the recommended wayto share these changes is for the project administrator to provide each userwith a private copy of the project. The user’s individual changes can then beshared using Project Builder’s export and import commands.

How to Export Projects1 Choose File—>Export Project.

2 Enter Details.

3 Click the Export button.

The project is exported to a file namedORACLE_HOME\PJ10\pjusernn.upd

nn indicates the national language




Generating and Printing ProjectReports


......................................................................................................................................................Generating and Printing Project Reports

Generating and Printing Project ReportsWith Project Builder, you can automatically generate and print projectreports showing information about the projects under development. Aproject report can include information about types you have defined, as wellas actions and macros that work on them, and information about the toolsincluded in the Launcher.

Choose File—>Print Project Info. This will display the Print Project ReportsDialog Box.

Option Description

Print Typedefinitions

Click this checkbox if you want your report to contain type definitions.

Print Itemdefinitions

Click this checkbox if you want your report to contain item definitions.

Selected itemonly

Choose this to print definitions for only the items selected in the Navigator.

Selected itemand itsdescendants

Choose this to print definitions for the items selected in the Navigator, andthe descendants of those items.

All items inproject

Choose this to print definitions for all items in the selected project.

Includeactions

Choose this to include information on defined actions.

Include user-definedmacros

Choose this to include information on user-defined macros.

IncludeLauncherinformation

Choose this to include information on the current configuration of theLauncher.




Summary

• Create connect strings

• Team development with Project Builder


......................................................................................................................................................Summary

Summary

Customizing the Project Builder Environment• Create connections

Team Development with Project Builder• Provide a Global Registry of common types

• Import and export projects

• Print and distribute project reports

................................

D

Oracle Terminal 2 andDeveloper/2000 Forms

D-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix D: Oracle Terminal 2 and Developer/2000 Forms

Oracle Terminal 2 &Developer/2000 Forms

An Oracle White Paper

July 1997

Oracle Terminal 2 & Developer/2000 Forms -1

Oracle Terminal 2 & Developer/2000 Forms

INTRODUCTION ....................................................................... 2

What is Oracle Terminal 2?................................................................. 2

A brief history of Terminal.................................................................. 2

Upgrading Forms 3 to Developer/2000 Forms.................................... 3

VERSIONS AND PLATFORMS................................................. 4

Where does Forms find the resource file?........................................... 5MS Windows 3.1/NT/95 ..............................................................................5Unix.............................................................................................................5OpenVMS....................................................................................................6

KEY MAPPINGS ........................................................................ 7

Key Mapping Hierarchy...................................................................... 7

Product Actions................................................................................... 8

What is tk2?......................................................................................... 9

Understanding the Keyboard............................................................. 11

Standard Keys and names................................................................. 11

What key was pressed?...................................................................... 13

A note on Terminal Emulators.......................................................... 14

A note on Modifiers........................................................................... 14

The Control Key................................................................................ 16

When is KEY-F1 not the F1 Key?..................................................... 17

DISPLAY ATTRIBUTES .......................................................... 18

Device Attributes............................................................................... 18

Logical Attributes.............................................................................. 19Character Mode example ........................................................................... 19MS Windows example ............................................................................... 20Using DISPLAY_ITEM............................................................................. 21

Uifont.ali and Character-mode Forms............................................... 22

Character-mode Terminal Setup....................................................... 23

What appears in Show Keys?............................................................ 24

CONCLUSION.......................................................................... 24

2 - Oracle Terminal 2 & Developer/2000 Forms

INTRODUCTION

What is Oracle Terminal 2?

Oracle Terminal 2 is a utility which allows a system administrator to makemodifications to certain types of Toolkit 2 resource files includingDeveloper/2000 Forms resource files. These resource files can contain :-

• terminal characteristics for character-mode devices

• configuration of display attributes

• list of product actions available

• mappings of physical keys to product actions

The following article attempts to give an explanation of the way that OracleTerminal 2 works and it’s role in the implementation of Forms applications.

A brief history of Terminal

Prior to SQL*Forms V3, Forms did not use the toolkit for handling interactionwith the screen and keyboard - the terminal characteristics of Forms 2.3 wereconfigured using a product called CRT.

With the introduction of SQL*Forms V3, Toolkit V1 resource files were usedwhich may be edited using Oracle Terminal 1 (or Oraterm). CRT definitionscan be upgraded to Toolkit V1 files (with a .r extension) using theCRTTORES utility. See the Oracle Terminal User’s Guide for details.

Forms 4 was the first version of Forms to use Toolkit 2 and it’s associatedresource files (.res files) and these are modified using Terminal 2. Note that inthe same way that Developer/2000 Forms cannot directly open a SQL*FormsV3 form, Terminal 2 cannot read resource files from Toolkit V1 directly asthey have a different internal structure. In order to use these files, they mustfirst be upgraded using OTX - see Note:32886.1 for details.


Upgrading Forms 3 to Developer/2000 Forms

There are a large number of architectural differences between SQL*FormsV3 and Developer/2000 Forms in terms of the functionality available withinthe product. This also means that there are many more actions and associatedkeys to perform them available within Forms e.g. the window handling keys,pop-lists, buttons etc. Thus, there are many manual actions which will need tobe performed after using OTX to get a complete, working resource file. Youwill also find that Forms 4 is much less forgiving of inconsistencies in aresource file and what may have worked in SQL*Forms V3 may now beillegal.

If you are using only a slightly modified SQL*Forms V3 resource file it willoften be simpler to take the equivalent resource file supplied with Forms 4and make the equivalent changes using Terminal 2.

Also note that OTX will only upgrade a resource file to an equivalentcharacter mode resource file - it will not provide a simple way to get yourForms 4.5 MS Windows application to use the same key mappings that yourcharacter-based Forms 3 application used. In fact, because of architecturaldifferences it is almost impossible to develop a Forms 4.5 resource file onWindows which allows exactly the same keystrokes as those used whenrunning the same application in character mode.

It is not possible to port a character mode Toolkit 2 resource file to MSWindows or vice-versa - the basic types are charmode, Windows and Motifand they are in general incompatible.


VERSIONS AND PLATFORMS

Terminal 2 can be run as a character mode application but it can also be runon MS Windows and Motif as a GUI application. The executable namesdiffer depending on the platform and the version, but typically :-

Version Character Mode Motif MS Windows

2.0 t20des

oraterm20

t20desm

oraterm20m

ot

2.1 t21des

oraterm21

t21desm

oraterm21m

ot21

2.3 t23des

oraterm23

t23desm

oraterm23m

ot23

Terminal (and Toolkit) version numbers correspond generally with Formsversions, thus :-

Forms Terminal

4.0.13 2.0

4.5.5 & 4.5.6 2.1

4.5.7 2.3


Where does Forms find the resource file?

MS Windows 3.1/NT/95

On the MS Windows platforms, the default resource file is namedfmrusw.res and is located in the \ORAWIN\FORMS40 or the\ORAWIN\FORMS45 directory.

To use a custom resource file, either replace the one supplied (after taking abackup copy of the original) or take a copy of the resource file and place it inthe same directory (i.e. \ORAWIN\FORMS4x) or the working directory ofthe F45RUN icon.

To specify the name of the resource file on the command line use :-

F45RUN module=... TERM=myfile:windows

where myfile.res is the name of the customised resource file. There is noORACLE.INI parameter or registry entry which can be set to alter either thename of the default resource file used or it’s location.

Note that running from the designer will always use the default fmrusw.resfile in the FORMS4x directory.

Unix

On Unix operating systems, the resource file will be picked up eitherfrom a single default directory or from a directory indicated by anenvironment variable if set. For Motif resource files, the filename isfmrm.res. For character mode resource files, the file has the namefmrc<TERM>.res where <TERM> is picked up from

1. The TERM= parameter on the f45run command line

2. The ORACLE_TERM environment variable

3. The TERM environment variable

e.g. If TERM=vt220 is specified on the command line to f45run, the resourcefile fmrcvt220.res is used

The default locations and path environment variables are as follows :-

Motif

Default File

$ORACLE_HOME/forms4x/admin/resource/US/fmrm.res

Env. Variable FORMS4x_RESOURCE


Character Mode

Default File

$ORACLE_HOME/forms45/admin/terminal/US/fmrc<TERM>.res

Env. Variable FORMS4x_TERMINAL

To confirm which resource file is being used and why (and as an aid toproblem diagnosis), set the environment variable DEBUG_SLFIND to afilename (Forms 4.0.13 onwards) and examine the trace output.

e.g.

setenv DEBUG_SLFIND debug.txt

f45run module=forma userid=scott/tiger

more debug.txt

OpenVMS

On OpenVMS systems, the resource file will be picked up either froma single default directory or from a directory indicated by a logical ifset. For Motif resource files, the filename is fmrm.res. For charactermode resource files, the file has the name fmrc<TERM>.res where<TERM> is picked up from

1. The TERM= parameter on the f45run command line

2. The ORACLE_TERM logical

e.g. If TERM=vt220 is specified on the command line to f45run, the resourcefile fmrcvt220.res is used

The default locations and path logicals are as follows :-

Motif

Default File

ORA_ROOT:[ORAFORMS4x.ADMIN.RESOURCE.US]fmrm.res

Logical FORMS4x_RESOURCE

Character Mode

Default File

ORA_ROOT:[ORAFORMS4x.ADMIN.TERMINAL.US]fmrc<TERM>.res

Logical FORMS4x_TERMINAL

To debug, use logical DEBUG_SLFIND (Forms 4.0.13 onwards) as for Unix.


KEY MAPPINGS

Key Mapping Hierarchy

Oracle Terminal allows the administrator to configure which keys correspondto particular actions within Forms1 for a specific context. The fact that it iscontext sensitive allows the same action to be performed by a different keydepending on what type of item currently has focus. For example, Select Textin a normal text item (defined under tk2 -> viewclasses -> field - see later)can be performed by a different key to that used in a multi-line item (definedunder tk2 -> viewclasses -> tedit).

These bindings are hierarchical e.g.

Highest Level <---------------------------> Lowest Level

-------o | help | -------o | editor --------o------- | runform |------o sqlforms --| listvalues | --------o------| design

and mappings at a higher level will be available at all lower levels unless theyare overridden at that lower level. Thus, Show Keys, which is mapped at thesqlforms level, is common to all areas, both at runtime and design.

There are notes available from the UK Support Notes system which showexamples of this structure e.g. Note:33344.1 shows a typical hierarchy for avt220 resource file including the mappings defined at each level. There arealso tutorials which explain in detail (with examples) how to map keys so thisis not covered in detail here - see Note:28834.1 and Note:14845.1.

1 Please note that some resource files shipped with Developer/2000 Forms onUnix and OpenVMS are affected by a bug which prevents any changes to thekey bindings being see by Forms. See Note:33477.1 for a straightforward fix.


Product Actions

The terminal resource file also contains details of Product Actions - thesedefine the actual actions that a product can perform and may be mapped to akey through the resource file. Thus, Enter Query as an Action only makessense for a Forms resource file. Each action has a name and a correspondingnumber which are listed in the Forms Advanced Techniques manualAppendix A.

It is rare that product actions require modification although a character moderesource file migrated from Forms 4.0 to Forms 4.5 would require the extra’Window Menu’ action detailed in the Toolkit 2.1 documentation. On Unix,for example, this would be found in

$ORACLE_HOME/guicommon2/tk21/dos/README.doc.


What is tk2?

It is important to note that for character mode resource files there is another‘product’ within the resource file besides sqlforms. This extra product, tk2,can be viewed as the character-mode equivalent of Windows or Motif i.e. itimplements buttons, text items, windows and so on - all the things whichwould be taken care of by the window manager if there was one.

Tk2 corresponds to the Toolkit 2 layer, and although used by Forms on allplatforms, it can be configured via Terminal for character mode only.

Like the sqlforms bindings, the tk2 key bindings are also hierarchical. Someof the keys correspond directly to types of items, and these are grouped underthe viewclasses section. e.g.

field Select Text Select Cut command+x Copy command+c Paste command+y Clear command+k tlist Previous Item UpArrow Next Item DownArrow Select Item ’ ’ Select Item Select checkbox Select ’ ’ Select Select pushb Select ’ ’ Select Select poplist Select ’ ’ Select Select radio Select ’ ’ Select Select

Thus, pressing the space key while focus is in a T-list will select the currententry but if the current item is a push button, it will 'press' the button.


Just like the window manager itself, tk2 will ’see’ any key strokes beforesqlforms. Thus, if a key is mapped at the tk2 level it will take precedence overany mapping at the sqlforms level.

tk2 sqlformskeypress -> understood? -----> understood? ----> FRM- | no | no41000 | yes | yes v v handle key handle key

A common example of this can be seen in the standard vt220 resource file -fmrcvt220.res. Usually, pressing the NextScreen key will perform the NextBlock action. However, if the current item is a multi-line text item, theNextScreen key will perform a Page Down action - i.e. it will scroll the textin the text item.

This compares to running under Motif or Windows where it is impossible toremap the F1 key, for example, as this is handled directly by the windowsystem. For Motif environments, see the Installation and ConfigurationGuide for keys which cannot be mapped.

The keys which are passed on to the toolkit and hence Forms will depend onthe particular widget

1 or control which has focus. Thus, mapping a keypad

key to perform an action will not be seen by Forms if focus is in an enterabletext item as the widget itself ’understands’ the keystroke. But if focus iscurrently on a button, say, then the action mapped will work as the buttonwidget does not understand the key and passes it on to Forms. It is sometimespossible on Motif to override what keys a widget understands and hence varywhat keys can be mapped - see Note:32888.1 for an example.

1 A widget or control is a user-interface element provided by the windowingenvironment or the toolkit. Common examples include text fields, buttons,checkboxes etc.


Understanding the Keyboard

On MS Windows, the keys available to be mapped are determined by thewindow manager and are not configurable by the user or Forms developer.For character mode, the resource file defines the keys which are available onthe terminal (and probably some that aren’t if you are using an emulator - butmore on that later). For character mode devices, the resource file contains thekey name and the actual character sequence sent by that particular key. Thisallows for an enormous degree of flexibility and customization. The User andStandard key definition screens within the Device Display Definition screenallow you to specify individual keys on your terminal including the charactersequence it generates and the key label.

Standard Keys and names

When a key is mapped using Terminal, depending on how the key is defined itcan be referred to in a number of ways. For User Keys, the Name of the keyis used.

e.g. given the following User Keys definitions :-

Name Encoding

Find \e[1~

Select \e[4~

We could then map an action such as :-

Action Binding

Enter Query Select


For Standard Keys, there are two ways that the key can be referenced. Eachkey has both a Standard Key name and a Name (the Name is displayed byForms in the Show Keys screen).

e.g. in the standard fmrcvt220.res file

Standard Key Encoding Name

F11 \e[23~ F11(Esc)

F16 \e[29~ Do

and

Action Binding

Commit Do

Enter Query F11

Thus Commit uses the Name whereas Enter Query uses the Standard Keyname.

Note that since a key mapping refers to a name, changing the key name willrequire all these mappings to be changed also - and you must remember thatthis may affect the tk2 product too

1 .

1 The generate option within Oracle Terminal 2 only generates a single productat a time. If a key is renamed in the Device section and the resource file is thengenerated with ‘sqlforms’ selected, say, then there will be no warnings given ifany tk2 bindings are no longer correct. This will prevent forms from runningwith this resource file but it does not fail gracefully!


What key was pressed?

The toolkit can be thought of as something which listens to thekeyboard and tries to work out what key has been pressed. Oncharacter mode devices, one or more characters may correspond to aphysical key e.g. on a vt220 terminal, the PF4 key sends the followingcodes (given as hexadecimal numbers) :-

1B 4F 53

which is often written as

\eOS

To prove that the toolkit is actually looking out for this sequence, you canactually type at the keyboard <Escape>OS. Toolkit will see this as PF4 and(assuming you are using the default vt220 resource file) exit the form!

You must ensure that the physical escape sequences assigned to a key areunique and that they match up exactly to the sequences generated by yourterminal. If a terminal emulator is used, consult the emulator documentationfor details. Note that on Unix systems you may be able to use cat -v or od-x1 to find out what codes a key is generating but this information should beprovided by your terminal or emulator supplier.

It is also important to ensure that an escape sequence defined for a keyis not also the start sequence for another key. For example

F1 \e1

F2 \e2

F3 \e3

will work fine. But if you continue this pattern :-

F11 \e11

F12 \e12

then when the toolkit receives ’\e1’, it cannot work out whether this is an F1 orthe first 2 characters of an F11 or an F12. In these circumstances, you wouldsee that the F1 does not work until another key is pressed and then bothactions are performed.

1 The Unix commands cat -v and od -x will show the escape character as ^[ (i.econtrol + [ ) or as the hexadecimal value 1B.


A note on Terminal Emulators

Many people using character based applications will be accessing them via aPC running MS Windows for example. In order to use Developer/2000 Formsin character mode, some form of terminal emulator will be used. This is apiece of software which allows the PC to behave like one or more terminals.Common emulations include vt100 and vt220. This can cause a great deal ofconfusion as a real vt220 has function keys up to F20, for example, but thePC keyboard stops at F12!

Any software running on the server will be unaware that a different device isconnected, and so Forms will quite happily tell you (via Show Keys) to pressF18 to do Clear Record. What PC key corresponds to F18 is down to theemulator and should be documented by the emulator supplier.

A note on Modifiers

Modifier keys are also defined as part of the device display definition screenand so are only relevant to character mode. These are keys which are pressedbefore another key - often to give a different but related action.

e.g.

Next Item Tab

Previous Item command+Tab

where command = PF1.

This can cause some confusion as some of the modifiers have names incommon with physical keys. This is purely coincidental - the modifier calledALT, for example, does not relate to the Alt key on the keyboard in any way.Also, the ALT modifier is not pressed at the same time as the key it modifies(unlike the Alt key).

An especially good example of this is the SHIFT key. The SHIFT modifierdoes not relate in any way to the shift key on the keyboard. If the developermapped, for example :-

Previous Item shift+Tab


then Toolkit would be expecting the key which is declared in the modifiers listas ‘shift’ to be pressed prior to the ‘tab’ key - which is not the same asholding down the shift key and then pressing tab at the same time. In fact, onterminals such as the vt100 and vt220, shifted function keys such as ‘Tab’and ‘PF1’ generate the same escape sequence as the plain key and so cannotbe distinguished by Toolkit (and hence Forms).

The (often confusing) exception to this is where an emulator is used whichdoes differentiate between shifted and un-shifted function keys. In this case,define a user key with the relevant (unique) escape sequence and map that asa single key.

e.g. in the User Keys list

Shift+Tab \e[999

Since modifiers are mapped using the + notation (as above) you must becareful when defining User Keys with a '+' character in the name - these needto be enclosed in " " when used in a mapping and are only available fromversion 2.1 onwards (see Note:40707.1). Thus :-

Previous Item “Shift+Tab”

Note also that mapping a key which is defined as a modifier will not work atruntime since toolkit would be unable to determine whether or not to wait forthe next key stroke.

A prime example of this is the PF1 key on a vt220. In the standardfmrcvt220.res resource file, 2 modifiers use the PF1 key :-

command PF1

super PF1 PF1

In order to map the PF1 key we must first redefine command and super touse a different key.


The Control Key

Although the Modifiers list has an entry for a modifier named ‘CONTROL’,this is not treated as a modifier and should not be used in this way. TheControl key has a special defined function when used in conjunction withalphabetic characters. For example, Control+A corresponds to the characterwith ASCII value 1 (octal \001).


When is KEY-F1 not the F1 Key?

Answer : Pretty much all of the time!

Forms provides a variety of key triggers which are mapped to keys on thekeyboard via the resource file. There are also a set of extra user-defined keysavailable which correspond to key triggers named KEY-F0 to KEY-F9. Thiscan cause a lot of confusion since a KEY-F1 trigger does not usuallycorrespond to the F1 key on the keyboard. In the resource file under runform -> normal the actions ’User Defined Key 0’ to ’User Defined Key 9’ define theactual keys which correspond to these triggers.

To map an action to the F1 key on Windows, say, you would have to create aKEY-HELP trigger as that is the action fired by the F1 key (and cannot beoverridden).

If you want to fire a KEY-Fn trigger, ensure that the following are set :-

1. Create the KEY-Fn trigger - remember to set the ‘Show Keys’ propertyof the trigger to TRUE and enter a meaningful value for ‘Show KeysDescription’

2. Check in the resource file under Runform -> Normal that there is amapping for User Defined Key Fn.

3. Check that any keys or modifiers referenced in the mapping are valid.


DISPLAY ATTRIBUTES

Device Attributes

Device attributes (for character mode only) are a way of associating a namewith a particular display style and consist of the character sequence (escapesequence) which must be sent to the terminal to activate that feature e.g. on avt220 terminal, Bold = \e[0;1m.

Forms does not use these device attributes directly (except when using fontaliasing - see later) - a corresponding logical attribute must be defined whichreferences it.

The actual escape sequence sent will vary depending on the terminal used -consult the terminal documentation for details.

1

1 Note also that certain PC emulation packages cannot display certain types ofvideo attribute and so use colour instead. Thus, bold may be displayed as Cyanand underline may display as Red.


Logical Attributes

Logical attributes allow access to particular display styles - either globally forevery instance of an item on every form or for a specific instance on 1 form.These tend to be of most use on character mode forms as many of thecorresponding GUI attributes are set by the window manager on GUIplatforms.

Character attributes which can be set globally include :-

TextControlCurrent attribute applied to the current text item

TextControlNonCurrent attribute applied to all other text items

Character Mode example

For example, there are 6 logical attributes which affect menus, and 4 whichaffect the status and message lines :-

---- MenuItemEnableMnemonic ---- MenuItemSelectMnemonic

| |

| ---- MenuItemEnable | ---- MenuItemSelect

| | | |

| | | |

v v v v

Action Edit Block Field Record Query Help

------------------------------------- ---------------- ------

| Enter |

| eXecute |

| Last criteria |

MenuItemDisableMnemonic ----> | Cancel <--- MenuItemDisable

| count Hits |

| Fetch next set |

----------------

----- Status-Message

|

v

FRM-41000: At first record.

Count: *0 <Replace>

^ ^

| |

--------------------- Status-Items ----------------------

Note that these are documented in Appendix A of the Forms 4.5 AdvancedTechniques Manual.


Using the standard fmrcvt220.res file, the menu mnemonic1 for an enabledmenu item is UnderlineBold. To change the appearance of this character toBlinking, say, we need to modify the logical MenuItemEnableMnemonic.

• Select the attribute and navigate to the Edit Attribute Node screen for thecharmode component. Ensure that the CharacterMode Device Attributecheckbox is checked and change the device attribute from UnderlineBoldto Blinking.

|------------------ Edit Attribute Node : charmode -------||| Logical Attribute Name: MenuItemEnableMnemonic|||| [X] CharacterMode Device Attribute: Blinking|

Note that if you tried the above on an emulator it may not have worked asflashing or blinking text is often not implemented by an emulator.

MS Windows example

When running Forms on MS Windows, there is little visible indication thatthe user has entered invalid data or is trying to navigate through a mandatoryitem without entering any data at all. The forms logical which defines theappearance of a text item which has failed validation isTextControlFailValidation. To make an item which has failed validationappear as white text on a red background, modify the fmrusw.res file asfollows :-

• Select the Forms-Logical TextControlFailValidation and choose to edit theROOT node - i.e. the one above charmode and x nodes. In the EditAttribute Node screen check the checkboxes for foreground andbackground and select appropriate colours2 of your choice.

1 The menu mnemonic is the single character shortcut key which may bepressed to activate that menu item if focus is in the menu containing it.

E.g. with the standard Action menu open in the Forms runtime, pressing the‘E’ key will select ‘Exit’.

2 Note that any colours which will eventually be applied to Text Items aresubject to the same colour restrictions on MS Windows as any native Windowscontrol i.e. only the solid colours should be used.


You can also set a font e.g.

Face Courier New

Size 1000 (measured in 100ths of a point so

1000 = 10 point)

Style Italic

Weight Bold

Make use of the Apply Font button to verify that the font combination is valid.

Using DISPLAY_ITEM

Logical attributes can also be accessed programmatically either via theCharmode Logical Attribute property or by using DISPLAY_ITEM (orDISPLAY_FIELD in upgraded SQL*Forms 3 applications). SinceDISPLAY_FIELD was available in Forms 3, there are many attributesdefined in the supplied character mode resource files which are there forupwards compatibility only.

For example, Field-current has no direct effect in Forms 4, but it can be usedin Display_Item :-

DISPLAY_ITEM(’DEPT.DNAME’,’Field-current’);

Note that you cannot access device attributes directly with Display_Item butmust go via a logical attribute.


Uifont.ali and Character-mode Forms

Even though character mode Developer/2000 Forms has no concept of a font,the display attributes can be affected by font aliasing. This is of most usewhen used for boilerplate as it provides a mechanism to have different typesof boilerplate if your terminal supports it. E.g. bold, reverse, blinking andeven coloured boilerplate is possible! See Note:35077.1 for details.

Some vt220 emulations support colour. To display a Text Item in a particularcolour, you would need to create a device attribute and a logical attribute, andthen apply the logical attribute as above. But for boilerplate, simply create thedevice attribute and then alias a font in your uifont.ali file to reference this

e.g. Device Attribute :-

Yellow \e[0;7;1;30;43m

Uifont.ali entry :-

[Display:CM]

*...Bold = Yellow

Any boilerplate created in the designer as Bold will then display as Yellow onBlack at runtime on a vt220. This allows much more freedom than wasavailable in Forms 3 in terms of boilerplate attributes.


Character-mode Terminal Setup

The character mode resource file has an initialisation string which is sent tothe terminal when Forms starts up. This sequence can be used to configure theterminal as required. In order to understand these initialisation strings youwill need a programming manual for the actual terminal itself.

E.g. vt220 initialisation

\e[62;1"p \e[;r \e> \e[?1l \e[?6l \e(B \e)0 \017

From a VT320 manual we see that :-

\e[62;1"p VT300/VT200 mode, 7-bit controls

\e> Exit alternate keypad mode

\e[?1l Cursor keys mode = cursor

\e(B Select character set G0

\e)0 Select character set G1

\017 DC1 or XON

Note that if you intend to use 8 bit characters such as £ (pound sign) you mayneed modify the initialisation string to use \e[62;0"p.

Other useful vt220 codes include :-

\e[?6l Origin Mode - move outside margins

\e[?1l Cursor Keys mode - Cursor

\e> Keypad - numeric mode

You may use the terminal setup string to program user definable functionkeys, switch to 132 column mode or perform other setup actions.


What appears in Show Keys?

Within a runform session, the Show Keys key will display a list of all thekeys available in the current context. The keynames and function names aretaken from the resource file. Note, however, that key triggers can overridewhat is shown. When a new key trigger is created, the following propertiesare defaulted :-

Show Keys False

Show Keys Description <NULL>

Thus, if a KEY-PRINT trigger is created, say, then by default you will nolonger see this key in the Show Keys window unless you set the Show Keysproperty to True.

CONCLUSION

Terminal 2 is a complex utility which allow you to configure many aspects ofthe Forms product. It can sometimes be difficult to work out where a problemlies e.g. the Form itself, the resource file, the terminal (emulator)

Armed with the above information, using and understanding terminal will be asimpler task!


Oracle Terminal 2 & Developer/2000 Forms

May 1997

Author: Nick Triggs

Contributing Authors:

Copyright © Oracle Corporation 1997

All Rights Reserved

This document is provided for informational purposes

only and the information herein is subject to change

without notice. Please report any errors herein to

Oracle Corporation. Oracle Corporation does not

provide any warranties covering and specifically

disclaims any liability in connection with this document.

Oracle is a registered trademark and Enabling the

Information Age, and Developer/2000 are trademarks of

Oracle Corporation.

Oracle Corporation

World Headquarters

500 Oracle Parkway

Redwood Shores, CA 94065

U.S.A.

Worldwide Inquiries:

415.506.7000

Fax 415.506.7200

Copyright © Oracle Corporation 1997

All Rights Reserved

................................

E

Using Oracle Server Rolesat Run Time

E-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix E: Using Oracle Server Roles at Run Time

E-2 Copyright Oracle Corporation, 1998. All rights reserved.

Oracle Server Views for Roles

• SESSION_ROLES

• ROLE_ROLE_PRIVS

• ROLE_SYS_PRIVS

• ROLE_TAB_PRIVS

Developer/2000: Build Forms II E-3......................................................................................................................................................

......................................................................................................................................................Using Oracle Server Roles at Run Time

Using Oracle Server Roles at Run Time

OverviewYou can manipulate roles dynamically at runtime using Forms. For example,you can selectviews from thedata dictionary to get information aboutexisting roles. Subprograms belonging to the DBMS_SESSIONpackageallow you to modify the roles that are used by the menu module to enforcesecurity.

SESSION_ROLES ViewRoles that the user currently hasenabled.

ROLE_ROLE_PRIVS ViewInformation about roles granted to other roles.

ROLE_SYS_PRIVS ViewInformation about system privileges granted to roles.

Column DescriptionROLE Name of the Role.

Column DescriptionROLE Name of the Role.GRANTED_ROLE Role that was granted.ADMIN_OPTION The subprogram that signifies that the role was granted with the

ADMIN option.

Column DescriptionROLE Name of the Role.PRIVILEGES System privileges granted to the Role.ADMIN_OPTION The column that signifies the grant was with the ADMIN option.




Oracle Server Views for Roles

• SESSION_ROLES

• ROLE_ROLE_PRIVS

• ROLE_SYS_PRIVS

• ROLE_TAB_PRIVS



ROLE_TAB_PRIVS ViewInformation about table privileges granted to roles.

Column DescriptionROLE Name of the Role.OWNER Owner of the object.TABLE_NAME Name of the Object.COLUMN_NAME Name of the Column, if applicable.PRIVILEGE Object privilege granted to the Role.GRANTABLE YES if the Role was granted with the ADMIN option, otherwise NO.




Procedures for Managing Roles

• DBMS_SESSION.SET_ROLE

• DBMS_SESSION.IS_ROLE_ENABLED



Procedures for Managing Roles

ExampleThe following Pre-Form trigger uses the DBMS_SESSION package to test auser’s role before allowing access to the application.

Package DescriptionDBMS_SESSION DBMS_SESSION is a standard

package, which comprises manysubroutines. Two of them areused to manage menu moduleroles dynamically.

DBMS_SESSION.SET_ROLE (‘RoleName’) Used to assign a particular role tothe current user.

DBMS_SESSION.IS_ROLE_ENABLED (‘RoleName’)

IF not (DBMS_SESSION.IS_ROLE_ENABLED(’ADMINISTRATIVE’) or(DBMS_SESSION.IS_ROLE_ENABLED(’TECHNICAL’)

THENMESSAGE(’You are not authorized to run this application’);PAUSE;RAISE form_trigger_failure;

END IF;

................................

F

EMP_PKG Package

F-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix F: EMP_PKG Package

Package Specification

/*******************************************************//* This is an example of a stored procedure that: *//* query, insert, update, delete, and lock rows of the *//* EMP table. *//* A package is used to logically group the related *//* data types and procedures *//*******************************************************/PACKAGE emp_pkg AS

TYPE emprec IS RECORD( --- Defines eack row of the table EMP empno emp.empno%TYPE, ename emp.ename%TYPE, job emp.job%TYPE, mgr emp.mgr%TYPE, hiredate emp.hiredate%TYPE, sal emp.sal%TYPE, comm emp.comm%TYPE, deptno emp.deptno%TYPE);

--- Defines each row of the table EMP.EMPNOTYPE empnorec IS RECORD ( empno emp.empno%TYPE);

--- Define the Ref CursorTYPE empcur IS REF CURSOR RETURN emprec;

--- Defines the Table of Records EMPTYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;

--- Defines the Table of Records EMP.EMPNOTYPE empnotab IS TABLE OF empnorec INDEX BY BINARY_INTEGER;

Developer/2000: Build Forms II F-3......................................................................................................................................................

......................................................................................................................................................Package Specification

--- Defines the procedures used for querying records PROCEDURE empquery_refcur ( block_data IN OUT empcur, p_deptno IN NUMBER); PROCEDURE empquery ( block_data IN OUT emptab, p_deptno IN NUMBER);

--- Defines the procedures used for DML operations PROCEDURE empinsert (block_data IN emptab); PROCEDURE empupdate (block_data IN emptab); PROCEDURE empdelete (block_data IN empnotab); PROCEDURE emplock (block_data IN empnotab);END;



Package Body

PACKAGE BODY emp_pkg AS

/**************************/ /* QUERY USING REF CURSOR */ /**************************/ PROCEDURE empquery_refcur( block_data IN OUT empcur, p_deptno IN NUMBER) IS BEGIN OPEN block_data FOR SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE deptno = NVL(p_deptno, deptno) ORDER BY empno; END;

. . .


......................................................................................................................................................Package Body

/********************************/ /* QUERY USING TABLE OF RECORDS */ /********************************/ PROCEDURE empquery( block_data IN OUT emptab, p_deptno IN NUMBER)

IS i NUMBER; CURSOR empselect IS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE deptno = NVL(p_deptno, deptno) ORDER BY empno;

BEGIN OPEN empselect;

i := 1;LOOP

FETCH empselect INTO block_data(i).empno, block_data(i).ename, block_data(i).job, block_data(i).mgr, block_data(i).hiredate, block_data(i).sal, block_data(i).comm, block_data(i).deptno; EXIT WHEN empselect%NOTFOUND;

i := i + 1; END LOOP;

END;. . .



/**********//* INSERT *//**********/PROCEDURE empinsert(block_data IN emptab)IS i NUMBER; cnt NUMBER;BEGIN cnt := block_data.count; FOR i IN 1..cnt LOOP INSERT INTO emp( empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES( block_data(i).empno, block_data(i).ename,

block_data(i).job, block_data(i).mgr, block_data(i).hiredate, block_data(i).sal, block_data(i).comm, block_data(i).deptno);

END LOOP;END;

. . .


......................................................................................................................................................Package Body

/**********//* UPDATE *//**********/ PROCEDURE empupdate(block_data IN emptab) IS i NUMBER; cnt NUMBER; BEGIN cnt := block_data.count; FOR i IN 1..cnt LOOP UPDATE emp

SET ename = block_data(i).ename,job = block_data(i).job,mgr = block_data(i).mgr,hiredate = block_data(i).hiredate,sal = block_data(i).sal,comm = block_data(i).comm,deptno = block_data(i).deptnoWHERE empno = block_data(i).empno;

END LOOP;END;

/**********/ /* DELETE */ /**********/ PROCEDURE empdelete(block_data IN empnotab) IS i NUMBER; cnt NUMBER; BEGIN cnt := block_data.count; FOR i IN 1..cnt LOOP DELETE FROM emp WHERE empno = block_data(i).empno; END LOOP; END;



/********/ /* LOCK */ /********/

PROCEDURE emplock(block_data IN empnotab)IS

i NUMBER;cnt NUMBER;block_rec emprec;

BEGINcnt := block_data.count;FOR i IN 1..cntLOOP

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

INTO block_recFROM empWHERE empno = block_data(i).empnoFOR UPDATE OF ename NOWAIT;

END LOOP;END;

END;


......................................................................................................................................................Package Body

To have an idea of how Form Builder works when a data block is based on astored procedure returning a REF CURSOR, you can run the followingscript under Sql*Plus:

DECLARE

v_rec emp_pkg.emprec;

v_ref emp_pkg.empcur;

BEGIN

emp_pkg.emp_query_refcur(v_ref, 10);

LOOP

FETCH v_ref INTO v_rec;

EXIT WHEN v_ref%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_rec.empno);

DBMS_OUTPUT.PUT_LINE(v_rec.ename);

DBMS_OUTPUT.PUT_LINE(v_rec.job);

DBMS_OUTPUT.PUT_LINE(v_rec.mgr);

DBMS_OUTPUT.PUT_LINE(v_rec.hiredate);

DBMS_OUTPUT.PUT_LINE(v_rec.sal);

DBMS_OUTPUT.PUT_LINE(v_rec.comm);

DBMS_OUTPUT.PUT_LINE(v_rec.deptno);

END LOOP;

CLOSE v_ref;

END;

................................

G

Implementing Online HelpReusable Component

G-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix G: Implementing Online Help Reusable Component

G-2 Copyright Oracle Corporation, 1998. All rights reserved.

Objectives

After completing this lesson, you shouldbe able to implement online help class

Developer/2000: Build Forms II G-3......................................................................................................................................................

......................................................................................................................................................Introduction

Introduction

OverviewIn this lesson, you will learn about the online help reusable component thatis available with Developer/2000. You will learn how this component can fitinto any application.


• Implement online help class



Technical NoteThis component requires Oracle7 release 7.3 or later.


The Online Help Class

• Similar to Windows Help system

• Context-sensitiveHelp

• Text into thedatabase

• Portable


Implementing the Online HelpClass

How to create the onlineHelp class:

1. Create the database objects.

2. Create the online Help content.

3. Display the online Help.


......................................................................................................................................................The Online Help Class

The Online Help ClassThis component enables you to easily add a Help system to an application. Itprovides functionality that mimics the Windows 95 Help system. It allowsyou to provide context-sensitive Help to the end users. The Help text isstored in the database so that it can be easily shared among all users, makingupdates and changes immediately available to everybody.

Note: This component is built entirely using Form Builder and PL/SQLnative capabilities, so it is portable to all Form Builder supported platforms.

Using the Online Help ClassTo use the online Help class in your application, do the following:

1 Create the database objects.

2 Create the online Help content.

3 Display the online Help content.



Technical NoteThe database schema is also provided as an Oracle Database Designer file.Open the D2KHELP.ODD file from the directoryORACLE_HOME\TOOLS\DEVDEM20\ADMIN to see a visualrepresentation of the schema.


Creating the Database Objects

1. Run SQL*Plus.

2. Connect with the CREATE USER privilege.

3. Execute the D2KHELP.SQL script.

- A user- Four tables

- One sequence- One package


......................................................................................................................................................Creating the Database Objects

Creating the Database ObjectsOne of the online help class features is to share the help text among the usersby way of the database. The first step is to create the database objects. Thoseobjects will contain the help text.

Creating the Database Objects1 Run SQL*Plus and connect as a user with the CREATE USER privilege.

Example: the SYSTEM user.

2 Execute the D2KHELP.sql script from the directoryORACLE_HOME\TOOLS\DEVDEM20\ADMIN.

3 Enter a default tablespace name.

4 Enter a SQL*Net V2 alias for the connection.

The script creates the following objects:

Note: The script also populates the tables to provide online Help for theD2KHMAIN (Help Maintenance) form. The maintenance form uses thisHelp component to document itself.

Object Type Object Name

User D2KHELP with D2KHELP as password

Table Four tables in this new schema: APP_HELP, FORMS_HELP,HELP_RELATED, and HELP_KEYWORDS

Sequence SEQ_HELP_ID

Package HLP_TREE




Creating the Online HelpContent

Use the maintenance form D2KHMAIN.FMX


......................................................................................................................................................Creating Online Help Content

Creating Online Help ContentThe next step to use the online Help class is to create the Help text into thedatabase. Developer/2000 Form builder provides a maintenance form to helpyou to do this task.

The Maintenance FormThe maintenance form is a Form executable file called D2KHMAIN.FMX.The directory for this file isORACLE_HOME\TOOLS\DEVDEM20\DEMO\FORMS. This formcontains six tabs, each corresponding to a specific step to create the Helptext.

The Application TabIn this first tab, you can see an existing application called HELP. This is usedto provide help for the maintenance form itself.

To create an application, you only need to insert a new record and enter acode and a title for this application. This application will be the owner of allthe topics you will add to it.

Item Name Description

Code Identifier of the application

Title Application title (This title will be displayed at the top of the Winhelpwindow.)



1 Level 4 Context sensitive

2 Help title 5 Seq

3 Pop-up menu 6 Node


The Content Tab1

2

3

45

6



The Content TabThe Content tab allows you to view and modify the Help system hierarchy.

Item Name Description

Level Indicates the parent level you are in. You can choose the level you wantto see as the parent level with the button beside the Level field.

Node If it is checked, indicates that there are children under this topic. If youpress the iconic button beside the check box, you go one level down inthe hierarchy.

Seq This item gives you the ability to sort your topics as they will bedisplayed in the Help system. Enter a number or nothing if you do notwant to sort the topics.

Help Title This is the title that will be displayed in the Help system.

Popup Menu If you click the right mouse button in the Seq or Help Title fields, apop-up menu lets you go up one level, down one level, reparent a topic,or change the level displayed as a parent.

Context Sensitive These fields are used to bind a topic to a particular module, block, oritem of your application.These fields are available only for leaf nodes.




The Help Text and ExampleTabs


The Related and Keywords Tabs



The Help Text TabIf the current topic is a leaf, this multi-lines item enables you to enter the textthat will be displayed in the Help system.

The Examples TabIf the current topic is a leaf, this multi-lines item enables you to enter theexample text that will be displayed in the Help system.

If you enter text here, an “Example” button will be displayed to indicate tothe end user that examples are available.

The Related TabThis field enables you to display a list of other topics that are related to thetopics you are displaying. Use the iconic button to display the topic list andchoose the one you want to add.

If you enter a related topic, a button will appear in the Help system to tell theend user that related topics are available.

Note: You can only choose child topics.

The Keywords TabThis functionality enables you to add keywords to a topic. These keywordswill be displayed in the Index tab of the Help system and make thenavigation easier for the end user.

Technical NoteYou can also create any other Help topics that you require that are not linkedto a specific module, block, or item. Use fictitious module, block, and itemnames for these topics that do not conflict with real modules, blocks, anditems. For example, you might create Help topics associated with particularwindows, menus, or alerts. These topics will also appear in the Contents andIndex of your Help.



Technical NoteEnsure that the form D2KHELP.FMX is in a directory included in theFORMS50_PATH search list.

If you want to customize the Help system, the source code of the forms isprovided as D2KHMAIN.FMB (the Help data entry form) andD2KHELP.FMB (the Help display form). You can translate them orcustomize them. For example, you can change the icons displayed bymodifying the package specification TRT_HELP in D2KHELP.FMB.


Displaying Online Help

• Context-sensitive help: Key-HelpTrigger

• Specific topic

• Contents page

call_hlp.help_context(’my_application’);

call_hlp.help_topic(’my_application’,

’module’, ’block’, ’item’);

call_hlp.help_content(’my_application’);


......................................................................................................................................................Displaying Online Help

Displaying Online HelpThe first step to display the online Help is to attach the PL/SQL libraryD2KHELP.PLL to your module. This library contains the CALL_HLPpackage.

Displaying Context-Sensitive HelpModify the Key-Help trigger of your module to call the Help system. Addthe following line:

This procedure calls the D2KHELP.FMX form, which displays the Helptopic for the current context.

Run your application. When the end user presses the Help key ([F1] inWindows), the help system displays:

• The topic text of the cursor item, if defined; otherwise

• The topic text of the cursor block, if defined; otherwise

• The topic text of the module, if defined; otherwise

• The Help contents

Note: If you want your end users to be able to call Help other than bypressing the Help key, for example, from the toolbar, from the Help menu, orfrom a pop-up menu on an item, add the following code to the appropriatetriggers:do_key(‘HELP’) ; This will cause theKey-Help trigger to fire, which in turn calls the Help system.

Displaying a Specific Topic DirectlyAdd the following code to the appropriate trigger:

This can be used to display Help for an object that does not have a module,block, and item as its context, such as a window, an alert, or a menu.

Displaying the Contents Page of the Help DirectlyAdd the following code to the appropriate trigger:

call_hlp.help_context(’my_application’);

call_hlp.help_topic(’my_app’, ’module’, ’block’, ’item’);

call_hlp.help_content(’my_application’);




Summary

• Online Help class:

– Create the database objects

– Create online Help content– Display online Help content


......................................................................................................................................................Summary

Summary

The Online Help Class• Create the database objects

• Create online Help content

- The maintenance form

• Display online Help

- Display context-sensitive help

- Display a specific topic directly

- Display the contents page of Help directly

................................

H

Working with ActiveXControls

H-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix H: Working with ActiveX Controls

H-2 Copyright Oracle Corporation, 1998. All rights reserved.

Objectives


• Explain the differences between VBX,OCX, and ActiveX controls

• Access ActiveX properties at designtime and run time

• Import ActiveX control methods andevent packages

• Customize event handling

Developer/2000: Build Forms II H-3......................................................................................................................................................

......................................................................................................................................................Introduction

Introduction

OverviewIn this lesson, you will learn how to include and use ActiveX controls inyour Developer/2000 application.


• Explain the differences between VBX, OCX, and ActiveX controls

• Access ActiveX properties at design time and run-time

• Import ActiveX control methods and events packages

• Customize event handling




What Are VBX, OCX andActiveX Controls?

VBX• Custom Interface Object• Special dynamic link library

OCX• Independent program module• Run in any Windows application that is

OLE-enabledActiveX• Third version of OCX• Provide integrated controls in browsers


......................................................................................................................................................What Are VBX, OCX, and ActiveX Controls?

What Are VBX, OCX, and ActiveX Controls?VBX, OCX, and ActiveX controls provide encapsulated functionality thatcan be embedded into your application.

Instead of one monolithic application that provides you with almost everyfeature that you want, component objects and good integration can provideyou with a light-weight custom application to suit all your needs.

Word processor documents, spreadsheet documents, knob controls, andvideo clips are examples of component objects that can enhance thecapabilities of your form. Instead of re-creating functionality that alreadyexists, you can access these capabilities by exploiting a VBX, an OCX, or anActiveX control.

What Is a VBX?Short for Visual Basic custom control, a VBX is a reusable softwarecomponent designed for use in many different applications. While VBXscan be used in other environments, they were initially created for developingWindows applications with Visual Basic. An application developer can use anumber of selected VBXs to quickly develop an application.

What Is an OCX?Short for OLE Custom control, an OCX is an independent program modulethat can be accessed by other programs in a Windows environment. OCXcontrols end with the .ocx extension. OCX controls represent Microsoft’ssecond generation of control architecture, the first being VBX controlswritten in Visual Basic.

What Is an ActiveX?ActiveX controls are a superset of OLE Custom control (OCX). ActiveX isan open platform that combines desktop and Web technologies. An ActiveXcontrol can be automatically downloaded and executed by a Web browser.ActiveX is not a programming language, but rather a set of rules for howapplications should share information. Programmers can develop ActiveXcontrols in a variety of languages, including C, C++, Visual Basic, and Java.They represent Microsoft’s third generation of control architecture, the firstbeing VBX controls and the second OCX controls.




VBX, OCX, and ActiveX ControlsComparison

Operating

“ platform ”

Candidate

applications

Coding

interface

Web-

enabled

VBX

Controls

OCX

Controls

16-bit applications

only

Only those written

with a specific

interface to VBXs

Proprietary

No No Yes

ActiveX

Controls

Both 16- and 32- bit

platforms

Any application with an

OLE container type

Standard OLE2


......................................................................................................................................................Comparison of VBX, OCX, and ActiveX Controls

Comparison of VBX, OCX, and ActiveX ControlsA VBX is an extension of Microsoft Visual Basic and is designed to workwithin applications written with a specific interface to VBXs. They are 16-bit-only controls designed to execute an event.

An OCX is an extension of Windows Open Architecture, so it is designed torun in any Windows application that is OLE-Enabled. They support 16- and32-bit platforms, can execute events, and give the developer the ability tocode methods and properties.

ActiveX controls are the third version of OLE controls (OCX). This meansthat all OCX are ActiveX controls. They provide a number of enhancementsspecifically designed to facilitate distribution of components over high-latency networks and to provide integration of controls into Web browsers.

The table below summarizes some of the differences between VBX controls,OCX controls, and ActiveX controls.

VBX Controls OCX Controls ActiveX Controls

Operatingplatform

16-bitapplications only

Both 16- and 32-bitplatforms

Both 16- and 32-bitplatforms

Candidateapplications

Only thosewritten with aspecific interfaceto VBXs

Any application with anOLE container type

Any application with anOLE container type

Codinginterface

Proprietary Standard OLE2 Standard OLE2

Web-enabled No No Yes




ActiveX in Form Builder

ActiveX controls:

• Dynamic library in .ocx files

• Support events, properties, and methods

ActiveX control item:

• Container for the ActiveX control

• Single-record property set to Yes


......................................................................................................................................................ActiveX in Form Builder

ActiveX in Form Builder

What Are ActiveX Controls?Form Builder provides support for ActiveX controls. ActiveX controls arereusable components that can be embedded in your Form Builderapplication. Form Builder acts as an ActiveX control container.

ActiveX controls are a special type of dynamic library. A single ActiveX filethat is distributed with the.ocxextension can contain more than one class ofActiveX objects.

ActiveX Controls ComponentsEach ActiveX control exposes a set of:

• Properties

Properties define the ActiveX control’s physical and logical attributes.

• Methods

Methods define actions that the ActiveX control can perform.

• Events

Events denote some change in status in the ActiveX control.

A client application manipulates an ActiveX control by writing values to andobtaining values from its properties, invoking methods that are used toperform actions, and intercepting and acting on events.

ActiveX Control ItemThe ActiveX Control Item type creates a container for your ActiveX control.An ActiveX control is not a separate application, but a server that plugs intoan ActiveX container.

It is recommended that you use ActiveX controls in blocks with the SingleRecord property set to Yes. The Single Record property is not applicable tomultiple record blocks. It is advised that you set the Single Record propertyto Yes because single records are immediately initialized when Formsrun-time starts up. For multiple records, each record is not initialized untilyou navigate to the record. Without initialization, the ActiveX Control Itemis empty, giving the impression that no ActiveX control is available.




Implementing an ActiveXControl in Form Builder

1. Create an ActiveX control container.

2. Insert an ActiveX control.

3. Import the desired ActiveX control methods and events.

4. Write the appropriate PL/SQL code.


Creating an ActiveXControl Item


......................................................................................................................................................Implementing an ActiveX Control

Implementing an ActiveX Control

Using an ActiveX Control in Form BuilderTo use an ActiveX control in Form Builder:

1 Create an ActiveX control container.

2 Insert an ActiveX control.

3 Import the desired ActiveX control methods and events.

4 Write the appropriate PL/SQL code— calling property accessors,invoking methods, or responding to events— to interact with theActiveX control.

Creating an ActiveX Control ItemYou can create an ActiveX control item by:

• Converting an existing item into an ActiveX control item.

• Using the ActiveX control tool in the Layout Editor.

• Using Create Icon in the Object Navigator.

• Using the Data Block Wizard.

To create an ActiveX control item in the Layout Editor:

1 Invoke the Layout Editor.

2 Click the ActiveX control tool.

3 Click the canvas.

The ActiveX control item is displayed.

4 Double-click the ActiveX control item.

The Properties Palette is displayed for the item.

5 Set the item properties as required.



Technical NoteIf you have created a container using the OCX button from the LayoutEditor, then Create Control is the only available option on the Insert ObjectDialog.


Inserting an ActiveX Control

In the Layout Editor:

1. Select the ActiveX control item.

2. Click the right mouse button.

3. Select InsertObject in thepop-up menu.

4. Select a control type.


ActiveX Control Properties

Set the followingActiveX Control properties:

OLE Class

Control Properties

About Control

Control Help


......................................................................................................................................................Inserting an ActiveX Control in an ActiveX Control Item

Inserting an ActiveX Control in an ActiveX Control Item

Inserting an ActiveX Control1 In the Layout Editor, select the ActiveX Control Item.

2 Click the right mouse button to enable the pop-up menu.

3 Select Insert Object.

4 In the Insert Object dialog box, click the Create Control option button todisplay the valid control types.

5 Click a control type.

6 Click OK.

ActiveX Control PropertiesOnce the ActiveX control is inserted, you may set the following properties:


OLE Class Determines what class of OLE objects can reside in an OLEcontainer.You select a specific class if you want to create anapplication that allows end users to change the current OLE object inthe OLE container, but want to restrict the end users to creating OLEobjects from a particular class.

Control Properties Displays the specific properties for the current ActiveX control.

About Control Displays information about the control. Please note that this featuremight not be exposed by the control that you are using.

Control Help Displays online Help for the control. Please note that this featuremight not be exposed by the control that you are using.




Importing ActiveX Methods andEvents Packages

Select Program OLE Importer1

2

3

4 5

- Program Units + PackageName1 (Spec) + PackageName1 (Body) + . . .

6


......................................................................................................................................................Importing ActiveX Control Methods and Events Packages

Importing ActiveX Control Methods and Events PackagesActiveX controls export interfaces that expose methods and events. FormBuilder imports the methods and events in PL/SQL, enabling you to interactwith ActiveX controls in the native Form Builder environment.

Importing ActiveX Control Methods and Events1 Select Program––>OLE Importer...

The OLE Importer dialog box appears.

2 Select an OLE Class.

The methods and events packages appear for the selected OLE class.

3 [Ctrl]-click on the desired methods and events packages.

4 Select the Methods check box to import the methods.

5 Select the Properties check box to import the get and set accessors forproperties.

6 Click OK.

In the Object Navigator, notice the newly created packages under theProgram Units node.




Interacting with an ActiveXControl

You can interact for:

• Setting and getting ActiveX controlproperties

• Invoking ActiveX control methods

• Responding to ActiveX control events


Setting and Getting ActiveXControl Properties

Three methods:

• Bind variable

Syntax

Example

:Item(’item_name’).OCX.server_name.property

:item(’ActXitem’).OCX.Spindial.spindialctrl

.1.Needleposition:=:item(’ActXitem’).OCX.

Spindial.spindialctrl.1.Needleposition + 1;


......................................................................................................................................................Setting and Getting ActiveX Control Properties

Setting and Getting ActiveX Control PropertiesOnce the ActiveX control is inserted and the methods and events packagesimported, you can interact with ActiveX controls in many ways:

• Setting and getting ActiveX control properties

• Invoking ActiveX control methods

• Responding to ActiveX control events

There are several ways to set and get ActiveX properties values.

Bind Variable SyntaxYou can use the bind variable syntax to assign or retrieve property values.

Server_name represents the name of the server. This name is the one youhave selected for the OLE Class property.Propertyis the name of a specificproperty of the ActiveX control. To obtain more information, see the ControlHelp property, if available, or the documentation.

:item(’item_name’).ocx.server_name.property

/* Setting an ActiveX control property using bind variablesyntax */

:item(’ActXitem’).OCX.Spindial.spindialctrl.1.Needleposition:=:item(’ActXitem’).OCX.Spindial.spindialctrl.1.Needleposition +1;/* ActXitem is the name of the item,

Spindial.spindialctrl.1 is the name of the ActiveXcontrol server, and Needleposition is the name of theproperty. */

/* The following code also works if your system.cursor_itemis an ActiveX control: */

:form.cursor_item.OCX.spindial.spindialctrl.1.Needlposition :=:form.cursor_item.OCX.spindial.spindialctrl.1.Needlposition + 1;





• Property accessors (PL/SQL procedures)

tblname varchar2;

tblname := table_pkg.TableName(

:item(’Oblk.Oitm’).interface);



• Control properties dialog box– Displays ActiveX-specific

properties– See Control

Help propertyordocumentation


......................................................................................................................................................Setting and Getting ActiveX Control Properties

Property Accessors (PL/SQL Procedures)You can set and get property values with the program units, calledpropertyaccessor functions andproperty accessor procedures, generated from theOLE Importer. Use the property accessor functions to get a property value,and use the property accessor procedures to set a property value.

Control Properties Dialog Box from the Property Palette1 In the Object Navigator, double-click the object icon next to the ActiveX

Control Item to display the Property Palette.

2 Click the Control Properties property.

The Control Property dialog box appears.

3 Set the control properties accordingly.

4 Click OK.

/* Getting an ActiveX control property using get accessor*/tblname varchar2;tblname := table_pkg.TableName(:item(’Oblk.Oitm’).interface);/* table_pkg is the name of the program unit created from

the OLE Importer. TableName is the name of the propertyaccessor. Oblk is the name of the block and Oitm is thename of the item.*/




Invoking ActiveX ControlMethods

MMVX_DMMVX.PLAY(:item

(’video_ocx.video’).INTERFACE);


......................................................................................................................................................Invoking ActiveX Control Methods

Invoking ActiveX Control MethodsMethods define actions that the ActiveX control can perform.

How to Invoke a Method?You can access methods exposed by an ActiveX control with the programunits generated from the OLE Importer. Call any of the methods in any ofthe packages available under the Program Units node. To obtain informationon a specific method of an ActiveX control, see the documentation of thisActiveX control or, if available, the Control Help property.

Example

/* Getting a cell value from a Spread Table ActiveX controlusing the GetCellByColRow method provided in theSpreadTable package */

DECLARE

Cur_Row number;

Cur_Col number;

OLEObj OleObj;

BEGINCur_Row:=SpreadTable.CurrentRow(:ITEM(’BLK.ITM’).interface);Cur_Col:=SpreadTable.CurrentCol(:ITEM(’BLK.ITM’).interface);

OLEObj:=SpreadTable.GetCellByColRow(:ITEM(’BLK.ITM’).interface,Cur_Col, Cur_Row);

END;




Responding to ActiveX ControlEvents

Two ways to deal with events:

• Directly call the appropriate restrictedprocedure.

• Code the On-Dispatch-Event trigger.

PROCEDURE /*Click*/ event4294966696

(interface OleObj);

BEGIN

/* Write your code here */

END;


Responding to ActiveX ControlEvents

All event procedures are restricted.

• To run in unrestricted mode, use:

Initialization events do not fire.

• Use the When-New-Item-Instance orWhen-New-Record-Instance triggers inplace.

FORMS4W.DISPATCH_EVENT

(RESTRICTED_UNALLOWED);


......................................................................................................................................................Responding to ActiveX Control Events

Responding to ActiveX Control EventsActiveX controls invoke events. Form Builder exposes these events in theevents package created from the OLE Importer. The events are defined bythe ActiveX control. Each event is associated with a PL/SQL proceduredefined in the events package. The procedures are named according to aninternal numeric representation as defined by the control. When the controlfires an event, the code in the procedure is automatically executed. You areresponsible for providing the code to respond to the event. Type directly intothe event package body and compile the package. The restricted procedureproduced by an event has an application programming interface similar tothe following:

Another method to deal with the event is to add code to the On-Dispatch-Event trigger.

Restricted or Unrestricted ModeAll event procedures are restricted; that is, go_item cannot be called fromwithin the procedure code. However, there are instances when the sameevent may apply to multiple items and a go_item is necessary; this is theunrestricted case.

In the unrestricted case, you need to do the following: In the On-Dispatch-Event Trigger (block or form level), call:

There is no need to explicitly call the event procedure because it willautomatically be called following the On-Dispatch trigger code.

Initialization EventsInitialization events for ActiveX controls do not fire in Forms run-time.These initialization events are intentionally disabled. Instead, you can useWhen-New-Item-Instance or When-New-Record-Instance triggers in placeof the control’s native initialization events.

PROCEDURE /*Click*/ event4294966696(interface OleObj);

FORMS4W.DISPATCH_EVENT(RESTRICTED_ALLOWED);FORMS4W.DISPATCH_EVENT(RESTRICTED_UNALLOWED);



Technical NoteLAST_OLE_EXCEPTION built-in takes variables, not constants.


Handling Exceptions

• When a failure occurs, theFORMS_OLE_FAILURE is raised.

• Use LAST_OLE_EXCEPTION built-in toget more detailed information.

. . .

WHEN FORMS_OLE_FAILURE THEN

ExceptNum :=LAST_OLE_EXCEPTION(errScr,

errDescription, errHelpfile,

errHelpContext);

. . .


......................................................................................................................................................Handling Exceptions

Handling Exceptions

FORMS_OLE_FAILURE ExceptionWhen a failure is detected in handling an ActiveX control method, an FRMmessage appears or the FORMS_OLE_FAILURE exception is raised. Whena FORMS_OLE_FAILURE exception is raised, you can get more detailedinformation regarding the cause of the exception by calling theLAST_OLE_EXCEPTION built-in.

/*

Handling the exception raised as a result of calling:Var_To_Number(To_Variant(’Jane’));

An exception is raised because Jane is not a number.

*/

DECLARE

errCode PLS_INTEGER;

errSrc VARCHAR2(200);

errDescription VARCHAR2(2000);

errHelpfile VARCHAR2(200);

errHelpContext PLS_INTEGER;

BEGIN

. . .

WHEN form_ole_failure THEN

errCode := LAST_OLE_EXCEPTION(errSrc,

errDescription,

errHelpfile,

errHelpContext);

-- Inform the user of the problem

MESSAGE(errSrc||’;’||errDescription);

END;




Registering an ActiveX Control

Two methods to register:

• Implicitly register using the installationprogram.

• Use regsvr32.exe explicitly.

ActiveX controls require supporting DLL:

• Must be in \Windows\System or searchpath

Reregister the ActiveX control if you moveit or rename its directory.


......................................................................................................................................................Registering an ActiveX Control

Registering an ActiveX ControlYou must register all ActiveX controls. If you install an ActiveX controlusing the installation program supplied with the ActiveX control, theregistration is most likely taken care of by the installation program. Formanual registration of ActiveX controls, useregsvr32.exe; this program isavailable with Microsoft development tools and from ActiveX controlvendors.

Supporting FilesMost ActiveX controls require a supporting DLL, such as the MicrosoftFoundation Class run-time library, mfc40.dll. The DLL must be in the\WINDOWS\SYSTEM directory or in the search path. If the DLL is out ofdate or missing, your ActiveX control will not register properly. Mostreputable ActiveX control suppliers provide documentation regarding theversion and name of the DLLs required; some even supply and install themalong with the ActiveX controls.

Moving ActiveX Control FilesAt installation, the directories in which the ActiveX control is installed areregistered in the Windows Registration Database in Windows 95 andWindows NT, making the ActiveX control visible to your developmentenvironment. If you move the ActiveX control to a different directory, orrename the directory, the information in the registry is no longer valid. If youfind it necessary to move the ActiveX control or rename its directory, useregsrv32.exe or regActiveX.exe utilities provided with most Microsoftdevelopment products to reregister the ActiveX in its new location.



.


Summary

• VBX, OCX, and ActiveX controls

• Differences between controls

• ActiveX control in Form Builder

• Create an ActiveX control container

• Set and get ActiveX control properties

• Invoke ActiveX control methods

• Respond to ActiveX control events

• Register an ActiveX control


......................................................................................................................................................Summary

SummaryIn this lesson, you saw how include and use ActiveX Controls in yourDeveloper/2000 application.

• VBX, OCX, and ActiveX controls

• Differences between controls

• ActiveX control in Form Builder

• Create an ActiveX control

• Set and get ActiveX control properties

• Invoke ActiveX control methods

• Respond to ActiveX control events

• Register an ActiveX control

................................

I

Oracle Rdb Overview

I-2 Developer/2000: Build Forms II......................................................................................................................................................

......................................................................................................................................................Appendix I: Oracle Rdb Overview

What Is Oracle Rdb?Oracle Rdb is a multiuser relational database management system designedfor high availability, high performance, and ease of use. It supports a full setof utilities and an industry-standard data definition and manipulationlanguage that lets you create, query, and maintain your Oracle Rdbdatabases.

Oracle Rdb is currently running on over 45 thousand servers at more than 15thousand sites worldwide. It is used in many large online transactionprocessing (OLTP) systems and its customer list comprises some of theworlds’ leading corporations. Oracle continues to enhance the functionalityof Oracle Rdb by adding new features and making Oracle technology andtools work with Rdb.

Supported PlatformsOracle Rdb is available on the following computing platforms:

• Digital OpenVMS VAX

• Digital OpenVMS Alpha

• Digital UNIX

• Microsoft Windows NT on Digital Alpha

• Microsoft Windows NT on Intel

Developer/2000: Build Forms II I-3......................................................................................................................................................

......................................................................................................................................................Oracle Rdb Features

Oracle Rdb FeaturesThese are some of the features that Oracle Rdb offers:

• Industry Standard SQL Interface

- This includes interactive SQL for prototyping queries and datadefinition as well as SQL module language and SQL precompilersfor many programming languages. Oracle Rdb SQL also supportsmultistatement SQL stored procedures, user defined functions, andmany other features.

• Database Maintenance and Diagnostic Tools

- The Oracle Rdb management utility (RMU) provides a full set oftools for the database administrator to use to perform databasemaintenance tasks and monitor and display information about thedatabase.

• Multiversion Support

- Oracle Rdb will support multiple software versions simultaneouslyon the same system. This allows developers to test new applicationswithout disturbing the production environment.

• Data Security

- Access to the database and individual database objects can be limitedto authorized users only. Role-oriented privileges can be placed onmaintenance functions and database events can be audited.

• Data Consistency

- Data definitions can be stored in the database instead of applicationprograms. Views can be used to manage data stored in multipletables. Constraints and triggers can be used to ensure that changeddata remains consistent throughout the database. Journal files ensurethat incomplete transactions and database recoveries preserve theoriginal data.

• VLDB and Record Partitioning

- Oracle Rdb can cache large amounts of data in memory for fastaccess and data records can be partitioned across storage areas anddisks.

• Optional Hot Standby

- This option provides a transaction-consistent standby system for usein the event of a catastrophic failure.



Standard User InterfacesOracle and Oracle Rdb users can use the same applications developmenttools, system and network management environment, and share data acrossRdb and Oracle7 databases.

• The SQL*Net for Rdb transport allows applications using the OracleCall Interface (OCI) or software such as PL/SQL or Developer/2000 toaccess and manage data in an Oracle Rdb database.

• Applications can also use the ODBC interface to access both Oracle andOracle Rdb databases.

• Internet and intranet applications using the Rdb Web Cartridge can bedelivered to the user through the Oracle Web Server.

• The Oracle Enterprise Manager can be used to manage both Oracle andOracle Rdb databases.

• Oracle Trace and Oracle Expert can be used for diagnosis and tuning ofboth Oracle and Oracle Rdb databases.

• Oracle Express Server can query Oracle Rdb databases and load Rdbdata into the Express Server engine for present or future onlineanalytical processing (OLAP).

Developer/2000: Build Forms II I-5......................................................................................................................................................

......................................................................................................................................................Other Information

Other Information

Oracle Rdb EducationOracle education offers a comprehensive series of Oracle Rdb courses. Thecourse titles are:

• Introduction to Oracle Rdb: Technical Overview and SQL Language

• Oracle Rdb for the Database Administrator

• Oracle Rdb for the Database Designer

• Oracle Rdb 3GL Programming

• Oracle Rdb Performance and Tuning

• Oracle Rdb Internals

To receive the Oracle Rdb education schedule and catalogue, which containscomplete course descriptions, pricing and enrollment information, call1.800.633.0575 within the continental US or 415.506.6800 from outside theUS.

Oracle Rdb Product Information and Demonstration CD-ROMTo receive an Oracle Rdb product information and demonstration CD-ROM,send Email with your name, address and company name to:

• [email protected]

Specify that you’d like to receive the Oracle Rdb CD-ROM.

Oracle Rdb WebsiteThe Oracle Rdb website is a resource for finding out the latest news on theproduct and its features. Look here for information such as:

• New product release information

• Product interoperability information

• Feature descriptions

• Calendar of product-related events

To access the website, go to the following address:

• http://www.oracle.com/rdb

developer/2000: build forms ii -...

Documents