Creating a basic application in APEX 5.0

iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the first tutorial is presented, you will learn how to create a basic application.

This tutorial helps you step-by-step through the process of creating a basic application in Oracle Application Express 5.0.
To create a basic application with a few pages, you have to follow the next steps. We decided to split up this tutorial into six steps.

In this tutorial we talk about Oracle Application Express when Oracle APEX is mentioned. The intention of this tutorial is to become familiar with Oracle APEX 5.0 and its features.

We assume you have an environment where you can work with APEX 5.0.

Step 1: Upload and run SQL script.

First, go over here: https://goo.gl/cg2izy. Click ‘Tutorial 1 – Creating a basic application in APEX 5.0’ and you a page appears where 3 SQL-files are presented. Download the scripts to your local drive.

Login to the workspace provided to you. You will reach a window with these icons at the top of the page. Click ‘SQL Workshop’.

tut1image004

Now click ‘SQL Scripts’.

tut1image006

You reach a page where you can upload the SQL files by clicking ‘Upload’. Unfortunately the script cannot be loaded all at once. Click ‘Choose File’ and select the SQL-file you want to upload. Finally fill in the field ‘Script Name’ and click ‘Upload’ at the bottom of the page. In this tutorial keep the script name the same as the filename.

tut1image008

Repeat this steps for the other files. When all files are uploaded, they have to be executed. Select the file you want to execute by clicking the checkbox at the left side. Now click on the ‘Play’ button at the right side of the row.

tut1image010

The next page will appear. Just click ‘Run in Background’.

tut1image012

When you run these scripts in the right order ( 1_tutori… .sql, 2_tutori… .sql, 3_tutori… .sql) they should return no errors.

Step 2: Create a new application

After you have logged in to the right workspace, a window with this icons will appear. Click Application Builder and the next window will appear:

tut1image014

To create the application, click Create. Now it’s possible to choose a type of application. In this tutorial we want to create a desktop application. This option is already selected by Oracle APEX. To continue, click the icon or click Next.

tut1image016

The next page allows you to enter basic information about your new application.

tut1image018

Some values (Schema, Application, Theme, Theme Style) already have a default value. In this window only change Name. In this example, let’s name it ‘My APEX 5.0 Application’. Click the button Next. This same action also has to be performed in the next page.

tut1image020

In the following page, it’s possible to indicate that you want to copy Shared Components from other Applications. The default value is ‘No’. Don’t change this value and click Next.

tut1image022

The next page allows us to enter global settings for the application such as language and date/time settings. Don’t change the default values except for Date Format. Click the arrow at the right of the field and select the value ’12-JAN-2004’ in the popup window that appears. The popup will close automatically and will set the date format to ‘DD-MON-YYYY’.

tut1image024

Later you will see it is possible to use another format on a date field.

tut1image026

Click Next. A page with summarized information of the new application will appear. Now it’s time to create the application by clicking on ‘Create Application’.

tut1image028

You will see the Application Builder for the application we just created. In this example, the application number is 107. Two pages are already generated. (1 – Home, 101 – Login Page).

Step 3: Create an interactive report

We continue where step 2 ended: in the Application Builder. Click the button ‘Create page’.

tut1image030

A window with many page options appears. Click the icon ‘Report’.

tut1image032

A window with three options for report types appears. Click ‘Interactive Report’.

tut1image034

In the next window it is possible to enter the first basic information about the page we are creating. Only enter a clear page name. Here we enter ‘Department employees’. When you leave this field it automatically updates ‘Region Name’ with the value you just entered. In this situation this is ok.
Click Next.

tut1image036

Now it is possible to add information about navigation in menu entries. Accept the defaults by clicking on Next.

tut1image038

Next add information about which table or view we want to use in this interactive report.

  • Don’t change anything for the default values of ‘Source Type’ and ‘Table/View Owner’.
  • Choose ‘IADV_DPT_EMPLOYEES_VW (view)’ at ‘Table/View Name’.
  • As you can see, all available columns in the shuttle are at the right side so no action at this field is required.
  • Change value to ‘No’ at ‘Link to Single Row View’.

