Oracle APEX Cookbook : Second Edition

Click on the Application Builder icon on the left-hand side of the screen.

You will see a page where you can choose between database applications or websheet applications. Furthermore, you see already created applications.

Click on the Create button on the right-hand side of the screen. Two options are shown. You can now choose between a database application and a websheet application. We select the database application.

In APEX you have two ways of selecting and proceeding to the next step most of the time. Usually, there is an icon accompanied by a radio button. When you check the radio button, you must then click on the Next button to proceed. When you click on the icon, you automatically go to the next step. In this book, when there is the situation that you have to select one of the shown options, we will only tell which one of the options you should select. You can decide for yourself which way to select and proceed:

Select Database.

In the next step we can now choose between From Scratch, From Spreadsheet, and Demonstration Application. When you choose Demonstration Application, Oracle APEX creates an application which shows the possibilities of APEX. However, we want to create an application by ourselves, and we can install this demonstration application at a later time. For now, we select "From Scratch".

Enter a name and an application ID . Preferably choose a name which covers the goal of the application. In our case, we call the application Intranet.

Application ID is a generated and unique identification number, but you can also use some other number for your convenience.

At the Create Application radio group, leave this option to From Scratch.

Finally, select the schema where the tables that you want to use for your application will reside and click on Next.

The next step in the wizard is selecting pages. You can start with a blank page and, from that starting point, extend your APEX application. You can also choose to add reports and forms beforehand. At this point you don't actually define the content of the pages, you just create the "skeleton" of them.

We choose to add one blank page and proceed to the next step.

Now you can choose to include tabs in your application. Tabs are components that help you navigate through the application. For now, we are not going to use tabs, so select No Tabs.

Select the standard Application Express authentication scheme.

You can select the language used in your application, as well as where the user language should be derived from.

The last option in this step is the date format mask. Click on the LOV button next to the text item to get a list with possible date format masks and select one.

The last step in the wizard is the theme. Theme 1 and Theme 2 are basic themes. If you don't like all those colors, just select something like Theme 18. That is quite a simple theme.

We select Theme 1 and that completes the Create Page wizard.

Click on the Create Page button.

You will get an overview of page types. Select Form.

Now you get an overview of types of forms, such as forms based on a procedure, forms based on a table or view, or forms based on a query. Select Tabular form.

The next step is to choose the table owner and the allowed operations. Here you can decide what your form should do: update only, update and insert, update and delete, or all operations (update, insert, and delete). Select all operations.

Select the table or view your form should be based on. If you know the table name, you can type it in the text field. Otherwise, click on the button next to the field and select a table from the pop-up list. We choose the employees table, EMP.

Now you can select the columns, which should be visible in the form. You can select columns by clicking on the column while holding the Ctrl key. To select all columns, you can click on the first column and drag your mouse to the last column.

If a Primary Key constraint is defined on the table we use, then it will automatically be selected. Otherwise, select the primary key by hand. APEX needs to know this to be able to update the changed rows.

Next, you can choose which way the Primary Key is automatically filled. Maybe you created a trigger which updates the Primary Key in case it is empty. However, we choose to update the Primary Key via an existing sequence. Select this option, and in the following listbox, select the appropriate sequence and proceed to the next step.

Next, you can select which columns in the form should be updatable. Select the desired columns and click on Next.

In this step, you can give the page a name and a page ID. Furthermore, you can specify a region title, a region template, a report template and decide if your forms page should contain breadcrumbs. A breadcrumb is a navigation component that shows the path to the current page. Leave the options as they are and click on Next.

We are not going to use tabs, so leave this option on its default selection and click on Next.

Next, fill in the names that should appear on the buttons in the form and click next.

In this step, you must define the branches. Branches are links to other pages. It is important to know which ID a page had in order to fill in the branches at this point. Usually, the Cancel button branches to the main page of the application. But it's also possible to find the page to branch to by using the LOV button. For the Page Submit branch, select the page ID assigned to the page we want to branch to. Click on Next.

