This tutorial is designed to walk you through chapter 2 of the Oracle APEX Advanced Tutorial, Creating a Tabular Form. We will be creating an application with a form, testing the base form, then extending the functionality of that base form.

If you haven’t already, please review our APEX Tutorial Preparation and OEHR Sample Data Install Video Walkthrough.

APEX 4.2 Series

We created and made the original set of tutorials back in 2009 when version 3.2 of APEX was released. Since then we have had an amazing response to our series, even as new versions were released. As of 2013, APEX is now at version 4.2, with significant changes made to it’s appearance, style, and abilities. As the mounting number of requests came in, we decided to redo our original series, updating them for the newer software. But we are leaving the originals in place as well.

Oracle has updated APEX, but has not updated the tutorial documentation for the newer versions. These new videos will be using the original documentation as their starting point, with changes made where needed to adapt to the updated software. Many of these changes are small, icon changes, flow changes and the like. But there are enough of them over the gamut to have introduced confusion and requests to explain. We will note where major differences are in the videos between the older 3.x and the newer 4.x versions. So welcome to the APEX 4.2 Advanced Tutorial Series.

Video Walkthrough – Creating A Tabular Form

 

[youtube height=”315″ width=”100%”]http://www.youtube.com/watch?v=v9mw7XCCP84&feature=youtu.be[/youtube]  

High Level Steps

Before you begin, we recommend you go through our APEX Tutorial Preparation and OEHR Sample Data Install.

1.     Login to Apex (1:33)
2.     Create Application (2:20)
3.     Create Form (4:40)
4.     Run and verify Form (9:00)
5.     Extend Form (9:45)
6.     Test extended Form Functions (12:40)

Times in parenthesis are the approximate start time for that step.

Entered Code

List of Values SQL call

 SELECT DISTINCT department_id a, Department_id b FROM oehr_employees

Notes and Details

Login

It is important that the account you log into in APEX has at least Developer level rights, or Workspace Admin rights. You also must use an account (of this level) that has access to the workspace that the OEHR sample data was installed to. For a walk through of this process, there is a link above to the OEHR install tutorial.

Interface Appearance

APEX has undergone changes from version 3.x to version 4.x and many of these are visual changes. If you were familiar with APEX 3.x take a few moments on a screen to familiarize yourself with the layout changes. Most of the labels are still the same, but the icon may have changed. For example, in 3.x the ‘run page’ icon was a traffic light with a green light lit. In 4.x it is simply a square ‘play’ button (triangle pointing right).

Application Attributes

This screen of the application creation process gives us the option of how to authenticate our users and select the interface languages. For most of these tutorials we will use the APEX (Application Express) authentication option. Only users setup within the APEX system will be available to use the application. The No Authentication option allows anyone to use the created application, and Database Account allows you to use the Database Authentication model. This last one is most useful if APEX is installed against a full Standard or Enterprise Oracle Database install. We also have the option of forcing the language the application displays in. For most of these tutorials, we will be accepting the defaults.

Table/View Owner

This screen allows you to specify which schema setup will be used to find the source table or view. In other words, this would allow us to use tables owned by other schemas if the permissions exists. We also use this page to limit the allowed operations for the form, as well as what columns will appear.

Displayed Columns

This tutorial is a simple one, so to keep the form on a single screen, we are limiting the column we are showing. By default APEX includes all rows for display. The box on the left shows columns that will NOT be displayed, while the right box is the ones that will be displayed. A way to remember it is the ones on the left are ‘left out’ of the display. We need the following column to be displayed

First name  Last Name  Hire Date  Salary  Department

Primary Key

This allows you to select either the defined primary key for the source table, or a different key. You can also select a second key column. You also can alter the source of the key trigger.

Updateable Columns

Here we have the option of including or excluding a given column from being updateable. We can also adjust the order in which the columns are displayed. We can make these changes elsewhere as well, but here is a graphical, somewhat easier place to make such edits if they are needed.

Branching

Branching is the process by which you can define the behavior of the application when an event occurs. In this case, you can select what page of the application will be brought up when the ‘Submit’ (Apply Changes) button is pressed. This allows you to branch off to confirmation screens, or secondary data input screens, or by putting in the same page number as the page your creating, just refresh the existing page.

Regions and Templates

Every page is an APEX application is filled with predefined regions and points for objects to be displayed in. Most of the time we will be using the defaults, but there are options, and lots of them. We mention it so you know about the potential for display changes in your applications.

Dynamic List of Values

Any List of Values (LOV) in APEX requires either a static or dynamic list to operate. Any element in an LOV has 2 parts, a Displayed value, and a Returned value. We will see in later tutorials more of the abilities of LOV. The SQL query that drives a dynamic LOV have a built in sort of help during creation by typing out the basic framework of the query for you. They also allow you to have different values for displayed and returned values. In this case, you could make it so rather than the department ID being both the display and return, you could make it so the department name is displayed, but it’s value is returned (User would see the Department name in the drop down list, but when any updates are committed, the ID is actually what is altered). You can also choose to have null value display (IE a ‘please select one’ option) if there is no set value for that field already. These have their own fields called Null Displayed Value and Null returned Value.

Many of the topics touched upon will be revisited in later tutorials, and of course, you can experiment yourself with the APEX system to further your understanding of it’s conventions and workflow patterns.