Oracle forms developer tutorial Designer

BCIS 5420

Oracle 10 10G G For Form m Bui B uild lder er and Repor portt Buil B uilder der By Anna Sidorova

Tutorial pl an • Create, Create, modif modify y tables, tables, insert insert,, select select data data in SQL • Create Create interfa interface ce in Oracle Oracle designer designer – Forms based based on one one table or or two tables tables – Custom forms forms – A switchboard switchboard – Reports

Introduction to Oracle Form Builder

By Anna SidorovaIntro to Oracle Form Builder

2

1

BCIS 5420

Example: COURSE_STUDENT

STUDENT

S ID

Na me

M a j or

100 150 200 250 300 350 400 450

Jones P arks Bake r Gla ss Bake r Russell Rogers Jones

History Acctg Ma th History Acctg Ma th Acctg History

Grade_ Level SR SO GR

SR SR JR FR SR

Age

S ID ID

C ou ou rs rs eN eN am ame

21 19 32 28 24 20 18 25

100

MIS520

150

ACC220

200

MIS520

200

CSI312

300

ACC415

400

ACC220

400

CSI312

400

MAT101

COURSE

Name

Time Time

ACC2 ACC22 20 ACC4 ACC41 15 CSI3 CSI31 12 MAT10 MAT101 1 MIS5 MIS520 20

M-F9 MWF MWF3 MWF8 WF8 MWF MWF3 3 MWF1 MWF12 2

Room

BA11 A110 BA2 BA210 SS213 ES12 ES123 3 BA224 BA224 BCIS4610, Spring 2009

Creatin Crea tin g tables and and in serti serting ng data in SQL SQ L Plus Plu s •

Star Startt SQL SQL Plus Plus and log log in in

•

Downloa Download d file Dev_T Dev_Tuto utoria rial_S l_SQL_ QL_SQL SQL_Pl _Plus us from from the course course web web site

•

Create Create tables tables STUDE STUDENT NT,, COURSE COURSE,, COURSE COURSE_STUD _STUDENT ENT

•

Insert Insert data data using using INSER INSERT T state statemen ments ts

Starting Oracle Designer


4

2

BCIS 5420

Example: COURSE_STUDENT

STUDENT

S ID

Na me

M a j or

100 150 200 250 300 350 400 450

Jones P arks Bake r Gla ss Bake r Russell Rogers Jones

History Acctg Ma th History Acctg Ma th Acctg History

Grade_ Level SR SO GR

SR SR JR FR SR

Age

S ID ID

C ou ou rs rs eN eN am ame

21 19 32 28 24 20 18 25

100

MIS520

150

ACC220

200

MIS520

200

CSI312

300

ACC415

400

ACC220

400

CSI312

400

MAT101

COURSE

Name

Time Time

ACC2 ACC22 20 ACC4 ACC41 15 CSI3 CSI31 12 MAT10 MAT101 1 MIS5 MIS520 20

M-F9 MWF MWF3 MWF8 WF8 MWF MWF3 3 MWF1 MWF12 2

Room

BA11 A110 BA2 BA210 SS213 ES12 ES123 3 BA224 BA224 BCIS4610, Spring 2009

Creatin Crea tin g tables and and in serti serting ng data in SQL SQ L Plus Plu s •

Star Startt SQL SQL Plus Plus and log log in in

•

Downloa Download d file Dev_T Dev_Tuto utoria rial_S l_SQL_ QL_SQL SQL_Pl _Plus us from from the course course web web site

•

Create Create tables tables STUDE STUDENT NT,, COURSE COURSE,, COURSE COURSE_STUD _STUDENT ENT

•

Insert Insert data data using using INSER INSERT T state statemen ments ts

Starting Oracle Designer


4

2

BCIS 5420

Start Sta rt Oracle Form Form Bui lder


5

CREATING A FORM USING USING A WIZARD Introduction to Oracle Form Builder


6

3

BCIS 5420

Oracle Form Bui lder


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



8

4

BCIS 5420

Create a data bloc k b ased o n a table • Right click on the STUDENT module and select Data Block Wizard •


9

Creating a data block (cont’ s) • Select Table/view option • Select a table by clicking on Browse button



10

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


11

Select database items • Move all the attributes into selected attributes pane using arrow buttons



12

6

BCIS 5420

Name your data blo ck


13

Select the “ … then call the Layout wizard” option



14

7