The last step in the wizard is the confirmation page. Check the data. If something is wrong, you can go back using the Previous button. Otherwise, click on the Finish button.

Click on Create page.

Click on the form icon.

Click on the form in the table or view.

Select the schema where the employees table resides and click on Next.

Enter the table name (in this case, it is EMP) and click on Next.

In the page number and page name dialogs, just leave the settings as they are and click on Next.

Select Do not use tabs and click on Next.

Select the Primary Key and click on Next.

Select Existing and select the desired sequence name in the listbox. Click on Next.

Select all of the columns and click on Next.

Change the button labels or leave them as they are and click on Next.

Enter the page numbers APEX should call when submitting or cancelling and click on Next.

In the confirmation dialog, click on Finish.

In the success message dialog, click on Edit.

In the Application Builder, go to the application we just created and click on the Create Page button.

In the Page Type dialog, select Report.

A page is shown where we can choose between the different types of reports. Options are: interactive reports, classic reports, reports based on a web service result, and wizard reports. We will choose classic reports.

Some of the other types of reports will be covered in other recipes in this book. The next recipe is on interactive reports. In Chapter 8, Using Web Services, some examples of building reports on web services are explained. The Wizard Report option is not explained separately, because it just offers an easier, step-by-step way of building a report.

In the next step you can assign a page number and a page name to the report. Furthermore, you can indicate whether you would like to have breadcrumbs on your report page. Leave the Breadcrumb option to Do not use breadcrumbs on page and click on Next.

In this step you can choose to include tabs in your report page. We leave it to Do not use tabs. Click on Next.

In this step you must enter a query in the textarea. You can use the Query Builder to help you build your query or you can also enter it manually. We use the following query:

select * 
  from emp

After you have entered the query, click on Next.

In the next step, you can define a report template, the region name, a number of rows displayed per page, and whether the user should be able to print the report on paper or spool it to a comma-separated file. Leave the options as they are and click on Next.

In the last step, you see the confirmation page. If the choices made are not satisfactory, click on the Previous button to go back and modify the options. Otherwise, click on the Finish button.

Right-click on the region name of the report and click on Edit to see the details of the report.

In the Region source you can see the query you just entered. If you want to see the column details, click on the Report Attributes tab:

Here you can modify the column heading, the heading alignment, or the names of the columns. To get more into details about a column you can click on the pencil icon next to the column name.

Sometimes in a project, the business case for a report changes. Instead of a classic report, the customer would like an interactive report. In case there are two options, remove the Current Report region and create a new one based on an interactive report or just migrate the current report using built-in functionalities.

When we are looking at the Region Definition tab of the Edit Region page, we can see a Tasks list on the right-hand side of the screen. One of the options is Migrate to Interactive Report. This migration is not a Holy Grail, but can save a lot of time in the migration process.

Click on the Migrate to Interactive Report link.

In the following page, enter EMPNO in the Unique Column field and click on the Migrate button to see what happens.

Go to Shared Components and then to Lists of Values.

Click on Create to make a new LOV.

Create it from scratch and call it ADDRESS_TYPE; it should be a dynamic LOV.

The query that it's based on is as follows:

select rv_meaning display_value
     , rv_low_value return_value
  from app_ref_codes
 where rv_domain = 'ADDRESSES'
[9672_01_02.txt]

Create a new region by right-clicking on the Regions label and selecting Create:

Select Report as the type of region.

Then select Interactive Report as the report implementation.

Give the region a title Contacts.

Select the value for Region Template APEX 4.0 – Reports Region. Keep the default values in the other fields.

As the source for the region, enter the SQL Query:

SELECT *
  FROM app_vw_contacts
[9672_01_03.txt]

Leave the other options with the default values.

Click on Create Region to finish this part of the recipe.

As you can see in the tree view, we now have a new region with all columns from the view:

