Creating reports and forms through the Oracle Developer and Designer

The Oracle DBMS is a comprehensive data storage solution. However, an oracle database itself is of little use to an end user if a DBA or other programming savvy person has to enter data into tables or retrieve appropriate views and queries all the time. Today, users may want to enter data from remote locations and view select data in a certain format. Even the earliest versions of Oracle that simply supported command line text output supported basic formatting and reporting. The following is an example of such a utility used to recover the output of a certain format. Note that using such a utility still requires a level of programming skill and SQL knowledge. Normally, a DBA would execute such a query, save the results in a file and send it to the user who requested the data:

Screenshot 10a: Format Columns in CLI

Oracle forms and reports are applications created using Oracle data objects to hide the logical and technical design of the database from the end user. Forms represent units of data entry. End users with little or no technical knowledge may complete and submit a form. Scripts written by the DBA or programmer who designed and developed the form process the form and insert the data into the database. Similarly, frequently invoked queries may be programmed into a well-formatted and clear report that summarizes required data.

Oracle Developer and Designer are powerful GUI tools that allow developers a great deal of flexibility in creating applications for Oracle databases. Developer 2000 may be used to design a form or report on a canvas and add widgets like text boxes, radio buttons and so on. Triggers may be written and associated with submit buttons; these triggers insert data into the database or modify existing data. Triggers are also used to check if entered values are valid. The Object Navigator is used to view, add, delete, or rename form and report objects. Oracle Developer also contains a Layout Editor to design forms and add widgets, a PL/SQL Editor that allows users to write programs that add functionality to form elements and a Menu Editor that allows user to add pull down menus to a form or report.

Screenshot 10b: Form layout Through Developer

While using Developer 2000 requires a level of expertise, the built-in Application Tools that come with Oracle 10g Express Edition to create forms and reports is a good introductory exercise to creating oracle applications. Download Oracle 10g Express Edition and install it on your computer. Make sure that the database server is up; if not, start it by clicking Start -> Programs -> Oracle Database 10g Express Edition -> Start Database. Wait until the database starts before continuing. We will first create a report that displays the number of books in the database for each author.

1. Click on the Start -> Programs -> Oracle Database 10g Express Edition -> Go To Database Home Page. This will open up a login screen. Login as user 'booksdb' with password 'booksdb'. The following screen will be displayed upon login:

Screenshot 10c: Database Home Page

2. Click on the 'Application Builder' icon. This should take you the following screen. Click on 'Create'.

Screenshot 10d: Create Application

3. Do not change anything on the next screen; the 'Create Application' Radio Button should be checked by default. Simply click 'Next >'
4. In the next screen, enter 'AuthorReport' next to 'Name' and leave the 'Application' field value as it is (it is probably 100). The 'From Scratch' Radio button should be checked by default. If not, check it and proceed by clicking 'Next'
5. In the next screen, check the radio button next to 'Report' under 'Select Page Type:'. Select 'SQL Query' from the list next to 'Page Source:'. Change the 'Page Name:' field to 'Books Count By Author'

Screenshot 10e: Page Details

6. Copy and paste the SQL query below into the Blank Text Area under 'Query:'. This query joins the book and author table on author id. Then, a count of books for each author is obtained. Click 'Add Page'. Select LASTNAME, FIRSTNAME, count(BOOKID) as "NUM_BOOKS" from BOOK, AUTHOR where BOOK.AUTHORID = AUTHOR.AUTHORID group by LASTNAME, FIRSTNAME order by LASTNAME, FIRSTNAME
7. You should now see a record of the added page (look at the following screen shot). Click 'Next >'

Screenshot 10f: Added Page Record

