Oracle iDS Forms: Build Internet Applications II Volume 2 • Student Guide .......................................................................................
l a n r e t n I e l c a r O 40034GC11
Production 1.1
June 2001
D33130
ly n O e s U I A O &
Author
Copyright Oracle Corporation, 2001. All rights reserved.
Ian Purvis
This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:
Technical Contributors and Reviewers Cem Ayberkin Gunnar Bohrs Brian Boxx Libor Danek Laurent Dereac Brain Fry Pam Gamer Pascal Gibert Thierry Guibert Ellen Gravina Norris Gray Anthony Hargreaves Ali Hiemstra Barry Hiern Ursula Hovy Sarah Jones Jan Keuben Aija Lehtonen Regis Louis Kenji Makita Jayne Marlow Sergei Pechersky Milos Randak Jonas Reborg Bryan Roberts Candace Stover Hiroyuki Sugiyama Laura Tejero Jack Walsh
Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c) (1) (ii) of DFARS 252.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 means without the express prior written permission of the Worldwide Education Services group of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 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 find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box 659806, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle Developer, Oracle Server, and PL/SQL are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.
l a n r e t n I e l c a r O
Publisher
Shane Mattimoe
ly n O e s U I A O &
Contents .....................................................................................................................................................
Contents Preface Profile xv Related Publications xvi Typographic Conventions xvii Introduction Introduction I-2 Course Content I-3 Lesson 1: Creating a Menu Module Introduction 1-3 Components of the Menu Module 1-5 The Default Menu 1-7 The Menu Editor 1-9 Creating a Menu Module 1-11 Module Properties 1-13 Menu Properties 1-15 Menu Item Properties 1-17 Menu Item Types 1-19 Menu Item Command Types 1-21 Implementing Menu Toolbars 1-23 Storing the Menu Module 1-25 Attaching the Menu Module 1-27 Pop-up Menus 1-29 Summary 1-31 Practice Overview: Lesson 1 1-33 Practice 1 1-34
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 2: Managing Menu Modules Introduction 2-3 Using PL/SQL in Menu Item Code 2-5 Useful Built-in Menu Subprograms 2-7
..................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II iii
Contents .....................................................................................................................................................
Showing and Hiding the Current Menu 2-9 Managing Menu Security 2-11 Defining Security Roles 2-13 Assigning Access to Menu Items 2-15 Summary 2-17 Practice Overview: Lesson 2 2-19 Practice 2 2-20 Lesson 3: Programming Function Keys Introduction 3-3 Key Triggers 3-5 Defining Key Triggers 3-7 Characteristics of Key Triggers 3-9 Classification of Key Triggers 3-11 Using Key Triggers 3-15 Association with Other Interface Controls 3-17 Summary 3-19 Practice Overview: Lesson 3 3-21 Practice 3 3-22 Lesson 4: Responding to Mouse Events Introduction 4-3 What Are Mouse Events? 4-5 Responding to Mouse Movement 4-7 Responding to Mouse Button Actions 4-11 Summary 4-15 Practice Overview: Lesson 4 4-17 Practice 4 4-18
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 5: Controlling Windows and Canvases Programmatically Introduction 5-3 Using Window-Interaction Triggers 5-5 Built-ins for Manipulating Windows 5-7 Built-ins for Manipulating Canvases 5-9
..................................................................................................................................................... iv Oracle iDS Forms: Build Internet Applications II
Contents .....................................................................................................................................................
Working with Tab-Style Canvases 5-11 Windows and Blocks 5-15 Manipulating Window Properties Programmatically 5-17 Closing Windows 5-19 Manipulating Windows in Web-Deployed Forms 5-21 Sizing Windows for Web-Deployed Forms 5-23 Display Large Data Blocks in a Window 5-25 Managing Images for Web-Deployed Forms 5-27 Summary 5-29 Practice Overview: Lesson 5 5-31 Practice 5 5-32 Lesson 6: Defining Data Sources Introduction 6-3 Data Source Types 6-5 Basing a Data Block on a FROM Clause Query 6-7 Basing a Data Block on a Stored Procedure 6-9 Example of a Query Using a REF Cursor Procedure 6-11 Example of Query Using a Table of Records Procedure 6-13 Example of DML Using a Stored Procedure 6-15 Deciding Whether to Use a REF Cursor or a Table of Records 6-17 Data Block Wizard 6-19 Data Block Properties 6-21 Data Source Guidelines 6-23 Summary 6-27 Practice Overview: Lesson 6 6-29 Practice 6 6-30
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 7: Working with Oracle8i Objects in Form Builder Introduction 7-3 What Are Object Types? 7-5 Review of Objects 7-7 Object REFs 7-9
..................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II v
Contents .....................................................................................................................................................
Supported Oracle8i Features 7-11 How Oracle Forms Developer Treats Objects 7-13 Object Type Displays 7-15 Creating Data Blocks Based on Oracle8i Objects 7-17 Selecting Object Table Columns 7-19 Selecting Object Column Attributes 7-21 Blocks with REF Lookups 7-23 The REF Column Value 7-25 LOVs for REFs 7-27 Summary 7-29 Practice Overview: Lesson 7 7-31 Practice 7 7-32 Lesson 8: Controlling Data Block Relationships Introduction 8-3 Creating a Relation 8-5 Block Coordination 8-7 Coordinate Blocks Using REFs 8-9 Characteristics of Relation-Handling Triggers 8-11 Principles of Relation-Handling Code 8-13 Obtaining Relation-Handling Information 8-15 Implementing a Coordination-Type Toggle 8-17 Forcing a Commit Per Master 8-19 Summary 8-21 Practice Overview: Lesson 8 8-23 Practice 8 8-24
ly n O e s U I A O &
Lesson 9: Building Multiple Form Applications Introduction 9-3 OPEN_FORM to Invoke Additional Forms 9-5 Closing Forms 9-7 Navigating Between Forms 9-9 Transaction Processing for Opened Forms 9-11 CALL_FORM to Invoke Additional Forms 9-13
l a n r e t n I e l c a r O
..................................................................................................................................................... vi Oracle iDS Forms: Build Internet Applications II
Contents .....................................................................................................................................................
Transaction Processing for Called Forms 9-15 NEW_FORM to Invoke Additional Forms 9-19 Controlling Open Forms and Called Forms Together 9-21 Different Ways of Invoking Forms 9-23 Using Form Parameters 9-25 Parameter Lists 9-29 Creating and Manipulating Parameter Lists 9-31 Passing Data Between Forms 9-33 Summary 9-35 Practice Overview: Lesson 9 9-37 Practice 9 9-38 Lesson 10: Working with Record Groups Introduction 10-3 Record Groups 10-5 Using Record Groups 10-7 Defining Record Groups at Design Time 10-9 Built-in Functions for Controlling Record Groups 10-11 Defining Query Record Groups Programmatically 10-17 Defining Nonquery Record Groups Programmatically 10-19 Manipulating Record Group Rows 10-21 Manipulating Selected Record Group Rows 10-23 Defining Global Record Groups 10-25 Manipulating List Items Programmatically 10-27 Implementing Dynamic List Items 10-29 Adding Values to Combo Boxes 10-31 Summary 10-33 Practice Overview: Lesson 10 10-35 Practice 10 10-36
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 11: Using Reusable Components Introduction 11-3 Reusable Components 11-5 Reusable Components List 11-7
..................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II vii
Contents .....................................................................................................................................................
The Calendar Class 11-11 Calendar Object Group Content 11-13 Built-in for Manipulating the Calendar 11-15 Summary 11-17 Practice Overview: Lesson 11 11-19 Practice 11 11-20 Lesson 12: Using Java with Web-Deployed Forms Introduction 12-3 Java and Oracle Forms Developer 12-5 Java in Web-Deployed Forms 12-7 The Forms Applet 12-9 Defining Fonts for Web-Deployed Forms 12-11 Pluggable Java Components 12-13 What Is a Bean Area? 12-17 Hosting a JavaBean 12-19 How a JavaBean Communicates 12-21 Communicating with the JavaBean 12-23 Exploiting Oracle8i Features 12-25 How to Create JavaBeans 12-27 The Java Importer 12-29 The Java Importer Tool 12-31 Summary 12-33 Practice Overview: Lesson 12 12-35 Practice 12 12-36
ly n O e s U I A O &
Lesson 13: Using Server Features in Form Builder Introduction 13-3 Using Oracle Database Server Functionality in Forms 13-5 Dealing with PL/SQL Code 13-7 PL/SQL8 Support 13-9 PL/SQL8 Scalar Data Types 13-11 Unsupported Oracle Forms Developer PL/SQL8 Features 13-13 Handling Errors Raised by the Oracle Database Server 13-15
l a n r e t n I e l c a r O
..................................................................................................................................................... viii Oracle iDS Forms: Build Internet Applications II
Contents .....................................................................................................................................................
Performing DDL with FORMS_DDL 13-19 Using FORMS_DDL 13-21 Summary 13-23 Practice Overview: Lesson 13 13-25 Practice 13 13-26 Lesson 14: Applying Timers Introduction 14-3 Timers 14-5 Handling Timer Expiration 14-7 Creating a Timer 14-9 Modifying a Timer 14-11 Deleting a Timer 14-13 Summary 14-15 Practice Overview: Lesson 14 14-17 Practice 14 14-18 Lesson 15: Including Charts and Reports Introduction 15-3 Including Charts by Using the Chart Wizard Chart Item Properties 15-11 Reporting Within Form Builder 15-13 Report Object Properties 15-15 Working with Reports 15-17 Summary 15-23 Practice Overview: Lesson 15 15-25 Practice 15 15-26 Appendix A: Practice Solutions Practice 1 Solutions A-2 Practice 2 Solutions A-6 Practice 3 Solutions A-9 Practice 4 Solutions A-11 Practice 5 Solutions A-12
l a n r e t n I e l c a r O
15-5
ly n O e s U I A O &
..................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II ix
Contents .....................................................................................................................................................
Practice 6 Solutions Practice 7 Solutions Practice 8 Solutions Practice 9 Solutions Practice 10 Solutions Practice 11 Solutions Practice 12 Solutions Practice 13 Solutions Practice 14 Solutions Practice 15 Solutions
A-15 A-16 A-18 A-21 A-24 A-27 A-30 A-33 A-35 A-37
Appendix B: Table Descriptions and Data Summit Sporting Goods Database Diagram S_CUSTOMER Description B-3 S_CUSTOMER Data B-4 S_DEPT Description and Data B-8 S_EMP Description B-9 S_EMP Data B-10 S_ITEM Description B-13 S_ITEM Data B-14 S_ORD Description and Data B-16 S_PRODUCT Description B-17 S_PRODUCT Data B-18 S_REGION Description and Data B-22 S_TITLE Description and Data B-23 Oracle8i Objects: Types, Tables B-24 Appendix C: Mouse Movement Events Introduction C-2 Drag and Drop C-4 Summary C-6
l a n r e t n I e l c a r O
B-2
ly n O e s U I A O &
Appendix D: Introduction to Oracle8i Object Features Introduction D-3
..................................................................................................................................................... x Oracle iDS Forms: Build Internet Applications II
Contents .....................................................................................................................................................
New Oracle8i Data Types D-5 Object Types D-7 Creating Oracle8i Objects D-11 Object Columns D-13 Object Views D-15 INSTEAD-OF Triggers D-17 Referencing Objects D-19 Displaying Oracle8i Objects in the Object Navigator D-21 Summary D-29 Appendix E: Menu Run-Time Parameters Introduction E-3 Substitution Parameters E-5 Defining a User-Named Substitution Parameter E-7 Substitution Parameter Built-ins E-9 Validating a Substitution Parameter Value E-11 Summary E-13 Appendix F: Handling Server-Side Errors Introduction F-3 Obtaining the Cause of Declarative-Constraint Violations F-5 Customizing Oracle Server Error Messages F-7 Example Procedure for Handling Oracle Server Errors F-9 Summary F-13 Appendix G: EMP_PKG Package Package Specification G-2 Package Body G-4
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix H: Working with ActiveX Controls Introduction H-3 What Are VBX, OCX, and ActiveX Controls? H-5 Comparison of VBX, OCX, and ActiveX Controls H-7 ActiveX in Form Builder H-9 Implementing an ActiveX Control H-11
..................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II xi
Contents .....................................................................................................................................................
Inserting an ActiveX Control in an ActiveX Control Item H-13 Importing ActiveX Control Methods and Events Packages H-15 Setting and Getting ActiveX Control Properties H-17 Invoking ActiveX Control Methods H-21 Responding to ActiveX Control Events H-23 Handling Exceptions H-25 Registering an ActiveX Control H-27 Summary H-29 Appendix I: Using Oracle Server Roles at Run Time Using Oracle Server Roles at Run Time I-3 Procedures for Managing Roles I-7
l a n r e t n I e l c a r O
ly n O e s U I A O &
..................................................................................................................................................... xii Oracle iDS Forms: Build Internet Applications II
................................
Using Reusable Components
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 11: Using Reusable Components ......................................................................................................................................................
Objectives
After completing this lesson, you should be able to do the following:
• •
List the reusable components Include the calendar object in an application
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview In this lesson, you will learn about the reusable components that are available with Oracle Forms Developer. You will learn how these components can fit into any application. Objectives After completing this lesson, you should be able to do the following: • List the reusable components • Include the calendar object in an application
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-3
Lesson 11: Using Reusable Components ......................................................................................................................................................
Reusable Components
Picklist
Calendar
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-4 Oracle iDS Forms: Build Internet Applications II
Reusable Components ......................................................................................................................................................
Reusable Components Oracle Forms Developer contains a complete set of demonstrations that illustrate the power and productivity of the product. These demonstrations contain many tips and techniques that you can copy and use in your own applications. Reusable Component Definition A reusable component is a generic object (object group, block, PL/SQL library, and so on) that you can reuse in all your applications. You can create your own reusable components (code and object) by using the generic programming characteristics. Oracle Forms Developer also includes a number of reusable components that enable you to easily and rapidly build applications that provide power and performance while conforming to GUI standards. You can customize these components. Web Design Tip JavaBeans and Enterprise JavaBeans are reusable components that you can use with Web-deployed Oracle Forms Developer applications. These allow you to create a customized look and feel for the user interface, and to add additional functionality. The deployment of JavaBeans is covered in a later lesson.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-5
Lesson 11: Using Reusable Components ......................................................................................................................................................
Reusable Components List • • • • • • • •
ActiveX controls Java Beans Sample Icons Standard Menus PL/SQL Drag & Drop Library PL/SQL Utilities Library PL/SQL Conversion Library PL/SQL Window System Interface Library
Copyright Oracle Corporation, 2000. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-6 Oracle iDS Forms: Build Internet Applications II
Reusable Components List ......................................................................................................................................................
Reusable Components List The reusable components include: Reusable Component
Description
ActiveX controls
An ActiveX control is a stand-alone software component that you can include in your applications. Five ActiveX controls are shipped with Oracle Forms Developer: Image, Sound, Spread Table, Tabsheet, and a Video object. ActiveX controls cannot be used with Web-deployed forms.
JavaBeans
A JavaBean is a stand-alone software component that you can include in your Web-deployed applications. JavaBeans allow you to customize the user interface, and to add new functionality.
Sample icon files
Sample icons can be found on the demo CD provided with Oracle Forms Developer CD pack. ICO and GIF icon files used in the demonstrations and sample menus are included.
Standard menus
These demonstrations include menudef.mmb and a number of standard menus that you may want to reuse and customize.
PL/SQL Drag and drop library
This PL/SQL library provides procedures to implement drag and drop in client server applications.
PL/SQL Utilities library
This PL/SQL library contains a procedure to write out the contents of a block.
PL/SQL Conversion library
This PL/SQL library contains procedures to perform complex conversions.
PL/SQL Window system interface library
This PL/SQL library contains many commonly used routines specific to the Windows environment. The utility consists of a PL/SQL library and a Windows dynamic-link library (DLL) and can be used with either Form Builder or Report Builder.
ly n O e s U I A O &
The PL/SQL library uses the ORA_FFI package and can be used as a model for creating your own DLL calls. The utility FFI_GEN is also available to help you generate a PL/SQL interface to DLLs.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-7
Lesson 11: Using Reusable Components ......................................................................................................................................................
Reusable Components List • • • • • • •
Standard Object Library Calendar class Picklist class Wizard class Navigator class Help class Oracle Applications Object Library
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-8 Oracle iDS Forms: Build Internet Applications II
Reusable Components List ......................................................................................................................................................
Reusable Component
Description
Standard object library
The object library, stndrd20.olb, contains standard classes and visual attribute groups to help you build your applications to a standard look and feel. Some objects in the library are set as SmartClasses so that they can be readily applied to the objects created in your forms.
Calendar class
This stndrd20.olb library component enables you to add a calendar or date list of values to your application
Picklist class
This stndrd20.olb library component enables you to easily add a picklist or chooser-style interface to an application, and to provide functionality that mimics the picklists of wizards and other dialogs.
Wizard class
This stndrd20.olb library component enables you to easily add a wizard-style interface to an application, and to provide functionality that mimics the wizards of the Oracle Forms Developer builders and other Windows products.
Help class
This stndrd20.olb library component enables you to build an online Help facility with a Windows look and feel. Help text is stored on the database.
Oracle applications object library
The object library, appsstds.olb, contains property classes and visual attribute groups that enable you to build modules that have the same look and feel as Oracle Applications. They have a Windows look and feel, but they have also been designed for maximum portability.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-9
Lesson 11: Using Reusable Components ......................................................................................................................................................
The Calendar Class
Copyright Oracle Corporation, 2001. All rights reserved.
®
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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 11-10 Oracle iDS Forms: Build Internet Applications II
The Calendar Class ......................................................................................................................................................
The Calendar Class This component enables you to easily add a calendar or date list of values to an application. The Calendar window automatically displays the calendar according to the NLS settings that are currently in effect, adjusting the start of the week (for example, Sunday in the United States, Monday in Western Europe) and the month and day names accordingly. Creating a Calendar To create a calendar, follow the steps below: 1 Open the stndrd20.olb object library. From the Components page, copy or subclass the Calendar object group. 2 Attach the PL/SQL library calendar.pll, which contains the Date_LOV package, to your module. 3 Create a Key-Listval trigger on the date item for which you would like to use the Date List of Values window. Add code to display the calendar using the Date_LOV package. 4 If you want the end user to be able to close the Date List of Values window by clicking the WindowClose button in the title bar, create a form-level When-Window-Closed trigger. The calendar appears whenever the end user invokes a list of values for the date item. A single calendar can be reused on many different date items. Simply create a Key-Listval trigger for each item.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-11
Lesson 11: Using Reusable Components ......................................................................................................................................................
Calendar Object Group Content
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-12 Oracle iDS Forms: Build Internet Applications II
Calendar Object Group Content ......................................................................................................................................................
Calendar Object Group Content The component contains many different Form Builder objects. You can customize those objects for your specific application. The Blocks The calendar object contains two blocks: • Date_Control_Block: This block contains the buttons that control which month and year is displayed. • Date_Button_Block: This block contains the buttons that represent each day of the month. The Canvas The calendar class contains a content canvas named Date_Lov_Canvas. This canvas contains all the items from both blocks. The Window The calendar class contains a window named Date_Lov_Window. This window is used to display the Date_Lov_Canvas. The Visual Attributes The calendar class contains three visual attributes: • Date_Normal_VA: This visual attribute contains the attributes for a date button that is displayed normally. • Date_Selected_VA: This visual attribute contains the attributes for a date button that is displayed as selected. • Date_Weekend_VA: This visual attribute contains the attributes for a date button that is displayed as a weekend day.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-13
Lesson 11: Using Reusable Components ......................................................................................................................................................
Built-in for Manipulating the Calendar Use Date_LOV.Get_Date to display the calendar
•
Syntax
date_lov.get_date(
•
display_date,return_item, window_x_pos,window_y_pos, window_title, ok_label,cancel_label, highlight, autoconfirm,autoskip );
Example
date_lov.get_date(sysdate,’s_ord.date_ordered’,240 date_lov.get_date(sysdate,’s_ord.date_ordered’,240 ,60,’Order ,60,’Order Date’,’OK’,’Cancel’,TRUE,FALSE,FALSE); Date’,’OK’,’Cancel’,TRUE,FALSE,FALSE); Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-14 Oracle iDS Forms: Build Internet Applications II
Built-in for Manipulating the Calendar ......................................................................................................................................................
Built-in for Manipulating the Calendar To use the calendar class, you have to attach a PL/SQL library called calendar.pll. This library contains a package called DATE_LOV. This package contains routines that the developer uses to interact with the component. Date_LOV.Get_Date Procedure This procedure displays the calendar with the month and year specified in the Display_Date argument. The day of the month is displayed with bold numbers. The date chosen by the end user is returned to the item specified by the Return_Item argument. . . . PROCEDURE Date_LOV.Get_Date( display_date DATE, return_item VARCHAR2, v_x_pos NUMBER := 0, v_y_pos NUMBER := 0, v_title VARCHAR2 := ’Date List of Values’, v_ok VARCHAR2 := ’OK’, v_cancel VARCHAR2 := ’Cancel’, v_highlight BOOLEAN := TRUE, v_autoconfirm BOOLEAN := TRUE, v_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 which to return the chosen date
v_x_pos
Specifies the X coordinate of the Date List of Values window
v_y_pos
Specifies the Y coordinate of the Date List of Values window
v_title
Title to display in the Date List of Values window
v_ok
Label for the OK button
v_cancel
Label for the Cancel button
v_highlight
Specifies that weekend days appear in a different color
v_autoconfirm
Specifies that the date immediately be returned when the end user clicks a day
v_autoskip
Specifies that the input focus moves to the next item in sequence
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-15
Lesson 11: Using Reusable Components ......................................................................................................................................................
Summary
In this lesson, you should have learned to:
•
Identify reusable components in Oracle Forms Developer
•
Create a Calendar class: – Copy forms objects from calendar object library – Copy PL/SQL code from calendar PL/SQL library
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-16 Oracle iDS Forms: Build Internet Applications II
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
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-17
Lesson 11: Using Reusable Components ......................................................................................................................................................
Practice 11 Overview
This practice covers the following topics:
•
Creating a picklist class to display and select the products available
•
Attaching a calendar class to ORDERS date items: – date_ordered – date_shipped
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 11-18 Oracle iDS Forms: Build Internet Applications II
Practice Overview: Lesson 11 ......................................................................................................................................................
Practice Overview: Lesson 11 This practice guides you through the development of form functionality based on 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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-19
Lesson 11: Using Reusable Components ......................................................................................................................................................
Practice 11 1 Create a picklist by using the picklist class. This picklist enables the end
user to select order items from a product list, and to place selections in a second list. a Create a new form module, called PICKXX where XX is your student course number. Open the Form_Builder_II.olb object library. This library contains form objects and code for the picklist. Copy the PICKLIST_BASIC object group to your form module. Copy the PICKLIST object group into your new form module. b Ensure the CONTROL block is the last defined data block in the Object Navigator. c Attach the picklist.pll library. This contains the functions and procedures required for creating the picklist. d Define a When-New-Form-Instance trigger which will create and populate the picklist. You can import the code from the pr11_1.txt file. Create the picklist with the picklist.Create_Picklist procedure. Call function pick_list.Populate_Picklist_With_Query to populate the List_in object. You must define a query that returns the ID, name and suggested prices from the S_PRODUCT table. Call pick_list.Set_Picklist_Selection procedure to select the first element in the list. Display both lists by using the pick_list.Display_Picklist procedure. Exclude from the query any products already in the current S_ITEM block. You should use the global variable :GLOBAL.where_cls. e Save and compile your form. Deploy your form to the Web. f In your ORDERS form, modify the When-Button-Pressed trigger for the CONTROL.PRODUCT_LOV_BUTTON so that it calls your new form module. g Save and compile your form. Deploy your form to the Web to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
If you have time 2 In the ORDERS form, add a calendar on the S_ORD.DATE_ORDERED item and S_ORD.DATE_SHIPPED item. a From the calendar page of the Form_Builder_II.olb object library, copy the calendar object group.
...................................................................................................................................................... 11-20 Oracle iDS Forms: Build Internet Applications II
Practice 11 ......................................................................................................................................................
Attach the PL/SQL library calendar.pll, which contains the Date_LOV package, to your module. c Create a Key-Listval trigger on the date ordered item. Add code to display the calendar using the Date_LOV package. You can import the code from the pr11_2.txt file. d Create a Key-Listval trigger on the date shipped item. Add code to display the calendar using the Date_LOV package.You can import the code from the pr11_3.txt file. e Save and compile your form module. Deploy your form to the Web to test. b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 11-21
Lesson 11: Using Reusable Components ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 11-22 Oracle iDS Forms: Build Internet Applications II
................................
Using Java with Web-Deployed Forms
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Objectives
After completing this lesson, you should be able to do the following:
• •
Customize the user interface Incorporate JavaBeans in Web-deployed forms by: – Inserting Pluggable Java Components – Creating Bean Areas
• •
Use Oracle8i Java with Web-deployed forms Use the Java Importer Tool
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview In this lesson you will learn how to enhance Web-deployed forms by using JavaBeans to customize the user interface. You will also learn about Oracle8i features that may be used within Web-deployed Oracle forms. Objectives After completing this lesson, you should be able to do the following: • Customize the user interface • Incorporate JavaBeans in Web-deployed forms by - Inserting Pluggable Java Components - Creating Bean Areas • Exploit Oracle8i features with Web-deployed forms • Invoke the Java Importer Tool
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-3
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Java and Oracle Forms Developer •
Java class files
Platform independent programs
•
Java Runtime Environment
A Java Virtual Machine executes class files in Web browser
•
Forms Applet
Downloads class files to create and maintain the Forms user interface
JavaBeans ³reusable programs for Forms! Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-4 Oracle iDS Forms: Build Internet Applications II
Java and Oracle Forms Developer ......................................................................................................................................................
Java and Oracle Forms Developer Oracle Forms Developer applications are deployed on the Web by downloading Java class files to the client browser. The class files create the user interface for the Forms Server application modules and maintain communications between Server and client browser. Java Runtime Environment At run time Java class files are executed by the Java Virtual Machine (JVM). This is either included with a Web browser or with an AppletViewer. The Java Runtime Environment (JRE) is the combination of the JVM and supporting files. Oracle JInitiator provides an alternative to the standard browser JRE; it enhances the Web deployment of Oracle Forms Developer applications. It is supplied as a Netscape plug-in or as an ActiveX component for MicroSoft Internet Explorer. Forms Applet The Forms Applet is a Java code component embedded in the base HTML file. It is responsible for downloading and executing the Java class files that create the Oracle Forms Developer user interface. JavaBeans JavaBeans are portable, reusable and platform independent Java programs. You can change the appearance and behavior of the Forms user interface by including JavaBeans in your Oracle Form Builder applications.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-5
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Java in Web-Deployed Forms Oracle Look and Feel Bean Areas
Pluggable Java Components
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-6 Oracle iDS Forms: Build Internet Applications II
Java in Web-Deployed Forms ......................................................................................................................................................
Java in Web-Deployed Forms You can customize the user interface for your Oracle Forms Developer applications by using the following Java features. Oracle Look and Feel As an alternative interface to the traditional gray Windows look, Oracle Forms Developer provides a Look and Feel parameter for the Forms Applet. This allows you to change the appearance and color of the user interface for an entire application. JavaBeans You can add JavaBeans to extend existing Oracle Form Developer components using Pluggable Java Components. With Bean Areas you can insert JavaBeans that provide new application functionality, and may also be used to execute code on the user interface. Pluggable Java Components You can replace the original appearance or functionality of existing Form items with a Pluggable Java Component (PJC). You can introduce variety with your applications by displaying, for example, rounded buttons, “traffic light” option buttons, and “smoking” checkbox. Bean Areas You can insert new functionality, such as slider controls or progress bars, by defining a Bean Area Item in your form module. With supplied triggers and PL/SQL built-ins, you can communicate between the Form Server and the client JavaBean.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-7
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Oracle Look and Feel • • •
Oracle Look and Feel Pluggable Java Components Bean Areas
Copyright Oracle Corporation, 2001. All rights reserved.
®
Oracle Look and Feel
Oracle Look and Feel is defined in the Forms Applet base HTML file <EMBED type="application/x-jinit-applet" java_CODE="oracle.forms.engine.Main" java_CODEBASE="/web_frms/" java_ARCHIVE="f60all.jar" WIDTH=750 HEIGHT=400 serverPort="5555" serverArgs="module=emp userid=scott/tiger@mydatabase" serverApp="/web_html/summit" pluginspage="http://machine/jinit_download.htm" splashScreen="/web_img/summitlo.gif" lookAndfeel="Oracle" colorScheme="teal">
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 12-8 Oracle iDS Forms: Build Internet Applications II
The Forms Applet ......................................................................................................................................................
The Forms Applet When the base HTML file is downloaded, the embedded Forms Applet is executed by the Java Virtual Machine (JVM) on the Forms Client. The JVM may either be in a browser or in an AppletViewer. You can test your Web forms without deploying them to the middle tier by using Form Builder’s Web Previewer. This uses the AppletViewer as a JVM. Oracle Look and Feel IView is a Java class file which manages the display of Web-deployed forms. Oracle Look and Feel exploits IView to provide you with an alternative user interface to the MS Windows look. You can implement your choice by specifying the Forms Applet lookAndFeel parameter in the base HTML file: Parameter
Parameter Value
lookAndFeel
Determines the application’s look and feel. Values: Oracle or Generic (Windows 95 look-and-feel). Default value is Generic.
colorScheme
Determines the application’s color scheme. Values: Teal, Titanium, Red, Khaki, Blue, Olive, or Purple. Ignored when lookAndFeel is set to Generic.
Technical Note To enable the Web Previewer, you must have installed the Forms Server Runtime Engine in your test environment.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-9
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Defining Fonts for Web-Deployed Forms Use Java supported fonts: Java Font
Windows Font
XWindows Font
Mac Font
Monospaced
Courier New
Adobe-courier
Courier Geneva
Dialog
MS San Serif
b&h-lucida
DialogInput
MS San Serif
b&h-lucidatypewriter Geneva
Helvetica
Arial
Adobe-Helvetica
Symbol
Wingdings
itc-zapfdingbats
Times Roman
Times New Roman Adobe-times
Helvetica Symbol Times Roman
Define your default Java font in: Oracle_Home… /form60/java/oracle/forms/registry/reg istry.dat Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-10 Oracle iDS Forms: Build Internet Applications II
Defining Fonts for Web-Deployed Forms ......................................................................................................................................................
Defining Fonts for Web-Deployed Forms When you select a font for building your Web-deployed forms applications, you should ensure that it will map to supported Java fonts. The Java environment for Oracle Forms Developer supports the following fonts: Java Font
MS Windows Font Windows Font X
Macintosh Font
Monospaced
Courier New
Adobe-courier
Courier
Dialog
Ms Sans Serif
b&h-lucida
Geneva
DialogInput
Ms Sans Serif
b&h-lucidatypewriter
Geneva
Helvetica
Arial
Adobe-Helvetica
Helvetica
Symbol
WingDings
itc-zapfdingbats
Symbol
TimesRoman
Times New Roman
Adobe-times
Times Roman
At run time, Oracle Form Developer fonts are converted to their Java equivalent using a reference to an alias file, Registry.dat. If your selected Oracle Form Builder font does not map to a supported Java font, it will be replaced with the default Java font. You can specify the default Java font by editing the Java registry file. The Registry.dat file is located in: ORACLE_HOME/forms60/java/oracle/forms/registry/
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-11
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Pluggable Java Components • • •
Oracle Look and Feel Pluggable Java Components Bean Areas
Copyright Oracle Corporation, 2001. All rights reserved.
®
Pluggable Java Components Pluggable Java Components ( PJCs ) are JavaBeans that enable you to customize the appearance and functionality of existing Form Builder control items.
ly n O e s U I A O &
Pluggable Java Components are supported with the following Form Items: Check box
List Item
Push Button
Text Item
Radio Button
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 12-12 Oracle iDS Forms: Build Internet Applications II
Pluggable Java Components ......................................................................................................................................................
Pluggable Java Components When you deploy Oracle Forms Developer modules on the Web, native user interface controls, such as push buttons or radio buttons, are replaced by a Java user interface component. The Java components are defined as defaults within Form Builder. They are based on Java class files supplied with Oracle Forms Developer; you do not need to specify them. As an alternative to the standard Java user interface component, you can specify a Pluggable Java Component, which will enable you to customize the appearance and functionality of the original control. The PJC is a JavaBean that must support the properties and events specific to the type of user interface component, for which it is being used. Specifying Use of a PJC in an Oracle Forms Developer Module You may specify a PJC for any form module user interface control which has an Implementation Class property. Supported control types are: • Check Box • List Item • Push Button • Radio Button • Text item
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-13
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Pluggable Java Components Examples Text Items: Checkboxes:
Y
N
Smoking ?
Radio Buttons
Cash Credit Card Cheque
Copyright Oracle Corporation, 2001. All rights reserved.
®
Using Pluggable Java Components •
Select a valid Form Item
•
Specify the name and path of your PJC in the ‘Implementation Class’ property:
– Name: PasswordFieldPJC.class
ly n O e s U I A O &
– Path : FORMS60_JAVADIR oracle\forms\demos
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 12-14 Oracle iDS Forms: Build Internet Applications II
Pluggable Java Components ......................................................................................................................................................
Pluggable Java Components Oracle provides Java class files for building components in Web-deployed forms. The default value is the name of the standard PJC supplied with Oracle Forms Developer. To use your own PJC, you must supply the fully qualified name of the PJC Java class file: Implementation Class property
Description
Default value
Oracle supplied PJC; value not displayed in property palette
Fully qualified name of PJC
Custom PJC specified by application developer.
Example
oracle.forms.demos.SecureText
Example: Specifying the ‘Securetext’ PJC This PJC will display salary data as asterisk characters. When the user clicks on the field, the PJC will reveal the salary values: • Open the Property Palette for :EMP.SAL and select the Implementation Class property. • Specify the full name of the PJC: oracle.forms.pjc.demo.SecureText Form Builder will search for the file SecureText.class in the demos directory, in the path FORMS60_JAVADIR/oracle/forms/ • Compile the form module.
ly n O e s U I A O &
Location of PJC Class Files You should specify the FORMS60_JAVADIR environment variable to enable Oracle Forms Developer to locate your JavaBeans. The default path is: ORACLE_HOME/forms60/java. It is recommended that you place your custom JavaBean class files within a subdirectory in this path.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-15
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Bean Areas • • •
Pluggable Java Components Oracle Look and Feel Bean Areas
Copyright Oracle Corporation, 2001. All rights reserved.
®
What is a Bean Area?
A Bean Area is a Forms item that enables you to incorporate JavaBeans in your Web-deployed forms. With Bean Areas you can: • Add new control Hello Forms! items and new Someone hit the OK functionality
•
Execute Clientside Java code
•
Communicate between Forms Server and Client with PL/SQL
ly n O e s U I A O &
button…. here’s the “Date Changed” event and a parameter list with the selected date
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 12-16 Oracle iDS Forms: Build Internet Applications II
What Is a Bean Area? ......................................................................................................................................................
What Is a Bean Area? Bean Areas are Forms objects that allow you to host JavaBeans in your Web-deployed forms application. The behavior of these JavaBeans is determined by you, and you can therefore use Bean Areas to provide new functionality for your form module. Unlike Pluggable Java Components, there is no assumed behavior. A PJC, such as a button, knows about being pressed; the Bean Area functionality is derived totally from within the JavaBean. With PL/SQL built-ins and triggers, you can also communicate with the JavaBean at run time. Uses and Benefits of Bean Areas • Add new control items and new functionality • Execute Client side Java code • Communicate between the Form Server and Client
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-17
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Hosting a JavaBean 1. Define a Bean Area 2. Specify the JavaBean 3. JavaBean is visible in Layout Editor
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-18 Oracle iDS Forms: Build Internet Applications II
Hosting a JavaBean ......................................................................................................................................................
Hosting a JavaBean You can host a JavaBean in your Oracle Forms Developer application by creating a Bean area control item. Creating a Bean Area You can create a Bean Area in three ways: • Use the Bean Area Item tool in the Layout Editor • Use the Create icon in the Object Navigator • Convert an existing item Associating a JavaBean with Your Bean Area In order to associate your JavaBean with the Bean Area, you will need to name the JavaBean class file in the Bean Area Implementation class property. This property entry is mandatory. Oracle Forms Developer will locate your JavaBean class files by searching the Java directory, specified in the FORMS60_JAVADIR environment variable. Java is a case sensitive language, and you must ensure that the class file name is precisely specified. You must also supply a fully qualified name. For example, the location of MyEg.class file is: FORMS60_JAVADIR/oracle/forms/demos/MyEg.class You should therefore specify an entry in the Implementation class property: oracle.forms.demos.MyEg After you have specified the Implementation class property, the JavaBean will be visible in the Bean Area (in the Layout Editor).
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-19
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
How a JavaBean Communicates
A JavaBean communicates with a Web-deployed form by issuing an ‘event’. •
When-Custom-Item-Event trigger
•
System variables: – :SYSTEM.CUSTOM_ITEM_EVENT – :SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-20 Oracle iDS Forms: Build Internet Applications II
How a JavaBean Communicates ......................................................................................................................................................
How a JavaBean Communicates Communicating with Forms from a JavaBean A JavaBean will initiate communication with a form by firing a CustomEvent. The JavaBean CustomEvent sends an event name, and the name of a parameter list containing associated data, to the form module. You can obtain this information from the system variables:
System Variable
Description
:SYSTEM.CUSTOM_ITEM_EVENT
Name of Custom event dispatched by Java Bean; this is case sensitive.
:SYSTEM.
Name of Parameter list containing associated event data
CUSTOM_ITEM_EVENT_PARAMETERS
How Forms Receives JavaBean CustomEvents Whenever the JavaBean communicates with your form, the When-Custom-Item-Event trigger will fire. This trigger should be attached to the Bean Area hosting the JavaBean. By examining the form module system variables from this trigger, you can identify which JavaBean CustomEvent has occurred, and take appropriate action.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-21
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Communicating with the JavaBean SET_CUSTOM_PROPERTY built-in Example: Declare EventName varchar2(20) := :system.custom_item_event; EventValues ParamList; EventvalueType number; TempString varchar2(4000); Begin If EventName = ‘ALARMFIRED’ then EventValues := get_parameter_list(:system.custom_event_parameters); get_parameter_attr(EventValues, ALARMTIME’, EventvalueType, TempString); If Summit_Show_Alert(‘Alarm Call:’||TempString) = alert_button1 then set_custom_property(‘jAlarmClock’, 1, ‘wakeuptime’, 1000); end if; end if; End;
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-22 Oracle iDS Forms: Build Internet Applications II
Communicating with the JavaBean ......................................................................................................................................................
Communicating with the JavaBean The JavaBean will accept parameters from PL/SQL that enable you to change its run-time behavior. For example, you can alter the speed of a progress bar or the time for a clock. You can communicate with your JavaBean by calling the built-in SET_CUSTOM_PROPERTY: SET_CUSTOM_PROPERTY(’bean_area’, row, ’property’, value);
Example SET_CUSTOM_PROPERTY(’alarm_clock’,1, ’wakeuptime’, 1000);
In the following example the When-Custom-Item-Event trigger examines the system variables to identify the contents of a parameter list sent by the JavaBean. The JavaBean has sent date and time information to the form, which is then displayed to the user using an alert. Declare EventName varchar2(20) := :system.custom_item_event; EventValues ParamList; Eventvalue Typenumber; TempString varchar2(4000); Begin If EventName = ‘ALARMFIRED' then EventValues := get_parameter_list( :system.custom_event_parameters); get_parameter_attr(EventValues, 'ALARMTIME', EventvalueType, TempString); If Summit_Show_Alert(‘Alarm Call: '||TempString) = alert_button1 then set_custom_property(‘AlarmClock', 1,‘wakeuptime', 1000); end if; end if; End;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-23
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Exploiting Oracle 8i Features
Oracle 8i Java features that can be exploited in Oracle Forms Developer:
• • •
Java stored procedures Enterprise JavaBeans Business Components for Java
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-24 Oracle iDS Forms: Build Internet Applications II
Exploiting Oracle8i Features ......................................................................................................................................................
Exploiting Oracle8i Features With Oracle8i you can create Java Stored procedures, develop Enterprise JavaBeans (EJB), and build Business Components for Java (BC4J). These features are available to Oracle Forms Developer applications, and they allow you to develop features such as e-mail, and automated messaging. Java Stored Procedures Java stored procedures are Java classes which, like PL/SQL stored procedures, are held on the Oracle8i database. In Oracle Forms Developer applications, you are able to call Java stored procedure by writing a PL/SQL wrapper. Enterprise JavaBeans Unlike JavaBeans, Enterprise JavaBeans (EJB) do not perform user interface functions. EJB encapsulate and distribute Java backend logic across many servers in order to manage data, transaction security, and resources. Business Components for Java Business Components for Java (BC4J) is an application framework for generating reusable business logic. You can encapsulate business rules in Java component libraries, and reuse them by generating SQL-based views. For example, with Oracle8i object views and XML (Extended Markup Language), you can build message based e-commerce applications that implement your business rules.
ly n O e s U I A O &
Developing Server Side Java You can develop Java stored procedures, EJB, and BC4J by using Oracle JDeveloper. You will require knowledge in Java programming.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-25
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
How to Create JavaBeans
JavaBeans in Oracle Forms Developer are: •
Predefined Java class files
•
Reusable Java class files
•
Managed with a PL/SQL interface (triggers and built-ins) No Java programming is required
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-26 Oracle iDS Forms: Build Internet Applications II
How to Create JavaBeans ......................................................................................................................................................
How to Create JavaBeans With Web-deployed form applications, you can reuse existing JavaBeans supplied by Oracle or by a third-party developer. Java programming skills are not required for implementing JavaBeans. In Oracle Forms Developer, triggers and built-ins allow you to directly manipulate Java class files with PL/SQL. Creating Your Own JavaBeans In order to create your own JavaBeans, you should use a Java development tool such as Oracle JDeveloper or the Sun Java Developers Kit (JDK). You can find examples of writing JavaBeans in Forms online Help and in JDeveloper online Help. The JDK can be downloaded from http://www.javasoft.com/. Implementing a Development Environment for Testing JavaBeans You should ensure that your JavaBean class files are placed in the Oracle_Home/forms60/java directory or subdirectory. At run time certain environment variables are used by Forms Server in order to obtain Java classes. You should include: The java ‘bin’ directory within the PATH environment variable setting for your User: Oracle_Home/jdk/bin The ‘java’ directory within the CLASSPATH environment variable setting for your User: Oracle_Home/forms60/java
ly n O e s U I A O &
Technical Note You should only deploy light-weight JavaBeans with your Web-deployed forms applications; the light-weight JavaBean user interface (UI) components are rendered completely and can be used independently of any operating system. In contrast, heavy-weight JavaBeans rely on UI objects provided by the client operating system. Oracle Forms Developer uses lightweight JavaBeans to provide a common look and feel across different operating systems.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-27
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
The Java Importer
The Java Importer enables you to:
•
Access components written in Java and invoke them from Forms applications
•
Build middle-tier integration layers between applications and technologies – XML, EJB, BC4J – Data sources other than Oracle – Nonstandard devices
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-28 Oracle iDS Forms: Build Internet Applications II
The Java Importer ......................................................................................................................................................
The Java Importer While PL/SQL provides a powerful and productive development environment, it is sometimes necessary to integrate Forms applications with other external application services and providers. Usually, these external applications are not written in PL/SQL so some form of integration capability is required. Traditionally, the interfaces and libraries exposed by external applications have been based on the C programming language, and the ORA_FFI built-in package has proved more than sufficient. With the increasing momentum of the Java movement within the Information Technology industry, many applications are now providing integration points in Java. Similarly, the number of standard Java libraries for enterprise applications is continually growing. Oracle Forms 6i Release 2 introduced a new feature, the Java Importer, to facilitate the invocation of business logic written in Java from a Forms application. With the Java Importer, you can automatically generate PL/SQL packages and procedures that allow you to create and use Java objects directly in your Forms applications. Components of the Java Importer The Java Importer enables a Forms application to call out to Java to make use of code contained within compiled Java classes. The components of the Java Importer make it possible to create PL/SQL packages for specified Java classes within a Forms application, and to instantiate, use, and destroy the Java object instances when the Forms application is run. These components are: • Java Importer Tool, which allows you to select and specify which Java classes you want to access in your application. • Java Importer Generator, which creates PL/SQL packages that provide access to the specified Java class. • ORA_JAVA package, which provides a set of functions to assist you in working with the selected Java packages. • Oracle Forms Services Java Native Interface (JNI) Bridge, which handles low level interaction with the Java classes at runtime.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-29
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Invoking the Java Importer
Copyright Oracle Corporation, 2001. All rights reserved.
®
Using the Java Importer
1 2
ly n O e s U I A O & 3
4
5
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 12-30 Oracle iDS Forms: Build Internet Applications II
The Java Importer Tool ......................................................................................................................................................
The Java Importer Tool You can invoke the Java Importer Tool from the Program Menu in Form Builder. The dialog box that displays enables you to select or specify the Java classes you wish to make use of in your Forms application. Once you select the required Java classes, the Java Importer Tool calls the PL/SQL Generator to create a PL/SQL package for each class selected. You can invoke the tool multiple times during a development session as new Java class access requirements are discovered; it is not necessary to identify all the classes needed at one time. The Java Importer Tools consists of the following sections: 1 Class Browser: The class browser lists all of the Java classes found on the CLASSPATH, displaying the classes in a hierarchical tree format. When a class is selected, the fully qualified name of the class is added to the Import Classes List field. 2 Import Classes List: This field displays a list of fully qualified classes that will be made accessible to the Forms application when you press the Import button. In addition to populating this list by selecting the class in the Class Browser, you can directly enter the fully qualified class names of the Java classes you wish to access. 3 Message Display: Output from the PL/SQL Generator is directed to the Message display. Progress of the PL/SQL generation phase is displayed and upon completion, so are the names of the packages generated for each Java class selected. 4 Import Button: The Import button starts the PL/SQL generation process for each Java class specified in the Import Classes list. 5 Options Button: The Options button displays a dialog box that you use to set the PL/SQL generation options. These options are described in the documentation that accompanies the Java Importer.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-31
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Summary
In this lesson, you should have learned how to:
• •
Define Oracle ‘LookandFeel’ and Java fonts Incorporate Java in Web-deployed forms by: – Inserting Pluggable Java Components – Creating Bean Areas
•
Use Oracle 8i features by exploiting Java stored procedures, EJB and BC4J
•
Use the Java Importer to build PL/SQL interfaces to Java classes
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-32 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary Customize the User Interface • Specifying Oracle look and feel • Specifying default Java fonts Create JavaBean Areas • Inserting Pluggable Java Components • Creating Bean Areas • Communicating with JavaBeans Exploit Oracle8i Features with Web-Deployed Forms • Java stored procedures • Enterprise JavaBeans • Business Components for Java Use the Java Importer Tool • Utilize Java code contained in standard, compiled Java class libraries from your Forms applications • Automate the generation of PL/SQL procedures and functions to interface with a specified Java class
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-33
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Practice 12 Overview
This practice covers the following topics:
•
Providing an alternative UI for text items by including PJC for – Changing the mouse cursor style – Displaying secure data as asterisks
•
Add a Bean Area to allow users to specify hyperlinks of their choice
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 12-34 Oracle iDS Forms: Build Internet Applications II
Practice Overview: Lesson 12 ......................................................................................................................................................
Practice Overview: Lesson 12 This practice guides you through defining Pluggable Java Components, and hosting Java Beans with a Bean Area. Practice Contents • Provide an alternative UI for text items by including PJC - Change the mouse cursor style - Display secure data as ‘asterisks’. • Add a Java Bean to allow users to specify hyperlinks of their choice.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 12-35
Lesson 12: Using Java with Web-Deployed Forms ......................................................................................................................................................
Practice 12 1 Provide an alternative user interface for your Web-deployed forms by
including Pluggable Java Components (PJCs). a Open the JAVAEMP.fmb form module. Save as JAVAXX.fmb, where XX is your student course code. b Change the :EMP.SAL item so that values are displayed as asterisks at run time. Use the PJC ‘PasswordFieldPJC.class’. This class file is held in the folder: FORMS60_JAVADIR /oracle/forms/demos c Change the :EMP.ENAME item so that the mouse cursor changes to ‘hand’ style whenever the cursor is moved over the item. Use the PJC ‘ModCursor.class’. This class file is held in the folder: FORMS60_JAVADIR /oracle/forms/demos d Use the built-in SET_CUSTOM_PROPERTY in an appropriate trigger, to specify the cursor style: SET_CUSTOM_PROPERTY(’Emp.Ename’,1, ’DOCURSOR’,’HAND’); e Save and compile your form. Deploy your form to the Web to test. 2 Create a JavaBean area in the Layout Editor to enable users to specify
hyperlinks of their choice. a Create a new data block called ‘LINK’ in your JAVAEMP form. b Create a Bean Area called ‘HYPERLINK1’ in the new data block and specify the class file ‘HyperLinkWrapper.class’. c Create an appropriate trigger to identify the user-supplied URL from the JavaBean, and to download his or her Web page. You can import the code from the pr12_1.txt file. d Create two text items, one called ‘URL’ to accept a URL and one called ‘LABEL’ to accept a URL label. These will enable the user to select a different URL. Set the maximum length for the URL text item to 255. e Create a push button with an appropriate trigger that will accept values from the URL and LABEL text items, and pass them to the JavaBean. You can import the code from the pr12_2.txt file. f Save and compile your form. Deploy your form to the Web to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 12-36 Oracle iDS Forms: Build Internet Applications II
................................
Using Server Features in Form Builder
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Objectives
After completing this lesson, you should be able to do the following:
• • •
Use Oracle server functionalities in forms
• •
Handle Oracle server errors
Deal with server-side PL/SQL Recognize which PL/SQL8 features are supported in forms Perform DDL commands by using the FORMS_DDL built in subprograms
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview This lesson covers the use of Oracle database server features in Form Builder applications. You will learn about storing and calling PL/SQL code, handling Oracle database server errors, and issuing DDL commands from within forms. Objectives After completing this lesson, you should be able to do the following: • Use Oracle database server functionalities in forms • Deal with server-side PL/SQL • Recognize which PL/SQL8 features are supported in forms • Handle Oracle database server errors • Perform DDL commands by using the FORMS _DDL built-in subprograms
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-3
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Using Oracle Database Server Functionality in Forms Useful Oracle server features:
• • • •
Declarative constraints Stored program units Database triggers DDL
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-4 Oracle iDS Forms: Build Internet Applications II
Using Oracle Database Server Functionality in Forms ......................................................................................................................................................
Using Oracle Database Server Functionality in Forms The Oracle8i database offers several powerful features to implement functionality in the Oracle database server. You can call that functionality in your forms to handle possible errors. Oracle Database Server Features Useful for Forms The 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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-5
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
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
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-6 Oracle iDS Forms: Build Internet Applications II
Dealing with PL/SQL Code ......................................................................................................................................................
Dealing with PL/SQL Code You can store and call your code in two places: the Oracle database server and the Oracle Form Builder application. Where to Store PL/SQL Code To maintain your code, you should not store the code in more locations than necessary. The places to store PL/SQL code, in order of preference, are as follows: 1 The Oracle Database Server 2 Attached PL/SQL libraries 3 The form itself Note: PL/SQL code cannot be stored in the Oracle database server if it contains bind variables or calls to Forms built-ins. Where to Call PL/SQL Code PL/SQL code is eventually called from triggers. Also, for easier maintenance, the places to call PL/SQL code, in order of preference, are as follows: 1 Database triggers 2 Forms triggers Note: Database triggers fire only at commit time. If you want to give immediate feedback to the user, you must use forms triggers.
ly n O e s U I A O &
Application Partitioning For enhanced performance, you may want to call or store PL/SQL code on either the Oracle database server or the Oracle Form Builder application. In Form Builder you can drag and drop PL/SQL program units between the Oracle database server and the application. This is called application partitioning. With Web-deployed forms it is also possible to place code on the client by using JavaBeans. In this way, you can optimally divide application code over the available resources.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-7
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
PL/SQL8 Support in Oracle Forms •
Client-side program units cannot support Oracle8i object-related functionality.
•
Stored program units can use PL/SQL8 features.
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-8 Oracle iDS Forms: Build Internet Applications II
PL/SQL8 Support ......................................................................................................................................................
PL/SQL8 Support Oracle Forms Developer uses PL/SQL8 in the Oracle database server and the Oracle Form Builder application. However, Form Builder module program units currently cannot support Oracle8i object-related functionality. The Stored Program Unit editor has been extended in Oracle Forms Developer to allow editing of the type body (methods) and type specification (attributes) of Oracle8i user-defined data types. Object Iron Packages Object Iron is a set of database packages that helps you to access tables containing instances of Oracle8i object types. The packages generate a table-specific package, which you use to access data from the specified table. After generation, the table-specific package appears under the Stored Program Units node. It contains code that allows you to select, insert, update, delete, and lock records in the specified table.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-9
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
PL/SQL8 Scalar Types • • • • • •
NCHAR and NVARCHAR2 SIGNTYPE FLOAT NATURALN POSITIVEN PLS_INTEGER
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-10 Oracle iDS Forms: Build Internet Applications II
PL/SQL8 Scalar Data Types ......................................................................................................................................................
PL/SQL8 Scalar Data Types •
NCHAR stores fixed-length (blank-padded if necessary) NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as US7ASCII or a variable-width encoding such as JA16SJIS. • NVARCHAR2 stores variable-length NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as WE8EBCDIC37C or a variable-width encoding such as JA16DBCS. • SIGNTYPE lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic. • FLOAT is a subtype of NUMBER. However, you cannot specify a scale for FLOAT variables. You can specify only a binary precision. • NATURALN is like subtype NATURAL but prevents the assignment of NULL. • POSITIVEN is like subtype POSITIVE but prevents the assignment of NULL. • PLS_INTEGER stores signed integers. Its magnitude range is -2147483647 ... 2147483647. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. Besides the database character set, which is used for identifiers and source code, PL/SQL8 now supports a second character set called the national character set, which is used for NLS data. The PL/SQL data types NCHAR and NVARCHAR2 allow you to store character strings formed from the national character set.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-11
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Unsupported Client-Side PL/SQL8 Features • • • • • •
Untrusted external procedures Object types Collection types LOB types Methods Objects as stored procedure parameters
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-12 Oracle iDS Forms: Build Internet Applications II
Unsupported Oracle Forms Developer PL/SQL8 Features ......................................................................................................................................................
Unsupported Oracle Forms Developer PL/SQL8 Features The following features are supported in database server PL/SQL8 but are not currently supported in Form Builder module PL/SQL8: • Object types • Collection types • LOB types • Methods • Objects as stored procedure parameters Because these features are supported in database server PL/SQL8, you can use them by writing stored (server-side) subprograms, and calling the subprograms from Oracle Forms Developer. However, the last restriction requires that you decompose any object data types before returning them to the client side.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-13
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Causes of Oracle Server Errors Form
Base table block Implicit DML
Trigger/PU
Oracle Server
Declarative constraint
Database trigger
Explicit DML Stored PU call
Stored program unit
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-14 Oracle iDS Forms: Build Internet Applications II
Handling Errors Raised by the Oracle Database Server ......................................................................................................................................................
Handling Errors Raised by the Oracle Database Server Oracle server errors can have different causes; for example, a declarative constraint or a stored program unit. You should know how to handle errors that may occur in different situations. Causes of Oracle Server Errors Cause Declarative constraint Database trigger Stored program unit
Error Message Causes predefined error message Error message specified in RAISE_APPLICATION_ERROR Error message specified in RAISE_APPLICATION_ERROR
Types of DML Statements Declarative-constraint violations and firing of database triggers are in turn caused by DML statements. For error-handling purposes, you must distinguish between the following two types of DML statements: Type Implicit DML
Explicit DML
Description DML statements that are associated with base table blocks. Implicit DML is also called base table DML. By default, Forms constructs and issues these DML statements. DML statements that a developer explicitly codes in triggers or program units.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-15
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Trapping Server Errors Form
Base table block On-Error: DBMS_ERROR_CODE DBMS_ERROR_TEXT
Explicit DML/PU call When Others: SQLCODE SQLERRM
Oracle Server
Constraint Predefined message
DB trigger RAISE_ APPLICATION_ ERROR
Stored PU RAISE_ APPLICATION_ ERROR
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-16 Oracle iDS Forms: Build Internet Applications II
Handling Errors Raised by the Oracle Database Server ......................................................................................................................................................
How to Trap Different Types of Oracle Database Server Errors Type Implicit DML Explicit DML
Stored program unit
Error Handling Use the Forms built-ins DBMS_ERROR_CODE and DBMS_ERROR_TEXT in an On-Error trigger. Use the PL/SQL functions SQLCODE and SQLERRM in a WHEN OTHERS exception handler of the trigger or program unit that issued the DML statements. Use the PL/SQL functions SQLCODE and SQLERRM in a WHEN OTHERS exception handler of the trigger or program unit that called the stored program unit.
Note: Declarative-constraint violations and database triggers may be caused by both implicit DML and explicit DML. Stored program units are always called explicitly from a trigger or program unit. Technical Note The values of DBMS_ERROR_CODE and DBMS_ERROR_TEXT are the same as what a user would see after selecting [Display Error]; the values are not automatically reset following successful execution. Appendix F “Handling Server-Side Errors” covers server-side error handling in more detail. FRM-Error Messages Caused by Implicit DML Errors If an implicit DML statement causes an Oracle server error, Forms displays one 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 and then use DBMS_ERROR_CODE and DBMS_ERROR_TEXT to determine the ORA-error code and message.
l a n r e t n I e l c a r O
ly n O e s U I A O &
FRM-Error Messages with Web-Deployed Forms A generic FRM-999999 is the most common error displayed with Web forms. You can obtain meaningful information from the JInitiator Control Panel, or from the DOS window if you run the form from a Java Appletviewer. For further information see Oracle 9iAS Forms Services: Deploy Internet Applications.
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-17
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Performing DDL with FORMS_DDL • •
Syntax: FORMS_DDL (statement);
•
Characteristics:
Parameter (<=32K):PL/SQL block, DML or DDL statement – Unrestricted; valid in Enter Query mode – Statement must not contain bindvariable references
Copyright Oracle Corporation, 2001. All rights reserved.
®
Performing DDL with FORMS_DDL •
Characteristics: – Statement cannot return results directly – Use FORM_SUCCESS to check success of statement
•
Hints: – Create SQL Query record group instead of FORMS_DDL
ly n O e s U I A O &
– Call stored program unit instead of FORMS_DDL
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 13-18 Oracle iDS Forms: Build Internet Applications II
Performing DDL with FORMS_DDL ......................................................................................................................................................
Performing DDL with FORMS_DDL Forms is intended primarily to perform DML operations. However, in some cases you may find it useful to issue DDL statements from a form. You can do this using the FORMS_DDL built-in function. Syntax FORMS_DDL(statement);
Description FORMS_DDL issues dynamic SQL statements at run time, including server-side PL/SQL and DDL. Parameter The 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 a semicolon. Characteristics FORMS_DDL is an unrestricted procedure that is also valid in Enter Query mode. • 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 Forms directly. • Use the FORM_SUCCESS built-in to check whether the statement issued using FORMS_DDL executed correctly. Note: Consider using a query record group with a SELECT statement or calling a stored program unit, instead of executing FORMS_DDL.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-19
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Performing DDL with FORMS_DDL •
Example: Create temporary table FORMS_DDL (‘CREATE table temp (n_col number)‘); IF not FORM_SUCCESS THEN MESSAGE (‘Table creation failed.‘); RAISE form_trigger_failure; END IF;
•
Example: Execute procedure with given name FORMS_DDL FORMS_DDL (‘BEGIN (‘BEGIN ‘‘ || || p_proc_name p_proc_name ||‘; ||‘; END; END; ‘); ‘); IF IF not not FORM_SUCCESS FORM_SUCCESS THEN THEN handle_server_error(DBMS_ERROR_CODE, handle_server_error(DBMS_ERROR_CODE, DBMS_ERROR_TEXT); DBMS_ERROR_TEXT); END END IF; IF;
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-20 Oracle iDS Forms: Build Internet Applications II
Using FORMS_DDL ......................................................................................................................................................
Using FORMS_DDL The following examples show how you can use the FORMS_DDL built-in function. Example Create a temporary table at the start of a post. Pre-commit trigger at form level: BEGIN FORMS_DDL(’CREATE table temp(n_col number)’); IF not FORM_SUCCESS THEN MESSAGE (’Table creation failed.’); RAISE form_trigger_failure; END IF; END;
Example Execute a procedure with a given name. This is useful if you want to determine dynamically which procedure should be executed in a certain situation. PROCEDURE exec_proc (p_proc_name IN VARCHAR2) IS BEGIN FORMS_DDL(’BEGIN ’|| p_proc_name ||’; END;’); IF not FORMS_SUCCESS THEN handle_server_error(DBMS_ERROR_CODE,DBMS_ERROR_TEXT); END IF; END;
ly n O e s U I A O &
Note: If the FORMS_DDL built-in fails, Forms sets the DBMS_ERROR_CODE and DBMS_ERROR_TEXT built-ins. Therefore, you can handle Oracle server errors using the HANDLE_SERVER_ERROR procedure discussed earlier. Always test the SYSTEM.FORM_STATUS before calling the FORMS_DDL built-in.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-21
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Summary
In this lesson, you should have learned how to:
•
Identify Oracle server features useful for Form Builder
• •
Identify PL/SQL8 supported features Write PL/SQL code to trap errors raised by the Oracle server – Implicit DML – Explicit DML or stored program units
•
Perform DDL with FORMS_DDL
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-22 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary Oracle Database 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 Database Server • Causes: Declarative constraints, database triggers, stored program units • Trap implicit-DML errors by using DBMS_ERROR_CODE and DBMS_ERROR_TEXT in an On-Error trigger • Trap explicit DML errors and stored program unit errors by using SQLCODE and SQLERRM in a WHEN OTHERS exception handler
ly n O e s U I A O &
Performing DDL with FORMS_DDL Parameter is a string (less than or equal to 32 K) representing a PL/SQL block or DML or DDL statement.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-23
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Practice 13 Overview
This practice covers the following topics:
• •
Handling errors caused by stored program units Handling errors caused by declarative constraint violation
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 13-24 Oracle iDS Forms: Build Internet Applications II
Practice Overview: Lesson 13 ......................................................................................................................................................
Practice Overview: Lesson 13 This practice guides you through handling Oracle server errors. Practice Contents • Handle errors caused by stored program units. • Handle errors caused by declarative-constraint violations.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 13-25
Lesson 13: Using Server Features in Form Builder ......................................................................................................................................................
Practice 13 1 Create a stored procedure, and process error messages that it may
generate in the CUSTSERVER form. a Open the CUSTSERVER form and save as CUSTSERVXX where XX is your student course number. Copy the code in the SELECT_CODE program unit and create a stored procedure called SELECT_CUSTOMER. This procedure takes a user supplied value from :CONTROL.NAME to check whether a customer name already exists. Existing names will generate an error. b Modify the When-Button-Pressed trigger for the CONTROL block CHECK_CUSTOMER button, to trap any errors generated from the stored procedure SELECT_CUSTOMER. If a customer already exists, display a warning message to the user. c Save and compile your form module; test. 2 Whenever the user attempts to save a new customer record with an
existing primary key, display an appropriate warning message. a Run your CUSTSERVER form. Attempt to insert a record with an existing primary key. Note the generated error message. b Define an appropriate trigger to trap the generated error message, and display the database constraint violation message and error code. Ensure that any other generated messages will continue to be displayed. c Display all messages in an ALERT called ‘errors’. d Save and compile your form module; test your form.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 13-26 Oracle iDS Forms: Build Internet Applications II
................................
Applying Timers
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 14: Applying Timers ......................................................................................................................................................
Objectives
After completing this lesson, you should be able to do the following:
• • • • •
Describe timers Create a timer Modify a timer Delete a timer Handle timer expiration
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview This lesson covers time-initiated processing; that is, processing that occurs after a certain amount of time has elapsed. The mechanism you use to do this is called a timer and it is created, modified, and deleted at run time. Objectives After completing this lesson, you should be able to do the following: • Describe timers • Create a timer • Modify a timer • Delete a timer • Handle timer expiration
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-3
Lesson 14: Applying Timers ......................................................................................................................................................
Timers • •
What is a timer? Built-ins for timers: – FIND_TIMER – CREATE_TIMER – SET_TIMER – DELETE_TIMER – GET_APPLICATION_PROPERTY (TIMER_NAME)
Copyright Oracle Corporation, 2001. All rights reserved.
®
Timers • •
When-Timer-Expired trigger Using timers: – Poll database
ly n O e s U I A O &
– Periodically query, commit, or rollback
– Show “About…” information at startup
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note Timers are not suitable means of shutting down an application. It is the job of the operating system to recognize idle processes and shut them down.
...................................................................................................................................................... 14-4 Oracle iDS Forms: Build Internet Applications II
Timers ......................................................................................................................................................
Timers Forms typically processes events that are user initiated. You can use timers when you want Forms to initiate actions after a specific time period time. What Is a Timer? A timer is a programmatic construct similar to an “internal alarm clock.” You can create, modify, or delete timers with built-ins. When you create or modify a timer, you can specify the time period for the timer. Using a trigger, you can specify actions to be performed at the end of that period. Built-in Functions for Timers Built-in FIND_TIMER CREATE_TIMER
SET_TIMER DELETE_TIMER GET_APPLICATION_PROPERTY
Description Returns the internal timer ID (of data type TIMER) of a timer with the given name Creates a timer with a given name, a defined time interval, and whether the timer should repeat on expiration. Changes the settings for the given timer (You can modify the time interval and the repeat behavior.) Deletes the given timer The TIMER_NAME property returns the name of the most recently expired timer.
When-Timer-Expired Trigger This trigger fires when the specified time interval of the timer has elapsed.
ly n O e s U I A O &
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 automatic commits or rollbacks after a specified idle time
l a n r e t n I e l c a r O
Web Design Tip You should restrict the frequency of timers in Web-deployed forms in order to reduce a potential increase in network traffic. Replacing timers with JavaBeans, which provide similar functionality, will benefit users as code would be executed on the Web client without invoking network traffic.
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-5
Lesson 14: Applying Timers ......................................................................................................................................................
Handling Timer Expiration Timer A created
Timer B created
Start A
Timer A expired (R)
When-Timer-Expired (A) Timer B expired
Query issued B B,A
Timer A expired (R)
Query complete When-Timer-Expired (B) When-Timer-Expired (A)
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-6 Oracle iDS Forms: Build Internet Applications II
Handling Timer Expiration ......................................................................................................................................................
Handling Timer Expiration When a timer expires, the When-Timer-Expired trigger fires and executes the desired actions. If you define more than one timer, you need to know which timer expired and how expired timers are handled. Timer Queue When a timer expires, it is put in a queue of expired timers. Forms services this timer queue on a first-in-first-out basis, but only while it is waiting for user 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 in the timer queue. When-Timer-Expired Trigger When using the When-Timer-Expired trigger remember that it: • Fires once for each timer that expires, but only after Form Builder has completed any current processing of triggers and built-in functions • Fires after the specified time interval, rather than exactly on the moment of expiration • Must be defined at the form level • Should include the GET_APPLICATION_PROPERTY built-in function for you to find out which timer has expired Example Handle the expiration of two timers named HOUR_ALARM and ABOUT_STARTUP.
ly n O e s U I A O &
DECLARE v_timer_name VARCHAR2(30); BEGIN v_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;
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-7
Lesson 14: Applying Timers ......................................................................................................................................................
Creating a Timer •
Syntax: CREATE_TIMER CREATE_TIMER (timer_name, (timer_name, milliseconds, milliseconds, iterate) iterate)
•
Example: v_timer_id v_timer_id := := CREATE_TIMER CREATE_TIMER ((′hour_alarm’, ′hour_alarm’, cst_hour); cst_hour);
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-8 Oracle iDS Forms: Build Internet Applications II
Creating a Timer ......................................................................................................................................................
Creating a Timer You can create a timer by using the CREATE_TIMER built-in function, which returns type TIMER. Syntax CREATE_TIMER(timer_name, milliseconds, iterate)
Parameter timer_name milliseconds iterate
Description The timer name The duration of the timer in milliseconds (Value must be between 1 and 2147483648, approximately 25 days.) Specifies whether the timer should repeat upon expiration (Valid values are REPEAT—the default—and NO_REPEAT.)
Example At form startup, create a timer named HOUR_ALARM that expires every hour. DECLARE cst_hour constant NUMBER(7) := 3600000; --3600000 is one hour in milliseconds v_timer_id TIMER; BEGIN v_timer_id := CREATE_TIMER(’hour_alarm’, cst_hour); END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-9
Lesson 14: Applying Timers ......................................................................................................................................................
Modifying a Timer •
Syntax:
SET_TIMER SET_TIMER SET_TIMER SET_TIMER
•
(timer_name, (timer_name, milliseconds, milliseconds, iterate) iterate) (timer_id, milliseconds, (timer_id, milliseconds, iterate iterate))
Example:
SET_TIMER SET_TIMER ((′hour_alarm’, ′hour_alarm’, no_change, no_change, no_repeat no_repeat); );
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-10 Oracle iDS Forms: Build Internet Applications II
Modifying a Timer ......................................................................................................................................................
Modifying a Timer You can modify a timer by using the SET_TIMER built-in procedure. Syntax 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 1 and 2147483648—approximately 25 days—or must be NO_CHANGE.)
iterate
Specifies whether the timer should repeat upon expiration (Valid values are REPEAT (default), NO_REPEAT, and NO_CHANGE.)
Example Set the repeat behavior of a timer named HOUR_ALARM without changing the time interval. The trigger name depends on the situation. BEGIN SET_TIMER(’hour_alarm’, no_change, no_repeat); END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-11
Lesson 14: Applying Timers ......................................................................................................................................................
Deleting a Timer •
Syntax: DELETE_TIMER DELETE_TIMER DELETE_TIMER DELETE_TIMER
•
(timer_name) (timer_name) (timer_id) (timer_id)
Example: ... ... IF IF NOT NOT ID_NULL ID_NULL (FIND_TIMER (FIND_TIMER ((′hour_alarm ′hour_alarm′)) ′)) THEN THEN DELETE_TIMER (’hour_alarm’); DELETE_TIMER (’hour_alarm’); END END IF; IF; ... ...
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-12 Oracle iDS Forms: Build Internet Applications II
Deleting a Timer ......................................................................................................................................................
Deleting a Timer You can delete a timer by using the DELETE_TIMER built-in procedure. Syntax DELETE_TIMER(timer_name) DELETE_TIMER(timer_id)
Parameter
Description
timer_name
The timer name
timer_id
The internal timer ID
Note: Forms generates an error if you attempt to delete a nonexistent timer. Example Delete a timer named HOUR_ALARM after first checking that it exists. The trigger name depends on the situation. BEGIN IF NOT ID_NULL(FIND_TIMER(’hour_alarm’)) THEN DELETE_TIMER(’hour_alarm’); END IF; END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-13
Lesson 14: Applying Timers ......................................................................................................................................................
Summary
In this lesson, you should have learned how to:
•
Create and modify a timer with built-in functions – FIND_TIMER – CREATE_TIMER – SET_TIMER – DELETE_TIMER – GET_APPLICATION_PROPERTY (TIMER_NAME)
Copyright Oracle Corporation, 2001. All rights reserved.
®
Summary •
Handle timer expiration: – Timer queue – When-Timer-Expired trigger
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 14-14 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary A 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 of idle 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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-15
Lesson 14: Applying Timers ......................................................................................................................................................
Practice 14 Overview
This practice covers the following topic:
•
Periodically checking to see if there are locked records and asking the user to commit or rollback
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 14-16 Oracle iDS Forms: Build Internet Applications II
Practice Overview: Lesson 14 ......................................................................................................................................................
Practice Overview: Lesson 14 This practice guides you through the implementation of time-initiated processing. You will use the appropriate timer built-ins and triggers to create a timer and handle timer expiration. Practice Contents If table rows are locked, display an alert that asks the user to commit or roll back changes after a set period of time.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 14-17
Lesson 14: Applying Timers ......................................................................................................................................................
Practice 14 1 Automatically ask the users if they want to commit after a set period of
time. a Open your CUSTOMERS form module. b At the startup of the form, create a global variable called GLOBAL.LOCKS_PENDING. This will indicate at all times whether rows of the S_CUSTOMER table are locked. The default value is NULL. Append your new code to existing code. c Create the On-Lock trigger to implement the default lock processing, update the global variable, and create the timer. You can import the code from the pr14_1.txt file. d Display an alert if locks are still pending after a certain period of time elapses. This alert should ask the user to commit or roll back the changes. The alert, ASK_SAVE, already exists in your form. Define an appropriate trigger. You can import the code from the pr14_2.txt file. e Create a Post-Database-Commit trigger to restore the NULL value to the global variable. You can import the code from the pr14_3.txt file. f Create the On-Rollback trigger to implement the default rollback processing and restore the NULL value to the global variable. You can import the code from the pr14_4.txt file. g Save and compile your form to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 14-18 Oracle iDS Forms: Build Internet Applications II
................................
Including Charts and Reports
l a n r e t n I e l c a r O
ly n O e s U I A O &
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Objectives
After completing this lesson, you should be able to do the following:
• • •
Include charts in an application Include reports in an application Integrate other documents with Web-deployed forms
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview In this lesson, you will learn how to include charts and reports in your application. You will also learn how to invoke additional document types with Web-deployed forms. Objectives After completing this lesson, you should be able to do the following: • Include charts in an application • Include reports in an application • Integrate other documents with Web-deployed forms
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-3
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Including Charts by Using the Chart Wizard To invoke the Chart Wizard:
•
Use the Chart tool to drag a chart area onto the canvas.
•
Select Tools—>Chart Wizard
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-4 Oracle iDS Forms: Build Internet Applications II
Including Charts by Using the Chart Wizard ......................................................................................................................................................
Including Charts by Using the Chart Wizard You are able to create charts and reports within Form Builder without launching separate chart or reports engines. The Chart Wizard leads you step-by-step through the process of creating a chart from within a form or a report. Once the chart is created, you can modify or enhance the chart by using Graphics Builder. Invoking the Chart Wizard To invoke the Chart Wizard: • From the Layout Editor, use the Chart tool to drag a chart area onto the canvas. • The New Chart Wizard dialog box appears. Click on the OK button to accept the default option, “Use the Chart Wizard.” Incorporating a Chart The 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 chart columns. 3 Specify the data to appear on the Category (X) axis of the chart. 4 Specify the data to appear on the Value (Y) axis of the chart. Follow the prompts on each page of the wizard to specify the settings for the chart you want to create.
ly n O e s U I A O &
Re-entering the Chart Wizard A powerful quality of the Chart Wizard is its ability to operate in reentrant mode. This means you can use the Chart Wizard to modify an existing chart created with the wizard. To invoke the Chart Wizard in reentrant mode, select the chart object in the Object Navigator, and then select Tools—>Chart Wizard from the menu.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-5
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Including Charts by Using the Chart Wizard
Chart type
Copyright Oracle Corporation, 2001. All rights reserved.
®
Including Charts by Using the Chart Wizard
Arrow indicates that data from the block is represented in the chart.
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 15-6 Oracle iDS Forms: Build Internet Applications II
Including Charts by Using the Chart Wizard ......................................................................................................................................................
Chart Type Select the style of chart that you require. The picture on the left of the wizard displays the relevant chart style. The chart subtype options are context-sensitive; the number and style of options change depending on the chart type that you choose. Select the chart subtype that you require, and then click Next to continue to the next wizard page. 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 to the whole.
Line
Data is plotted as points along a line.
Mixed
Data is charted using multiple chart types, such as bar and line.
Data Source The next step in the Chart Wizard is to select the block that contains the data you want to represent in the chart. The data block you select determines the data that is available for assignment to the Category and Value axes of your chart.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-7
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Including Charts by Using the Chart Wizard
Arrow points to the Category axis.
Copyright Oracle Corporation, 2001. All rights reserved.
®
Including Charts by Using the Chart Wizard
Arrow points to the Value axis.
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 15-8 Oracle iDS Forms: Build Internet Applications II
Including Charts by Using the Chart Wizard ......................................................................................................................................................
Category Axis To 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 the Category (X) axis of the chart. 2 Transfer the selected field from the Available Fields list to the Category Axis list. Chart Type
Description
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.
Value Axis To 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 the Value (Y) axis of the chart. 2 Transfer the selected field from the Available Fields list to the Value Axis list. Chart Type
Description
Column, Line, Mixed chart
Values are represented by the height of the column against the y-axis.
Bar chart
Values are represented by the length of the column against the x-axis.
Pie chart
Values are represented by the size of each segment.
ly n O e s U I A O &
You must transfer at least one field from the Available Fields list to the Category Value list before navigating to the next page.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-9
Lesson 15: Including Charts and Reports ......................................................................................................................................................
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
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-10 Oracle iDS Forms: Build Internet Applications II
Chart Item Properties ......................................................................................................................................................
Chart Item Properties The 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
Specifies the communication mode to be used when calling Graphics Builder: 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 a query
Update on Commit
Specifies that the chart is refreshed each time you perform a commit
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-11
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Reporting Within Form Builder • • •
Create report objects in Object Navigator Run a report against a local or remote server Base a report on a data block
Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note Passing data is applicable only if running against a local server.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 15-12 Oracle iDS Forms: Build Internet Applications II
Reporting Within Form Builder ......................................................................................................................................................
Reporting Within Form Builder Oracle Forms Developer provides the ability to run a report against a local or a remote server with considerable ease. A report object is available that allows you to specify all the commands necessary for running a report. Using the Report Wizard Using the Report Wizard within Form Builder, you can: • Create a new report module based on one data block in your form • Create a new report module • Use an existing report module Note: You can modify or enhance the report module by using Oracle Reports Developer. Creating a Report Object Based on a Data Block The ability to base a report on a data block represents an efficient way of passing data from a form module to a report. Select the Report node in the Object 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 want 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 of possible calculations. (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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-13
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Report Object Properties
Oracle Forms Developer integration properties
Filename Filename Execution Execution Mode Mode Communication Communication Mode Mode Data Data Source Source Data Data Block Block Query Query Name Name
Copyright Oracle Corporation, 2001. All rights reserved.
®
Report Object Properties Report Destination Type Report Destination Name Report Destination Format Report Server Other Report Parameters
ly n O e s U I A O &
Reports properties
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 15-14 Oracle iDS Forms: Build Internet Applications II
Report Object Properties ......................................................................................................................................................
Report Object Properties Oracle Forms Developer Integration Properties Property Name
Description
Filename
Specifies the name of the report module to be executed by Report Builder
Execution Mode
Specifies the execution mode to be used when running Report Builder (Valid numeric constants for this property are BATCH and RUNTIME.)
Communication Mode
SYNCHRONOUS specifies that control returns to Form Builder only after Report Builder has been exited. The end user cannot work in the form while Report Builder is running. ASYNCHRONOUS specifies that control returns to the calling application immediately, even if Report Builder has not completed 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 (When the report is based on data from a Form Builder block, the query name property value is the block name.)
Reports Properties
ly n O e s U I A O &
Property Name
Description
Report Destination Type
Specifies the type of device that will receive the report output
Report Destination Name
Specifies the name of the file, printer, or Oracle Office username (or distribution list) to which the report output will be sent
Report Destination Format
Specifies the format of your report (values are PDF, HTML, HTMLCSS, XML) or the printer driver to be used when DESTYPE is File.)
Report Server
Name of the remote server on which the report is run
Other Report Parameters
Declares additional parameters
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-15
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Working with Reports
Built-ins for interacting with reports:
• • • • • • • •
RUN_REPORT_OBJECT FIND_REPORT_OBJECT GET_REPORT_OBJECT_PROPERTY SET_REPORT_OBJECT_PROPERTY CANCEL_REPORT_OBJECT COPY_REPORT_OUTPUT REPORT_OBJECT_STATUS RUN_PRODUCT
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-16 Oracle iDS Forms: Build Internet Applications II
Working with Reports ......................................................................................................................................................
Working with Reports When running a form you can control reports in the Report Server dynamically by including the following 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
GET_REPORT_OBJECT_PROPERTY
Dynamically gets report object properties at run time
SET_REPORT_OBJECT_PROPERTY
Dynamically sets report object properties at run time
CANCEL_REPORT_OBJECT
Cancels a report in the server queue
COPY_REPORT_OUTPUT
Copies the report output back to the client
REPORT_OBJECT_STATUS
Returns the current status of a report (The status codes can be FINISHED, RUNNING, CANCELED, OPENING_REPORT, ENQUEUED, INVALID_JOB, TERMINATED_WITH_ERROR, or CRASHED.)
RUN_PRODUCT
Invokes one of the supported Oracle tools products and specifies the name of the module or modules to be run
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-17
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Running a Report Against a Local Server
DECLARE v_rep VARCHAR2(100); repid REPORT_OBJECT; BEGIN repid := FIND_REPORT_OBJECT(’deptrpt’); v_rep := RUN_REPORT_OBJECT(repid); end;
Copyright Oracle Corporation, 2001. All rights reserved.
®
Running a Report Against a Remote Server
. . . repid := FIND_REPORT_OBJECT(’barcode’); v_rep := RUN_REPORT_OBJECT(repid);
ly n O e s U I A O &
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; . . .
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 15-18 Oracle iDS Forms: Build Internet Applications II
Working with Reports ......................................................................................................................................................
Running a Report Against a Local Server This example allows the user to run a report against a local server. DECLARE v_rep VARCHAR2(100); repid REPORT_OBJECT; rep_status VARCHAR2(20); BEGIN repid := FIND_REPORT_OBJECT(’deptrpt’); v_rep := RUN_REPORT_OBJECT(repid); end;
Running a Report Against a Remote Server You can run a report on a remote server by naming the server in the Report Object property, Report Server. RUN_REPORT_OBJECT returns a string that uniquely identifies the report. In this Client Server example, this string can be used to get the report status and to copy the output. .... v_rep := RUN_REPORT_OBJECT(repid); IF REPORT_OBJECT_STATUS(v_rep) = ’FINISHED’ THEN COPY_REPORT_OBJECT_OUTPUT(v_rep,’c:\local.pdf’); HOST(’netscape c:\local.pdf’); ELSE MESSAGE(’Error when running report.’); END IF;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-19
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Running a Report from a Web-Deployed Form Built-in for downloading reports:
•
WEB.SHOW_DOCUMENT WEB.SHOW_DOCUMENT(‘url’, WEB.SHOW_DOCUMENT(‘url’, ‘target’); ‘target’);
Example my_url my_url := := ‘http://www.summit.com/repts/emps.pdf’; ‘http://www.summit.com/repts/emps.pdf’; WEB.SHOW_DOCUMENT(my_url, WEB.SHOW_DOCUMENT(my_url, ‘_SELF’); ‘_SELF’);
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-20 Oracle iDS Forms: Build Internet Applications II
Working with Reports ......................................................................................................................................................
Running a Report from a Web-Deployed Form When you call RUN_REPORT_OBJECT from a Web-deployed form, the report is created on the Web server. You can specify a directory location for report output, and a Web-deployable report format, with the following Report Object properties: Property
Value
Report Destination Format
File
Report Destination Name
‘pathname’ / ‘report filename’
Report Destination Format
HTML HTMLCSS PDF XML
Viewing Report Output from a Web-Deployed Form You can view report output with the built-in WEB.SHOW_DOCUMENT. This is supplied with the Oracle Forms Developer WEB built-in package, and you can only use it with Web-deployed forms. The built-in has two parameters: • URL of the document to be downloaded. The document may, for example, be a report, an image, a form that you wish to run on the Web, or for another Web-site. • Target window or frame for displaying the report: ‘_BLANK’ specifies that the document can be downloaded in a new HTML window. ‘_SELF’ specifies that the document is downloaded to the base HTML page window or frame. ‘_TOP’ specifies that the document is downloaded to the base HTML page, replacing any frames. ‘_PARENT’ specifies that the document is downloaded to the parent window of the base HTML page. In this example, WEB.SHOW_DOCUMENT is used to download the emps report which has been formatted in pdf:
l a n r e t n I e l c a r O
ly n O e s U I A O &
WEB.SHOW_DOCUMENT(’http://www.summit.com/repts/emps.pdf’, ‘_blank’);
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-21
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Summary
In this lesson, you should have learned how to:
• •
Invoke the Chart Wizard Exploit Report features in Form Builder – Create report objects – Run against local or remote server – Base reports on data blocks
•
Manage report objects with forms built-ins – GET_REPORT_OBJECT_PROPERTY – SET_REPORT_OBJECT_PROPERTY
Copyright Oracle Corporation, 2001. All rights reserved.
®
Summary •
Communicate with Report server – RUN_REPORT_OBJECT – FIND_REPORT_OBJECT – CANCEL_REPORT_OBJECT – REPORT_OBJECT _STATUS – COPY_REPORT_OUTPUT
•
Communicate with a Web server – WEB.SHOW_DOCUMENT
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... 15-22 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary In this lesson, you should have learned how to include charts and reports in your application. You should have learned how to invoke the Chart Wizard. Reporting Features in Form Builder • Create report objects • Base reports on data blocks • Run against local or remote server • Run reports from Web-deployed forms 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 • WEB.SHOW_DOCUMENT
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-23
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Practice 15 Overview
This practice covers the following topics:
•
Creating a chart to display the total orders for each customer
•
Creating a report based on the S_CUSTOMER block
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... 15-24 Oracle iDS Forms: Build Internet Applications II
Practice Overview: Lesson 15 ......................................................................................................................................................
Practice Overview: Lesson 15 This practice guides you in creating and displaying charts and reports within your forms application. Practice Contents • Create a chart to display the total orders for each customer. • Create a report based on the S_CUSTOMER block.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-25
Lesson 15: Including Charts and Reports ......................................................................................................................................................
Practice 15 1 Create a chart object by using the Chart Wizard. This chart should
display the total orders for each customer. a Open the ORDCHART form module. Save as ORDCHARTXX where XX is your student course number. This form module contains a S_ORD block. Only one item from this block is visible: TITLE. This item displays a title for your chart. b In the Layout Editor, use the Chart tool to drag a chart area onto the canvas. Select “Use the Chart Wizard” from the New Chart Object dialog box. Do not specify a title for the chart. c Select Column as chart type, Plain as chart subtype. d Specify the S_ORD block as the data block that contains the data you want to assign to chart columns. e Select CUSTOMER_NAME to appear on the Category (X) axis, and TOTAL to appear on the Value (Y) axis of the chart. f Click the Finish button. g Save and compile your form module. Deploy your form to the Web to test. To display the chart, click in the Title item on the Chart tab. 2 Create a report object based on the S_CUSTOMER block. This report
displays a customer list using a tabular layout. Display only the ID, NAME, COUNTRY, and REGION_ID fields. a Open the CUSTREPT form. Save as CUSTREPXX where XX is your student course number. Create a new report object. This will invoke Report Builder. Within the Report Wizard: Choose the report style Tabular. Select the ID, NAME, COUNTRY, and REGION_ID fields. Do not select fields to total. Change labels and widths for your fields. Select a template for your report. b Save your report and exit Report Builder. c Set the Execution Mode report object property to “runtime” and the Report Destination Type report object property to “screen”. Rename the report object to “my_report”. d Create a button named CUST_REP_BUTTON in the control block. Display this button on the CANVAS2 canvas.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 15-26 Oracle iDS Forms: Build Internet Applications II
Practice 15 ......................................................................................................................................................
Create a When-Button-Pressed trigger on the CUST_REP_BUTTON button to run the report. f Change the Query All Record property value to Yes for the S_CUSTOMER block. g Save and compile your form module. Deploy your form to the Web to test. Query a customer record before calling the report. e
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II 15-27
Lesson 15: Including Charts and Reports ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... 15-28 Oracle iDS Forms: Build Internet Applications II
................................
Practice Solutions
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 1 Solutions 1 Create a new menu module. a
Using the following screenshot as a guideline, create a new menu module called M_SUMMITXX where XX is your student course number.
-
Select the Menus node in the Object Navigator and click the Create tool. Invoke the Menu Editor. This automatically creates the main menu. Change the name of the menu to MAIN_MENU. Rename the menu item New Item to File. Click New Item twice to obtain the text cursor. Erase the old name. Enter the name File. Create new menu items under File. Click File. Click the Create Down tool. Change the name.
-
-
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-2 Oracle iDS Forms: Build Internet Applications II
Practice 1 Solutions ......................................................................................................................................................
-
Set the properties as follows: File Menu Visible in Menu
Visible in Horiz. Menu Toolbar
Icon Filename
DO_KEY(’CO MMIT_FORM’ );
Yes
Yes
Save
DO_KEY(’ CLEAR_FORM ’);
Yes
Yes
Abort
Yes
Yes
Yes
Yes
No
Yes
Label
Menu Item Type
Command Type
Menu Item Code
Save
Plain
PL/SQL
Clear
Plain
PL/SQL
Null1
Separator
Null
Exit
Plain
PL/SQL
Null2
Separator
Null
-
DO_KEY( ’EXIT_FORM ’);
Exit
Note the exact names of the Main Menu, and each of its menu items. These will be needed in a later exercise. 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 the new menu item Edit. Create new menu items under Edit. Click Edit. Click the Create Down tool. Change the name.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-3
Appendix A: Practice Solutions ......................................................................................................................................................
•
- For each of the items, set the properties as follows: Edit Menu
Label
Menu Item Type
Magic Item
Command Type
Visible in Menu
Visible in Horiz. Menu Toolbar
Icon Filename
Cut
Magic
Cut
Null
Yes
Yes
Cut
Copy
Magic
Copy
Null
Yes
Yes
Copy
Paste
Magic
Paste
Null
Yes
Yes
Paste
Null3
Separator
Null
No
Yes
-
•
Create a new menu item to the right of Edit and rename it Sort By. Click Edit. Click the Create Right tool. Rename the new menu item Sort By. - Create three new menu items under Sort By. Click Sort By. Click the Create Down tool. Change the name. Set additional properties as follows: Sort By Menu
Menu Label
Menu Item Type
Menu Item Radio Group
Command Type
Order Id
Radio
Order_By
PL/SQL
SET_BLOCK_PROPERTY( ’s_ord’, order_by, ’id’ ) ;
Customer Id
Radio
Order_By
PL/SQL
SET_BLOCK_PROPERTY( ’s_ord’, order_by, ’customer_id’ ) ;
Sales Rep Id
Radio
Order_By
PL/SQL
SET_BLOCK_PROPERTY( ’s_ord’, order_by, ’sales_rep_id’ ) ;
l a n r e t n I e l c a r O
Menu Item Code
ly n O e s U I A O &
...................................................................................................................................................... A-4 Oracle iDS Forms: Build Internet Applications II
Practice 1 Solutions ......................................................................................................................................................
-
•
Create a new menu to the right of Sort By and rename it Query. See the previous solution. - Create two new menu items under Query. See the previous solution. Set the properties as follows: Query Menu Visible in Horiz. Menu Toolbar
Icon Filename
Menu Item Type
Command Type
Enter Query
Plain
PL/SQL
DO_KEY(’ ENTER_QUERY’);
Yes
query
eXecute Query
Plain
PL/SQL
DO_KEY(’EXECUT E_QUERY’);
Yes
execute
Label
Menu Item Code
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 to M_SUMMIT for the form module. d Save and compile the form module as ORDGXX where XX is your student course number. e Run and test your ORDERS form. 2 Add pop-up menus to the CUSTOMERS form. a Open the CUSTOMERS form module. Save as CUSTGXX where XX is your student course number. b Copy the EDIT_MENU menu from your M_SUMMIT menu module to your CUSTOMERS form. Attach this pop-up menu to the Comments item. - Select EDIT_MENU from the M_SUMMIT menu module. - Click the right mouse button and select Copy. - Select the Pop-up Menus node from the CUSTOMERS form module. - Click the right mouse button and select Paste. - Select this pop-up menu in the Popup Menu property for the Comments item. c Save and compile the form module. Deploy your form to the Web to test. b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-5
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 2 Solutions 1 Define a check menu item. a
-
Open your M_SUMMIT menu module and add a new menu item to the Sort By menu; name it Display_Help. See previous solution. Set the properties as follows: Menu Label
Menu Item Type
Command Type
Display Help
Check
PL/SQL
If Display_Help is selected, enable the ORDERS form toolbar HELP button and set the toolbar canvas background color to grey. If Display_Help is not selected, disable the ORDERS form toolbar HELP button and set the toolbar canvas background color to red. You can import the code from the pr2_1.txt file. - Import the pr2_1.txt file. If get_menu_item_property( ’sort_by_menu.display_help’, checked) = ’TRUE’ then set_canvas_property(’toolbar’, visual_attribute,’Toolnormal’); set_item_property(’control.showhelp’, enabled, property_true); Else set_canvas_property(’toolbar’, visual_attribute,’Toolred’); set_item_property(’control.showhelp’, enabled, property_false); End If; c Save and compile the menu module. d Deploy your ORDERS form on the Web to test your menu. 2 Synchronize the menu module with the form. a Write startup code for your M_SUMMIT menu module that synchronizes the Display_Help menu item with the toolbar HELP button. You can import the code from the pr2_2.txt file. b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-6 Oracle iDS Forms: Build Internet Applications II
Practice 2 Solutions ......................................................................................................................................................
-
Use the Startup Code menu module property to invoke the PL/SQL Editor. If Get_Item_Property(’Control.Showhelp’, Enabled) = ’TRUE’ Then Set_Menu_Item_Property( ’Sort_By_Menu.Display_Help’, Checked, Property_True ); Else Set_Menu_Item_Property( ’Sort_By_Menu.Display_Help’, Checked, Property_False ); End If;
Save and compile your menu module. c Deploy your ORDERS form on the Web to test your menu. 3 Attach your M_SUMMIT menu to your CUSTOMERS form. The menu item Sort_By is not relevant, and should not be visible to the user. a Attach the menu module to your CUSTOMERS form. - Open the CUSTOMERS form and set the Menu Module property to M_SUMMIT for the form module. b Modify the startup code of the M_SUMMIT menu module to hide the SORT_BY menu when the CUSTOMERS form is opened. You can import the code from the pr2_3.txt file to replace the existing code. You may need to change the code to conform to the exact names you gave to the Main Menu and its menu items. Also, be sure the When-Button-Pressed trigger on the Orders button refers to your Orders form. - Import pr2_3.txt file to add the following code. If :System.current_form = ’CUSTOMERS’then set_menu_item_property( ’main_menu.sort_by’, visible, property_false); End if; c Save and compile the menu module. d Deploy your CUSTOMERS form to the Web and test your application. Execute a Query and click the Orders button to move to the ORDERS form. Note that the Sort_By menu item is hidden when the CUSTOMERS form is current, and is visible when the ORDERS form is current. b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-7
Appendix A: Practice Solutions ......................................................................................................................................................
4 With both the ORDERS and CUSTOMERS form modules, restore the default
menu, (DEFAULT&SMARTBAR).
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-8 Oracle iDS Forms: Build Internet Applications II
Practice 3 Solutions ......................................................................................................................................................
Practice 3 Solutions 1 Maintain consistent functionality in your form module by using key
triggers. Define a key trigger in your ORDERS form to give users consistent functionality whenever they exit the form, either using the menu or when the CONTROL.EXIT_BUTTON button is pressed. a Redefine [Exit Form] in your ORDERS form so that an alert message is displayed to users before allowing them to exit the form. b Amend the entry in Keyboard Help to “Exit Orders” for the key trigger. c Save and compile your form. Deploy your form to the Web to test. - Create a form-level KEY-EXIT trigger. Copy the code from the CONTROL.EXIT_BUTTON When-button-pressed trigger. Set_Alert_Property(’Exit_Alert’, ALERT_MESSAGE_TEXT, ’Do you really want to leave the form?’); If show_alert(’EXIT_ALERT’) = alert_button1 then exit_form; End if; - Change the keyboard help text in the KEY-EXIT trigger property palette to “Exit Orders”, and set keyboard help display to YES. d Customize the Alert Text message in your key trigger and ensure that it is displayed whenever the user exits the form using the EXIT_BUTTON on the Control block. - Replace the code in the EXIT_BUTTON When-Button -Pressed trigger with: do_key(’Exit_Form’); e Save and compile your form module. Deploy to the Web and test your form. If you have time 2 Disable [Enter query] in your CUSTOMERS form by creating an appropriate key trigger. Replace [Enter Query] functionality with a call to the customized query form CUSTQUERY. This form allows the user to select a CUSTOMER ID, a CUSTOMER NAME, or a SALES REP ID. The user selections are returned to your CUSTOMER form using global variables.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-9
Appendix A: Practice Solutions ......................................................................................................................................................
a
Add the following code to your key trigger: call_form(’custquery’,no_hide); execute_query;
-
Create a form-level KEY-ENTQRY trigger. Add the code: call_form(’custquery’,no_hide); execute_query; You may import the code from pr3_1.txt file. b The CUSTQUERY form will return three values, held in global variables. The contents should be passed to your CUSTOMERS form to allow the user to execute a restricted query. Use the following global variables: :global.custid; :global.custname; :global.repid;
-
Pass the values to the S_CUSTOMER block items in a PRE_QUERY trigger: :s_customer.id := :global.custid; :s_customer.name := :global.custname; :s_customer.sales_rep_id := :global.repid; c Define the global variables in your CUSTOMERS form to ensure that the user can perform unrestricted queries. - Define the global variables in the When-New-Form-Instance trigger: default_value(’’,’global.custid’); default_value(’’,’global.custname’); default_value(’’,’global.repid’); d Save and compile your CUSTOMER form. Deploy your form to the Web to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-10 Oracle iDS Forms: Build Internet Applications II
Practice 4 Solutions ......................................................................................................................................................
Practice 4 Solutions In this practice, you provide additional functionality in your forms by defining mouse event triggers. 1 In your CUSTOMERS form, define a mouse event trigger on S_CUSTOMER.COMMENTS that invokes the item’s editor when the user double-clicks the item. - When-Mouse-Doubleclick on s_customer.comments Edit_TextItem; 2 In your ORDERS form change the mouse cursor symbol to “busy” while the HELP canvas is displayed. a Ensure that the default symbol is restored whenever the user hides the HELP canvas. - When-Button-Pressed on :control.showhelp Set_Application_Property(cursor_style,’help’);
-
When-Button-Pressed on :control.hidehelp Set_Application_Property(cursor_style,’normal’);
The user can close the HELP canvas without pressing the HIDEHELP button, by navigating to an item in another block. How can you ensure that the mouse cursor is always restored? - Define a Post-Block trigger for the Control Data block. Copy the code to restore the mouse cursor from your :Control.hidehelp button When-Button-Pressed trigger. c Save and compile your form modules. Deploy your forms to the Web to test.You can call the ORDERS form from your CUSTOMERS form. b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-11
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 5 Solutions 1 Manipulate a multiple-window form.
In your ORDERS form module, ensure that the form is exited when a user closes the orders window using the window system menu box. An alert should be displayed to users asking them to confirm that they wish to leave the application. - Define a When-Window-Closed trigger at Form Level. - Call the do_key built-in to execute [Exit_Form]. b If a user closes the Inventory window through the system menu box, ensure that the form remains open with the cursor positioned in the Orders window. - In the When-Window-Closed trigger, use :system.event_window to identify the current window: If :system.event_window = ’WIN_ORDER’ then do_key(’exit_form’); else go_block(’s_ord’); end if; c Whenever the user navigates between the order and inventory windows, place the cursor in the first navigable item within the current window. - Define a When-Window-Activated trigger at form level. Use :system.event_window to identify the current window, and use the GO_BLOCK built in to enforce navigation within the appropriate window. d Save and compile your form. Deploy your form to the Web to test. 2 In your CUSTOMERS form module, define triggers to manage tab page navigation. a Using an appropriate trigger, disable the COMMENTS tab page whenever the COMMENTS item contains no text. b The trigger should fire whenever the user navigates to another record. a
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-12 Oracle iDS Forms: Build Internet Applications II
Practice 5 Solutions ......................................................................................................................................................
-
Modify the WHEN-NEW-RECORD-INSTANCE trigger. If the item COMMENTS contains no data, use the built-in SET_TAB_PAGE_PROPERTY to disable the COMMENTS page. You may import the code from the pr5_1.txt file. Declare tb_pg_id TAB_PAGE; Begin tb_pg_id := FIND_TAB_PAGE(‘COMMENTS’); If :s_customer.comments is null then set_tab_page_property(‘comments’, enabled, property_false); else set_tab_page_property(‘comments’, enabled, property_true); end if; :GLOBAL.custid := s_customer.id; End;
Whenever the user moves from one tab page to another, ensure that the mouse cursor is placed in the first navigable field within the page. - Define a WHEN-TAB-PAGE-CHANGED trigger. Identify the current tab page with the :system.tab_new_page system variable. Use the GO_ITEM built-in to place the cursor in the first navigable field. Begin if :SYSTEM.tab_new_page = ‘ADDRESS’ then GO_ITEM(‘s_customer.name’); else if :SYSTEM.tab_new_page = ‘BILLING’ then GO_ITEM(‘s_customer.credit_rating’); else GO_ITEM(‘s_customer.comments’); end if; end if; End; d Save and compile your form. Deploy your form to the Web to test.
c
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-13
Appendix A: Practice Solutions ......................................................................................................................................................
If you have time 3 How can you ensure that the user is returned to the first navigable item within a tab page after navigating from the last navigable item? - You can use the ‘NEXT’ and ‘PREVIOUS’ navigation item properties. 4 How can you enforce item navigation dynamically within a tab page? - Use GO_ITEM in a KEY-NEXT-ITEM or KEY-PREV-ITEM trigger.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-14 Oracle iDS Forms: Build Internet Applications II
Practice 6 Solutions ......................................................................................................................................................
Practice 6 Solutions 1 Create a server-side package containing a procedure to return a REF
cursor. a Create the Orders_pkg package specification; you can import the code from the pr6_1.txt file. b Create the Orders_pkg package body; you can import the code from the pr6_2.txt file. - Use the Stored Procedure editor in Forms and import the code from the files. c Examine the Orders_pkg package body to identify required parameter values. - A value of ‘P’,’C’ or ‘S’ is passed to the REF cursor to determine which query is required. The REF cursor can query on either products, customers, or sales reps. 2 Open the ORDERREFCURSOR.fmb file and save as ORDREFXX where XX is your student course number. This form contains a control block, called CHOOSE, and a group of option buttons which allows the user to specify a parameter value to be passed to the REF cursor. a Create a data block called SELECTION and base it on the REF cursor procedure that you have just created. - Use the Data Block Wizard and select “Stored Procedures” for data block type. - Specify full procedure name: ‘orders_pkg.query_proc’ - Press the Refresh button. Available columns will be displayed. - Select all as Database Items. b Pass the name of the radio group as the value for the P_VIEW argument: ‘:choose.view_type’. - For the P_VIEW argument, set the value to ‘:choose.view_type’ - Select Finish to end the wizard. c In the layout editor, customize the canvas layout to ensure that SELECTION items are displayed to the left of the option buttons group. d Save and compile your form. Deploy your form to the Web to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-15
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 7 Solutions 1 Create a data block based on a relational table with an object column.
Create a form called OBJ_COLXX where XX is your student course number. b Using the Data Block Wizard, create a block based on the REL_EMP_TABLE_OBJCOL relational table. c Select the columns ID, FIRST_NAME, and LAST_NAME. d Expand the object column DEPT_ID and select the attributes ID and NAME. Note the names that are given to the new items. e In the Layout Wizard, select all available items for display. Change the prompt for item DEPT_ID to Department ID and DEPT_ID_NAME to Department Name. Use a form layout. f Save, compile, and test your form. If you have time 2 Create a block based on a relational table with an REF column, and specify an LOV to populate the REF column. a Create a form called REF_COLXX where XX is your student course number. b Create a block based on the REL_EMP_TABLE_REFCOL relational table. c Select the columns ID, FIRST_NAME, and LAST_NAME. d Select the DEPT_ID attributes ID and NAME. - Note that there are two items called DEPT_ID. The first is expandable and has the term “LOOKUP” in parentheses after the name. The second represents the REF column value itself. The REF item is included automatically when the LOV is created.) e Create an LOV for the REF item DEPT_ID. Select the check box, and select OO_DEPT_TABLE as the source for the LOV. f In the Layout Wizard, select all available items for display. - The REF item DEPT_ID is included in the list of items in the Object Navigator but is not included in the list of available items in the Layout Wizard. g In the Object Navigator, identify the canvas with which item DEPT_ID is associated. a
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-16 Oracle iDS Forms: Build Internet Applications II
Practice 7 Solutions ......................................................................................................................................................
-
REF item values are normally not displayed, so by default the REF item is assigned to the Null canvas.) h In the Object Navigator, check that an LOV and associated record group have been created. i Save, compile, and test the form.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-17
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 8 Solutions 1 Open your ORDERS form and examine the properties of the relation
called 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 Implement a query coordination-type toggle. a Add two check boxes to the control block with the following properties: 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 of Other Value
CHECKED
UNCHECKED
Keyboard Navigable
No
No
Mouse Navigate
No
No
Data Type
CHAR
CHAR
Initial Value
Y
DataBase Item
No
Canvas
TOOLBAR
ly n O e s U I A O & Y
No
TOOLBAR
Use the Layout Editor to position the check boxes appropriately in the Toolbar canvas. c Make sure that the first check box enables a user to toggle between immediate coordination and deferred coordination. You can import the code from the pr8_1.txt file. - When-Checkbox-Changed on CONTROL.IMMEDIATE b
l a n r e t n I e l c a r O
...................................................................................................................................................... A-18 Oracle iDS Forms: Build Internet Applications II
Practice 8 Solutions ......................................................................................................................................................
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; Make sure that the second check box enables a user to toggle between automatic query and no automatic query for the detail block. This check box should be disabled if the other check box indicates immediate coordination. You can import the code from the pr8_2.txt file. - When-Checkbox-Changed on CONTROL.AUTO_QUERY If GET_RELATION_PROPERTY(’s_ord_s_item’, AUTOQUERY ) = ’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; e Save, compile, and test your form. d
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-19
Appendix A: Practice Solutions ......................................................................................................................................................
If you have time 3 Synchronize the check boxes at form startup. a Open your ORDERS form module b Create a procedure called SYNCHRONIZE_CHECKBOX. This procedure synchronizes the IMMEDIATE and AUTO_QUERY check boxes with the current default value. You can import the code from the pr8_3.txt file. - Procedure synchronize_checkbox 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; c Call this procedure from the When-New-Form-Instance trigger. d Save, compile, and run the module.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-20 Oracle iDS Forms: Build Internet Applications II
Practice 9 Solutions ......................................................................................................................................................
Practice 9 Solutions 1 Create a multiple-form application by linking the CUSTOMERS and the
SALESREP forms. The SALESREP form provides a look up facility for :S_CUSTOMER.SALES_REP_ID. It replaces the SALES_REP_LOV list of values. Values are selected by mouse double click. a Open SALESREP form and save as SALESREPXX where XX is your student course number. b In your CUSTOMERS form, modify the code in the SALES_LOV_BUTTON When-Button-Pressed trigger to call the SALESREP form. c Ensure that the user can see, but not navigate to, your CUSTOMERS form, while the SALESREP form is open. d Update :S_CUSTOMER.SALES_REP_ID with the value selected by the user in the SALESREP form. The selected Sales Rep ID is returned in a GLOBAL variable called “salesrepid”. This variable contains either a valid value or the text “NOT SELECTED”. - Ensure that you call your SALESREP form which is identified by the XX suffix. CALL_FORM(‘SALESREP’,no_hide); If :global.salesrepid <> ‘NOT SELECTED’ then :S_CUSTOMER.SALES_REP_ID := :global.salesrepid End if; e Save and compile your form. Deploy your form to the Web to test.
ly n O e s U I A O &
2 Modify your multiple-form application to enable the SALESREP form
to be called in post mode only. a Hide the CUSTOMERS form when the SALESREP form is displayed. - Change your When-Button-Pressed trigger code for the SALES_LOV_BUTTON in the CUSTOMERS form: CALL_FORM(‘SALESREP’,hide); If :global.salesrepid <> ‘NOT 4SELECTED’ then :S_CUSTOMER.SALES_REP_ID := :global.salesrepid End if;
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-21
Appendix A: Practice Solutions ......................................................................................................................................................
In the SALESREP form, ensure that whenever the form has been opened by ‘CALL_FORM’, that a [Commit Form] performs a POST. - In a KEY-COMMIT trigger, define the following code: If get_application_property(calling_form) is not null then post; else commit_form; end if; c Ensure that updates in SALESREP are not cleared when the user exits the form. - SALESREP form KEY-EXIT trigger If get_application_property(calling_form) is not null then exit_form(do_commit,no_rollback); else exit_form; end if; d Save and compile your form. Deploy your form to the Web to test. b
3 The SALESREP form displays a list of all employees; modify your
application to ensure that only Sales Representatives are displayed when the form is called from CUSTOMERS. a Create a parameter called ‘EVERYONE’ in the SALESREP form. Specify a default value of ‘Y’. - Create a PARAMETER object, called “EVERYONE” in the Object Navigator. Define as character type CHAR, with a default value of “Y”. b Ensure that the SALESREP form will perform an unrestricted query whenever the parameter value = ‘Y’. If the parameter value = ‘S’, a restricted query should be performed, selecting only Sales Representatives. - Create a PRE-QUERY trigger : If :parameter.everyone = ‘S’ then
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-22 Oracle iDS Forms: Build Internet Applications II
Practice 9 Solutions ......................................................................................................................................................
:s_emp.title := Sales Representative’; end if; c In the CUSTOMERS form, pass the parameter value “S” to the SALESREP form. Create a parameter list called EMPS. Add the ‘EVERYONE’ parameter to the parameter list. The type of this parameter is TEXT_PARAMETER, and it is initialized with the value “S”. Pass the parameter list to the SALESREP form by using the CALL_FORM built-in. You can import the code from the pr9_1.txt file. Be sure to change the name of the module in the CALL_FORM built-in. - Insert the following code for the SALES_LOV_BUTTON When-Button-Pressed trigger: Declare vpid PARAMLIST; Begin If not Id_Null( Get_Parameter_List( ’EMPS’ ) ) Then Destroy_Parameter_List(’EMPS’); End If; vpid := Create_Parameter_List(’EMPS’); Add_Parameter( vpid, ’everyone’, TEXT_PARAMETER,’S’); Call_Form(’SALESREP’, Hide, No_Replace, No_Query_Only, No_Share_Library_Data, ‘EMPS’ ); If :global.salesrepid = ‘NOT SELECTED’ then message(‘New Sales Rep not selected’); Else :s_customer.sales_rep_id := :global.salesrepid; End if; End; d Save and compile your forms. Deploy your form to the Web to test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-23
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 10 Solutions 1 Create a dynamic list item populated from a design-time query record
group. The record group will hold region ID and the name of the departments in the S_DEPT table. a Open the Employees form. Save as EmpXX where XX is your student course number. In the Object Navigator, create a record group named DEPT_ID based on the query below: SELECT name || ‘ in region ’ || TO_CHAR(region_id) Name, TO_CHAR(id) Id FROM s_dept UNION SELECT ‘none’ Name, ‘0’ Id from dual ORDER BY 1
Convert the text item DEPT_ID into a list item, with a Poplist style. Resize the list item in the Layout Editor. c Create one element in the list item. Specify a label of ‘Dummy’ and a value of ‘0’. Set the Mapping of Other Values property to ‘0’. d Create a procedure called POPULATE_DEPT_LIST. This procedure will accept the list item name as an argument and populate the list item dynamically at run time, using the design-time query record group. You can import the code from the pr10_1.txt file. - Procedure Populate_Dept_List 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_nom ) = 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;
b
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-24 Oracle iDS Forms: Build Internet Applications II
Practice 10 Solutions ......................................................................................................................................................
e
-
f
Call the procedure each time a new record is created. When-Create-Record Trigger on the EMP Block: Clear_List(‘S_Emp.Dept_Id’); POPULATE_DEPT_LIST(‘S_Emp.Dept_Id’); Compile and save your form module. Test.
If you have time 2 Replace the design-time query record group in the Employee form with a run-time query based record group. a Delete the DEPT_ID record group and remove the code from the When-Create-Record trigger. b Create a procedure called CREATE_DEPT_LIST which will dynamically create a record group called QDEPT, if it does not already exist. The procedure will also populate the record group by using the query: SELECT
name ||TO_CHAR(region_id) Name, TO_CHAR(id) Id s_dept
FROM UNION SELECT ‘none’ Name, TO_CHAR(0) Id FROM dual ORDER BY 1
ly n O e s U I A O &
The procedure will also populate the list item S_EMP.DEPT_ID. d You can import the procedure code from pr10_2.txt file. - Procedure Create_Dept_List is v_rg_id recordgroup; v_error number; rg_count number; Begin v_rg_id := find_group(’qdept’); if id_null(v_rg_id) then v_rg_id := create_group_from_query(’qdept’,
c
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-25
Appendix A: Practice Solutions ......................................................................................................................................................
’Select name||to_char(region_id), to_char(id) from s_dept UNION Select ‘‘none’’ Name, to_char(0) Id from dual order by 1'); end if; v_error := populate_group(v_rg_id); populate_list('s_emp.dept_id',v_rg_id); Copy(Get_List_Element_Value('s_emp.dept_id', 1), 's_emp.dept_id'); End; e Call the procedure from the When-Create-Record trigger. - When-Create-Record Trigger on the S_EMP Block: Create_Dept_List; f Compile and save your form module. Test.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-26 Oracle iDS Forms: Build Internet Applications II
Practice 11 Solutions ......................................................................................................................................................
Practice 11 Solutions 1 Create a picklist by using the picklist class. This picklist enables the end
user to select order items from a product list, and to place selections in a second list. a Create a new form module, called PICKXX where XX is your student course number. Open the Form_Builder_II.olb object library. This library contains form objects and code for the picklist. Copy the PICKLIST_BASIC object group to your form module. Copy the PICKLIST object group into your new form module. b Ensure the CONTROL block is the last defined data block in the Object Navigator. c Attach the picklist.pll library. This contains the functions and procedures required for creating the picklist. d Define a When-New-Form-Instance trigger which will create and populate the picklist. You can import the code from the pr11_1.txt file. - Declare dummy number; Begin Default_Value(‘Y’,’Global.where_cls’); pick_list.create_picklist; if :GLOBAL.where_cls = ‘Y’ then dummy := pick_list.populate_picklist_with_query( pick_list.list_in, ‘select name, id from s_product ‘ order by 1’); else dummy := pick_list.populate_picklist_with_query( pick_list.list_in, ‘select name, id from s_product where ‘ || :GLOBAL.where_cls ||’ order by 1’); end if; 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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-27
Appendix A: Practice Solutions ......................................................................................................................................................
Create the picklist with the picklist.Create_Picklist procedure. Call function pick_list.Populate_Picklist_With_Query to populate the List_in object. You must define a query that returns the ID, name and suggested prices from the S_PRODUCT table. Call pick_list.Set_Picklist_Selection procedure to select the first element in the list. Display both lists by using the pick_list.Display_Picklist procedure. Exclude from the query any products already in the current S_ITEM block. You should use the global variable :GLOBAL.where_cls. e Save and compile your form. Deploy your form to the Web. f In your ORDERS form, modify the When-Button-Pressed trigger for the CONTROL.PRODUCT_LOV_BUTTON so that it calls your new form module. - Call_Form(’pickXX’, No_Hide ); g Save and compile your form. Deploy your form to the Web to test. If you have time 2 In the ORDERS form, add a calendar on the S_ORD.DATE_ORDERED item and S_ORD.DATE_SHIPPED item. a From the calendar page of the Form_Builder_II.olb object library, copy the calendar object group. b Attach the PL/SQL library calendar.pll, which contains the Date_LOV package, to your module. c Create a Key-Listval trigger on the date ordered item. Add code to display the calendar using the Date_LOV package. You can import the code from the pr11_2.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
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-28 Oracle iDS Forms: Build Internet Applications II
Practice 11 Solutions ......................................................................................................................................................
Create a Key-Listval trigger on the date shipped item. Add code to display the calendar using the Date_LOV package.You can import the code from the pr11_3.txt file. date_lov.get_date( sysdate + 7, -- initial date ’s_ord.date_shipped’, -- return block.item 240, -- window x position 60, -- window y position ’Shipped Date’, -- window title ’OK’, -- ok button label ’Cancel’, -- cancel button label TRUE, -- highlight weekend days FALSE, -- autoconfirm selection FALSE); -- autoskip after selection e Save and compile your form module. Deploy your form to the Web to test. d
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-29
Appendix A: Practice Solutions ......................................................................................................................................................
Practice 12 Solutions 1 Provide an alternative user interface for your Web-deployed forms by
including Pluggable Java Components (PJCs). a Open the JAVAEMP.fmb form module. Save as JAVAXX.fmb, where XX is your student course code. b Change the :EMP.SAL item so that values are displayed as asterisks at run time. Use the PJC ‘PasswordFieldPJC.class’. This class file is held in the folder: FORMS60_JAVADIR /oracle/forms/demos - In the :EMP.SAL Property Palette, set the implementation class to ‘oracle.forms.demos.PasswordFieldPJC’. c Change the :EMP.ENAME item so that the mouse cursor changes to ‘hand’ style whenever the cursor is moved over the item. Use the PJC ‘ModCursor.class’. This class file is held in the folder: FORMS60_JAVADIR /oracle/forms/demos - In the :EMP.ENAME property palette, set the implementation class to ‘oracle.forms.demos.ModCursor’. Use the built-in SET_CUSTOM_PROPERTY in an appropriate trigger, to specify the cursor style: SET_CUSTOM_PROPERTY(‘Emp.Ename’,1, ‘DOCURSOR’,‘HAND’); - In the When-New-Form-Instance trigger, include: SET_CUSTOM_PROPERTY(‘EMP.ENAME’,1, ‘DOCURSOR’,‘HAND’); e Save and compile your form. Deploy your form to the Web to test. d
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-30 Oracle iDS Forms: Build Internet Applications II
Practice 12 Solutions ......................................................................................................................................................
2 Create a JavaBean area in the Layout Editor to enable users to specify
hyperlinks of their choice. a Create a new data block called ‘LINK’ in your JAVAEMP form. b Create a Bean Area called ‘HYPERLINK1’ in the new data block and specify the class file ‘HyperLinkWrapper.class’. - Create a new data block called ‘LINK’, and define a Bean Area in the Layout editor. In the Bean Area property palette, set the Implementation class to ‘oracle.forms.demos.HyperLinkWrapper’. c
-
Create an appropriate trigger to identify the user-supplied URL from the JavaBean, and to download his or her Web page. You can import the code from the pr12_1.txt file. Attach the When-Custom-Item-Event trigger to the Bean Area and include the following code: Declare eventName varchar2(20) := :system.custom_item_event; eventValues ParamList; eventValueType number; url varchar2(255); Begin eventValues := get_parameter_list( :system.custom_item_event_parameters); if(eventName = ’LINKEVENT’) then get_parameter_attr(eventValues, ’LINKEVENTDATA’, eventValueType,url); web.show_document(url,’_blank’); end if; End;
l a n r e t n I e l c a r O d
ly n O e s U I A O &
Create two text items, one called ‘URL’ to accept a URL and one called ‘LABEL’ to accept a URL label. These will enable the user to select a different URL. Set the maximum length for the URL text item to 255.
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-31
Appendix A: Practice Solutions ......................................................................................................................................................
-
Create two text items, URL and LABEL, in the Data Block ‘LINK’.
e
Create a push button with an appropriate trigger that will accept values from the URL and LABEL text items, and pass them to the JavaBean. You can import the code from the pr12_2.txt file. Create a push button and add the following code to the When-Button-Pressed trigger: If(:URL is not null) and (:LABEL is not null) then set_custom_property(’HYPERLINK1’,1, ’LINKLABEL’, :label); set_custom_property(’HYPERLINK1’,1, ’LINKURL’, :URL); set_custom_property(’HYPERLINK1’,1, ’ACTIVECOLOR’, ’#8BFC5E’); set_custom_property(’HYPERLINK1’,1, ’VISITEDCOLOR’, ’#DF37E1’); set_custom_property(’HYPERLINK1’,1, ’NORMALCOLOR’, ’#F3F4AA’); else set_custom_property(’HYPERLINK1’,1, ’LINKLABEL’, ’Oracle Technet!’); set_custom_property(’HYPERLINK1’,1, ’LINKURL’, ’http://technet.oracle.com’); end if;
l a n r e t n I e l c a r O f
ly n O e s U I A O &
Save and compile your form. Deploy your form to the Web to test.
...................................................................................................................................................... A-32 Oracle iDS Forms: Build Internet Applications II
Practice 13 Solutions ......................................................................................................................................................
Practice 13 Solutions 1 Create a stored procedure, and process error messages that may be
generated in the CUSTSERVER form. a Open the CUSTSERVER form and save as CUSTSERVXX where XX is your student course number. Copy the code in the SELECT_CODE program unit and create a stored procedure called SELECT_CUSTOMER. This procedure takes a user supplied value from :CONTROL.NAME to check whether a customer name already exists. Existing names will generate an error. b Modify the When-Button-Pressed trigger for the CONTROL block CHECK_CUSTOMER button, to trap any errors generated from the stored procedure SELECT_CUSTOMER. If a customer already exists, display a warning message to the user. - Add the following code: exception when others then message(sqlcode||’ ‘||sqlerrm); raise form_trigger_failure; c Save and compile your form module; test. 2 Whenever the user attempts to save a new customer record with an
existing primary key, display an appropriate warning message. a Run your CUSTSERVER form. Attempt to insert a record with an existing primary key. Note the generated error message. - Form - 40508 error code is generated b Define an appropriate trigger to trap the generated error message, and display the database constraint violation message and error code. Ensure that any other generated messages will continue to be displayed.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-33
Appendix A: Practice Solutions ......................................................................................................................................................
Display all messages in an ALERT called ‘errors’. - Define a form level ON-ERROR trigger and include the following code: Declare nums number; errmess varchar2(200); v_constraint_name varchar2(255); begin if error_code = 40508 then errmess := ’Constraint violation ’ ||dbms_error_text|| ’ (’||dbms_error_code||’)’; else errmess := error_type || to_char( -error_code ) || ’: ’ || error_text; end if; set_alert_property(’errors’, alert_message_text, errmess); nums := show_alert(’errors’); End; d Save and compile your form module; test your form. c
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-34 Oracle iDS Forms: Build Internet Applications II
Practice 14 Solutions ......................................................................................................................................................
Practice 14 Solutions 1 Automatically ask the users if they want to commit after a set period of
time. a Open your CUSTOMERS form module. b At the startup of the form, create a global variable called GLOBAL.LOCKS_PENDING. This will indicate at all times whether rows of the S_CUSTOMER table are locked. The default value is NULL. Append your new code to existing code. - When-New-Form-Instance Trigger at Form Level ... Default_Value( NULL, ’GLOBAL.locks_pending’); Create the On-Lock trigger to implement the default lock processing, update the global variable, and create the timer. You can import the code from the pr14_1.txt file. - On-Lock Trigger at Form Level 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 Raise Form_Trigger_Failure; End If; End; d Display an alert if locks are still pending after a certain period of time elapses. This alert should ask the user to commit or roll back the changes. The alert, ASK_SAVE, already exists in your form. Define an appropriate trigger. You can import the code from the pr14_2.txt file. c
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-35
Appendix A: Practice Solutions ......................................................................................................................................................
-
When-Timer-Expired Trigger at Form Level If 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;
Create a Post-Database-Commit trigger to restore the NULL value to the global variable. You can import the code from the pr14_3.txt file. - Post-Database-Commit Trigger at Form Level :GLOBAL.locks_pending := NULL; f Create the On-Rollback trigger to implement the default rollback processing and restore the NULL value to the global variable. You can import the code from the pr14_4.txt file. - On-Rollback Trigger at Form Level Issue_Rollback( Get_Application_Property( savepoint_name ) ); If Form_Success then :Global.locks_pending := NULL; End If; g Save and compile your form to test. e
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-36 Oracle iDS Forms: Build Internet Applications II
Practice 15 Solutions ......................................................................................................................................................
Practice 15 Solutions 1 Create a chart object by using the Chart Wizard. This chart should
display the total orders for each customer. a Open the ORDCHART form module. Save as ORDCHARTXX where XX is your student course number. This form module contains a S_ORD block. Only one item from this block is visible: TITLE. This item displays a title for your chart. b In the Layout Editor, use the Chart tool to drag a chart area onto the canvas. Select “Use the Chart Wizard” from the New Chart Object dialog box. Do not specify a title for the chart. c Select Column as chart type, Plain as chart subtype. d Specify the S_ORD block as the data block that contains the data you want to assign to chart columns. e Select CUSTOMER_NAME to appear on the Category (X) axis, and TOTAL to appear on the Value (Y) axis of the chart. f Click the Finish button. g Save and compile your form module. Deploy your form to the Web to test. To display the chart, click in the Title item on the Chart tab. 2 Create a report object based on the S_CUSTOMER block. This report
displays a customer list using a tabular layout. Display only the ID, NAME, COUNTRY, and REGION_ID fields. a Open the CUSTREPT form. Save as CUSTREPXX where XX is your student course number.Create a new report object. - Select the Reports node in the Object Navigator. - Click the Create icon. - Specify a filename of ‘myreport’ and a block name. Click OK. b This will invoke Report Builder. Within the Report Wizard: Choose the report style Tabular. Select the ID, NAME, COUNTRY, and REGION_ID 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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II A-37
Appendix A: Practice Solutions ......................................................................................................................................................
Set the Execution Mode report object property to “runtime” and the Report Destination Type report object property to “screen”. Rename the report object to “my_report”. - 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 to “SCREEN”. e Create a button named CUST_REP_BUTTON in the control block. Display this button on the CANVAS2 canvas. f Create a When-Button-Pressed trigger on the CUST_REP_BUTTON button to run the report. - DECLARE repid REPORT_OBJECT; v_rep VARCHAR2(100); rep_status VARCHAR2(20); BEGIN repid := FIND_REPORT_OBJECT(‘my_report’); v_rep := RUN_REPORT_OBJECT(repid); END; g Change the Query All Record property value to Yes for the S_CUSTOMER block. h Save and compile your form module. Deploy your form to the Web to test. Query a customer record before calling the report. d
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... A-38 Oracle iDS Forms: Build Internet Applications II
................................
Table Descriptions and Data
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix B: Table Descriptions and Data .....................................................................................................................................................
Summit Sporting Goods Database Diagram ORD_ID
ID
S_ITEM PRODUCT_ID
S_ORD CUSTOMER_ID
SALES_REP_ID
*
S_INVENTORY PRODUCT_ID ID ID
ID
S_CUSTOMER
S_PRODUCT
SALES_REP_ID
IMAGE_ID
ID
ID
S_IMAGE
ID
S_EMP DEPT_ID
ly n O e S_DEPT Us I A O & ID
*Unique occurrences are identified by PRODUCT_ID and WAREHOUSE_ID.
l a n r e t n I e l c a r O
...................................................................................................................................................... B-2 Oracle iDS Forms: Build Internet Applications II
S_CUSTOMER Description .....................................................................................................................................................
S_CUSTOMER Description Column Name -------------- --------ID NAME PHONE ADDRESS CITY STATE COUNTRY ZIP_CODE CREDIT_RATING SALES_REP_ID REGION_ID COMMENTS
Null? ------------NOT NULL NOT NULL
l a n r e t n I e l c a r O
Datatype ----------------NUMBER(7) VARCHAR2(50) VARCHAR2(25) VARCHAR2(400) VARCHAR2(30) VARCHAR2(20) VARCHAR2(30) VARCHAR2(75) VARCHAR2(9) NUMBER(7) NUMBER(7) VARCHAR2(255)
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-3
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_CUSTOMER Data 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 Bahia Sao Paolo Brazil Excellent 12 2 Customer usually orders large amounts and has a high order total. This is okay as long as the credit rating remains excellent. 202 Osaka
Oj Athletics
81-20101
6741 Takashi Blvd. Japan Poor 14 4 Customer should always pay by cash until his credit rating improves.
ly n O e s U I A O &
203 Delhi Sports 91-10351 11368 Chanakya New Delhi India Good 14 4 Customer specializes in baseball equipment and is the largest retailer in India.
l a n r e t n I e l c a r O
...................................................................................................................................................... B-4 Oracle iDS Forms: Build Internet Applications II
S_CUSTOMER Data .....................................................................................................................................................
Id Name Phone Address ----------------------- ------------ ----------City State Country ------------------ -------------------------Zip_code Credit_ra Sales_rep_id Region_id ----------------------------- --------Comments -------------------------------------------------204 Womansport 1-206-104-0103 281 King Street Seattle Washington USA 98101 EXCELLENT 11 1 205 Hong Kong
Kam’s Sporting Goods 852-3692888 15 Henessey Road EXCELLENT 15 4
206 Cannes
Sportique
33-2257201
172 Rue de Rivoli France EXCELLENT 15 5 Customer specializes in soccer. Likes to order accessories in bright colors. 207 Lagos
Sweet Rock Sports
234-603620
l a n r e t n I e l c a r O
ly n O e s U I A O & 6 Saint Antoine Nigeria
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-5
Appendix B: Table Descriptions and Data .....................................................................................................................................................
Id Name Phone Address ----------------------- ------------ ----------City State Country ------------------ -------------------------Zip_code Credit_ra Sales_rep_id Region_id ----------------------------- --------Comments -------------------------------------------------208 Muench Sports 49-527454 435 Gruenestrasse Stuttgart Germany GOOD 15 5 Customer usually pays small orders by cash and large orders on credit. 209 Beisbol Si! San Pedro de Macon’s EXCELLENT
809-352689 11
789 Playa Del Mar Dominican Republic 1
210 Futbol Sonora 52-404562 3 Via Saguaro Nogales EXCELLENT 12 2 Customer is difficult to reach by phone. Try mail. 211 Prague
Kuhn’s Sports EXCELLENT
ly n O e s U I A O &
42-111292 15
l a n r e t n I e l c a r O
7 Modrany Czechoslovakia 5
...................................................................................................................................................... B-6 Oracle iDS Forms: Build Internet Applications II
S_CUSTOMER Data .....................................................................................................................................................
Id Name Phone Address ----------------------- ------------ ----------City State Country ------------------ -------------------------Zip_code Credit_ra Sales_rep_id Region_id ----------------------------- --------Comments -------------------------------------------------212 Hamada Sport 20-1209211 57A Corniche Alexandria Egypt EXCELLENT 13 3 Customer orders sea and water equipment. 213 Big John’s Sports 1-415-555-6281 4783 18th Street Emporium San Francisco CA USA 94117 EXCELLENT 11 1 Customer has a dependable credit record. 214 Ojibway Retail Buffalo NY 14202 POOR
1-716-555-7171 415 Main Street USA 11 1
ly n O e s U I A O &
215 Sporta Russia 7-3892456 6000 Yekatamina St. Petersburg Russia POOR 15 5 This customer is very friendly, but has difficulty paying bills. Insist upon cash.
l a n r e t n I e l c a r O Note: This display has been formatted.
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-7
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_DEPT Description and Data Column Name ---------------------ID NAME REGION_ID
Null? ------------NOT NULL NOT NULL
Datatype -------------NUMBER(7) VARCHAR2(25) NUMBER(7)
SQL> SELECT * FROM s_dept; ID -----10 31 32 33 34 35 41 42 43 44 45 50
NAME ---------------Finance Sales Sales Sales Sales Sales Operations Operations Operations Operations Operations Administration
12 rows selected.
l a n r e t n I e l c a r O
REGION_ID ----------------1 1 2 3 4 5 1 2 3 4 5 1
ly n O e s U I A O &
...................................................................................................................................................... B-8 Oracle iDS Forms: Build Internet Applications II
S_EMP Description .....................................................................................................................................................
S_EMP Description Column Name -----------------------ID LAST_NAME FIRST_NAME USERID START_DATE COMMENT MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
Null? -------------NOT NULL NOT NULL
l a n r e t n I e l c a r O
Datatype -------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE VARCHAR2(255) NUMBER(7) VARCHAR2(25) NUMBER(7) NUMBER(11,2) NUMBER(4,2)
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-9
Appendix B: Table Descriptions and Data .....................................................................................................................................................
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-90 President 50 2500 2 Ngao LaDoris lngao 08-MAR-90 1 VP, Operations 41 1450 3
Nagayama 1 VP, Sales
Midori
4
Quick-To-See Mark 1 VP, Finance
5
Ropeburn Audry 1 VP, Administration
6
Urguhart Molly 2 Warehouse Manager
l a n r e t n I e l c a r O 7 8
9
Menchu Roberta 2 Warehouse Manager Biri Ben 2 Warehouse Manager
mnagayam 17-JUN-91 31 1400 mquickto 07-APR-90 10 1450
ly n O e s U I A O &
Catchpole Antoinette 2 Warehouse Manager
aropebur 04-MAR-90 50 1550 murguhar 18-JAN-91 41 1200
rmenchu 14-MAY-90 42 1250 bbiri 07-APR-90 43 1100 acatchpo 09-FEB-92 44 1300
...................................................................................................................................................... B-10 Oracle iDS Forms: Build Internet Applications II
S_EMP Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID START_DAT ---- ----------------- ----------------------- --------COMMENTS -------------------------------------------------------------MANAGER_ID TITLE DEPT_ID SALARY ---------- ---------------------------------- -----COMMISSION_PCT --------------10 Havel Marta mhavel 27-FEB-91 2 Warehouse Manager 45 1307 11 Magee Colin 3 Sales Representative 10
cmagee 14-MAY-90 31 1400
12 Giljum Henry 3 Sales Representative 12.5
hgiljum 18-JAN-92 32 1490
13 Sedeghi Yasmin 3 Sales Representative 10
ysedeghi 18-FEB-91 33 1515
14 Nguyen Mai 3 Sales Representative 15
mnguyen 22-JAN-92 34 1525
l a n r e t n I e l c a r O
ly n O e s U I A O &
15 Dumas Andre 3 Sales Representative 17.5
adumas 09-OCT-91 35 1450
16 Maduro Elena 6 Stock Clerk
emaduro 07-FEB-92 41 1400
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-11
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID START_DAT ---- ----------------- ----------------------- --------COMMENTS -------------------------------------------------------------MANAGER_ID TITLE DEPT_ID SALARY ---------- ---------------------------------- -----COMMISSION_PCT --------------17 Smith George gsmith 08-MAR-90 6 Stock Clerk 41 940 18 Nozaki Akira 7 Stock Clerk
anozaki 09-FEB-91 42 1200
19 Patel Vikram 7 Stock Clerk
vpatel 06-AUG-91 42 795
20 Newman Chad 8 Stock Clerk
cnewman 21-JUL-91 43 750
21 Markarian Alexander 8 Stock Clerk 22 Chang Eddie 9 Stock Clerk 23 Patel Radha 9 Stock Clerk
l a n r e t n I e l c a r O
ly n O e s U I A O & amarkari 26-MAY-91 43 850
echang 30-NOV-90 44 800 rpatel 17-OCT-90 34 795
24 Dancs Bela 10 Stock Clerk
bdancs 17-MAR-91 45 860
25 Schwartz Sylvie 10 Stock Clerk
sschwart 09-MAY-91 45 1100
Note: This display has been formatted. ...................................................................................................................................................... B-12 Oracle iDS Forms: Build Internet Applications II
S_ITEM Description .....................................................................................................................................................
S_ITEM Description Column Name ------------------------ORD_ID ITEM_ID PRODUCT_ID PRICE QUANTITY QUANTITY_SHIPPED
Null? -------------NOT NULL NOT NULL NOT NULL
l a n r e t n I e l c a r O
Datatype ---------NUMBER(7) NUMBER(7) NUMBER(7) NUMBER(11,2) NUMBER(9) NUMBER(9)
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-13
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_ITEM Data SQL> SELECT * FROM s_item; ORD_ID -----100 100 100 100 100 100 100 101 101 101 101 101 101 101 102 102 103 103 104 104 104 104 105 105 105
ITEM_ID -------1 2 3 5 7 6 4 1 3 5 6 7 4 2 1 2 1 2 1 4 2 3 1 3 2
PRODUCT_ID ----------10011 10013 10021 30326 41010 30433 10023 30421 41010 50169 50417 50530 41100 40422 20108 202011 30433 32779 20510 30421 20512 30321 50273 50532 50419
PRICE QUANTITY QUANTITY_SHIPPED ------ -------- ----------------135 500 500 380 400 400 14 500 500 582 600 600 8 250 250 20 450 450 36 400 400 16 15 15 8 20 20 4.29 40 40 80 27 27 45 50 50 45 35 35 50 30 30 28 100 100 23 45 45 20 15 15 7 11 11 9 7 7 16 35 35 8 12 12 1669 19 19 22.8 16 16 47 28 28 80 13 13
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-14 Oracle iDS Forms: Build Internet Applications II
S_ITEM Data .....................................................................................................................................................
ORD_ID ------106 106 106 106 106 106 107 107 107 107 107 108 108 108 108 108 108 108 109 109 109 109 109 109 109 110 110 111 111 97 97 98 99 99 99 99
ITEM_ID ------1 4 5 6 2 3 1 3 5 4 2 1 6 7 5 2 4 3 1 5 7 6 4 2 3 1 2 1 2 1 2 1 1 2 3 4
PRODUCT_ID ----------20108 50273 50418 50419 20201 50169 20106 20201 30421 30321 20108 20510 41080 41100 32861 20512 32779 30321 10011 30426 50418 32861 30326 10012 10022 50273 50536 40421 41080 20106 303211 404218 20510 20512 50417 50530
l a n r e t n I e l c a r O
PRICE QUANTITY QUANTITY_SHIPPED ------ -------- ----------------28 46 46 22.89 75 75 75 98 98 80 27 27 123 21 21 4.29 125 125 11 50 50 115 130 130 16 55 55 1669 75 75 28 22 22 9 9 9 35 50 50 45 42 42 60 57 57 8 18 18 7 60 60 1669 85 85 140 150 150 18.25 500 500 75 43 43 60 50 50 582 1500 1500 175 600 600 21.95 300 300 22.89 17 17 50 23 23 65 27 27 35 29 29 9 1000 1000 500 50 50 5 7 7 9 18 18 8 25 25 80 53 53 45 69 69
ly n O e s U I A O &
Note: This display has been formatted.
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-15
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_ORD Description and Data Column Name -----------------------ID CUSTOMER_ID DATE_ORDERED DATE_SHIPPED SALES_REP_ID TOTAL PAYMENT_TYPE ORDER_FILLED
Null? -----NOT NULL NOT NULL
Datatype --------------------NUMBER(7) NUMBER(7) DATE DATE NUMBER(7) NUMBER(11,2) VARCHAR2(6) VARCHAR2(1)
SQL> SELECT * FROM s_ord; ID CUSTOMER_ID
DATE_ORDE
DATE_SHIPSALES_REP_IDTOTAL PAYMEN
ORDER_F
--- -----------
---------
----------------------------------
------
100 101 102 103 104 105 106 107 108 109 110 111 97 98 99 112
31-AUG92 31-AUG-92 01-SEP-92 02-SEP-92 03-SEP-92 04-SEP-92 07-SEP-92 07-SEP-92 07-SEP-92 08-SEP-92 09-SEP-92 09-SEP-92 28-AUG-92 31-AUG-92 31-AUG-92 31-AUG-92
10-SEP-9211 15-SEP-9214 08-SEP-9215 22-SEP-9215 23-SEP-9215 18-SEP-9211 15-SEP-9212 21-SEP-9215 10-SEP-9213 28-SEP-9211 21-SEP-9211 21-SEP-9211 17-SEP-9212 10-SEP-9214 18-SEP-9214 10-SEP-9212
Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
204 205 206 208 208 209 210 211 212 213 214 204 201 202 203 210
16 rows selected.
l a n r e t n I e l c a r O
601100 8056.6 8335 377 32430 2722.24 15634 142171 149570 1020935 1539.13 2770 84000 595 7707 550
CREDIT CREDIT CREDIT CASH CREDIT CREDIT CREDIT CREDIT CREDIT CREDIT CASH CASH CREDIT CASH CREDIT CREDIT
ly n O e s U I A O &
...................................................................................................................................................... B-16 Oracle iDS Forms: Build Internet Applications II
S_PRODUCT Description .....................................................................................................................................................
S_PRODUCT Description Column Name Null? ---------------------------------ID NOT NULL NAMENOT NULL SHORT_DESC LONGTEXT_ID IMAGE_ID SUGGESTED_WHLSL_PRICE WHLSL_UNITS
l a n r e t n I e l c a r O
Datatype ----------------NUMBER(7) VARCHAR2(50) VARCHAR2(255) NUMBER(7) NUMBER(7) NUMBER(11,2) VARCHAR2(25)
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-17
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_PRODUCT Data SQL> SELECT * FROM s_product; ID ---IMAGE_ID -------10011 1001
NAME SHORT_DESC LONGTEXT_ID -----------------------------------SUGGESTED_WHLSL_PRICEWHLSL_UNITS -------------------------------Boot Beginner’s ski boot 518 150
10012 1002
Ace Ski BootIntermediate ski boot519 200
10013 1003
Pro Ski BootAdvanced ski boot 410
10021 1011
Bunny Ski PoleBeginner’s ski pole528 16.25
10022 1012
Ace Ski PoleIntermediate ski pole529 21.95
10023 1013
Pro Ski PoleAdvanced ski pole 40.95
20106
Junior Soccer BallJunior soccer ball613 11
520
530
ly n O e s U I A O &
20108
World Cup Soccer BallWorld cup soccer ball615 28
20201
World Cup NetWorld cup net 123
l a n r e t n I e l c a r O
708
...................................................................................................................................................... B-18 Oracle iDS Forms: Build Internet Applications II
S_PRODUCT Data .....................................................................................................................................................
ID NAME SHORT_DESC LONGTEXT_ID ---- --------------------------------------IMAGE_IDSUGGESTED_WHLSL_PRICEWHLSL_UNITS ---------------------------------------20510 Black Hawk Knee pads, pair 1017 Knee Pads 9 20512Black Hawk Elbow pads, pair Elbow Pads 8
1019
30321 Grand Prix Bicycle Road bicycle828 1291 1669 30326 Himalaya BicycleMountain bicycle833 1296 582 30421 Grand Prix Road bicycle tires927 Bicycle Tires16 30426 Himalaya TiresMountain bicycle tires933 18.25 30433New Air PumpTire pump 20 32779 Slaker Water Water bottle Bottle 7 32861 Safe-T HelmetBicycle helmet 1829 60 40421 Alexeyer ProStraight bar Lifting Bar 1381 65
l a n r e t n I e l c a r O 40422 Pro Curling BarCurling bar 1382 50
940
1286
ly n O e s U I A O & 1368
928
929
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-19
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID ---IMAGE_ID -------40421 1381
NAME SHORT_DESC LONGTEXT_ID --------------------------------------SUGGESTED_WHLSL_PRICEWHLSL_UNITS -------------------------------Alexeyer ProStraight bar 928 Lifting Bar 65
40422 1382
Pro Curling BarCurling bar 50
41010
Prostar 10 Ten pound weight 517 Pound Weight 8
41020
Prostar 20 Twenty pound weight527 Pound Weight 12
41050
Prostar 50 Fifty pound weight557 Pound Weight 25
41080
Prostar 80 Eighty pound weight587 Pound Weight 35
41100
Prostar 100 One hundred pound 607 Pound Weightweight 45
50169 1119
Major League Baseball Baseball 4.29
l a n r e t n I e l c a r O
929
ly n O e s U I A O & 676
...................................................................................................................................................... B-20 Oracle iDS Forms: Build Internet Applications II
S_PRODUCT Data .....................................................................................................................................................
ID NAME SHORT_DESC LONGTEXT_ID ---- ----------------------------------------IMAGE_IDSUGGESTED_WHLSL_PRICEWHLSL_UNITS ---------------------------------------50273Chapman HelmetBatting helmet 780 1223 22.89 50417 Griffey GloveOutfielder’s glove 924 1367 80 50418 Alomar GloveInfielder’s glove 1368 75
925
50419 Steinbach GloveCatcher’s glove 1369 80
926
50530Cabrera Bat Thirty inch bat 1480 45
1037
50532 Puckett Bat Thirty-two inch bat 1039 1482 47 50536 Winfield BatThirty-six inch bat 1043 1486 50
Note: This display has been formatted.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-21
Appendix B: Table Descriptions and Data .....................................................................................................................................................
S_REGION Description and Data Column Name Null? -----------------------------ID NOT NULL NAME NOT NULL
Datatype ---------------NUMBER(7) VARCHAR2(50)
SQL> SELECT * FROM s_region; ID --1 2 3 4 5
NAME -------------------------North America South America Africa / Middle East Asia Europe
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-22 Oracle iDS Forms: Build Internet Applications II
S_TITLE Description and Data .....................................................................................................................................................
S_TITLE Description and Data Column Name Null? Datatype ---------------------------------- ---------------TITLE NOT NULL VARCHAR2(25)
SQL> SELECT * FROM s_title; TITLE -----------------------President Sales Representative Stock Clerk VP, Administration VP, Finance VP, Operations VP, Sales Warehouse Manager 8 rows selected.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-23
Appendix B: Table Descriptions and Data .....................................................................................................................................................
Oracle8i Objects: Types, Tables emp_type_ObjCol Name Null? -------------------- ------------ID LAST_NAME FIRST_NAME USERID START_DATE MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
Type ---------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE NUMBER(7) VARCHAR2(25) DEPT_TYPE NUMBER(11,2) NUMBER(4,2)
emp_type_RefCol Name Null? -------------------- ------------ID LAST_NAME FIRST_NAME USERID START_DATE MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
l a n r e t n I e l c a r O dept_type
Type ---------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE NUMBER(7) VARCHAR2(25) REF OF DEPT_TYPE NUMBER(11,2) NUMBER(4,2)
ly n O e s U I A O &
--------------------------------ID NAME REGION_ID
---------------NUMBER VARCHAR2(25) NUMBER(7)
...................................................................................................................................................... B-24 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
Tables oo_emp_Table_ObjCol Name Null? Type -------------------- ---------------------------ID NUMBER(7) LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) USERID VARCHAR2(8) START_DATE DATE MANAGER_ID NUMBER(7) TITLE VARCHAR2(25) DEPT_ID DEPT_TYPE SALARY NUMBER(11,2) COMMISSION_PCT NUMBER(4,2) ID LAST_NAME FIRST_NAME USERID --------- ----------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) ----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------1Velasquez Carmencvelasqu 03-MAR-90 President DEPT_TYPE(50, ’Administration’, 1) 2500 2Ngao 08-MAR-90 DEPT_TYPE(41, ’Operations’, 1) 1450
l a n r e t n I e l c a r O 3Nagayama 17-JUN-91 DEPT_TYPE(31, ’Sales’, 1) 1400
ly n O e s U I A O & LaDorislngao 1 VP Operations
Midorimnagayam 1 VP Sales
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-25
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID -------------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------4 Quick-To-See Mark mquickto 07-APR-90 1 VP Finance DEPT_TYPE(10, ’Finance’, 1) 1450 5 Ropeburn Audry aropebur 04-MAR-90 1 VP Administration DEPT_TYPE(50, ’Administration’, 1) 1550 6 Urguhart Molly murguhar 18-JAN-91 2 Warehouse Manager DEPT_TYPE(41, ’Operations’, 1) 1200
ly n O e s U I A O &
7 Menchu Roberta rmenchu 14-MAY-90 2 Warehouse Manager DEPT_TYPE(42, ’Operations’, 2) 1250 8 Biri Ben bbiri 07-APR-90 2 Warehouse Manager DEPT_TYPE(43, ’Operations’, 3) 1100
l a n r e t n I e l c a r O
9 Catchpole Antoinette acatchpo 09-FEB-92 2 Warehouse Manager DEPT_TYPE(44, ’Operations’, 4) 1300
...................................................................................................................................................... B-26 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- ------------------------------ -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------10 Havel Marta mhavel 27-FEB-91 2 Warehouse Manager DEPT_TYPE(45, ’Operations’, 5) 1307 11 Magee Colin cmagee 14-MAY-90 3 Sales Representative DEPT_TYPE(31, ’Sales’, 1) 1400 10 12 Giljum Henry hgiljum 18-JAN-92 3 Sales Representative DEPT_TYPE(32, ’Sales’, 2) 1490 13
ly n O e s U I A O &
13 Sedeghi Yasmin ysedeghi 18-FEB-91 3 Sales Representative DEPT_TYPE(33, ’Sales’, 3) 1515 10 14 Nguyen Mai mnguyen 22-JAN-92 3 Sales Representative DEPT_TYPE(34, ’Sales’, 4) 1525 15
l a n r e t n I e l c a r O
15 Dumas Andre adumas 09-OCT-91 3 Sales Representative DEPT_TYPE(35, ’Sales’, 5) 145018
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-27
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ----------------------- --------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------16 Maduro Elena emaduro 07-FEB-92 6 Stock Clerk DEPT_TYPE(41, ’Operations’, 1) 1400 17 Smith George 08-MAR-90 6 Stock Clerk DEPT_TYPE(41, ’Operations’, 1) 940
gsmith
18 Nozaki Akira 09-FEB-91 7 Stock Clerk DEPT_TYPE(42, ’Operations’, 2) 1200
anozaki
19 Patel Vikram 06-AUG-91 7 Stock Clerk DEPT_TYPE(42, ’Operations’, 2) 795
vpatel
20 Newman Chad 21-JUL-91 8 Stock Clerk DEPT_TYPE(43, ’Operations’, 3) 750
cnewman
21 Markarian Alexander 26-MAY-91 8 Stock Clerk DEPT_TYPE(43, ’Operations’, 3) 850
amarkari
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-28 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- ------------------------------ -------START_DAT MANAGER_ID TITLE --------- ---------------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------22 Chang Eddie echang 30-NOV-90 9 Stock Clerk DEPT_TYPE(44, ’Operations’, 4) 800 23 Patel Radha 17-OCT-90 9 Stock Clerk DEPT_TYPE(34, ’Sales’, 4) 795
rpatel
24 Dancs Bela 17-MAR-91 10 Stock Clerk DEPT_TYPE(45, ’Operations’, 5) 860
bdancs
25 Schwartz Sylvie 09-MAY-91 10 Stock Clerk DEPT_TYPE(45, ’Operations’, 5) 1100
sschwart
oo_emp_Table_RefCol
l a n r e t n I e l c a r O Name Null? -----------------------------ID LAST_NAME FIRST_NAME USERID START_DATE MANAGER_ID TITLE
ly n O e s U I A O & Type ---------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE NUMBER(7) VARCHAR2(25)
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-29
Appendix B: Table Descriptions and Data .....................................................................................................................................................
DEPT_ID SALARY COMMISSION_PCTNUMBER(4,2)
REF OF DEPT_TYPE NUMBER(11,2)
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------1 Velasquez Carmen cvelasqu 03-MAR-90 President 0000220208447F54A9ED64676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 2500 2 Ngao LaDoris lngao 08-MAR-90 1 VP Operations 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450
ly n O e s U I A O &
3 Nagayama Midori mnagayam 17-JUN-91 1 VP Sales 0000220208447F54A9ED5A676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400
l a n r e t n I e l c a r O
4 Quick-To-See Mark mquickto 07-APR-90 1 VP Finance 0000220208447F54A9ED59676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450 5 Ropeburn Audry aropebur 04-MAR-90 1 VP Administration 0000220208447F54A9ED64676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1550
...................................................................................................................................................... B-30 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- ------------------ --------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------6 Urguhart Molly murguhar 18-JAN-91 2 Warehouse Manager 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1200 9 Catchpole Antoinette acatchpo 09-FEB-92 2 Warehouse Manager 0000220208447F54A9ED62676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1300 10 Havel Marta mhavel 27-FEB-91 2 Warehouse Manager 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1307
ly n O e s U I A O &
11 Magee Colin cmagee 14-MAY-90 3 Sales Representative 0000220208447F54A9ED5A676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400 10
l a n r e t n I e l c a r O
13 Sedeghi Yasmin ysedeghi 18-FEB-91 3 Sales Representative 0000220208447F54A9ED5C676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1515 10
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-31
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- ------------------ --------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------14 Nguyen Mai mnguyen 22-JAN-92 3 Sales Representative 0000220208447F54A9ED5D676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1525 15 15 Dumas Andre adumas 09-OCT-91 3 Sales Representative 0000220208447F54A9ED5E676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450 18 16 Maduro Elena emaduro 07-FEB-92 6 Stock Clerk 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400
ly n O e s U I A O &
17 Smith George gsmith 08-MAR-90 6 Stock Clerk 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 940
l a n r e t n I e l c a r O
18 Nozaki Akira anozaki 09-FEB-91 7 Stock Clerk 0000220208447F54A9ED60676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1200
...................................................................................................................................................... B-32 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------19 Patel Vikram vpatel 06-AUG-91 7 Stock Clerk 0000220208447F54A9ED60676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 795 20 Newman Chad cnewman 21-JUL-91 8 Stock Clerk 0000220208447F54A9ED61676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 750 21 Markarian Alexander amarkari 26-MAY-91 8 Stock Clerk 0000220208447F54A9ED61676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 850
ly n O e s U I A O &
22 Chang Eddie echang 30-NOV-90 9 Stock Clerk 0000220208447F54A9ED62676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 800
l a n r e t n I e l c a r O
23 Patel Radha rpatel 17-OCT-90 9 Stock Clerk 0000220208447F54A9ED5D676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 795
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-33
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------24 Dancs Bela bdancs 17-MAR-91 10 Stock Clerk 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 860 25 Schwartz Sylvie sschwart 09-MAY-91 10 Stock Clerk 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1100
oo_dept_table Name -------------------ID NAME REGION_ID
Null? -------------
Type ---------------NUMBER VARCHAR2(25) NUMBER(7)
ID -------------------10 31 32 33 34 35 41 42 43
NAME ------------Finance Sales Sales Sales Sales Sales Operations Operations Operations
REGION_ID ---------------1 1 2 3 4 5 1 2 3
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-34 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
44 45 50
Operations Operations Administration
4 5 1
Null? -------------
Type ---------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE NUMBER(7) VARCHAR2(25) DEPT_TYPE NUMBER(11,2) NUMBER(4,2)
rel_emp_Table_Objcol Name -------------------ID LAST_NAME FIRST_NAME USERID START_DATE MANAGER_ID TITLE DEPT_ID SALARY COMMISSION_PCT
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------1 Velasquez Carmen cvelasqu 03-MAR-90 President DEPT_TYPE(50, ’Administration’, 1) 2500
l a n r e t n I e l c a r O
ly n O e s U I A O &
2 Ngao LaDoris 08-MAR-90 1 VP Operations DEPT_TYPE(41, ’Operations’, 1) 1450
lngao
3 Nagayama Midori 17-JUN-91 1 VP Sales DEPT_TYPE(31, ’Sales’, 1) 1400
mnagayam
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-35
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------4 Quick-To-See Mark mquickto 07-APR-90 1 VP Finance DEPT_TYPE(10, ’Finance’, 1) 1450 5 Ropeburn Audry aropebur 04-MAR-90 1 VP Administration DEPT_TYPE(50, ’Administration’, 1) 1550 6 Urguhart Molly murguhar 18-JAN-91 2 Warehouse Manager DEPT_TYPE(41, ’Operations’, 1) 1200
ly n O e s U I A O &
7 Menchu Roberta rmenchu 14-MAY-90 2 Warehouse Manager DEPT_TYPE(42, ’Operations’, 2) 1250 8 Biri Ben bbiri 07-APR-90 2 Warehouse Manager DEPT_TYPE(43, ’Operations’, 3) 1100
l a n r e t n I e l c a r O
9 Catchpole Antoinette acatchpo 09-FEB-92 2 Warehouse Manager DEPT_TYPE(44, ’Operations’, 4) 1300
...................................................................................................................................................... B-36 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------10 Havel Marta mhavel 27-FEB-91 2 Warehouse Manager DEPT_TYPE(45, ’Operations’, 5) 1307 11 Magee Colin cmagee 14-MAY-90 3 Sales Representative DEPT_TYPE(31, ’Sales’, 1) 1400 10 12 Giljum Henry hgiljum 18-JAN-92 3 Sales Representative DEPT_TYPE(32, ’Sales’, 2) 1490 13
ly n O e s U I A O &
13 Sedeghi Yasmin ysedeghi 18-FEB-91 3 Sales Representative DEPT_TYPE(33, ’Sales’, 3) 1515 10 14 Nguyen Mai mnguyen 22-JAN-92 3 Sales Representative DEPT_TYPE(34, ’Sales’, 4) 1525 15
l a n r e t n I e l c a r O
15 Dumas Andre adumas 09-OCT-91 3 Sales Representative DEPT_TYPE(35, ’Sales’, 5) 1450 18
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-37
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------16 Maduro Elena emaduro 07-FEB-92 6 Stock Clerk DEPT_TYPE(41, ’Operations’, 1) 1400 17 Smith George 08-MAR-90 6 Stock Clerk DEPT_TYPE(41, ’Operations’, 1) 940
gsmith
18 Nozaki Akira 09-FEB-91 7 Stock Clerk DEPT_TYPE(42, ’Operations’, 2) 1200
anozaki
19 Patel Vikram 06-AUG-91 7 Stock Clerk DEPT_TYPE(42, ’Operations’, 2) 795
vpatel
20 Newman Chad 21-JUL-91 8 Stock Clerk DEPT_TYPE(43, ’Operations’, 3) 750
cnewman
21 Markarian Alexander 26-MAY-91 8 Stock Clerk DEPT_TYPE(43, ’Operations’, 3) 850
amarkari
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-38 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------22 Chang Eddie echang 30-NOV-90 9 Stock Clerk DEPT_TYPE(44, ’Operations’, 4) 800 23 Patel Radha 17-OCT-90 9 Stock Clerk DEPT_TYPE(34, ’Sales’, 4) 795
rpatel
24 Dancs Bela 17-MAR-91 10 Stock Clerk DEPT_TYPE(45, ’Operations’, 5) 860
bdancs
25 Schwartz Sylvie 09-MAY-91 10 Stock Clerk DEPT_TYPE(45, ’Operations’, 5) 1100
sschwart
rel_emp_Table_RefCol
l a n r e t n I e l c a r O Name -------------------ID LAST_NAME FIRST_NAME USERID START_DATE MANAGER_ID TITLE
ly n O e s U I A O &
Null? -------------
Type ---------------NUMBER(7) VARCHAR2(25) VARCHAR2(25) VARCHAR2(8) DATE NUMBER(7) VARCHAR2(25)
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-39
Appendix B: Table Descriptions and Data .....................................................................................................................................................
DEPT_ID SALARY COMMISSION_PCT
l a n r e t n I e l c a r O
REF OF DEPT_TYPE NUMBER(11,2) NUMBER(4,2)
ly n O e s U I A O &
...................................................................................................................................................... B-40 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------1 Velasquez Carmen cvelasqu 03-MAR-90 President 0000220208447F54A9ED64676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 2500 2 Ngao LaDoris lngao 08-MAR-90 1 VP Operations 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450 3 Nagayama Midori mnagayam 17-JUN-91 1 VP Sales 0000220208447F54A9ED5A676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400
ly n O e s U I A O &
4 Quick-To-See Mark mquickto 07-APR-90 1 VP Finance 0000220208447F54A9ED59676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450
l a n r e t n I e l c a r O
5 Ropeburn Audry aropebur 04-MAR-90 1 VP Administration 0000220208447F54A9ED64676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1550
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-41
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------6 Urguhart Molly murguhar 18-JAN-91 2 Warehouse Manager 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1200 9 Catchpole Antoinette acatchpo 09-FEB-92 2 Warehouse Manager 0000220208447F54A9ED62676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1300 10 Havel Marta mhavel 27-FEB-91 2 Warehouse Manager 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1307
ly n O e s U I A O &
11 Magee Colin cmagee 14-MAY-90 3 Sales Representative 0000220208447F54A9ED5A676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400 10
l a n r e t n I e l c a r O
13 Sedeghi Yasmin ysedeghi 18-FEB-91 3 Sales Representative 0000220208447F54A9ED5C676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1515 10
...................................................................................................................................................... B-42 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------14 Nguyen Mai mnguyen 22-JAN-92 3 Sales Representative 0000220208447F54A9ED5D676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1525 15 15 Dumas Andre adumas 09-OCT-91 3 Sales Representative 0000220208447F54A9ED5E676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1450 18 16 Maduro Elena emaduro 07-FEB-92 6 Stock Clerk 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1400
ly n O e s U I A O &
17 Smith George gsmith 08-MAR-90 6 Stock Clerk 0000220208447F54A9ED5F676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 940
l a n r e t n I e l c a r O
18 Nozaki Akira anozaki 09-FEB-91 7 Stock Clerk 0000220208447F54A9ED60676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1200
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-43
Appendix B: Table Descriptions and Data .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------19 Patel Vikram vpatel 06-AUG-91 7 Stock Clerk 0000220208447F54A9ED60676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 795 20 Newman Chad cnewman 21-JUL-91 8 Stock Clerk 0000220208447F54A9ED61676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 750 21 Markarian Alexander amarkari 26-MAY-91 8 Stock Clerk 0000220208447F54A9ED61676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 850
ly n O e s U I A O &
22 Chang Eddie echang 30-NOV-90 9 Stock Clerk 0000220208447F54A9ED62676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 800
l a n r e t n I e l c a r O
23 Patel Radha rpatel 17-OCT-90 9 Stock Clerk 0000220208447F54A9ED5D676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 795
...................................................................................................................................................... B-44 Oracle iDS Forms: Build Internet Applications II
Oracle8i Objects: Types, Tables .....................................................................................................................................................
ID LAST_NAME FIRST_NAME USERID ---------- --------------------------------- -------START_DAT MANAGER_ID TITLE --------- ---------- ------------------------DEPT_ID(ID, NAME, REGION_ID) -----------------------------------------------------------SALARY COMMISSION_PCT ---------- -------------24 Dancs Bela bdancs 17-MAR-91 10 Stock Clerk 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 860 25 Schwartz Sylvie sschwart 09-MAY-91 10 Stock Clerk 0000220208447F54A9ED63676AE03408002072C15B447F54A9ED55676AE03408 002072C15B 1100
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II B-45
Appendix B: Table Descriptions and Data .....................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... B-46 Oracle iDS Forms: Build Internet Applications II
................................
Mouse Movement Events
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix C: Mouse Movement Events .....................................................................................................................................................
Introduction Overview You have already seen how to use mouse action triggers and built-ins. This appendix explains how to develop drag-and-drop functionality with Client Server forms applications. Objectives After completing this appendix, you should be able to implement drag-and-drop functionality with Client Server forms.
Drag and Drop
Items in original positions
Item-A highlighted
Items repositioned
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
...................................................................................................................................................... C-2 Oracle iDS Forms: Build Internet Applications II
Introduction .....................................................................................................................................................
Notes Page
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II C-3
Appendix C: Mouse Movement Events .....................................................................................................................................................
Drag and Drop The drag-and-drop functionality is the ability for users to move items around the canvas and arrange them in the order they require. Implementing Drag and Drop Functionality You can use the mouse button action triggers (When-Mouse-Down and When-MouseUp) and the mouse position system variables to implement drag-and-drop functionality in your form module. You need to consider the following when implementing drag and drop: • Changing the appearance of the item you want to drag and drop: Use the SET_ITEM_PROPERTY built-in and a visual attribute. • Changing the appearance of the cursor during the drag and drop process: Use SET_APPLICATION_PROPERTY(cursor_style, ‘value’). • Holding the name of the item being dragged and dropped.: Use a global variable to store the initial value of SYSTEM.MOUSE_ITEM. • Setting the new position, according to the mouse position: Use SET_ITEM_PROPERTY(:global.variablename, position, :SYSTEM.MOUSE_X_POS, :SYSTEM.MOUSE_Y_POS). • Repositioning other items that may be affected.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... C-4 Oracle iDS Forms: Build Internet Applications II
Drag and Drop .....................................................................................................................................................
Summary
In this appendix, you should have learned how to:
•
Drag and Drop Items
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II C-5
Appendix C: Mouse Movement Events .....................................................................................................................................................
Summary In this Appendix, you should have learned how to implement drag-and-drop functionality in your Client Server form applications by using appropriate mouse triggers and system variables. Mouse Triggers • When_Mouse_Move • When_Mouse_Up • When_Mouse_Down Mouse System Varaiables • :SYSTEM.MOUSE_ITEM • SYSTEM.MOUSE_X_POS • :SYSTEM.MOUSE_Y_POS
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... C-6 Oracle iDS Forms: Build Internet Applications II
................................
Introduction to Oracle8i Object Features
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Objectives
After completing this appendix, you should be able to do the following:
• • •
Describe the new Oracle8i scalar data types
• • •
Describe the INSTEAD-OF triggers
Describe object types and objects Describe object tables, object columns, and object views Describe object REFs Identify the display of objects in Object Navigator
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction In this appendix you will review certain object features of Oracle8i. This appendix also explains how these objects are displayed in the Object Navigator.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-3
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
New Oracle8i Scalar Data Types • • • •
NCHAR NVARCHAR2 FLOAT NLS types
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-4 Oracle iDS Forms: Build Internet Applications II
New Oracle8i Data Types ......................................................................................................................................................
New Oracle8i Data Types Scalar Data Types • NCHAR stores fixed-length (blank-padded if necessary) NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as US7ASCII or a variable-width encoding such as JA16SJIS. • NVARCHAR2 stores variable-length NLS character data. How the data is represented internally depends on the national character set, which might use a fixed-width encoding such as WE8EBCDIC37C or a variable-width encoding such as JA16DBCS. • FLOAT is a subtype of NUMBER. However, you cannot specify a scale for FLOAT variables. You can specify only a binary precision, which is the total number of binary digits. There is no change to the way scalar data types are displayed in Oracle Forms Developer. The new data types are automatically converted to existing Oracle Forms Developer item data types. NLS Types Oracle8i offers extended National Language Support (NLS) including national character sets and the data types NCHAR and NVARCHAR2, which store NLS data. With NLS, number and date formats adapt automatically to the language conventions specified for a user session. Thus, users around the world can interact with the Oracle Database Server in their native languages. NLS is discussed in Oracle8i Server Reference Manual.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-5
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Types Attributes
ORDER po_no custinfo line_items amount
Methods Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-6 Oracle iDS Forms: Build Internet Applications II
Object Types ......................................................................................................................................................
Object Types An object type is a user-defined composite data type. You build object types from predefined data types or from previously defined object types. When you create an object type you specify the component types from which it is built. You also specify the functions and procedures that can act on the object type. Thus, an object type is in some ways similar to a record type, and in some ways similar to a package. An object type must have one or more attributes and can contain methods. Attributes An object type is similar to a record type in that it is composed of one or more subparts that are of predefined data types. Record types call these subparts fields, but object types call these subparts attributes. Attributes define the object structure. CREATE TYPE address_type AS OBJECT (address VARCHAR2(30), city VARCHAR2(15), stateCHAR(2), zip CHAR(5)); CREATE TYPE phone_type AS OBJECT (country NUMBER(2), area NUMBER(4), phoneNUMBER(9));
Just as the fields of a record type can be of other record types, the attributes of an object type can be of other object types. Such an object type is called nested. CREATE TYPE address_and_phone_type AS OBJECT (address address_type, phonephone_type);
ly n O e s U I A O &
Object types are like record types in another sense: Both of them must be declared as types before the actual object or record can be declared.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-7
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Types
Ship
ORDER Cancel
Check status
Hold
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-8 Oracle iDS Forms: Build Internet Applications II
Object Types ......................................................................................................................................................
Object Types (continued) Methods An object type is also similar to a package. Once an object is declared, its attributes are similar to package variables. Like packages, object types can contain procedures and functions. In object types, these subprograms are known as methods. A method describes the behavior of an object type. Like packages, object types can be declared in two parts: a specification and a body. Like package variables, attributes declared in the object type specification are public and those declared in the body are private. As with package subprograms, all methods are defined in the package body, but only those whose specification appears in the object type specification are public methods. Here is an example of an object type: CREATE TYPE dept_type AS OBJECT (dept_idNUMBER(2), dnameVARCHAR2(14), loc VARCHAR2(3), MEMBER PROCEDURE set_dept_id (d_id NUMBER), PRAGMA RESTRICT_REFERENCES (set_dept_id, RNDS,WNDS,RNPS,WNPS), MEMBER FUNCTION get_dept_id RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (get_dept_id, RNDS,WNDS,RNPS,WNPS)); CREATE TYPE BODY dept_type AS MEMBER PROCEDURE set_dept_id (d_id NUMBER) IS BEGIN dept_id := d_id; END; MEMBER FUNCTION get_dept_id RETURN NUMBER IS BEGIN RETURN (dept_id); END; END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-9
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Tables
Object table based on object type
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-10 Oracle iDS Forms: Build Internet Applications II
Creating Oracle8i Objects ......................................................................................................................................................
Creating Oracle8i Objects Introduction After you have declared an object type, you can create objects based on the type. Object Tables One way to create an object is to create a table whose rows are objects of that object type. Here is an example of an object table declaration: CREATE TABLE o_dept OF dept_type;
SQL and PL/SQL treat object tables very similarly to relational tables, with the attribute of the object corresponding to the columns of the table, but there are significant differences. The most important difference is that rows in an object table are assigned object IDs (OIDs) and can be referenced by using a REF type. Note: REF types are reviewed later.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-11
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Columns
Object column based on object type
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-12 Oracle iDS Forms: Build Internet Applications II
Object Columns ......................................................................................................................................................
Object Columns Another construct that you can base on an object type is an object column in a relational table. Here is an example of a relational table creation statement with an object column: CREATE TABLE o_customer ( custid NUMBER (6) NOT NULL, name VARCHAR2 (45), repid NUMBER (4) NOT NULL, creditlimit NUMBER (9,2), address address_type, phone phone_type);
In the object table, the rows of a table are objects. In a relational table with an object column, the column is an object. The table will usually have standard columns, as well as one or more object column. Object columns are not assigned object IDs (OIDs), and therefore cannot be referenced by using object REF values. Note: Object REFs are reviewed later in this section.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-13
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Views
Object-oriented application
Relational table
Object view
Object views based on object types
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-14 Oracle iDS Forms: Build Internet Applications II
Object Views ......................................................................................................................................................
Object Views Often the most difficult part of adopting a new technology is the conversion process itself. For example, a large enterprise might have several applications accessing the same data stored in relational tables. If such an enterprise decided to start using objectrelational technology, it would not convert all of the applications at once, but would convert the applications one at a time. This presents a problem. The applications that have been converted need the data stored as objects, while the applications that have not been converted need the data stored in relational tables. This dilemma is addressed by object views. Like all views, an object view transforms the way a table appears to a user, without changing the actual structure of the table. Object views make relational tables look like object tables. This enables developers to postpone converting the data from relational structures to object-relational structures until after all of the applications have been converted. During the conversion process, the object-relational applications can operate against the object view, while the relational applications can continue to operate against the relational tables. Objects accessed through object views are assigned Object IDs (OIDs) and can be referenced by using Object REFs. Note: Object REFs are reviewed later in this section. Here is an example of an object view creation statement: CREATE VIEW emp_view OF emp_type WITH OBJECT OID (eno) AS SELECT e.empno, e.ename, e.sal, e.job FROM emp e;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-15
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
INSTEAD-OF Triggers
DECLARE BEGIN EXCEPTION END; Nonupdatable view
INSTEAD-OF trigger
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-16 Oracle iDS Forms: Build Internet Applications II
INSTEAD-OF Triggers ......................................................................................................................................................
INSTEAD-OF Triggers INSTEAD-OF triggers provide a transparent way of modifying views that cannot be modified directly through SQL DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD-OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger performs update, insert, or delete operations directly on the underlying tables. Users write normal INSERT, DELETE, and UPDATE statements against the view, and the INSTEAD-OF trigger works invisibly in the background to make the right actions take place. INSTEAD-OF triggers are activated for each row. Note: Although you can use INSTEAD-OF triggers with any view, they are typically required with object views.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-17
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
References to Objects OID
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-18 Oracle iDS Forms: Build Internet Applications II
Referencing Objects ......................................................................................................................................................
Referencing Objects Introduction In relational databases, primary-key values are used to uniquely identify records. In object-relational databases, OIDs provide an alternate method. When a row in an object table or object view is created, it is assigned automatically a unique identifier called an object ID (OID). Object REFs With relational tables, you can associate two records by storing the primary key of one record in one of the columns (the foreign-key column) of another. In a similar way, you can associate a row in a relational table to an object by storing the OID of an object in a column of a relational table. Or you can associate two objects by storing the OID of one object in an attribute of another. The stored copy of the OID then becomes a pointer, or reference (REF), to the original object. The attribute or column that holds the OID is of data type REF. Note: Object columns are not assigned OIDs and cannot be pointed to by a REF. Here is an example of a table declaration that includes a column with a REF data type: CREATE TABLE o_emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(10), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), dept REF dept_type SCOPE IS o_dept) ;
ly n O e s U I A O &
Note: The REF is scoped here to restrict the reference to a single table, o_dept. The object itself is not stored in the table; only the OID value for the object is stored there.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-19
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Types in Object Navigator
Copyright Oracle Corporation, 2001. All rights reserved.
®
Object Type Wizard
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... D-20 Oracle iDS Forms: Build Internet Applications II
Displaying Oracle8i Objects in the Object Navigator ......................................................................................................................................................
Displaying Oracle8i Objects in the Object Navigator In the Database Objects section, the Object Navigator lists declared types, along with tables, views, and other Oracle objects. Object Types Both the attributes and the methods are listed under each type. Additionally, the nested types within address_and_phone_type are displayed in an indented sublevel. This convention is used for nested object and object type displays throughout Oracle Forms Developer. Oracle8i Type Wizard You can create object types by using the Oracle8i Type Wizard, which enables you to define attributes and methods.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-21
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Tables in Object Navigator
Copyright Oracle Corporation, 2001. All rights reserved.
®
Object Columns in Object Navigator
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... D-22 Oracle iDS Forms: Build Internet Applications II
Displaying Oracle8i Objects in the Object Navigator ......................................................................................................................................................
Object Tables Object tables are displayed like relational tables, with the attributes of the object displayed like columns in a relational table. Additionally, the object table type name is displayed in parentheses after the name of the object table. Object Columns Object columns are displayed with the object type in parentheses after the column name, and with the attributes of the type indented underneath the column name.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-23
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object Views in Object Navigator
Copyright Oracle Corporation, 2001. All rights reserved.
®
INSTEAD-OF Trigger Dialog Box
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... D-24 Oracle iDS Forms: Build Internet Applications II
Displaying Oracle8i Objects in the Object Navigator ......................................................................................................................................................
Object Views Object views are displayed like any other view, except that the object type they are based on is written in parentheses after the view name. INSTEAD-OF Triggers You can create INSTEAD-OF database triggers through the trigger creation dialog box, just as you would any other database trigger. INSTEAD-OF INSERT, UPDATE, and DELETE triggers allow you to directly insert, update, and delete against object views. They can also be used with any other type of view that does not allow direct DML. When a view has an INSTEAD-OF trigger, the code in the trigger is executed in place of the triggering DML code. Reference For more information about INSTEAD-OF triggers, see: • Oracle8i Server SQL Reference Manual • Oracle8i Concepts Manual
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-25
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Object REFs in Object Navigator
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... D-26 Oracle iDS Forms: Build Internet Applications II
Displaying Oracle8i Objects in the Object Navigator ......................................................................................................................................................
Object REFs Object types that contain attributes of type REF, and relational tables that have columns of type REF, display the keyword REF before the name of the object type that is being referenced. The attributes of the referenced object type are displayed indented underneath the column or attribute.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-27
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
Summary
In this appendix, you should have learned how:
• •
Oracle8i introduced three new scalar datatypes.
•
Three kinds of objects are object tables, object columns, and object views.
Objects and object types allow representation of complex data.
Copyright Oracle Corporation, 2001. All rights reserved.
®
Summary
In this appendix, you should have learned how to:
•
INSTEAD-OF triggers allow DML on object views.
•
Object REFs store the object identifier of certain types of objects.
•
The Object Navigator can display certain types of objects.
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... D-28 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary New Oracle8i Data Types Oracle8i introduced three new scalar data types and new composite data types such as object types. Objects Three kinds of objects are object tables, object columns, and object views. INSTEAD-OF triggers allow DML on object views. Object REFs store the Object Identifier of certain types of objects. Oracle8i Objects In the Object Navigator The Object Navigator can display certain types of objects.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II D-29
Appendix D: Introduction to Oracle8i Object Features ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... D-30 Oracle iDS Forms: Build Internet Applications II
................................
Menu Run-Time Parameters
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Objectives
After completing this appendix, you should be able to customize menu modules using substitution parameters.
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... E-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview In this lesson, you will learn how to modify menus dynamically, as well as how to control application security through menu access. Objectives After completing this lesson, you should be able to customize menu modules by using substitution parameters.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-3
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Built-in Substitution Parameters
UN
Current username
PW
Current password
AD
Directory name
SO
Current menu name
TT
Terminal type
LN
Language used
Copyright Oracle Corporation, 2001. All rights reserved.
®
User-Named Substitution Parameters
Design time
ly n O e s U I A O &
Run time
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... E-4 Oracle iDS Forms: Build Internet Applications II
Substitution Parameters ......................................................................................................................................................
Substitution Parameters What Is a Substitution Parameter? A substitution parameter is a two-character variable of type CHAR that is referenced in a menu item command or in a PL/SQL program unit, and substituted with a value at run time. A menu module provides some predefined parameters, which automatically exist in every menu module. You can also create in Form Builder (valid for a given menu module) your own user-named parameters. Predefined Substitution Parameters You can use six predefined (built-in) substitution parameters. Form Builder automatically processes them at run time. Parameter UN PW AD SO TT LN
Description Current username Current password Directory name containing the current menu module run-time file Current menu name (selected option) Terminal type used at connection Language used (NLS_LANG parameter)
User-Named Substitution Parameters User-named substitution parameters are processed at run time by the user through a dialog box. Alternatively, they can be processed programmatically through built-in procedures. Note: Substitution parameter values can be referenced in triggers and userdefined subprograms.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-5
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Parameter Properties
- Name - Label - Case Restriction - Conceal Data - Associated Menus
- Maximum Length - Menu Parameter Initial Value - Required - Hint
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... E-6 Oracle iDS Forms: Build Internet Applications II
Defining a User-Named Substitution Parameter ......................................................................................................................................................
Defining a User-Named Substitution Parameter Creating a User-Named Substitution Parameter You can create a user-named substitution parameter from the Object Navigator: 1 Select the Parameters node. 2 Create a new parameter by clicking the Create icon. Forms displays a new parameter. 3 Set the desired properties for the parameter. Property Name Label
Description The two-letter parameter name The text prompt for the parameter value that is displayed in the Enter Parameter Values dialog box Case Restriction The property that converts the case of the user’s input to either uppercase, lowercase, or none Conceal Data The property that determines whether the user’s input is echoed on the screen Associated Menus All menu names where the parameter can be used (full-screen menus only) Maximum Length Value maximum length Menu Parameter Initial Value The default value for the parameter; displayed in the parameter value field when the Enter Parameter Values dialog box is displayed Required Mandatory or not; set Yes if the value is required or No to allow the parameter value to be set to Null Hint The text string displayed on the message line of the console
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-7
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Substitution Parameter Built-ins • •
QUERY_PARAMETER APPLICATION_PARAMETER
Copyright Oracle Corporation, 2001. All rights reserved.
®
Referencing and Applying Menu Parameters •
In a menu item command: – prefix the parameter name with & Example: ¶m_name
•
In a PL/SQL routine:
ly n O e s U I A O &
– Prefix the parameter name with & when entered through a dialog box – Prefix the parameter name with : when entered in the PL/SQL editor – Example: :param_name
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... E-8 Oracle iDS Forms: Build Internet Applications II
Substitution Parameter Built-ins ......................................................................................................................................................
Substitution Parameter Built-ins Prompting for Substitution Parameter Values Built-in QUERY_PARAMETER
Description Displays the Query Parameter dialog box showing the current values of the specified substitution parameters (End users can set the value of any parameter you include in the list.) APPLICATION_PARAMETER Displays all the parameters associated with the current menu, and their current values, in the Enter Parameter Values dialog box
Syntax for Referencing Substitution Parameters The syntax for referencing substitution parameters varies depending on how you enter the value. Once assigned, the values of menu substitution parameters can be referenced in triggers and user-named subprograms defined in the current form, provided that the menu in which the parameters are defined is the current menu. Referencing a Parameter in a Menu Item Command Prefix the parameter name with an ampersand (&). Example This example invokes SQL*Plus and runs a command file (CF parameter). PLUS80W &UN/&PW @&CF
ly n O e s U I A O &
Referencing a Parameter in a PL/SQL Routine Prefix the parameter name with an ampersand (&) if the value is entered through the Enter Parameter Values dialog box. Prefix the parameter name with a colon (:) if the value is entered programmatically. Example This example invokes Report Builder and enables the user to provide the report name.
l a n r e t n I e l c a r O
Query_Parameter(’&RN’); Run_Product(reports, :RN, synchronous, runtime, filesystem, pl_id, null);
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-9
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Validating Substitution Parameter Values MENU_SUCCESS function:
•
Returns TRUE if the user does not cancel the Enter Parameter Values dialog box
•
Is used in a WHILE loop
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... E-10 Oracle iDS Forms: Build Internet Applications II
Validating a Substitution Parameter Value ......................................................................................................................................................
Validating a Substitution Parameter Value You can validate the user-entered substitution parameter values by using a WHILE loop including the QUERY_PARAMETER procedure and testing the MENU_SUCCESS function status. DECLARE Status BOOLEAN:= TRUE; BEGIN WHILE Status LOOP QUERY_PARAMETER(’&p1’); IF MENU_SUCCESS THEN IF TO_NUMBER(:p1) not between 100 and 5000 THEN MESSAGE(’Qty must be in the range 100..5000’); BELL; ELSE Status := FALSE; END IF; END IF; END LOOP; END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-11
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
Summary
In this lesson, you should have learned how to customize menu modules using substitution parameters.
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... E-12 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary Substitution Parameters • Using &UN, &PW, &AD, &SO, &TT, and &LN • Creating user-named substitution parameters • Programmatically controlling substitution parameters with QUERY_PARAMETER and APPLICATION_PARAMETER
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II E-13
Appendix E: Menu Run-Time Parameters ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... E-14 Oracle iDS Forms: Build Internet Applications II
................................
Handling ServerSideErrors
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Objectives
After completing this appendix, you should be able to do the following:
• •
Handle server-side database errors Obtain the cause of declarative-constraint violations
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... F-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview This lesson covers the use of Oracle server features in Form Builder applications. You will learn about handling Oracle server errors, including declarative-constraint violations. Objectives At the end of this lesson, you should be able to: • Handle errors raised by the Oracle server • Obtain the cause of declarative-constraint violations
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-3
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Obtaining the Cause of DeclarativeConstraint Violation •
Predefined error messages for declarative constraints: – Primary/unique key; foreign key; check – ORA-02291: integrity constraint (schema.constraint) violated parent key not found
•
Obtaining the name of violated constraint: – Function STRIP_CONSTRAINT_NAME
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... F-4 Oracle iDS Forms: Build Internet Applications II
Obtaining the Cause of Declarative-Constraint Violations ......................................................................................................................................................
Obtaining the Cause of Declarative-Constraint Violations To handle declarative-constraint violations effectively, you must use the predefined error message to determine the constraint that was violated. Predefined Error Messages for Declarative Constraints Constraint Primary Key Foreign Key
Check
Error Message ORA-00001: unique constraint (schema.constraint) violated Unique Key ORA-02291: integrity constraint (schema.constraint) violated—parent key not found ORA-02292: integrity constraint (schema.constraint) violated—child record found ORA-02290: check constraint (schema.constraint) violated
Obtaining the Name of the Violated Constraint You can use string functions, such as the following, to strip out the name of the violated constraint from the Oracle Server error message. FUNCTION strip_constraint_name (p_error_text in VARCHAR2) RETURN VARCHAR2 IS v_start_pos NUMBER(4); v_end_pos NUMBER(4); BEGIN v_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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-5
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Customizing Oracle Server Error Messages •
Replacing declarative-constraint error messages: – Strip the constraint name from DBMS_ERROR_TEXT or SQLERRM – Retrieve the customized message from the messages table
Copyright Oracle Corporation, 2001. All rights reserved.
®
Customizing Oracle Server Error Messages •
Replacing database-trigger error messages: – Strip the application error messages from DBMS_ERROR_TEXT or SQLERRM
ly n O e s U I A O &
– Function STRIP_APPLICATION_ERROR
•
Replacing stored-program-unit error messages: – Same as for database triggers, except SQLERRM only
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note A foreign-key constraint violation can have two causes: “parent key not found” or “child record found.” Therefore, two error messages are associated with one foreign-key constraint name.
...................................................................................................................................................... F-6 Oracle iDS Forms: Build Internet Applications II
Customizing Oracle Server Error Messages ......................................................................................................................................................
Customizing Oracle Server Error Messages Replacing Error Messages Caused by Declarative-Constraint Violations The error is reported in DBMS_ERROR_TEXT (implicit DML) or SQLERRM (explicit DML). Once you have obtained the name of the violated constraint from the Oracle server error message, you can use this name to specify an appropriate customized message. A flexible approach for this is to maintain a table of messages. You can then use the constraint name to retrieve the corresponding message from this table. Replacing Error Messages Caused by Database Triggers The error is reported in DBMS_ERROR_TEXT (implicit DML) or SQLERRM (explicit DML). The first part of the error message is the message specified in RAISE_APPLICATION_ERROR in the database trigger, which is already a customized message. You can strip out this application-error message from the Oracle server error message by using the following function: FUNCTION strip_application_error (p_error_text in VARCHAR2) RETURN VARCHAR2 IS v_end_pos NUMBER(4); BEGIN v_end_pos := INSTR(p_error_text, ’ORA-’, 5) - 2; IF v_end_pos > 0 THEN RETURN(SUBSTR(p_error_text, 12, v_end_pos - 11)); ELSE RETURN(SUBSTR(p_error_text, 12)); END IF; END;
ly n O e s U I A O &
Replacing Error Messages Caused by Stored Program Units The error is reported in SQLERRM only. The customized message is obtained in the same way as for database triggers.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-7
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Example Procedure for Handling Oracle Server Errors •
If the Oracle server error is a declarative-constraint error: – Strip the constraint name from the Oracle server error text. – Adjust the name of the foreign-key constraints. – Retrieve the message from the messages table.
Copyright Oracle Corporation, 2001. All rights reserved.
®
Example Procedure for Handling Oracle Server Errors •
If the Oracle server error is an application error: – Strip the application error from the Oracle server error text.
ly n O e s U I A O &
– Raise FORM_TRIGGER_FAILURE.
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... F-8 Oracle iDS Forms: Build Internet Applications II
Example Procedure for Handling Oracle Server Errors ......................................................................................................................................................
Example Procedure for Handling Oracle Server Errors PROCEDURE handle_server_error (p_server_error_code IN NUMBER, p_server_error_text IN VARCHAR2) IS v_message_id VARCHAR2(255); BEGIN -- handle_server_error IF 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;
Note: The STRIP_CONSTRAINT_NAME and STRIP_APPLICATION_ERROR functions have already been shown earlier in this lesson. The RETRIEVE_MESSAGE function retrieves a message with a given ID from a messages table. Call the HANDLE_SERVER_ERROR procedure from the appropriate triggers and program units.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-9
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Example Procedure for Handling Oracle Server Errors •
Handle errors caused by implicit DML On-Error trigger on form: IF ERROR_TYPE = ¢FRM’ AND ERROR_CODE IN (40508, 40509, 40510) THEN handle_server_error(DBMS_ERROR_CODE, DBMS_ERROR_TEXT); END IF;
•
Handle errors caused by explicit DML or stored Program Units: EXCEPTION EXCEPTION WHEN WHEN OTHERS OTHERS THEN THEN handle_server_error(SQLCODE, handle_server_error(SQLCODE, SQLERRM); SQLERRM); Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note Error code 40508 is insert failed. Error code 40509 is update failed. Error code 40510 is delete failed.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... F-10 Oracle iDS Forms: Build Internet Applications II
Example Procedure for Handling Oracle Server Errors ......................................................................................................................................................
Handling Oracle Server Errors Caused by Implicit DML Always define an On-Error trigger to handle Oracle server errors caused by base table DML. On-Error Trigger at Form Level BEGIN IF 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;
Handling Oracle Server Errors Caused By Explicit DML or Stored Program Unit Calls Define a WHEN OTHERS exception handler in all triggers and program units that execute explicit DML or call stored program units. BEGIN /* DML statements or calls to stored program units. */ EXCEPTION WHEN others THEN handle_server_error(SQLCODE, SQLERRM); END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-11
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
Summary
In this lesson, you should have learned how to:
•
Replacing Oracle Server error messages with customized messages – Constraint: strip constraint name and retrieve message – DB triggers and stored program units: strip application error
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... F-12 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary Handling Errors Raised by the Oracle Server • Causes: Declarative constraints, database triggers, stored program units • Trap implicit-DML errors by using DBMS_ERROR_CODE and DBMS_ERROR_TEXT in an On-Error trigger • Trap explicit DML errors and stored program unit errors by using SQLCODE 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 error message from the Oracle Server error message
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II F-13
Appendix F: Handling Server-SideErrors ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... F-14 Oracle iDS Forms: Build Internet Applications II
................................
EMP_PKG Package
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix G: 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.EMPNO TYPE empnorec IS RECORD ( empno emp.empno%TYPE);
ly n O e s U I A O &
--- Define the Ref Cursor TYPE empcur IS REF CURSOR RETURN emprec;
--- Defines the Table of Records EMP TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;
l a n r e t n I e l c a r O
--- Defines the Table of Records EMP.EMPNO TYPE empnotab IS TABLE OF empnorec INDEX BY BINARY_INTEGER;
...................................................................................................................................................... G-2 Oracle iDS Forms: Build Internet Applications II
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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II G-3
Appendix G: EMP_PKG Package ......................................................................................................................................................
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; . . .
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... G-4 Oracle iDS Forms: Build Internet Applications II
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; . . .
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II G-5
Appendix G: EMP_PKG Package ......................................................................................................................................................
/**********/ /* 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; . . .
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... G-6 Oracle iDS Forms: Build Internet Applications II
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).deptno WHERE empno = block_data(i).empno; END LOOP; END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II G-7
Appendix G: EMP_PKG Package ......................................................................................................................................................
/**********/ /* 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; BEGIN cnt := block_data.count; FOR i IN 1..cnt LOOP SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO block_rec FROM emp WHERE empno = block_data(i).empno FOR UPDATE OF ename NOWAIT; END LOOP; END; END;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... G-8 Oracle iDS Forms: Build Internet Applications II
Package Body ......................................................................................................................................................
To have an idea of how Form Builder works when a data block is based on a stored procedure returning a REF CURSOR, you can run the following script 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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II G-9
Appendix G: EMP_PKG Package ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... G-10 Oracle iDS Forms: Build Internet Applications II
................................
Working with ActiveX Controls
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Objectives
After completing this appendix, you should be able to do the following:
•
Explain the differences between VBX, OCX, and ActiveX controls
•
Access ActiveX properties at design time and run time
•
Import ActiveX control methods and event packages
•
Customize event handling
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-2 Oracle iDS Forms: Build Internet Applications II
Introduction ......................................................................................................................................................
Introduction Overview In this appendix, you will learn how to include and use ActiveX controls in your Oracle Forms Developer application. Objectives After completing this lesson, you should be able to do the following: • 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
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-3
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
What Are VBX, OCX and ActiveX Controls? • •
•
VBX – Custom Interface Object – Special dynamic link library OCX – Independent program module – Run in any Windows application that is OLE-enabled ActiveX – Third version of OCX – Provide integrated controls in Browsers
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-4 Oracle iDS Forms: Build Internet Applications II
What Are VBX, OCX, and ActiveX Controls? ......................................................................................................................................................
What Are VBX, OCX, and ActiveX Controls? VBX, OCX, and ActiveX controls provide encapsulated functionality that you can embed into your application. Instead of one monolithic application that provides you with almost every feature that you want, component objects and good integration can provide you with a light-weight custom application to suit all your needs. Word processor documents, spreadsheet documents, knob controls, and video clips are examples of component objects that can enhance the capabilities of your form. Instead of re-creating functionality that already exists, you can access these capabilities by exploiting a VBX, an OCX, or an ActiveX control. What Is a VBX? Short for Visual Basic custom control, a VBX is a reusable software component designed for use in many different applications. While VBXs can be used in other environments, they were initially created for developing Windows applications with Visual Basic. An application developer can use a number of selected VBXs to quickly develop an application. What Is an OCX? Short for OLE Custom control, an OCX is an independent program module that can be accessed by other programs in a Windows environment. OCX controls end with the .ocx extension. OCX controls represent Microsoft’s second generation of control architecture, the first being VBX controls written in Visual Basic.
ly n O e s U I A O &
What Is an ActiveX? ActiveX controls are a superset of OLE Custom control (OCX). ActiveX is an open platform that combines desktop and Web technologies. An ActiveX control can be automatically downloaded and executed by a Web browser. ActiveX is not a programming language, but rather a set of rules for how applications should share information. Programmers can develop ActiveX controls in a variety of languages, including C, C++, Visual Basic, and Java. They represent Microsoft’s third generation of control architecture, the first being VBX controls and the second OCX controls.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-5
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
VBX, OCX, and ActiveX Controls Comparison VBX Controls
OCX Controls
ActiveX Controls
Operating “platform”
16-bit applications only
Both 16- and 32-bit platforms
Candidate applications
Only those written with a specific interface to VBXs
Any application with an OLE container type
Coding interface
Proprietary
Standard OLE2
Webenabled
No
No
Yes
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-6 Oracle iDS Forms: Build Internet Applications II
Comparison of VBX, OCX, and ActiveX Controls ......................................................................................................................................................
Comparison of VBX, OCX, and ActiveX Controls A VBX is an extension of Microsoft Visual Basic and is designed to work within 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 to run in any Windows application that is OLE-enabled. OCX controls support 16- and 32-bit platforms, can execute events, and give the developer the ability to code methods and properties. ActiveX controls are the third version of OLE controls (OCX). This means that all OCX controls are ActiveX controls. They provide a number of enhancements specifically designed to facilitate distribution of components over high-latency networks and to provide integration of controls into Web browsers. You cannot, however, deploy ActiveX controls with Web forms. The table below summarizes some of the differences among VBX controls, OCX controls, and ActiveX controls.
Operating platform
Candidate applications Coding interface Web-enabled
VBX Controls
OCX Controls
ActiveX Controls
16-bit applications only
Both 16- and 32-bit platforms
Both 16- and 32-bit platforms
Only those written with a specific interface to VBXs
Any application with an OLE container type
Any application with an OLE container type
Proprietary
Standard OLE2
Standard OLE2
No
No
Yes
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-7
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
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
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-8 Oracle iDS Forms: Build Internet Applications II
ActiveX in Form Builder ......................................................................................................................................................
ActiveX in Form Builder What Are ActiveX Controls? Form Builder provides support for ActiveX controls. ActiveX controls are reusable components that you can embed in your Form Builder application. Form Builder acts as an ActiveX control container. ActiveX controls are a special type of dynamic library. A single ActiveX file that is distributed with the .ocx extension can contain more than one class of ActiveX objects. ActiveX Control Components Each 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 and obtaining values from its properties, invoking methods that are used to perform actions, and intercepting and acting on events. ActiveX Control Item The ActiveX Control Item type creates a container for your ActiveX control. An ActiveX control is not a separate application, but a server that plugs into an ActiveX container. It is recommended that you use ActiveX controls in blocks with the Single Record property set to Yes. The Single Record property is not applicable to multiple record blocks.You should set the Single Record property to Yes because single records are immediately initialized when Forms run time starts up. For multiple records, each record is not initialized until you navigate to the record. Without initialization, the ActiveX Control Item is empty, giving the impression that no ActiveX control is available.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-9
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Implementing 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.
Copyright Oracle Corporation, 2001. All rights reserved.
®
Creating an ActiveX Control Item
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... H-10 Oracle iDS Forms: Build Internet Applications II
Implementing an ActiveX Control ......................................................................................................................................................
Implementing an ActiveX Control Using an ActiveX Control in Form Builder To 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 the ActiveX control. Creating an ActiveX Control Item You 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.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-11
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Inserting an ActiveX Control In the Layout Editor: 1. Select the ActiveX control item. 2. Click the right mouse button. 3. Select Insert Object in the pop-up menu. 4. Select a control type.
Copyright Oracle Corporation, 2001. All rights reserved.
®
ActiveX Control Properties
Set the following ActiveX Control properties:
ly n O e s U I A O &
OLE OLE Class Class Control Control Properties Properties About About Control Control Control Control Help Help
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note If you have created a container using the OCX button from the Layout Editor, then Create Control is the only available option on the Insert Object dialog box.
...................................................................................................................................................... H-12 Oracle iDS Forms: Build Internet Applications II
Inserting an ActiveX Control in an ActiveX Control Item ......................................................................................................................................................
Inserting an ActiveX Control in an ActiveX Control Item Inserting an ActiveX Control 1 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 to display the valid control types. 5 Click a control type. 6 Click OK. ActiveX Control Properties Once the ActiveX control is inserted, you can set the following properties: Property Name
Description
OLE Class
Determines what class of OLE objects can reside in an OLE container. (You select a specific class if you want to create an application that allows end users to change the current OLE object in the OLE container, but want to restrict the end users to creating OLE objects 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 feature might not be exposed by the control that you are using.)
Control Help
Displays online Help for the control (Please note that this feature might not be exposed by the control that you are using.)
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-13
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Importing ActiveX Methods and Events Packages 1 •
Select Program
OLE Importer
2
6 - Program Units + + +
3 4
PackageName1 (Spec) PackageName1 (Body) ...
5 Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-14 Oracle iDS Forms: Build Internet Applications II
Importing ActiveX Control Methods and Events Packages ......................................................................................................................................................
Importing ActiveX Control Methods and Events Packages ActiveX controls export interfaces that expose methods and events. Form Builder imports the methods and events in PL/SQL, enabling you to interact with ActiveX controls in the native Form Builder environment. Importing ActiveX Control Methods and Events 1 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 Control-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 for properties. 6 Click OK. In the Object Navigator, notice the newly created packages under the Program Units node.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-15
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Interacting with an ActiveX Control
You can interact for:
• • •
Setting and getting ActiveX control properties Invoking ActiveX control methods Responding to ActiveX control events
Copyright Oracle Corporation, 2001. All rights reserved.
®
Setting and Getting ActiveX Control Properties Three methods:
•
Bind variable Syntax
ly n O e s U I A O &
:Item( :Item(′item_name’).OCX.server_name.property ′item_name’).OCX.server_name.property Example
:item( :item(′ActXitem’).OCX.Spindial.spindialctrl ′ActXitem’).OCX.Spindial.spindialctrl .1.Needleposition:=:item(’ActXitem’).OCX. .1.Needleposition:=:item(’ActXitem’).OCX. Spindial.spindialctrl.1.Needleposition Spindial.spindialctrl.1.Needleposition ++ 1; 1;
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... H-16 Oracle iDS Forms: Build Internet Applications II
Setting and Getting ActiveX Control Properties ......................................................................................................................................................
Setting and Getting ActiveX Control Properties Once the ActiveX control is inserted and the methods and events packages are imported, 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 Syntax You can use the bind variable syntax to assign or retrieve property values. :item(’item_name’).ocx.server_name.property
Server_name represents the name of the server. This name is the one you have selected for the OLE Class property. Property is the name of a specific property of the ActiveX control. To obtain more information, see the Control Help property, if available, or the documentation. /* Setting an ActiveX control property using bind variable syntax */ :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 ActiveX control server, and Needleposition is the name of the property. */ /* The following code also works if your system.cursor_item is an ActiveX control: */ :form.cursor_item.OCX.spindial.spindialctrl.1.Needlposition := :form.cursor_item.OCX.spindial.spindialctrl.1.Needlposition + 1;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-17
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Setting and Getting ActiveX Control Properties •
Property accessors (PL/SQL procedures)
tblname varchar2; tblname := table_pkg.TableName( :item(’Oblk.Oitm’).interface);
Copyright Oracle Corporation, 2001. All rights reserved.
®
Setting and Getting ActiveX Control Properties •
Control properties dialog box – Displays ActiveX-specific properties – See Control Help property or documentation
l a n r e t n I e l c a r O
ly n O e s U I A O &
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... H-18 Oracle iDS Forms: Build Internet Applications II
Setting and Getting ActiveX Control Properties ......................................................................................................................................................
Property Accessors (PL/SQL Procedures) You can set and get property values with the program units, called property accessor functions and property accessor procedures, generated from the OLE Importer. Use the property accessor functions to get a property value, and use the property accessor procedures to set a property value. /* 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 property accessor. Oblk is the name of the block and Oitm is the name of the item.*/
Control Properties Dialog Box from the Property Palette 1 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 Properties dialog box appears. 3 Set the control properties accordingly. 4 Click OK.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-19
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Invoking ActiveX Control Methods
MMVX_DMMVX.PLAY(:item MMVX_DMMVX.PLAY(:item ((′video_ocx.video ′video_ocx.video′).INTERFACE); ′).INTERFACE); Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-20 Oracle iDS Forms: Build Internet Applications II
Invoking ActiveX Control Methods ......................................................................................................................................................
Invoking ActiveX Control Methods Methods define actions that the ActiveX control can perform. How to Invoke a Method You can access methods exposed by an ActiveX control with the program units generated from the OLE Importer. Call any of the methods in any of the packages available under the Program Units node. To obtain information on a specific method of an ActiveX control, see the documentation of this ActiveX control or, if available, the Control Help property. Example /* Getting a cell value from a Spread Table ActiveX control using the GetCellByColRow method provided in the SpreadTable package */ DECLARE Cur_Row number; Cur_Col number; OLEObj OleObj; BEGIN Cur_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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-21
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Responding to ActiveX Control Events
Two ways to deal with events:
•
Directly call the appropriate restricted procedure.
PROCEDURE /*Click*/ event4294966696 (interface OleObj); BEGIN /* Write your code here */ END;
•
Code the On-Dispatch-Event trigger. Copyright Oracle Corporation, 2001. All rights reserved.
®
Responding to ActiveX Control Events
All event procedures are restricted.
•
To run in unrestricted mode, use:
FORMS4W.DISPATCH_EVENT (RESTRICTED_UNALLOWED); Initialization events do not fire.
•
ly n O e s U I A O &
Use the When-New-Item-Instance or When-New-Record-Instance triggers in place.
l a n r e t n I e l c a r O
Copyright Oracle Corporation, 2001. All rights reserved.
®
...................................................................................................................................................... H-22 Oracle iDS Forms: Build Internet Applications II
Responding to ActiveX Control Events ......................................................................................................................................................
Responding to ActiveX Control Events ActiveX controls invoke events. Form Builder exposes these events in the events package created from the OLE Importer. The events are defined by the ActiveX control. Each event is associated with a PL/SQL procedure defined in the events package. The procedures are named according to an internal numeric representation as defined by the control. When the control fires an event, the code in the procedure is automatically executed. You are responsible for providing the code to respond to the event. Type directly into the event package body and compile the package. The restricted procedure produced by an event has an application programming interface similar to the following: PROCEDURE /*Click*/ event4294966696(interface OleObj);
Another way to deal with the event is to add code to the On-Dispatch-Event trigger. Restricted or Unrestricted Mode All event procedures are restricted; that is, go_item cannot be called from within the procedure code. However, there are instances when the same event may apply to multiple items and a go_item is necessary; this is the unrestricted case. In the unrestricted case, you need to do the following: In the On-DispatchEvent Trigger (block or form level), call: FORMS4W.DISPATCH_EVENT(RESTRICTED_ALLOWED); FORMS4W.DISPATCH_EVENT(RESTRICTED_UNALLOWED);
ly n O e s U I A O &
There is no need to explicitly call the event procedure because it will automatically be called following the On-Dispatch trigger code.
Initialization Events Initialization events for ActiveX controls do not fire in Forms run time. These initialization events are intentionally disabled. Instead, you can use When-New-Item-Instance or When-New-Record-Instance triggers in place of the control’s native initialization events.
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-23
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Handling Exceptions •
When a failure occurs, the FORMS_OLE_FAILURE is raised.
•
Use LAST_OLE_EXCEPTION built-in to get more detailed information.
. . . WHEN FORMS_OLE_FAILURE THEN ExceptNum :=LAST_OLE_EXCEPTION(errScr, errDescription, errHelpfile, errHelpContext); . . .
Copyright Oracle Corporation, 2001. All rights reserved.
®
Technical Note The LAST_OLE_EXCEPTION built-in takes variables, not constants.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... H-24 Oracle iDS Forms: Build Internet Applications II
Handling Exceptions ......................................................................................................................................................
Handling Exceptions FORMS_OLE_FAILURE Exception When a failure is detected in handling an ActiveX control method, an FRM message appears or the FORMS_OLE_FAILURE exception is raised. When a FORMS_OLE_FAILURE exception is raised, you can get more detailed information regarding the cause of the exception by calling the LAST_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;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-25
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
Registering an ActiveX Control •
Two methods to register: – Implicitly register using the installation program. – Use regsvr32.exe explicitly.
•
ActiveX controls require supporting DLL: – Must be in \Windows\System or search path
•
Reregister the ActiveX control if you move it or rename its directory.
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-26 Oracle iDS Forms: Build Internet Applications II
Registering an ActiveX Control ......................................................................................................................................................
Registering an ActiveX Control You must register all ActiveX controls. If you install an ActiveX control by using the installation program supplied with the ActiveX control, the registration is most likely taken care of by the installation program. For manual registration of ActiveX controls, use regsvr32.exe; this program is available with Microsoft development tools and from ActiveX control vendors. Supporting Files Most ActiveX controls require a supporting DLL, such as the Microsoft Foundation 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 of date or missing, your ActiveX control will not register properly. Most reputable ActiveX control suppliers provide documentation regarding the version and name of the DLLs required; some even supply and install them along with the ActiveX controls. Moving ActiveX Control Files At installation, the directories in which the ActiveX control is installed are registered in the Windows Registration Database in Windows 95 and Windows NT, making the ActiveX control visible to your development environment. If you move the ActiveX control to a different directory, or rename the directory, the information in the registry is no longer valid. If you must move the ActiveX control or rename its directory, use the regsrv32.exe or regActiveX.exe utilities provided with most Microsoft development products to reregister the ActiveX in its new location.
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-27
Appendix H: Working with ActiveX Controls ...................................................................................................................................................... .
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
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... H-28 Oracle iDS Forms: Build Internet Applications II
Summary ......................................................................................................................................................
Summary In this lesson, you saw how to include and use ActiveX controls in your Oracle Developer 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
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II H-29
Appendix H: Working with ActiveX Controls ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... H-30 Oracle iDS Forms: Build Internet Applications II
................................
Using Oracle Server Roles at Run Time
l a n r e t n I e l c a r O
ly n O e s U I A O &
Appendix I: Using Oracle Server Roles at Run Time ......................................................................................................................................................
Oracle Server Views for Roles • • • •
SESSION_ROLES ROLE_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... I-2 Oracle iDS Forms: Build Internet Applications II
Using Oracle Server Roles at Run Time ......................................................................................................................................................
Using Oracle Server Roles at Run Time Overview You can manipulate roles dynamically at run time by using Forms. For example, you can select views from the data dictionary to get information about existing roles. Subprograms belonging to the DBMS_SESSION package enable you to modify the roles that are used by the menu module to enforce security. SESSION_ROLES View Roles that the user currently has enabled Column ROLE
Description Name of the role
ROLE_ROLE_PRIVS View Information about roles granted to other roles Column ROLE GRANTED_ROLE ADMIN_OPTION
Description Name of the role Role that was granted Subprogram that signifies that the role was granted with the ADMIN option
ROLE_SYS_PRIVS View Information about system privileges granted to roles Column ROLE PRIVILEGES ADMIN_OPTION
ly n O e s U I A O &
Description Name of the role System privileges granted to the role Column that signifies the grant was with the ADMIN option
l a n r e t n I e l c a r O
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II I-3
Appendix I: Using Oracle Server Roles at Run Time ......................................................................................................................................................
Oracle Server Views for Roles • • • •
SESSION_ROLES ROLE_ROLE_PRIVS ROLE_SYS_PRIVS ROLE_TAB_PRIVS
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... I-4 Oracle iDS Forms: Build Internet Applications II
Using Oracle Server Roles at Run Time ......................................................................................................................................................
ROLE_TAB_PRIVS View Information about table privileges granted to roles. Column ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE
Description Name of the role Owner of the object Name of the object Name of the column, if applicable Object privilege granted to the role YES if the role was granted with the ADMIN option, otherwise NO
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II I-5
Appendix I: Using Oracle Server Roles at Run Time ......................................................................................................................................................
Procedures for Managing Roles • •
DBMS_SESSION.SET_ROLE DBMS_SESSION.IS_ROLE_ENABLED
Copyright Oracle Corporation, 2001. All rights reserved.
l a n r e t n I e l c a r O
®
ly n O e s U I A O &
...................................................................................................................................................... I-6 Oracle iDS Forms: Build Internet Applications II
Procedures for Managing Roles ......................................................................................................................................................
Procedures for Managing Roles Package DBMS_SESSION
Description DBMS_SESSION is a standard package that comprises many subroutines. Two of them are used to manage menu module roles dynamically. This package is used to assign a particular role to the current user.
DBMS_SESSION.SET_ROLE (‘RoleName’) DBMS_SESSION.IS_ROLE_ENABLED (‘RoleName’)
Example The following Pre-Form trigger uses the DBMS_SESSION package to test a user’s role before allowing access to the application. IF not (DBMS_SESSION.IS_ROLE_ENABLED(’ADMINISTRATIVE’) or (DBMS_SESSION.IS_ROLE_ENABLED(’TECHNICAL’) THEN MESSAGE(’You are not authorized to run this application’); PAUSE; RAISE form_trigger_failure; END IF;
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... Oracle iDS Forms: Build Internet Applications II I-7
Appendix I: Using Oracle Server Roles at Run Time ......................................................................................................................................................
l a n r e t n I e l c a r O
ly n O e s U I A O &
...................................................................................................................................................... I-8 Oracle iDS Forms: Build Internet Applications II