Transcript Chapter 23 – ASP.NET

1
Using Object Navigator,
Form Objects, and
Detail Data Blocks
Dr. Awad Khalil
Computer Science & Engineering Department
AUC
Object Navigator
 Object Navigator is the central Forms Builder tool for adding, deleting, and locating
form objects and components. These objects and components include:
 Triggers or PL/SQL scripts used by the form.
 Alerts, which are modal dialog boxes that display messages.
 Attached libraries of PL/SQL code that can be used by the form.
 Data blocks used by the form to issue Select, Update, Delete, and Insert
statements.
 Relationships used to coordinate records from parent and child tables.
 Canvases used to display form items.
 Editors associated to an item that are used to enter lengthy values.
 List of values (LOVs) that dynamically produce value lists used as item values.
 Object groups that hold form components that can be copied to another form.
 Parameters that are used to transfer values.
 Popup menus displayed by right-clicking the mouse.
 Program units that are named PL/SQL scripts defined within the form.
Wireless LANs
2
Object Navigator
 Property classes used to set form object characteristics such as font, width, height,
… etc.
 Record groups that return dynamic or static values to form objects.
 Reports that can be launched from the form.
 Visual attributes that control how form items appear.
 Windows that display canvases.
 Menu modules attached to a form that can be used to perform tasks, such as
calling another form.
 PL/SQL libraries that can be attached to the form.
 Object libraries that consist of form components that can be copied into a form.
 Built-in packages developed by Oracle for your use.
 Database objects such as tables, views, or stored procedures
 In this part, we create detail blocks that are related and synchronized to a
master block and data block FROM clause query property.
Wireless LANs
3
Object Navigator
 The Object Navigator is the central Forms Builder tool. It lists every component. If
you want to modify an object on the form, you simply locate and highlight the object
in the Object Navigator and double-click its icon. Forms Builder then opens the
appropriate editor for the selected object. The Object Navigator is also commonly
used to add, delete, and rename form components.
 The Object Navigator is a hierarchy of objects. At the highest level are binary files
that reside on the server. Whenever a file is opened or created, the object is listed
under the appropriate parent object. The file objects and their functions are:
 Forms: Form modules or applications used to view, modify, and delete data.
 Menus: Menu modules or applications containing paths to the various system
applications. Menu modules cannot be executed by themselves; they must be
attached to form modules.
 PL/SQL libraries: Modules containing various PL/SQL procedures. Libraries of
procedures can be shared between form modules.
 Object libraries: Modules containing form components that can be copied into a
form module. These are effective tools for standardizing form components.
 Built-in packages: A series of procedures developed by Oracle. These procedures
perform a variety of functions, such as sending messages or debugging procedures.
Wirelessavailable
LANs
 Database objects: Database objects
to the user, such as tables, views,4 or
Object Navigator Toolbar
 On the left side of the Object Navigator is a vertical toolbar that can perform a variety
of tasks. You can determine the tool functionality by placing the cursor over the tool
to make a ToolTip description appear.
Wireless LANs
5
Expanding and Collapsing the Object Navigator Hierarchy
 Each of the form file objects can have numerous child objects, and a complex form can have
one hundred or more objects. This is further complicated by the fact that it is common to have
several form modules open at the same time. Needless to say, it can be difficult to read and
locate items in the Object Navigator. Fortunately, the Object Navigator has the ability to
expand and collapse objects
Wireless LANs
6
Object Navigator Object Symbols and Toolbar
 Each Object Navigator item type has its own graphic symbol or icon. This symbol is didplayed
to the left of the object’s name. The way to determine the object type is by opening the object’s
Property Palette. The graphic symbol is most important for data block items, because a data
block item can be one of 15 different types. For example, display items and text items appear
exactly the same in the Layout Editor. The difference between the rwo is that a text item value
can be modified, while a display item cannot. The display item and text item symbols are
boxes displaying the characters “ABC”. The symbols differ in that the display item sumbol is
darkened.
Wireless LANs
7
Object Navigator Techniques
 There are a variety of Object Navigator techniques that help you work efficiently. The