When we run the page now, we can already see some data. APEX also generated a toolbar above the report that we can use to filter data or change the way it is presented:

The next step is to alter the report, so we can customize the column labels and change the way some of the data is presented.

In the tree view of Page Definition, right-click on the Contacts region and select Report Attributes from the pull-down menu.

Change the heading for the columns:

Click on Apply Changes to confirm the following changes.

This changes the labels for some of the columns in the report. Next, we will change the presentation of the data inside one of the columns.

Expand the tree view to show the content of Report Columns of the Contacts region.

Right-click on ADDRESS_TYPE and click on Edit.

Change the item Display Text As to Display as Text (based on LOV and escape special characters).

Under List of Values, select Use Named List of Values to Filter Exact Match from the pull-down Column Filter List of Values.

Select ADDRESS_TYPE as the value of Named List of Values.

Click Apply Changes.

Create a new region by right-clicking on the Regions label and choose Create.

Select Chart and click on Next.

Choose Flash chart and click on Next.

Select Pie & Doughnut and click on Next.

Select 3D Pie and click on Next.

Enter Employees in a department in the title, leave the other fields with the default values and click on Next.

Enter Employees in a department in the Chart title, leave the other fields the default values and click on Next.

We are going to select all employees per department, so we will enter a query that will get us that data into the chart.

Enter the following query into the SQL Query field:

select null link
     , dept.name label
     , COUNT(emp.ID) value1
  from APP_EMPLOYEES emp
     , APP_DEPARTMENTS dept
 where emp.dept_id = dept.id
 group by dept.name
[9672_01_04.txt]

Click on Create Region.

Run the page.

Right-click on Regions and select Create.

Select Map as the Region Type.

This will bring up a window with a couple of main categories of maps. Selecting one of these categories will bring up a list of sub categories that can be drilled down even further.

Select United States of America and click on Next:

In the following list select click on the + sign to open Country Maps and click on States:

Enter Contacts by State for the title and leave the rest with their default values and click on Next.

Enter the same text as the Map Title.

Enter the following query in the appropriate SQL Query area:

select null link
     , STATE label
     , COUNT(CONTACT_ID) value 
  from  APP_VW_CONTACTS
 group by STATE
[9672_01_06.txt]

Click on Create Region.

Run the page to see the result:

Create a new blank page. Name it Contact and assign page ID 999 to it.

Create a new HTML region on this page and enter the following text into it:

For more information send an e-mail to info@packtpublishing.com

For the second part of the recipe, we need an icon available. This can be any available icon as long as it measures 32 x 32 pixels. The Images directory of APEX offers some examples such as fndtip11.gif.

Go to Shared Components.

Click on Images.

Click on Create.

Select your application.

Select the image fndtip11.gif on your filesystem by using the Browse button.

Click on Upload.

Furthermore, check your page template. In the Navigation Bar Entry section you should see at least the tags for image, alt, width, and height. For example:

<a href="#LINK#"><img src="#IMAGE#" alt="#ALT#" height="#HEIGHT#" width="#WIDTH#" />#TEXT#</a>
[9672_01_06A.txt]

To check the page template, go to a page and click on the Page Template link on the right-hand side of the page (in the Templates section). Scroll down to the Subtemplate section and check the Navigation Bar Entry textarea.

Click on the button Create.

Select From Scratch and click on Next.

Select Navigation to URL and click on Next.

In the Entry Label, enter Contact and leave the rest on default. Click on Next.

Enter 999 into Page and default the other fields. Click on Next.

Click on Create.

As you can see, a new entry is created next to the Logout entry. This means we are done and can now test it by running the application:

Instead of a link text it is also possible to use images. This can be helpful when developing, for example, a multi language application with a short list of languages. Instead of writing the full language name, we can use small icons depicting the available language.

In this example, we will only show you how to create a navigation bar icon and reuse our Contact page for this. Later on in the book, we will show you the details of how to create a language switch.

