Oracle 10G Form Builder and Report Builder

(1)

By Anna SidorovaIntro to Oracle Form

Builder 1

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

(2)

Example:

STUDENT

SID Name Major Grade_ Level Age 100 Jones History SR 21 COURSE_STUDENT SID CourseName 100 MIS520 y 150 Parks Acctg SO 19 200 Baker Math GR 32 250 Glass History SR 28 300 Baker Acctg SR 24 350 Russell Math JR 20 400 Rogers Acctg FR 18 450 Jones History SR 25 150 ACC220 200 MIS520 200 CSI312 300 ACC415 400 ACC220 400 CSI312 400 MAT101 450 ACC220 COURSE BCIS4610, Spring 2009 450 ACC220

Name Time Room

ACC220 M-F9 BA110 ACC415 MWF3 BA210 CSI312 MWF8 SS213 MAT101 MWF3 ES123 MIS520 MWF12 BA224

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

(3)

Builder 3

Start Oracle Form Builder

Introduction to Oracle Form Builder 5

CREATING A FORM USING A

WIZARD

(4)

Oracle Form Builder

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

(5)

Builder 5

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 • Click next on the Data Block Wizard welcome page

Creating a data block (cont’s)

• Select Table/view option

• Select a table by clicking on Browse button

(6)

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 STUDENT from the displayed list

Select database items

• Move all the attributes into selected attributes

pane using arrow buttons

(7)

Builder 7

Name your data block

Select the “… then call the Layout wizard”

option

(8)

Creating a layout

• Layout wizard allows you to create a form layout • Click next on the welcome page and select content

canvas type canvas type

Select items to be displayed

• Select items to be displayed

• Modify the labels (prompts) and widths of

corresponding text boxes if necessary

(9)

Builder 9

Select a layout

• Select form layout

• Name the frame

• Select the number of students to be displayed

Complete and view the form

• Complete and view the form design

(10)

MODIFYING A FORM

Modifying the form

• To change color, select the form of its element

• Go to Property palette

• Modify Foreground and background color properties

(11)

Builder 11

Add a title to your form

• Add a label using the toolbox on the left

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 • Use Execute Query button to view corresponding

records

(12)

Convert Major into List Item

• In the Property palette, change item type to list

item

Converting Major into a list item

(13)

Builder 13

Converting an item into a radio group

• Change Item type for Grade_level to Radio Group. The

corresponding textbox will disappear.

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.

(14)

Format your radio buttons

If necessary, change background color of the radio buttons. Add a label and a border (a rectangle).

(15)

Builder 15

CREATING A FORM BASED

ON TWO TABLES

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))

(16)

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

Creating a form based on two linked

tables

• Follow the steps suggested by the wizard

• Include only course name as a visible attribute

(17)

Builder 17

• For the Courses frame, select tabular layout, 5

records per page and a scrollbar

Creating a form based on two linked

tables

records per page and a scrollbar.

Compile and run your form

(18)

Create an LOV

• Right-click anywhere in module REGISTRATION

and select LOV wizard

• Select record group based on a query

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 • Here you will may be asked to log into the DB

(19)

Builder 19

Create an LOV

• Select all the columns that you want to appear in you LOV

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

(20)

Create an LOV

• Specify title and dimensions

• Specify the number of rows displayed at once

• Select COURSENAME as assigned value

• Complete your LOV

(21)

Builder 21

• To test your LOV:

– Run the form Cli k

Create an LOV

– Click on

CourseName field – Press CTRL+L

• Close your form in the browser

• Return to the Form

Create an LOV control button

buikder, Layout editor

• Click on the Control Button item in the toolbar on the left and drag it to a desired location on

desired location on your canvas

• Name your button using Property pallete

(22)

• Right click on the button, and open PL/SQL editor • Select Type – TRIGGER, Name –

WHEN MOUSE CLICKED

Create an LOV control button

WHEN_MOUSE_CLICKED • Type in code the code below

• Compile and test

your button

(23)

Builder 23

CREATING A CUSTOM FORM

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

a data block manually

• Rename your block into COURSE_BLOCK

(24)

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 and open it with • Rename your canvas into COURSE, and open it with

Layout Editor

Create and modify a canvas

• Change the background color of the canvas using the Property palette

• Add a header “Course Information” • Add a header Course Information

• Add a picture (download any picture from the web, save in in C:\\temp, then go to Edit, Import)

(25)

Builder 25

Add text boxes and prompts

• Using the toolbox on the left, add the following text items and a corresponding prompts:

– Course nameCourse name – Meeting Time – Room

Add control buttons

• Using toolbox on the left, add four control buttons. • Name them New, Find, Save and Clear

(26)

Adding PL/SQL code

• Create triggers for each of New and Clear

buttons

• Add PL/SQL code to the triggers

Adding PL/SQL code

• Create triggers for each of the Save button • Add PL/SQL code to the triggers

(27)

Builder 27

Adding PL/SQL code

• Create triggers for each of the Find button • Add PL/SQL code to the triggers

Adding PL/SQL code

• Create triggers for each the Exit button

• Add PL/SQL code to the triggers

(28)

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

(29)

Builder 29

Creating a switchboard

• Add triggers to corresponding buttons

Creating reports

(30)

Opening report builder

(31)

Builder 31

Using report wizard

• Click OK on the welcome page and then specify the type of report that you want to create