following list describes them:
 Adding an object: You can add a new object by selecting any object snd using the Create
tool (green plus sign)
 Bookmarking: You can mark selected objects for quick return. This is done by highlighting
an object and selecting the Edit/Add Bookmark option. The Edit/Go To Bookmark option
moves you directly to the bookmark. Previous bookmarks are replaced when a new one is
set.
 Changing names: You can change the name of any form object by clicking the object
twice. Be sure to pause between clicks to fifferentiate from double-clicking. This technique
cause the object name to turn into a text item, which allows you to modify the value. You
can also change the object’s name by double-clicking any object icon and opening the
Property Palette.
 Copying: you can copy any object by pressing the Control+C keys, using the Copy toolbar
tool, or selecting the Edit/Copy menu option. This places the object in the Windows
clipboard.
 Cutting: You can cut any object by pressing the Control+X, using the Cut toolbar tool, or
selecting the Edit/Cut menu option. This removes the object from the Object Navigator and
places it in the Windows Clipboard.
Wireless LANs
8
Object Navigator Techniques
 Deleting an object: You can delete an object by selecting any object and using the Delete
tool (red x), pressing the Delete keyboard key, or selecting the Edit/Clear menu option.
 Duplicating: You can duplicate a highlighted object by selecting the Edit/Duplicate menu
option or by pressing the Control+D keys.
 Making multiple selections: Multiple objects can be selected for any of the previous
actions. If the items are adjacent, highlight one of the end objects, then hold down the Shift
key while highlighting the opposite end object,. This highlights the entire set of objects
between the two selected objects. To select disparate objects, hold down the Control key
while highlighting the objects. If, at some point, you select an object without holding the
Shift or Control keys, all the previously selected objects are unhighlighted.
 Navigating: Right-clicking the mouse on any object causes a popup menu to appear. The
menu has options to cut, copy, paste, and call the various Forms Builder editors, wizards and
other tools.
 Pasting: you can paste an object residing in the Windows Clipboard into a form by pressing
the Control+V keys, using the Paste toolbar tool, or selecting the Edit/Paste menu option.
 Searching: You can search on a word using Object Navigator tools. The Object Navigator
can contain a large quantity of objects. Sometimes you may want to search based on a name
rather than by scanning the hierarchy. The Object Navigator has search tool called Fast
Search located at the top-right corner of the tool that can perform the search.
Wireless LANs
9
Searching with the Object Navigator
The Object Navigator can contain a large quantity of objects. Sometimes you may want to
search based on a name rather than by scanning the hierarchy. The Object Navigator has a
search tool called Fast Search located at the top-right corner of the tool that can perform the
search. The Fast Search tool has the following characteristics:
 You can begin a search by entering a value in the Find text box. Object Navigator
immediately seeks the first object that matches the characters.
 The two search tools (flashlight icons) are used to perform forward and backward hierarchy
searches. The search tools can be used to continue the search in the opposite direction.
 The wild card (*) can be used to represent one or many characters.
 The left pick list always displays the currently selected object and contains any previously
selected object by clicking any object in the pick list.
Wireless LANs
10
Moving Objects
 One of the more common Object Navigator functions is to move objects between forms or
between data blocks. When you create objects in the Layout Editor, Forms Builder sometimes
places the object in the incorrect data block, so you must move the object. Another common
use is when you want to copy objects from another module. Object components are often
exactly the same or very similar in different forms. If you don’t want to use and maintain an
object library, you can drag or move selected objects to other forms. This, in effect, copies the
components to the new form module.
 As you drag the selected objects (if you have used the multiple select technique), Forms
Builder changes the cursor to a Stop symbol as you move the cursor over objects not
compatible with the selected objects.
Wireless LANs
11
Moving Objects
 When you move objects between form modules, Form Builder displays a Form dialog box