Go to Shared Components | Navigation Bar Entries and click on the button Create.

Select From Scratch and click on Next.

Select Navigation to URL and click on Next.

This time we need to enter some more information on this screen.

Alter the Sequence to 15.

In the Entry Label enter nothing.

In Icon Image Name, click on the button to the right of the field and select the icon fndtip11.gif.

Enter Contact into Image ALT.

Enter 32 for both Image Height and Width:

Click on Next.

Enter 999 into Page and default the other fields. Click on Next.

Click on Create.

Create a new navigation bar entry From Scratch.

Select Feedback and click on Next.

In the next screen, find the Tasks section to the far right and click on Create Feedback Page:

Set the page number to 102, leave everything else at default, and click on Create.

The focus will be returned to the navigation bar overview, so we have to repeat the first two steps.

Now select page 102 from the Feedback Page pull down list, enter 20 into the Display Sequence, and click on Create.

In the Applic ation Builder, navigate to Shared Components | List Of Values. On the List Of Values page, click on the Create button.

You can create a list of values from scratch or you can copy an existing list of values and make some modifications to the newly created list of values. In our case, we will create a list of values from scratch. Click on Next.

Enter a name for the list of values, Jobs for example, and select Dynamic or Static. Choose Static and click on Next.

In the next step, you can enter the desired values. There are two types of values: the display value and the return value. The display value indicates how it is displayed and the return value is the value which will be returned into the text item the list of values is called from:

After you have entered the values, click on the Create List of Values button.

The list of values is created and can now be used by items. To assign an existing item to the list of values, go to the page where you want to include your list of values. In this case, we go to the tabular form based on the EMP table that we created in the Creating a simple form page recipe.

Click on the R egions section on the Report link. Click on the pencil icon near the Job column.

In the Tabular Form Element section, select select list (named lov) at the Display As field.

Next, in the List Of Values section, at the Named Lov field, select the list of values you just created.

Click on the Apply Changes button.

Run the form to see the result. It should look like the following:

Go to the page where you want to have the item.

Right-click on the Items section of the region where the item should go and select Create Page Item.

In the Select Item Type dialog, click on the Select List.

In the next step, enter a name for the item. You may also enter a sequence number and the region where the item should appear. Click on Next.

In this step, provide some additional information such as the label, the height, and the alignment. Click on Next.

Next, define what APEX should do when you select some value from the list. You can redirect the user to another page or you can submit the page. Another option in this step is the indication whether APEX should allow multiselection. Leave this option to No and click on Next.

In the next step, enter the name of the existing list of values that you want to use in the Named LOV field. Leave the other options and click on Next.

In the last step, define the source of the item. Leave the options and click on the Create Item button.

Create a new item on your page by right-clicking on the IT Employees region and selecting Create Page Item.

Select Shuttle and click on Next.

Name the item P15_IT_SHUTTLE and click on Next until you reach the List of Values page in the wizard.

Enter the following query to populate the left side of the shuttle:

select username display_value
     , id return_value
  from app_employees
 where dept_id <> 3
[9672_01_07.txt]

After clicking on Next, you will be asked to enter a value for Source Value. This will populate the right-hand side of the shuttle. For this, we will use PL/SQL Function Body as the Source Type that will return a list of usernames delimited by colons:

declare
  v_list apex_application_global.vc_arr2;
begin

  select username return_value
    bulk collect
    into v_list
    from app_employees
   where dept_id = 3;

  return(apex_util.table_to_string(v_list));
end;
[9672_01_08.txt]

Click on Create Item to finish the wizard.

Create a new page item.

Use item type Select List.

Name it P15_DEPARTMENTS.

Give it a label and click on Next until you reach the LOV query, and enter the following SQL:

select name display_value
     , id return_value
  from app_departments
[9672_01_09.txt]

Now create the item.

Go to Shared Components.

Click on the Authorization Schemes link. You can find it in the Security section.