Continue the creation by clicking Next.

tut1image040

A window with summarized information about the interactive report appears. Now create the page by clicking Create.

tut1image042

Now you see the Page Designer from APEX 5.0.
You can watch the result by clicking on the blue ‘Play’ icon (Save and Run) at the right corner on top of the screen. Because this is (probably) the first time you navigate to the ‘runtime’ version of your application, you have to login. Use the same combination of user and password as you do in the Application Builder.

tut1image044
tut1image046
Keep in mind: in Oracle APEX 5.0 your application will by default not appear in the same window or tab as where you click ‘Save and Run’. When you use Google Chrome or Internet Explorer, it starts in a new tab. In Safari and Firefox a new window will open.

tut1image048

tut1image050

As long as you open both tabs, it is possible to use the Oracle APEX developer toolbar at the bottom of your ‘runtime’ screen. When you click ‘Edit Page 2’ you automatically switch to the tab window of your Page Designer. If you change something in the Page Designer and then click the blue icon (Save and Run), you automatically switch to the tab with your ‘runtime’ page.

tut1image051

A bit more about this toolbar: all items are clickable. What happens if you click on an entry of this toolbar?

  • Home:
    You will navigate to the workspace where your application belongs to.
  • Application xxx:
    You will navigate to the Application Builder of your application
  • Edit page x:
    Depending the settings you made before, you will navigate to your page in the Page Designer or in Component View
  • Session:
    Shows session state information about your page. Change the value in select list ‘View’ to ‘All’ to see more information.

tut1image054

  • View Debug:
    You will see the debug reports. Click on one of the numbers under ‘View identifier’ to see detailed debug information.

tut1image056

tut1image058

  • Debug:
    This toggles the page between Debug mode and No Debug mode.
  • Show Grid:
    This toggles between Show Grid and Hide Grid. This is only applies if a grid layout is used and that layout supports showing a grid and the page has more than one column.
  • Quick Edit:
    The cursor changes into a plus symbol (+) and then it’s possible to click the desired component in your page you want to (quick) edit. When you click you will navigate to the page in the Page Designer, even if you explicit showed this page in Component View before.
  • Theme Roller:
    It’s is possible to easily customize the appearance of the application by using all options that are shown in the window that appears. This theme roller only appears for themes supporting this feature. This theme roller offers some fancy features to change the layout of your page.

tut1image059

tut1image061

  • The ‘wheel’ icon:
    This offers you possibilities about where your toolbar is located. For example, the toolbar can be located at the right side of your page.

tut1image064

 

Step 4: Edit the interactive report

Let’s edit some little esthetical aspects in the interactive report we just created. As you can see, a few columns can be removed or hidden from the report and some headers have to be edited.

tut1image066

Columns Dee Id, Dpt Id and Epe Id are not interesting to show in the report. Besides this, Name and Employee Name have to be changed to more clear terms. Also despite our Application Setting concering date formats, we still want to change the date format for these columns into DD-MM-YYYY.

tut1image068

Click ‘Edit Page 2’ or navigate to page 2 in the Application Builder. Now you’re in the Page Designer. At the left side of this Page Designer you see all regions on this page. When you expand some nodes in this part of the page, all report items will be shown. In this example we have to edit region ‘Department employees’.

tut1image070

In APEX 5.0 it is possible to do a multiple selection by using CTRL and clicking on items you want to select. We decided to hide items Dee Id, Dpt Id and Epe Id. These items match with DEE_ID, DPT_ID and EPE_ID in the Page Designer. When you do a multiple selection, it will look like this in the Page Designer.

tut1image072

At the right side of the Page Designer it’s possible to change properties for all the selected items at once.

tut1image074

Again, we want to hide all selected items. Under ‘Identification’, change the item type to ‘Hidden Column’.