before copying the objects. This dialog box prompts you on whether the copied objects should
be subclassed.
• A subclass is a set of objects that are linked to similar objects in another form. The subclass
allows you to modify an object in one form and have the related object modified in another
form. To effect the changes in linked forms, you must recompile all the forms using the
subclassed objects. Objects are often used repeatedly on different forms, but it is difficult to
find an effective way to track the subclasses and linked forms.
Wireless LANs
12
Changing the Object Navigator
 The Object Navigator displays form objects in two different manners. The more common view
used in the previous figures is the Ownership view. The Ownership view hierarchy lists all the
form objects beginning with the highest-level object. The second is the VISUAL view that lists
only displayed form objects. These objects consist of the form’s visual attributes, windows,
canvases, items, and frames. The VISUAL view hierarchy of objects resembles the hierarchy
in which the items are displayed. At the top level is the WINDOW object that is needed to
display a canvas. A canvas is needed to display data block items. You can change the view
types using the Ownership View view or the Visual View view menu selections the following
figure illustrates a form module in the VISUAL view.
Wireless LANs
13
Definitions of Various Form Objects
 There are a variety of form objects. The following table offers a brief description of the objects
and the hierarchy or path used to locate the objects.
Wireless LANs
14
Definitions of Various Form Objects
 There are a variety of form objects. The following table offers a brief description of the objects
and the hierarchy or path used to locate the objects.
Wireless LANs
15
Types of Files Used by Forms Builder
 Files can be brought into Forms Builder in several ways. To create a new file, highlight the file
type object in the Object Navigator and perform one of the following:
 Click the New Module tool on the toolbar.
 Select the File/New menu selection.
 To open existing file within Forms Builder, perform one of the following:
 Click the Open tool on the toolbar.
 Select the File/Open menu option.
 The following table illustrates the different file types that are used by Forms Builder:
Wireless LANs
16
Creating and Using Detail Data Blocks
 Relational data is stored in a series of related tables. The tables are often related through a
series of one-to-many relationships, as shown in the following schema. The example is a
construction project, which is composed of many work orders. The work orders divide the
projects into a finer detail. Work orders can be further decomposed by identifying their
individual tasks. This series of one-to-many relationships form a grandfather, parent, and child
relationship. Grandfathers are created first, then the parents, and finally the children. Forms
seldom contain only one table.
 The majority of the forms require the data from multiple data sources. An engineer user would
want to see in one form the project description and location, the work orders for the project,
and the tasks for each work order. This requires a form that has a data block for each of the
tables (PROJECTS, WORK ORDERS, and, TASKS). The PROJECTS data block displays
information about a specific project, the WORK ORDERS data block displays information that
pertains to the selected project in the PROJECTS data block, and the TASKS data block
displays the tasks that correspond to the selected work order in the WORK ORDERS data
17
Wireless LANs
block.
Creating and Using Detail Data Blocks
 The following figure illustrates one of the forms that depicts the need for multiple forms. The
form has WORK_ORDERS, TASKS, and MONTHLY_COSTS data blocks that are
synchronized . Records in a child data block such as TASKS must always be related to a
selected work order. This synchronization is a complex coding problem, but fortunately Forms
Builder makes it easy for us.
Wireless LANs
18
Creating and Using Detail Data Blocks
 Forms Builder holds records in detail data blocks, so named because they offer more
detailed information about the regular or parent entity. Referring back to ERD
concepts, detail data blocks are used for weak entities, entities that cannot exist on
their own. In our example, a work order cannot exist without a project, and a task
cannot exist without a work order. Related Forms Builder data blocks are called
master-detail data blocks.
 Master-detail data blocks are generally created using the Data Block Wizard. The
wizard populates data block properties and creates data block triggers (PL/SQL code)
that ensure the detail data block displays records for the selected parent record. It is
far easier to set this up using the wizard than without. It is sometimes necessary to
create a detail data block manually, but this is exceedingly rare.
 It is highly possible to have multiple detail blocks for a master block. This is likely to