Next, you might see the authorization schemes that have already been created. If no authorization schemes have been created in the past, you see an empty page. Click on the Create button.

Next, select From Scratch and click on Next.

Next, enter a name for this authorization scheme and enter the conditions in the Authorization Scheme section.

In our case, we name the scheme AUTH_ADMIN. The scheme type could be a PL/SQL function returning Boolean or an EXISTS SQL query. You have to create this function or query by yourself. The code could be included in the Expression 1 textarea but you can also make a call to a function stored in the database. In our case, we put the code in here and we choose for the EXISTS SQL query. You see the query as follows:

select 1
from   app_users 
where  username = :APP_USER 
and    role = 'ADMIN'
[9672_01_11.txt]

The query returns 1 if the username is the current user (:APP_USER) and the user has admin privileges.

Also, enter the error message APEX should display if the query returns no rows. Click on the Create button.

In the Evaluation Point section, select Once per page view to validate the authorization scheme. This will make APEX check the authorization for every call that is made to pages (or other components) using this scheme.

The other option is Once per session. This is much more efficient, because the check is only done once (at the start of the session). But when it's possible that the session state changes or there is anything else that is not consistent during the session, this option is not reliable.

The authorization scheme is now ready. Now, page access must be restricted by this authorization scheme.

Go to the page that requires authorization and click on the Edit icon (the pencil) in the Page section.

Go to the Security section and select the Authorization Scheme we just created (AUTH_ADMIN) in the Authorization Scheme list element.

Click on the Create button.

Choose From Scratch and click on Next.

Name it Application Authentication and click on Next.

Click on Next on the following two screens as well.

Select Page in This Application and page 101 on the Invalid Session Target and click on Next until you reach Credentials verification method.

Select Use my custom function to authenticate and enter return app_auth in Authentication Function.

Click on Next until you reach Logout URL.

The Logout URL is wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

Click on Next and then on Create Scheme.

Right-click on the Dynamic Actions link in the leftmost section and click on Create.

Select Standard and proceed to the next step.

Enter a name for this dynamic action. For example, D_JOB_COMM_SHOW. Click on Next.

In the next screen, the Selection Type is Item(s) and in the Item(s) field, enter the name of the item holding Job. You can use the List of Values button to select the right Page Item.

In the Condition list box, select equal to.

In the Value textarea, enter SALESMAN. Click on Next.

Select Show as the True Action and go to the next step.

In the next screen select Item(s) in the Selection Type. In the shuttle item that now appears, move the name of the commission field to the right.

Click on the Create button.

Right-click on the Computations under After Submit in the Page Processing component and click on Create:

Choose Item on this page and press Next.

Select P5_COMM from the Compute Item select list, select PL/SQL Function Body from the Computation Type select list and click on Next.

The next step is to enter the PL/SQL code for this computation:

declare
  l_comm number;
begin
  if :P5_DEPTNO = 30
  then
    l_comm := :P5_SAL * 1.10;
  else
    l_comm := :P5_SAL * 1.05;
  end if;
  return l_comm;
end;
[9672_01_15.txt]

Click on Create.

In the Application Builder, click on the Create Page button.

Select the Blank page and click on Next.

Assign a number and enter a name in the Page Alias field. The page number is automatically filled by APEX.

In the next step, enter a name for this page and click on Next.

In the Tabs dialog, select No and click on Next.

In the Page Confirmation dialog, click on Finish.

Click on the icon of the new page.

Create a region by right-clicking on the Regions link and clicking on Create.

Select HTML and click on Next.

In the Type Of HTML dialog, select HTML and click on Next.

In the next step, enter a title for the region and click Next.

In the HTML text region source step, leave all options and click on Create Region.

A region is created and now we will add some items to this region. We will start with the Primary Key of the table.

Right-click on the name of the region and select Create Page Item.

Select Text Item and click on Next.

In the next step, select Text Field and click on Next.

