BCIS 5420
Oracle 10G Form Builder and
Report Builder
By Anna Sidorova
Tutorial plan
• Create, modify tables, insert, select data in SQL
• Create interface in Oracle designer
– Forms based on one table or two tables
– Custom forms
– A switchboard
– Reports
Introduction to Oracle Form Builder 2
By Anna SidorovaIntro to Oracle Form
Builder 1
BCIS 5420
Example:
STUDENT COURSE_STUDENT
Grade_
SID Name Major Age SID CourseName
Level
100 Jones History
y SR 21 100 MIS520
150 Parks Acctg SO 19 150 ACC220
200 Baker Math GR 32 200 MIS520
250 Glass History SR 28
200 CSI312
300 Baker Acctg SR 24
350 Russell Math JR 20
300 ACC415
400 Rogers Acctg FR 18 400 ACC220
450 Jones History SR 25 400 CSI312
400 MAT101
COURSE
450 ACC220
Name Time Room
ACC220 M-F9 BA110
ACC415 MWF3 BA210
CSI312 MWF8 SS213
MAT101 MWF3 ES123
MIS520 MWF12 BCIS4610,
BA224Spring 2009
Creating tables and inserting data in
SQL Plus
• Start SQL Plus and log in
• Download file Dev_Tutorial_SQL_SQL_Plus from the course web
site
• Create tables STUDENT, COURSE, COURSE_STUDENT
• Insert data using INSERT statements
Starting Oracle Designer 4
By Anna SidorovaIntro to Oracle Form
Builder 2
BCIS 5420
Start Oracle Form Builder
Introduction to Oracle Form Builder 5
CREATING A FORM USING A
WIZARD
Introduction to Oracle Form Builder 6
By Anna SidorovaIntro to Oracle Form
Builder 3
BCIS 5420
Oracle Form Builder
Introduction to Oracle Form Builder 7
Create a new form based on MODULE1
• Go to File/New/Form – a new module is added
to your object navigator window
• Click on MODULE2 and rename it into
STUDENT
Introduction to Oracle Form Builder 8
By Anna SidorovaIntro to Oracle Form
Builder 4
BCIS 5420
Create a data block based on a table
• Right click on the STUDENT module and select Data
Block Wizard
• Click next on the Data Block Wizard welcome page
Introduction to Oracle Form Builder 9
Creating a data block (cont’s)
• Select Table/view option
• Select a table by clicking on Browse button
Introduction to Oracle Form Builder 10
By Anna SidorovaIntro to Oracle Form
Builder 5
BCIS 5420
Selecting a table (cont’d)
• When you click browse, you will be prompted to log in
• Login into your usual account and select table
STUDENT from the displayed list
Introduction to Oracle Form Builder 11
Select database items
• Move all the attributes into selected attributes
pane using arrow buttons
Introduction to Oracle Form Builder 12
By Anna SidorovaIntro to Oracle Form
Builder 6
BCIS 5420
Name your data block
Introduction to Oracle Form Builder 13
Select the “… then call the Layout wizard”
option
Introduction to Oracle Form Builder 14
By Anna SidorovaIntro to Oracle Form
Builder 7
BCIS 5420
Creating a layout
• Layout wizard allows you to create a form layout
• Click next on the welcome page and select content
canvas type
Introduction to Oracle Form Builder 15
Select items to be displayed
• Select items to be displayed
• Modify the labels (prompts) and widths of
corresponding text boxes if necessary
Introduction to Oracle Form Builder 16
By Anna SidorovaIntro to Oracle Form
Builder 8
BCIS 5420
Select a layout
• Select form layout
• Name the frame
• Select the number of students to be displayed
Introduction to Oracle Form Builder 17
Complete and view the form
• Complete and view the form design
Introduction to Oracle Form Builder 18
By Anna SidorovaIntro to Oracle Form
Builder 9
BCIS 5420
MODIFYING A FORM
Introduction to Oracle Form Builder 19
Modifying the form
• To change color,
select the form of its
element
• Go to Property
palette
• Modify Foreground
and background color
properties
Introduction to Oracle Form Builder 20
By Anna SidorovaIntro to Oracle Form
Builder 10
BCIS 5420
Add a title to your form
• Add a label using the toolbox on the left
Introduction to Oracle Form Builder 21
Testing your form
• Go to Program/Run to view the form
• Use Enter Query button to enter the query
• Use Execute Query button to view corresponding
records
Introduction to Oracle Form Builder 22
By Anna SidorovaIntro to Oracle Form
Builder 11
BCIS 5420
Convert Major into List Item
• In the Property palette, change item type to list
item
Introduction to Oracle Form Builder 23
Converting Major into a list item
• Add item values using the property palette
Introduction to Oracle Form Builder 24
By Anna SidorovaIntro to Oracle Form
Builder 12
BCIS 5420
Converting an item into a radio group
• Change Item type for Grade_level to Radio Group. The
corresponding textbox will disappear.
Introduction to Oracle Form Builder 25
Adding radio buttons
• Add a radio button (by clicking on the toolbox) for each of the grade
levels (Freshman, Sophomore, Junior, Senior and Graduate).
All the radio buttons will correspond to the grade level radio group.
Introduction to Oracle Form Builder 26
By Anna SidorovaIntro to Oracle Form
Builder 13
BCIS 5420
Format your radio buttons
If necessary, change background color of the radio buttons.
Add a label and a border (a rectangle).
Introduction to Oracle Form Builder 27
Save and test your form
Introduction to Oracle Form Builder 28
By Anna SidorovaIntro to Oracle Form
Builder 14
BCIS 5420
CREATING A FORM BASED
ON TWO TABLES
Introduction to Oracle Form Builder 29
Creating a Form Based on Table Student
• In order to create a form based on 2 tables:
– Create a form similar to the STUDENT form we created (name it
REGISTRATION))
Introduction to Oracle Form Builder 30
By Anna SidorovaIntro to Oracle Form
Builder 15
BCIS 5420
Creating a form based on two linked
tables
• In order to create a form based on 2 tables:
– Create an additional data block using a wizard based on the
table course_student
_
– Establish master-detail relationship
Introduction to Oracle Form Builder 31
Creating a form based on two linked
tables
• Follow the steps suggested by the wizard
• Include only course name as a visible attribute
Introduction to Oracle Form Builder 32
By Anna SidorovaIntro to Oracle Form
Builder 16
BCIS 5420
Creating a form based on two linked
tables
• For the Courses frame, select tabular layout, 5
records per page and a scrollbar
scrollbar.
Introduction to Oracle Form Builder 33
Compile and run your form
Introduction to Oracle Form Builder 34
By Anna SidorovaIntro to Oracle Form
Builder 17
BCIS 5420
Create an LOV
• Right-click anywhere in module REGISTRATION
and select LOV wizard
• Select record group based on a query
Introduction to Oracle Form Builder 35
Create an LOV
• Type in a query that would retrieve all records from table
course (do not put a semi-column at the end)
• Here you will may be asked to log into the DB
Introduction to Oracle Form Builder 36
By Anna SidorovaIntro to Oracle Form
Builder 18
BCIS 5420
Create an LOV
• Select all the columns that you want to appear in you
LOV
Introduction to Oracle Form Builder 37
Create an LOV
• Specify the return value for COURSE_NAME field
– Return value is a field where the course name from your LOV
will be inserted
Introduction to Oracle Form Builder 38
By Anna SidorovaIntro to Oracle Form
Builder 19
BCIS 5420
Create an LOV
• Specify title and dimensions
• Specify the number of rows displayed at once
Introduction to Oracle Form Builder 39
Create an LOV
• Select COURSENAME as assigned value
• Complete your LOV
Introduction to Oracle Form Builder 40
By Anna SidorovaIntro to Oracle Form
Builder 20
BCIS 5420
Create an LOV
• To test your LOV:
– Run the form
– Click
Cli k on
CourseName field
– Press CTRL+L
Introduction to Oracle Form Builder 41
Create an LOV control button
• Close your form in
the browser
• Return to the Form
buikder, Layout
editor
• Click on the Control
Button item in the
toolbar on the left
and drag it to a
desired location on
your canvas
• Name your button
using Property
pallete
Introduction to Oracle Form Builder 42
By Anna SidorovaIntro to Oracle Form
Builder 21
BCIS 5420
Create an LOV control button
• Right click on the button, and open PL/SQL editor
• Select Type – TRIGGER, Name –
WHEN MOUSE CLICKED
WHEN_MOUSE_CLICKED
• Type in code the code below
Introduction to Oracle Form Builder 43
Test an LOV button
• Compile and test
your button
Introduction to Oracle Form Builder 44
By Anna SidorovaIntro to Oracle Form
Builder 22
BCIS 5420
CREATING A CUSTOM FORM
Introduction to Oracle Form Builder 45
Creating a custom form
• Create a new form module and name it COURSE
• Click on data bloc and then on the plus icon. Select Build
a data block manually
• Rename your block into COURSE_BLOCK
Introduction to Oracle Form Builder 46
By Anna SidorovaIntro to Oracle Form
Builder 23
BCIS 5420
Create and modify a canvas
• Add a canvas by clicking on Canvas in object navigator
and then on a plus sign
• Rename your canvas into COURSE,
COURSE and open it with
Layout Editor
Introduction to Oracle Form Builder 47
Create and modify a canvas
• Change the background color of the canvas using the
Property palette
• Add a header “Course
Course Information
Information”
• Add a picture (download any picture from the web, save
in in C:\\temp, then go to Edit, Import)
•
Introduction to Oracle Form Builder 48
By Anna SidorovaIntro to Oracle Form
Builder 24
BCIS 5420
Add text boxes and prompts
• Using the toolbox on the left, add the following text items
and a corresponding prompts:
– Course name
– Meeting Time
– Room
Introduction to Oracle Form Builder 49
Add control buttons
• Using toolbox on the left, add four control buttons.
• Name them New, Find, Save and Clear
Introduction to Oracle Form Builder 50
By Anna SidorovaIntro to Oracle Form
Builder 25
BCIS 5420
Adding PL/SQL code
• Create triggers for each of New and Clear
buttons
• Add PL/SQL code to the triggers
Introduction to Oracle Form Builder 51
Adding PL/SQL code
• Create triggers for each of the Save button
• Add PL/SQL code to the triggers
Introduction to Oracle Form Builder 52
By Anna SidorovaIntro to Oracle Form
Builder 26
BCIS 5420
Adding PL/SQL code
• Create triggers for each of the Find button
• Add PL/SQL code to the triggers
Introduction to Oracle Form Builder 53
Adding PL/SQL code
• Create triggers for each the Exit button
• Add PL/SQL code to the triggers
Introduction to Oracle Form Builder 54
By Anna SidorovaIntro to Oracle Form
Builder 27
BCIS 5420
Creating a switchboard
• Create a new form
• Create a control block, similar to the one in form
COURSE
• Add four control buttons
– Add student
– Add course
– Register student
– Exit
• Add code to open forms
Introduction to Oracle Form Builder 55
Creating a switchboard
Introduction to Oracle Form Builder 56
By Anna SidorovaIntro to Oracle Form
Builder 28
BCIS 5420
Creating a switchboard
• Add triggers to corresponding buttons
Introduction to Oracle Form Builder 57
Creating reports
Introduction to Oracle Form Builder 58
By Anna SidorovaIntro to Oracle Form
Builder 29
BCIS 5420
Opening report builder
Introduction to Oracle Form Builder 59
Use report wizard to create a report
Introduction to Oracle Form Builder 60
By Anna SidorovaIntro to Oracle Form
Builder 30
BCIS 5420
Using report wizard
• Click OK on the welcome page and then specify the type
of report that you want to create
Introduction to Oracle Form Builder 61
Using report wizard
• Name your report and select the desired style
Introduction to Oracle Form Builder 62
By Anna SidorovaIntro to Oracle Form
Builder 31
BCIS 5420
Using report wizard
• Select SQL query as a basis for the report
• Build your query (you may be asked to log in at this point)
Introduction to Oracle Form Builder 63
Using report wizard
• Designate columns as group fields
Introduction to Oracle Form Builder 64
By Anna SidorovaIntro to Oracle Form
Builder 32
BCIS 5420
Using report wizard
• Select columns to appear in the report
Introduction to Oracle Form Builder 65
Using report wizard
• Specify labels and field length for each of the items
Introduction to Oracle Form Builder 66
By Anna SidorovaIntro to Oracle Form
Builder 33
BCIS 5420
Using report wizard
• Select a template
Introduction to Oracle Form Builder 67
Using report wizard
• Finish and view your report
Introduction to Oracle Form Builder 68
By Anna SidorovaIntro to Oracle Form
Builder 34
BCIS 5420
Viewing Web report
Introduction to Oracle Form Builder 69
By Anna SidorovaIntro to Oracle Form
Builder 35