occur if multiple relationships exist for the entity. For example, an employee can
have eyeglass purchases and tool purchases. Each of these is a different relationship
and has different data blocks. An entity relationship drawing (or data schema
diagram) is an excellent tool to identify candidate detail data blocks.
Wireless LANs
19
Creating a Detail Data Block
 Forms Builder adds an additional page to the Data Block Wizard when the second of multiple data blocks
is added to the form. The purpose of this page (see the following figure), called the Master-detail page is
to identify the primary and foreign key fields in each of the data sources.
Wireless LANs
20
Creating a Detail Data Block
 This page has the following characteristics:
 Master Data Blocks list box: Identifies the data block(s) selected as a master data block(s). This
list box may initially be populated if the detail data block data source has a foreign key constraint.
 Create Relationship button: Launches a dialog box displaying the form’s data blocks. This button
is used if the Master Data Blocks list box is not populated. The desired master data block is identified
in this dialog box . To select a block, highlight the block and click OK.
 Delete Relationship button: Deletes the currently selected master block from the Master Data
Blocks list box.
 Auto-join data blocks check box: When this box is checked, Forms Builder scans the Oracle
database to determine form data blocks that are candidates for the master block. When the box is
unchecked, Forms Builder displays all the form’s existing data blocks.
 The following items are not displayed on the Master-detail page unless a master data block has been
selected:
 Detail Item drop-down list: Lists the detail data block data source columns. This drop-down list is
used to identify and select the relationship’s foreign key column.
 Master Item drop-down list: Lists the master data block data source columns. This drop-down list
is used to identify and select the relationship’s primary key column.
 Join-Condition list box: Displays the join condition that is created for the master-detail data blocks.
This box cannot be modified. If the conditions are not correct, they must be deleted using the Delete
Relationship button.
Wireless LANs
21
Creating a Detail Data Block
 Data sources that have composite keys, which are multiple-column primary keys, require
multiple join conditions. You can create multiple conditions by selecting the Create
Relationship button. After properly creating the JOIN conditions, Forms Builder can develop
the form components that keep the blocks in synchronization.
 As practice, perform the following steps to add the EMPLOYEE table as a detail data block to
the DEPARTMENT table as a master data block.
1. Open the previously created form in the Object Navigator.
2. Launch the Data Block Wizard. Be sure none of the existing data block (EMP) was not selected when the
wizard was launched. If it was, you will be updating the selected block rather than creating a new one.
3. On the Type page, select the Table or View option.
4. On the Table page, enter BONUS into the Table or view text item. Click the Refresh button populating the
Available Columns list. Move all of the Available Columns to the Database Items list.
5. The next page is the Master-detail page. Click the Create Relationship button. A message appears
specifying that there is no a master data blocks. This appears because the BONUS table does not have a
Foreign Key constraint to EMP table. Click OK to close the dialog box.
6. Uncheck the Auto-join data blocks check box and click the Create Relationship button. The Relation Type
dialog box opens. Select the Based on a join condition option and click OK. A dialog displaying the EMP
data block appears.
7. Select the EMP data block and click OK.
8. The EMP and BONUS tables are related by common ENAME values. You should see ENAME displayed
in the Detail Item drop-down list. Open the Master Item pick list and select the ENAME column. ENAME
should appear in the Master Item drop-down
list. LANs
22
Wireless
Creating a Detail Data Block
9. Perform the remaining steps to create and format the data block using
the Data Block and Layout Wizards. Accept the default settings on the
remainder of the Data Block Wizard pages. In the Layout Wizard, do
not select the ENAME column, because it is displayed in the master
block. Select the Tabular style, five records, and Display Scrollbar.
10.After creating the form, save and launch the form. Notice the detail
data block.
11.Retrieve a record in the master block. Enter values in the
corresponding detail block.
12.Save the records. Perform another query to determine whether the
records are displayed when the target master record is displayed.
13.Save the form.
Wireless LANs
23
How Forms Builder Keeps Master-Detail Data Blocks
Synchronized