Next, enter a name for this item. Since this item will display the Primary Key we give it the same name as the Primary Key column, empno. So, the item will be named P11_EMPNO (11 is the page number, this can be different depending on your situation). Enter this name in the text field and click on Next.

In the next step, you can enter a label for this text item. Enter empno and click on Next.

In the Source dialog, in the Source Type listbox, select Database Column and enter EMPNO in the Item Source Value textarea. Mind the capitals, otherwise it will not work.

Click on the Create Item button.

Right-click on the name of the region and select Create Page Item.

Select Text Item and click on Next.

In the next step select Text Field and click on Next.

Next, enter a name for this item. Enter P11_ENAME (again, change 11 to the page number in your situation) in the text field and click on Next.

In the next step you can enter a label for this text item. Enter ename and click on Next.

In the Source dialog, in the Source Type listbox, select Database Column and enter ENAME in the item Source Value textarea. Mind the capitals, otherwise it will not work.

Click on the Create Item button.

In the page you just created, expand the After Header section and right-click the Processes link. Now click on Create.

Select Data Manipulation and click on Next.

Select Automated Row Fetch and click on Next.

Enter a name for this process. In the Point listbox, select On Load – After Header. Click on Next.

In the next step, enter the table name, the item containing the Primary Key column value, and the Primary Key column. In our case, the table name is EMP and the item containing the Primary Key column value is P11_EMPNO. The Primary Key column is the column in the table that is part of the Primary Key. In our case it is EMPNO. Again, mind the capitals, otherwise it won't work.

Click on the Create Process button. The automated row fetch has been created and you can run the page to see what happens.

Go to your application.

Click on Create page.

Select Form.

Select Form on a table or view.

Select the table/view owner and click on Next.

In the table/view name field, enter app_user_profiles. Click on Next.

Click again on Next.

Select do not use tabs and click on Next.

In the Primary Key column 1, select ID. Click on Next.

Select Existing sequence. In the Sequence list box, select app_ups_seq. Click on Next.

Select all columns and click on Next.

Click on Next.

Enter the page numbers APEX should navigate to if the form is submitted or cancelled. You can use the page number of the home page, mostly 1, for both. But you can also use the same page number as this page. Click on Next.

Click on Finish.

In the Page view, go to the Page Processing section and right-click on Validating. Select Create Validation.

Select the Validation Level. In our case, we choose Item Level.

Select the Birthday item.

In the Select a validation method dialog, select the PL/SQL validation method.

In the type of PL/SQL validation dialog, select PL/SQL error.

The sequence number has already been issued but you can change it to your own comfort. You can also enter a name for the validation. These two fields are mandatory. In the third field, the display location, you can select where the error message should appear. Click on Next.

In the Validation Text area, enter the following code:

[9672_01_18.txt]

Right-click on Validating. Select Create Validation.

Select item level validation and click on Next.

Select the e-mail item.

In the next step, select a regular expression.

Check the sequence number and the name of the validation. Click on Next.

In the Regular Expression field, enter the following:

([[:alnum:]]+\.?){2}@([[:alnum:]]+\.?){3,4}/?

In the Error Message textarea, enter the error message: The email address is not valid.

Skip the condition and click on the Create button.

Right-click on Validating. Select Create Validation.

Select the item level validation.

Select the Twitter item.

Select the PL/SQL validation method.

Select function returning error text.

Enter the sequence number and a name for the validation and select where the error message should appear. Make sure that the sequence number is higher than the sequence from the previous validations. Validations are processed in the order of these sequence numbers; lowest sequence numbers are processed first.

In the next step, in the validation text area, enter the following code:

declare
  l_result varchar2(100);
begin
  app_search_user(:P15_TWITTER,l_result);
  if l_result = 'user found'
  then
    return null;
  else
    return 'no valid user';
  end if;
end;
[9672_01_19.txt]

Choose a PL/SQL Dynamic Content region type.

Name it Employees.