Now, we want to change the headers ‘Name’ and ‘Employee Name’ into ‘Department’ and ‘Employee’. Also align these headers at the left side. For ‘Name’, select report item ‘Name’ in the Page Designer.

tut1image076

At the right side, change the values under ‘Heading’.

Old properties

tut1image078

New properties

tut1image078

You can repeat this action for ‘Employee Name’. Change properties for item EMPLOYEE_NAME.

When you run the page, this result will be shown.

tut1image082

Now we have to change the alignment for ‘Start Date’ and ‘End Date’. You have already done this for other items, so the only hint we will give is that these items can be found in the Page Designer as START_DATE and END_DATE. The headers have to be aligned to the left. Also change the headings so only the first character is uppercase (Start date and End date).

When you have finished the actions for the headers, it’s time to change the format mask of these date items. At the right side of the Page Designer, navigate to the section ‘Appearance’. It’s important to notice properties can be shown in two ways. Here it is possible to add a format mask. You can use the list or you can write something yourself. Let’s enter DD-MM-YYYY.
It’s possible to show common properties or all properties. When common properties are shown, you cannot find ‘Format Mask’. Just click ‘Show all’ and this property will appear.

Show common properties

tut1image084

Show all properties

tut1image086

tut1image088

Again, push the Save and Run button at the right top of the Page Designer.

Now your interactive report is ready!

tut1image089

 

Step 5: Create a form

In this step we will create a report with a form based on an table with employees. (IADV_EMLOYEES). Make sure you’re in the Application Builder of the application you created before and click ‘Create Page’.

tut1image092

Choose the icon ‘Form’.

tut1image094

In the next window, choose ‘Form on a Table with Report’.

tut1image096

Only change ‘Page Name’ in the next page. As seen before, ‘Region Title’ will automatically be edited when leaving ‘Page Name’.
Click Next.

tut1image098

Now we can enter the table we want to display in the form and region. Select IADV_EMPLOYEES and click Next.

tut1image100

As before, in this tutorial we skip navigation, so you can immediately click Next.

tut1image102

Now we can select which columns will be shown in the report. Leave the defaults and click Next.

tut1image104

In the page that appears we can select the icon which will be used to navigate to the detail form. Select an applicable icon and click Next.

tut1image106

Next, we will arrange settings for the detail form. Edit ‘Page Name’ to ‘Employee’ and navigate out of this item (for example, user your tab key). You will see that ‘Region Title’ changes to ‘Employee’.
At ‘Page Mode’ choose ‘Modal Dialog’. Click Next.

tut1image108

At ‘Primary Key Type’ select ‘Select Primary Key Columns’. Two items will apear. In the first item (‘Primary Key Column 1’) select column ‘EPE_ID’ and then click Next.

tut1image110

On the next page leave all defaults and click Next.

tut1image112

Here, we can select the columns which have to be shown in the form. Select all columns by clicking on the double arrows which point to the right side of the shuttle. Then click Next.

tut1image114

Next you can indicate whether you want to use functionality concering insert, update and/or delete. Leave all defaults as generated by APEX and click Next.

tut1image116

The last page of this ‘wizard’ shows summarized information about the report and form we want to create. You can edit nothing over here. Just click Create.

tut1image118

Now you arrive in the Page Designer again. Click the Save and Run button (the blue icon with a with triangle inside) to take a look at your result.

tut1image120

The second column from the left (Epe Id) needs to be a hidden column. Besides this, we want all headers to align at the left side. We already did some actions in another report to change these settings. Can you do it again to let this page look like the following image?

tut1image122

Now let’s take a look at the form, the modal page. You can navigate to this form by clicking on the edit icon.

tut1image124

Edit properties to change prompts work in a simular way as shown before.

Aligning items is the next action for this page. Place First Name and Last Name on the same line. Also Email Address and Phone Number have to appear on the same line and change their order. In Oracle APEX 5.0 you can to this in the Page Designer by a kind of drag and drop action. Select the item you want to replace. Move your cursor over the item in the center part of the Page Designer (Grid Layout) until your mouse pointer like this:

tut1image125

tut1image127

tut1image129

For P4_EMAIL_ADDRESS and P4_PHONE_NUMBER you can do the same actions. Finally it looks like this in the Page Designer.

tut1image131

When you look at the ‘runtime’ version of this page, you can see ‘Last name’ is placed at the right side of ‘First name’ and ‘Phone Number’ appears before ‘Email Address’.

tut1image133

As you can see, the width for ‘Date of birth’ is not very nice so the alignment of the page can be better. One of the options to align the items is to give them all the same width. Because P4_DATE_OF_BIRTH is a Date Picker and all other items are Text Fields, it is not possible to edit the width for all items at once. So first select P4_DATE_OF_BIRTH and navigate to section ‘Appearance’ at the right side of the Page Designer and change width into 20. Select P4_FIRST_NAME, P4_LAST_NAME, P4_PHONE_NUMBER and P4_EMAIL_ADDRESS and change width into 20 for all items at once. You can do this multiple selection by clicking the items one by one while you use the CTRL-button from your keyboard.

tut1image135

tut1image137

When you run the application and navigate to the adjusted page, you see the alignment becomes much better.

tut1image139

The last action is to edit the label for ‘Phone Number’. Let’s change this to a label which indicates this field is required.  Use the developer toolbar by clicking ‘Quick Edit’. Click at ‘Phone Number’ in your screen and you will jump to the page in the Page Designer.

tut1image141

In the Page Designer go to section ‘Appearance’ at the right side of your page. There you can change the value in ‘Template’ to ‘Required’.

tut1image143

Now click ‘Save and Run’ and you will see the item is shown with a changed label.

tut1image145

Step 6: Adding navigation

In the final step of this tutorial we will add navigation to an interactive report to navigate to another page. At this point you can see this in your Application Builder.

tut1image147

In page 2 we will add a button to navigate to page 3. So now open page 2 by clicking on it. The Page Designer will appear. Right click the region ‘Department employees’ and a popup menu will appear. Click ‘Create Button’ as shown.

tut1image149

You will see a new folder name ‘Region Buttons’ under the existing region ‘Department employees’. Besides this, in the center part of the Page Designer there you can also see the new button.

tut1image151

At the right side of the Page Designer we can adjust some settings for this new button.
Copy the values as shown in the following images. In detail you have to edit a few properties in the sections Identification (Button Name, Label), Layout (Button Position), Appearance (Hot) and Behavior (Action, Target).

tut1image153

tut1image155

tut1image157

tut1image159

 

When you have finished this, click Save and Run. You wil see at the top of your interactive report a button ‘Employees’.

tut1image160

When you click this button you will navigate to the page containing more detailed information about Employees. To complete navigation we will add a menu to this application. Make sure you are in the Page Designer and select the page at the left side of your page.

tut1image164

Take a look at the properties under Navigation Menu at the the right side of your page.

These properties need the following values:

  • Override User Interface level: Yes
  • List: Desktop Navigation Menu
  • List Position: Side
  • List Template: Side Navigation Menu

Save this settings.

tut1image166

Make sure you’re in the Application Builder. There you will find the icon for Shared Components. Click it for the next action. On the page that appears you see several sections. Under ‘Navigation’ you can click ‘Navigation Menu’.

tut1image168

tut1image170

You will arrive on a page where you have to click ‘Desktop Navigation Menu’.

tut1image172

The screen that appears offers you the possibility to add menu entries. To do this click ‘Create List Entry’.

tut1image174

Add information on this page

  • Section Entry
    List Entry Label: Department employees
  • Section Target
    Target type: Page in this Application
    Page: Page 2 (Department employees)

tut1image174

Now use the button ‘Create and Create Another’ to repeat this for the other pages. When you add the last page use ‘Create List Entry’ instead. Navigate to the ‘runtime’ version from our application. Now you can see a menu at the left side of the application.

tut1image178

That’s it for this tutorial, great job!

One thought on “Creating a basic application in APEX 5.0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

About scholhu