A developer does not normally have to modify the PL/SQL triggers or other compenents that are created
for the master-detail blocks. However, it is wise to understand how they operate together. You may
occasionally wish to modify the default behavior. The following table details the various master-detail
objects and settings:
Wireless LANs
24
Relations
 Relations is a master data block object that controls the coordination between the master and detail data
blocks. The Data Block Wizard normally creates relations, but they can be created manually. Relations
properties determine whether a master record can be deleted if related detail records exist, whether the
related detail records should be deleted along with the master record, and whether the detail block should
be populated immediately following the selection of a new master record. Three properties control these
operations:
 Delete Record Behavior: Determines how the deletion of master records affects the detail records.
Options are: CASCADE, which causes the detailed records to be deleted along with the master record;
ISOLATED, which allows the master record to be deleted without the deletion of the detail record; and
NON-ISOLATED, which is the default setting that prevents master record deletion if existing detail
records exist.
 Deferred: Along with the Automatic Query property, determines when the detail block is populated
after a change of selected master records. Options are YES or NO.
 Automatic Query: Determines whether the detail block is populated before navigation to the block.
Options are YES or NO.
Deferred property
Automatic Query property
Action
YES
YES
Forms Builder retrieves detail records when the user
navigates to the data block.
YES
NO
Forms Builder retrieves detail records when the user
navigates to the data block and executes a query.
NO
YES
Forms Builder retrieves the records immediately.
NO
NO
Wireless LANs
Forms Builder fetches the detail records immediately. 25
Relations
 The Relations object has the following additional properties:
 Relation Type: Indicates the link between the two data blocks is a JOIN, which is
the default, or on object ref pointer. The values are JOIN and REF. setting a value
REF indicates that a REF column in a block points to data in another block.
 Detail Block: The name of the related detail block.
 Join Condition: The condition that describes the relationship.
 Prevent Masterless Operations: Prevents records from being inserted into the
detail block, if the master record does not exist.
 After reviewing the various Relations properties, you may wonder if they
supercede the foreign key constraints that may exist in the database. The answer is
no. The relation’s properties should emulate the database constraints. If they don’t,
Oracle returns a database error and voids the transaction. For example, if you try to
insert a detail record into a table that has a foreign key constraint, Oracle returns an
error message if a parent record does not exist, regardless of the Prevent Masterless
Operation property.
Wireless LANs
26
Coordination Type Toggle
 The previous table outlined the Deferred and Automatic Query Relations properties. For
optimal performance, these properties can be modified from the default in order to reduce the
number of records returned. However, there may be times when the operator may want to
change the original properties at runtime. For instance, suppose the operator is scrolling
through a set of master records, but does not want to see the detail records for each master
displayed. The Deferred and Automatic Query properties can be set to YES and NO,
respectively. This combination of properties would require the operator to navigate to the
detail block and execute a query.
 In some cases, the operator may want to override the properties and populate detail records
without having to execute queries. Changing data block coordination requires a coordinationtype-toggle trigger. This is a trigger that sets the Deferred and Automatic Query properties at
runtime. This trigger requires the use of an Oracle-developed built-in subprogram called
Set_relation_property. This built-in is available for use within a form.
 The following command can be placed inside the form to change the properties. The command
sets the Automatic Query property to YES for the named relation. The relations property name
value is the name displayed in the Object Navigator. The same command can be used for the
Deferred property.
Set_relation_property {‘relation_name’, autoquery, Property_true);
Wireless LANs
27
Coordination Type Toggle
 Relation properties can also be tested using the Get_relation_property built-in. using this