8. Check the 'No Tabs' Radio Button in the next screen and click 'Next - >'
9. Leave the next screen as is (The Radio Button next to 'No' under 'Copy Shared Components From Another Application' should be checked by default'). Click 'Next - >'
10. Select 'No Authentication' from the list below 'Authentication Scheme:'. Leave the other fields as is and click 'Next ->'
11. Select a theme you like by checking the Radio Button above it. I selected Theme 11 for this exercise. Click 'Next >'
12. You should see a confirmation similar to the following ScreenShot. Click 'Create'

Screenshot 10g: Create Application Confirmation

13. On the next page, click on the traffic signal icon that says 'Run Application'. You will see the report you just created:

Screenshot 10h: Report Output

Next, create a form that will allow you to add new books to the database for an existing author and subject. The first few steps are almost the same as those for report creation with a few important differences.

1. Click on the Start -> Programs -> Oracle Database 10g Express Edition -> Go To Database Home Page. This will open up a login screen. Login as user 'booksdb' with password 'booksdb'.
2. Click on the 'Application Builder' icon. Click on 'Create' .
3. Do not change anything on the next screen; The 'Create Application' Radio Button should be checked by default. Simply click 'Next >'
4. In the next screen, enter 'BookForm' next to 'Name' and leave the default number next to Application. This should reflect the number after number you entered for the report from the first section (probably 102). The 'From Scratch' Radio button should be checked by default. If not, check it and proceed by clicking 'Next'
5. In the next screen, check the radio button next to 'Form' under 'Select Page Type:'. Type 'BOOK' next to 'Table Name:'. Click 'Add Page'.

Screenshot 10i: Form layout Through Developer

6. You should now see a confirmation of the added page. Click 'Next >'
7. Check the 'No Tabs' Radio Button in the next screen and click 'Next - >'
8. Leave the next screen as is (The Radio Button next to 'No' under 'Copy Shared Components From Another Application' should be checked by default'). Click 'Next - >'
9. Select 'No Authentication' from the list below 'Authentication Scheme:'. Leave the other fields as they are and click 'Next ->'
10. Select a theme you like by checking the Radio Button above it. I selected Theme 8 for this exercise. Click 'Next >'
11. Click 'Create'
12. On the next page, click on the traffic signal icon that says 'Run Application'. You will see the form you just created:

Screenshot 10j: Form With Limitations

The problem with the above form is immediately apparent. The user is asked to enter the authorid and subjectid! We will now change the application so that these fields appear as selection lists that contain author names and subject descriptions.

On the main XE Database Home page (connected as user booksdb), click on the down arrow next to Application Builder. Scroll to View Applications and click.

In the next page, click on the icon above 'BookForm'. The following page will be displayed. Click on the icon above '1 - Book'

Screenshot 10k: Edit Application

You will now see a layout page; part of it is shown in the next screenshot. This page is the XE equivalent of Developer's layout page.

Screenshot 10l: Form layout - Elements

You will see an item 'P1_AUTHORID' under 'items' in the 'Page Rendering' section on the left hand side of the page. Click on 'P1_AUTHORID'

In the next page, select 'Select List' from the list next to 'Display As' in the 'Name' Section (you can also do this by clicking the '[Select List]' link in red font just underneath the list)

Screenshot 10m: Changes To Element P1_AUTHORID

Change the value next to 'Label' under the label section (currently authorid) to 'Author Name'.

Click on the [LOV] link in the top bar. This will take you to the LOV (List Of Values) section.

In the LOV section, select 'Yes' next to Dynamic Translation. Under 'List of values definition:', copy and paste the following query - 'select lastname||', '||firstname as authorname, authorid from author order by lastname, firstname' . The following Screenshot shows you the edited LOV section.

Screenshot 10n: Edited LOV for P1_AUTHORID

The select list will now be populated by the name of the authors. A dynamic LOV query for an XE application should be of the form 'select display column, value column from table'. We use the double pipe (||) to concatenate the author lastname and firstname with a comma in between and use this as the display column. The actual value that will go into the database upon form submission is authorid, the second column.

After you verify all the changes, scroll back up and click on 'Apply Changes'.

You will see the following message: Page "1" item "P1_AUTHORID" processed.

Now, click on the item 'P1_SUBJECTID' under 'items' in the 'Page Rendering' section on the left hand side of the page. This is just below the 'P1_AUTHORID'.

In the next page, select 'Select List' from the list next to 'Display As' in the 'Name' Section (you can do this by clicking the '[Select List]' link in red font just underneath the list also)

Change the value next to 'Label' under the label section (currently authorid) to 'Subject'.

Click on the [LOV] link in the top bar. This will take you to the LOV (List of Values) section.

In the LOV section, select 'Yes' next to Dynamic Translation. Under 'List of values definition:', copy and paste the following query - ' select subjectdesc, subjectid from subject order by subjectdesc' .

The select list will now be populated by the subjects.

After you verify all the changes, scroll back up and click on 'Apply Changes'.

You will see the following message: Page "1" item "P1_SUBJECTID" processed.

Screenshot 10o: Top Bar Links - Click Application ID

Now, click on the 'Application <App_ID> link(Application 102 in the screen shot a) on the top bar. Click on the traffic light 'Run Application' icon. You will now see a form that allows you to choose an author and subject from a list.

Screenshot 10p: Complete Book Entry Form

You may think of not being able to add a new author and subject as a restriction. Normally, an application would include forms for each task. Thus, a real application would include an author entry form, a subject entry form, and a category entry form. A user who wants to enter a book by a new author would add an author first. As an exercise, you can create forms that allow users to add new authors, categories, and books.