BCIS 5420

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


15

Select items t o be displayed • Select items to be displayed • Modify the labels (prompts) and widths of corresponding text boxes if necessary



16

8

BCIS 5420

Select a layout • Select form layout • Name the frame • Select the number of students to be displayed


17

Complete and view the form • Complete and view the form design



18

9

BCIS 5420

MODIFYING A FORM


19

Modifying the form •

To change color, select the form of its element

•

Go to Property palette

•

Modify Foreground and background color properties



20

10

BCIS 5420

Add a t itle to your form • Add a label using the toolbox on the left


21

Testing you r form • Go to Program/Run to view the form • Use Enter Query button to enter the query • records



22

11

BCIS 5420

Convert Major int o Li st Item • In the Property palette, change item type to list item


23

Convertin g Major into a list it em • Add item values using the property palette



24

12

BCIS 5420

Converting an item into a radio g roup • Change Item type for Grade_level to Radio Group. The corresponding textbox will disappear.


25

Adding r adi o b uttons •

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.



26

13

BCIS 5420

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


27

Save and test yo ur form



28

14

BCIS 5420

CREATING A FORM BASED ON TWO TABLES Introduction to Oracle Form Builder

29

Creating a Form B ased 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)



30

15

BCIS 5420

Creating a form based on two link ed 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


31

Creating a form based on two link ed tables • Follow the steps suggested by the wizard • Include only course name as a visible attribute



32

16

BCIS 5420

Creating a form based on two link ed tables • For the Courses frame, select tabular layout, 5 .


33

Compile and run yo ur form



34

17

BCIS 5420

Create an LOV • Right-click anywhere in module REGISTRATION and select LOV wizard • Select record group based on a query


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



36

18

BCIS 5420

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


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



38

19

BCIS 5420

Create an LOV • Specify title and dimensions • Specify the number of rows displayed at once


39

Create an LOV • Select COURSENAME as assigned value • Complete your LOV



40

20

BCIS 5420

Create an LOV • To test your LOV: – Run the form –

c on CourseName field

– Press CTRL+L


41

Create an LOV control butt on • 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 your canvas • Name your button using Property pallete Introduction to Oracle Form Builder


42

21

BCIS 5420

Create an LOV control butt on • Right click on the button, and open PL/SQL editor • Select Type – TRIGGER, Name – _ _ • Type in code the code below


43

Test an LOV button • Compile and test your button



44

22

BCIS 5420

CREATING A CUSTOM FORM


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 • Rename your block into COURSE_BLOCK



46

23

BCIS 5420

Create and m odi fy a canvas • Add a canvas by clicking on Canvas in object navigator and then on a plus sign •

, Layout Editor


47

Create and m odi fy a canvas • Change the background color of the canvas using the Property palette •

“

”

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



48

24

BCIS 5420

Add t ext b oxes an d p rompts • Using the toolbox on the left, add the following text items and a corresponding prompts: – Course name – Meeting Time – Room


49

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



50

25

BCIS 5420

Adding PL/SQL c ode • Create triggers for each of New and Clear buttons • Add PL/SQL code to the triggers


51

Adding PL/SQL c ode •

Create triggers for each of the Save button

•

Add PL/SQL code to the triggers



52

26

BCIS 5420

Adding PL/SQL c ode •

Create triggers for each of the Find button

•

Add PL/SQL code to the triggers


53

Adding PL/SQL c ode • Create triggers for each the Exit button • Add PL/SQL code to the triggers



54

27

BCIS 5420

Creating a sw itch board • 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


55

Creating a sw itch board



56

28

BCIS 5420

Creating a sw itch board • Add triggers to corresponding buttons


57

Creating reports



58

29

BCIS 5420

Opening r eport buil der


59

Use report wizard to c reate a report



60

30

BCIS 5420

Using report w izard • Click OK on the welcome page and then specify the type


61

Using report w izard • Name your report and select the desired style



62

31

BCIS 5420

Using report w izard •

Select SQL query as a basis for the report

•

Build your query (you may be asked to log in at this point)


63

Using report w izard • Designate columns as group fields



64

32

BCIS 5420

Using report w izard • Select columns to appear in the report


65

Using report w izard • Specify labels and field length for each of the items



66

33

BCIS 5420

Using report wizard • Select a template


67

Using report w izard • Finish and view your report



68

34

Oracle forms developer tutorial Designer

Recommend Documents