property enables you to determine the current state of the properties. If you know the current
state, you can toggle to the other value. The following is an example of a script that can be
used in a trigger to toggle the Automatic Query property on and off.
If get_relation_property ( ‘relation_name’, autoquery = ‘FALSE’ then
set_relation_property ( ‘relation_name’, autoquery, property_true);
else
set_relation_property ( ‘relation_name’, autoquery, property_false);
 You should note that the Get_relation_property and Set_relation_property built-ins are not
unique to Relations. All other form objects have similar Get and Set type built-ins that allow
you to modify properties dynamically.
Wireless LANs
28
Control Blocks and their Use
 Data blocks that are not associated with a data source are called control blocks. They are very
important form components, because they are used to hold form values. A form often has
values that must be retained throughout the life or scope. These values can be the current date,
user ID, passwords, or any other value that is not associated to a database table. These values
are stored or assigned to control block form items.
 Control blocks are used to store values rather than data blocks, because they are static. This
means the mode cannot be changed and the block cannot issue any SQL statement. If a value
were stored in a data block, it would be cleared every time the data block was placed in the
ENTER QUERY mode. The first task Forms Builder performs when placing a data block in
the ENTER QUERY mode is to issue a CLEAR_BLOCK command that flushes the data block
of all existing values. Thus, you cannot retain values in a data block.
 Control block generally contain values that are not displayed on a canvas, but this is not always
true. A custom form style called a Directory uses a control block to store search values. In
ENTER QUERY mode, search values are entered directly into data block items.when the
query returns records to the data block, the search values are overwritten. The user may not
remember the original search values or has to re-enter the search value if the original values
did not provide the proper filtering. In many cases, it is preferable to enter the search values
into a control box and use data item Copy Form properties to assign the data item a value from
an item on the control block. When the records are displayed, the original search values
continue to be displayed in the control block. The user can view the original search values and
only has to make small modifications rather than having to re-enter all of the search variables.
 A control block can be created by Wireless
selectingLANs
the data block’s object node and clicking29the
Create tool.
Creating a Data Block with a FROM Clause Query as the
Data Source
 Normally, a data block is based on a table. However, sometimes it is advantageous to change the
data source to a FROM clause query. A FROM clause query is a SELECT statement that resides as a
data block property. When Forms Builder creates the form’s SQL, it uses the FROM clause query
rather than developing it from the data block’s columns.
 Data blocks often contain descriptive items not existing in the source table. Normalization causes
descriptive fields such as task description or account description to be moved to a table of their own.
Yet these values are needed with their child tables to better understand the information. For example,
the Project Budgeting database schema has a WORK_ORDER_TASKS table contains several
foreign keys. It contains foreign keys to the TASK_DESCRIPTIONS and ACCOUNTS tables. The
descriptions of these items are needed along with the values of the WORK_ORDER_TASKS
records to make each record more meaningful.
Wireless LANs
30
Creating a Data Block with a FROM Clause Query as the
Data Source
 Look at the following listing. It contains two SELECT statements. The first statement creates a
result set of records in the WORK_ORDER_TASKS table. It is exactly the data that would be
viewed in a data block based on the table. The records are not very intuitive and require specialized
knowledge of the department ID and account codes. The second SELECT statement joins the
WORK_ORDER_TASKS, TASK_DESCRIPTION, and ACCOUNTS tables. The codes are
replaced by their descriptions. This makes each row much more meaningful to the common user.
Wireless LANs
31
Creating a Data Block with a FROM Clause Query as the
Data Source
Wireless LANs
32
Creating a Data Block with a FROM Clause Query as the
Data Source
 It should be apparent that the second result set is preferable. In order to
bring descriptive values into the data block, one of three techniques can
be used. The first technique is to add display items not associated to the
data source table to the block. A Post-Query trigger populates the items
by executing a PL/SQL script once for each record retrieved by the
data block. The trigger selects the descriptive values from the database,
based on the foreign key values in the data block record. This is a
common technique, and it allows the developer to avoid having to
create data manipulation language (DML) statements for the data
block. The problem with this technique is that the data block needs to
execute the Post-Query trigger many times. Each time the trigger is
executed, a database query is performed degrading performance. A
second problem is that the user cannot use the display items populated
by the Post-Query trigger to filter records, because the display items
are not related to the data source and are not in the data block.
Wireless LANs
33
Creating a Data Block with a FROM Clause Query as the
Data Source
 A second possibility is to use a view, which may also enhance performance. Creating a tuned
complex SQL statement substantially reduces the work Oracle must do to retrieve the result set, in
comparison with using the Post-Query trigger method. Because all of the values in the data block are
returned by the view, the descriptive items can be used to enter search criteria. A disadvantage of a
view is that the developer must create the DML statement used by the data block. This task isn’t
extremely difficult and consists of creating On-Lock, On-Update, On-Insert, and On-Delete triggers
that contain the various DML statements.
 A FROM clause query is a SELECT statement that replaces the default clause that was based on the
data block items. Default data block SELECT statements are composed of any data block item
associated to the database. Items are associated to the database by setting the item’s database item
property to YES. A FROM clause query is actually indistinguishable from using a view. The FROM
clause query is preferred, however, because it resides in the form rather than in the database. If the
database view, which is totally separate from the form, is deleted, moved, or has the permissions
revoked, the form is disabled. Placing the SELECT statement in the form rather than in the database
as a view eliminates many of these problems. It also keeps your database clean and unclutered by
database views that are simply used by a form.
 The FROM clause query techniques do have some disadvantages. The Data Block Wizard cannot be
used to create the data block. The data block, its items, and all the needed properties must be set
manually. This can take a great deal of work. However, once the data block is set up, the Layout
Wizard can be used to complete the formatting.
Wireless LANs
34
Creating a Data Block with a FROM Clause Query as the
Data Source
 To practice creating a FROM clause query, perform the following steps:
1. Manually create a data block.
2. Open the Property Palette for the newly created data block. Double-click the data block icon in the
Object Navigator or select the data block node and press the F4 key.
3. If necessary, locate the Database Data Block property and set the value toYES.
4. LOCATE THE Query Data Source Type property and set the value to FROM clause query.
5. Locate and highlight the Query Data Source Name property. A button appears on the right side of
the property text item. Clicking this button launches the Query Data Source Name dialog box used to
enter the SELECT statement.
6. Enter the following script in the Query Data Source Name dialog box. Before closing the dialog box,
copy the SELECT statement and test it in SQL*Plus to ensure it is accurate:
SELECT dname department_name, count(*) number_of_employees,
sum(Sal) total_salary,
avg(Sal) average_salary
FROM dept, emp
WHERE dept.deptno = emp.deptno(+)
GROUP BY dname
7. Create a data block item using the Create tool for DEPARTMENT_NAME,
NUMBER_OF_EMPLOYEES, TOTAL_SALARY, and AVERAGE_SALARY columns.
Wireless LANs
35
Creating a Data Block with a FROM Clause Query as the
Data Source
8. Name each data block item. It is preferable to give the data block item the same
name as its associated database item. This saves you the task of setting the item’s
Column Name property.
9. Modify the Data Type, Maximum Length, Database Item, Column Name, and
Query Length properties. The Data Type and Maximum Length properties should
be set to the same settings as the related database items. The Column Name
property should be set to the name of its associated FROM clause query column,
unless the item has the same name as the database item. The Query Length
property must be at least the same size as Maximum Length. However, it can be
larger, which is preferred.
 The following figure illustrates the Query Source Name dialog box and the data
block Property Palette. The SELECT statement is recorded in this dialog box. It is
accepted and inserted in the property by clicking the OK button. Forms Builder
does not validate this statement, and it is very possible to enter an invalid SQL
statement. If you enter a syntactically incorrect statement , you are notified when
you operate the form and attempt to execute a query. Oracle returns a database
error that you can view by launching
theLANs
Display Error dialog box.
36
Wireless
Creating a Data Block with a FROM Clause Query as the
Data Source
Wireless LANs
37