In this chapter we will cover the following topics:
Creating an APEX 4.0 application
Creating a simple form page
Creating a simple report page
Implementing an interactive report
Creating a chart
Creating a map chart
Creating a navigation bar
Creating a list of values
Including different item types
Protecting a page using an authorization scheme
Securing an application with Authentication
Controlling the display of regions and items with Dynamic Actions
Creating a computation
Creating an automated row fetch with a page process
Putting some validation in a form
Creating a report with PL/SQL Dynamic Content
This chapter describes the basic steps to create an APEX application. Using APEX, it is really simple to create a basic application. The user interface is web-based and very intuitive. A lot of objects can be created using wizards which will guide you through the creation process.
Our aim is to make an intranet application where employees can get information. When starting the application, it shows a homepage with information such as weather, traffic company information, latest news, blogs, and so on. Employees can see their colleagues' profiles, just like in Facebook. Employees also have access to documents such as timesheets and project plans.
This recipe describes the tasks needed to create an APEX 4.0 application. You should have APEX 4.0 installed or have an account on Oracle's online APEX environment at http://apex.oracle.com and your web browser should be a modern browser like Microsoft Internet Explorer 7 or higher or Mozilla Firefox 1.0 or later. The starting point is the Oracle Application Express home page:
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.
The next step is the option to copy shared components from another application. Shared components are objects that can be used throughout the application, for example, a list of values or images. Because we create a simple application from scratch, we don't want to copy shared components from another application. Select No.
An authentication scheme is a means of allowing users access to our application. APEX offers different methods for this. More will be explained in another recipe. The scheme for this application will be selected in the next step.
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.
Now that we have completed the wizard, we can click on the Create button to confirm. The application will be created and we will see a number of pages, depending on how many pages we already created in step 6.
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you
Depending on the type of authentication, we will also see page 101—Login. This is the default page APEX navigates to when you run an application using authentication. It is generated automatically with all functionalities to allow users to log in to our application.
Click on the large Run Application button to go to the login page.
We can log in on this page with the same credentials we use to gain access to the APEX development environment. So enter this username and password and click on the Login button.
Well, that's it! We've created and run our first Oracle APEX 4.0 application. We can now click on the pages to define them, or we can add new pages to extend our application. We can also click on the Run Application icon to see what has actually been created. Since we only included an empty page, we will see something like this—an empty application except for a single, also empty page:
After you have created the application, it is time to create one or more forms and reports. First let's get started with a simple form. We will build a tabular form with insert, update, and delete possibilities.
Make sure that the table our form is based on contains a Primary Key and a sequence to update the Primary Key. In this case, we will be using the EMP table, so we have to make sure it is available in our database schema.
Also, we have to make sure the application that we created in the previous recipe is available.
The starting point for this recipe is the overview of the intranet application we created in the previous recipe.
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.
The form will be created and here you can choose to run the form to see how it looks or you can edit the form to define things. When running the form, it should look like the following:
A tabular form is actually an updateable report. In the Region section, you can find the query that populates the data to show on the screen. By default, every row of the table is shown. We can restrict the result set by adding a where clause to this query.
When we edit the page, m we can see that the wizard created the four buttons and the processes for the data manipulation language (DML). The tree view shows an overview of the components the page is built up of. On the left-hand side we can see the components used for the rendering of the page (Regions, Items, and so on). It is built up in such a way that we can see the order of the components that are rendered when the page is loaded.
The middle section shows the components used for the processes on the page; in this case, these are validations, data manipulation, and branching. The right-hand side section shows an overview of all shared components used on this page, if any are available.
We can right-click on any component in the tree view to see the possible actions for that component:
You can see that there are two multirow update processes. The first one is triggered by the Submit button and updates the changed rows. The Add Rows button initiates two processes: the second multirow update process and the add rows process. So, actually the Add Rows button submits the changes the user made so far and after that it creates an empty row.
The delete button initiates a JavaScript process that asks the user for confirmation. And this confirmation starts the delete process. This JavaScript function can be found in the HTML header section of the page properties.
You can also make a simple single record form. Here's how to do it.
In the Application Builder, click on the created application.
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.
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 our application we would also like to have an overview of all employees in the company. We can get this overview by creating a report. There are several types of reports and we just start with a simple report based on a query.
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 empAfter 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.
The report is ready now. You can edit the report to define the settings or you can run the report to see how it looks. The result should be something like the following:
When you look at the page in the Application Builder, you will see that APEX created a Reports region.
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.
In the following page, enter
EMPNOin the Unique Column field and click on the Migrate button to see what happens.
In the tree view of the page, we can see that the old Report region still exists but it's labeled Disabled. The new interactive report is added as we can see it in the following screenshot:
More on interactive reports is explained in the following recipe.
Chapter 8, Using Web Services explains (among many other things) how to create a report on web service references.
In this recipe, we are going to create an interactive report and show you how to use it. An interactive report is a special kind of report, which offers a lot of options to the user for filtering, sorting, publishing, and much more.
It's always a good idea to start by creating a view that already selects all columns you want to show in your report. This simplifies the query required for your report region and separates the logic from presentation in your application architecture.
In this recipe, we are going to base the interactive report on the APP_VW_CONTACTS view that joins the tables for contacts, addresses, and communications. The query for this view is as follows:
select ctt.firstname
, ctt.lastname
, ctt.contact_type
, ads.address_type
, ads.address_line1
, ads.address_line2
, ads.postcode
, ads.city
, ads.state
, ads.country
, aac.default_yn
, cct.id contact_id
, ads.id address_id
from app_contacts ctt
, app_addresses ads
, app_ads_ctt aac
where aac.ctt_id = ctt.id
and aac.ads_id = ads.id
[9672_01_01.txt]We will also need a named LOV later on in the recipe. To create it, follow the next steps:
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 dynamicLOV.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]
The starting point for this recipe is an empty page, so the first thing that we're going to do is create a new region to contain the interactive report.
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:
Firstname as
First NameLastname as
Last NameDefault Yn as
Default Address?
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_TYPEand 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.
When we take a look at the page by clicking on we can see the changes to the column names and the Address Type no longer shows the abbreviation, but the full text:
After the developer is done with creating an interactive report, the user will have a host of possibilities in the action menu to change the way the information is presented and filtered. These possibilities can be granted or revoked by the developer to an extent.
To see these options, right-click on the region and click on the option named Edit region attributes. When scrolling down this screen, you can see there are two sections: Search Bar and Download:
The first section holds the options that can be used in the Search Bar. When a user clicks on the Action button in the Search Bar, a menu will unfold revealing all the possible options. Data can be filtered, sorted, highlighted, and aggregated for instance. It's also possible for the user to generate a chart.
They can even save the changes made to the report for personal or public use, so other users can benefit as well:
The second section holds the file types that can be used to download the information in the interactive report. These include well-known formats such as CSV, PDF, and XLS:
In many reports it's required to show some (or all) of the figures in charts. This will give users the opportunity to quickly see the data and possibly take actions accordingly.
This recipe will show how to create a chart and some possibilities to configure them.
In the earlier recipes, we created the regions while building a new page. Of course, it's also possible to add regions on existing pages. To do so in this example, first prepare an empty page which will hold the region with the chart.
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.
The page shows a graphical representation of the number of employees in each department:
There are a number of possibilities to change and enhance this chart. Besides the 3D pie we have created here, there are dozens of other chart types. But there is a catch. Once a chart is created, the number of types it can be changed to is limited. When we would like to use a completely different chart type, the chart region has to be re-created.
This recipe can be easily expanded with a link to another page which can be clicked inside the chart.
To do this, first navigate to the Chart Series by expanding the tree view under the Chart region or by clicking on the edit link in the chart properties page. Edit the query on which the chart is based and change the first column. If we, for example, would have a page with ID 888, a query with a link to that page would look like the following:
select 'f?p=&APP_ID.:888:&APP_SESSION.::NO::P888_DEPT_ID:'||dept.id 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, dept.id
[9672_01_05.txt]We have chosen to show a dynamically generated link here. A few of the parameters need an explanation:
It is also possible to create a link by altering the Action Link region on the Chart Series page. The same items will be filled by this process, but it is easier to use when it is a relatively simple application:
A new type of chart available in Application Express 4.0 is the map chart. This allows users to present data on a geographical map. APEX 4.0 offers many maps from a world overview to maps of single countries.
Map charts allow applications a new way to visualize location-related data without much programming.
In this recipe, we will show you how to create a map that lists all contacts in the United States by state.
First we need to have a structure ready in our database tables that holds at least one geography-related column (for example, country, state, or province names). This will be the pointer for our chart to which it can relate its map.
For this recipe, we will reuse the APP_VW_CONTACTS view. This view holds a column called STATE that we can use in our chart.
Our starting point is again an empty page. The first thing to do is to add a new region.
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:
As we can see, each state that contains one or more contacts is highlighted. APEX also generates labels containing the state name and number of contacts if there is room for it. The other labels can be viewed by hovering over the state with the mouse pointer.
Application Express uses AnyChart 5 for displaying its charts. This is different from earlier versions of APEX, so migrating applications that make use of charts from Version 3.x to 4.0 is a little more complicated than migrating other functionalities.
When using a Map chart it is important that a column for the label is used that contains the name of the geographical region that you want highlighted on the map. For the standard maps provided by APEX, it's not allowed to use abbreviations, but it has to be the full name (for example, New York instead of NY and Virginia instead of VA).
Just like any other chart type, it is possible to create links to other pages. This can be used to create, for instance, a drill-down type of structure. We could create a series of pages from a world map, to a continent map, to a country map, and so forth.
How such a link can be created is explained in the chart recipe before this one.
The navigation bar is the area of an APEX application that is normally placed into the header of each page (unless the template is changed of course). As a standard, the Logout link is provided here, so users can quickly log out of the application from any page.
This recipe will show how a navigation bar can be customized. This will allow users to quickly reach certain pages in the application.
In this recipe we are going to see how you can create a quick link to a contact page. Before we can do that, we first need to have this page ready.
Create a new blank page. Name it
Contactand assign page ID999to 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.comFor 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
Imagesdirectory of APEX offers some examples such asfndtip11.gif.Go to Shared Components.
Click on Images.
Click on Create.
Select your application.
Select the image
fndtip11.gifon 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.
The navigation bar can be found in Shared Components. To reach it, navigate to Shared Components | Navigation Bar Entries.
As a default, there is already a logout entry available. Our new entry will be added here:
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
Contactand leave the rest on default. Click on Next.Enter
999into 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.
Alternatively, you can enter
#APP_IMAGES#fndtip11.gifinto the field.
Enter
Contactinto Image ALT.Enter
32for both Image Height and Width:
Click on Next.
Enter
999into Page and default the other fields. Click on Next.Click on Create.
You will notice a new entry in the list of the navigation bar. When running the application, this will be shown with the selected icon.
New in Application Express is the possibility to add a feedback link to the navigation bar. This will allow visitors of the application to quickly send feedback to the application developers or administrators.
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
102from the Feedback Page pull down list, enter20into the Display Sequence, and click on Create.
If you were to run the application now, no feedback link would be visible, because we have to allow the feedback option first.
So click on Edit Application Properties and find the option Allow Feedback. Set the pull down to Yes and click on Apply Changes. Now the application is ready to receive some feedback from its visitors:
Chapter 6, Creating Multilingual APEX Applications, will cover translating your application and offering quick language switching to the visitors.
Chapter 12, Team Development, will show how feedback can be used to the advantage of the development team.
When you use forms with items that are Foreign Keys to other tables, it would be handy to derive the Primary Key from the lookup table instead of having to enter this ID manually in the text field. Or, when dealing with lots of similar, predefined data, you don't want to enter the same values over and over again. In those cases, you would want to use items such as listboxes, quick picks, or lists of values which display the data of the lookup items where the user can easily pick the right value.
In this recipe, we'll create a list of values. A list of values can not only be a list of predefined static values but it can also be a dynamic list with data retrieved from a table. We will create a static list of values. A list of values is a shared component so it can be used in more pages.
The starting point is an existing application like the one we created. To define a list of values, you don't need to have a page. However, to make use of a list of values, you must define an item with a reference to the list of values. We will make a list of values on the JOB column.
There are two ways to start the Create List of Values wizard. The first one is in the Edit page.
On the right-hand side of the screen, under Shared Components, click on the Add icon under List Of Values.
The second one is via Shared Components:
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,
Jobsfor 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
EMPtable 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
Jobcolumn.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:
The list of values serves as a source for items types such as a list box or pop-up list of values. A list of values contains two columns: a display column and a column with the return value. This return value will be the actual data which will be stored in the item which is assigned to the list of values.
A list item can also be created directly upon a report column or item, but then is not reusable.
Another possibility is to use a previously defined list of values as a basis when creating a new item. This is useful in situations where you want to reuse a list of values on more than one occasion. A question that requires the answer Yes or No for example, can return on many pages in an application:
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.
In APEX, it is possible to use many different item types. Some of these we've already seen in other recipes. In this recipe we're going to talk about some of the advanced item types such as shuttles and HTML editors and how to interact with them.
To start with this recipe, create an empty page with ID 15 and put an HTML region with the name IT Employees on it.
The first example of an advanced item type is the shuttle list. We will use this list to add employees to the IT department. The shuttle will show a list of available employees on the left-hand side and a list of employees already in the IT department on the right-hand side.
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_SHUTTLEand 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.
Now the item will be populated with the employees:
The right-hand side of the shuttle item can also be populated by the default value that can be defined on the item's properties.
Another type of item we want to discuss here is the Cascading Select Item. Let's say we want to make a list of all employees. This is potentially a very long list, so before showing the employees, we first want to select the department we are working with.
First, we create the item that shows all departments for our company:
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.
The next part is to create the select list for the employees in the department. Again, we create a select list like before and name it P15_EMPLOYEES.
Now when we reach the LOV wizard screen, we enter the following SQL:
select firstname||' '||lastname display_value
, id return_value
from app_employees
where dept_id = :P15_DEPARTMENTS
[9672_01_10.txt]Also on this screen, change the value of Cascading LOV Parent Item(s) to P15_DEPARTMENTS. Click on Next and then on Create Item.
When we now run the page and select a Department, we can see that the Employees list changes immediately.
In some cases, you might want to restrict access to certain parts of your application to certain users. For example, not everyone should have access to the Form Page where you can alter the salaries. In such a case, you can protect your page using an authorization scheme.
Before starting with the recipe you should have a table created in the database with usernames and roles. Let's say you have the following table APP_USERS:
ID NUMBER(5)
USERNAME VARCHAR2(50)
PASSWORD VARCHAR2(50)
ROLE VARCHAR2(10)The table contains two rows. One is a user with admin privileges (role is ADMIN). The other one is a user with the no extra privileges (role is DEFAULT).
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 theEXISTSSQLquery. 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
1if 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.
That's it. The page now requires authorization to be accessed. Run the page to see how it works. Also see what happens if you do an update on the APP_USERS table:
update app_users
set role = 'DEFAULT'
where username = ...;Or
update app_users
set role = 'ADMIN'
where username = ...;
[9672_01_12.txt]Don't forget the commit.
Application Express comes with three standard ways to authenticate users on applications. We can use the credentials of database users, we can use the credentials of users defined within APEX itself, or we can use the credentials defined in the Database Access Descriptor. In this recipe, we will show how to add our own Authentication Scheme to this list.
An Authentication Scheme controls access to an entire application as opposed to an Authorization Scheme that controls access to individual components inside the application.
Simply put, an Authentication Scheme is what is called when a user clicks on the Login button.
First, we need a table to store the data for our users. In our application, this table will be APP_USERS. It contains columns for username and password, so we can create a very basic authentication scheme. Make sure this table is ready before continuing in this recipe.
Enter at least one row of data into the table that we can use to login at the end of the recipe.
Also we need two functions in place. APP_HASH is a function that will use a hashing algorithm and a salt to mask the real password. To make it more secure, the current date can be used in the algorithm, but this is enough for our example.
In a production environment, it is probably a good idea to wrap this code, because it can help intruders gain access to the application.
create or replace function app_hash (p_username in varchar2, p_password in varchar2)
return varchar2
is
l_password varchar2(4000);
l_salt varchar2(4000) := 'DFS2J3DF4S5HG666IO7S8DJGSDF8JH';
begin
l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
(input_string => p_password || substr(l_salt,10,13) || p_username ||
substr(l_salt, 4,10)));
return l_password;
end;
[9672_01_13.txt]APP_AUTH is a function that will check if the user is valid and if the password is entered correctly:
create or replace function app_auth (p_username in VARCHAR2, p_password in VARCHAR2)
return BOOLEAN
is
l_password varchar2(4000);
l_stored_password varchar2(4000);
l_expires_on date;
l_count number;
begin
select count(*)
into l_count
from app_users
where upper(username) = upper(p_username);
if l_count > 0
then
select password
into l_stored_password
from app_users
where upper(username) = upper(p_username);
l_password := app_hash(p_username, p_password);
if l_password = l_stored_password
then
return true;
else
return false;
end if;
else
return false;
end if;
end;
[9672_01_14.txt]The first thing we have to do is add the new authentication scheme to the list of existing schemes:
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_authin 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.:1Click on Next and then on Create Scheme.
The last step is to make the new Authentication Scheme the current for the application. To do this, navigate to the tab Change Current on the Schemes Overview screen. Select the new scheme from the list and click on the Make Current button.
You can now log in to the application using a username and password from the APP_USERS table.
Dynamic Actions are control items that dynamically can affect the display of regions or items on a page. There are several situations when you want to show or hide items. For example, a text item asking for a maiden name should only be displayed when the person is female and married. In other cases, it is irrelevant to ask for a maiden name (on the other hand, with homosexual marriages it's possible for a man to have a "maiden" name, and in some cases a man can adopt the family name of his wife, but let's not make this example more difficult than necessary).
The same applies to the Commission field in the employees table. This item should only be enterable when the employee's job is a salesman. Let's build the functionality for this last situation.
You should already have an application and a simple single record form on the employees table.
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.
You have now created a dynamic action which shows the Commission field when the job is SALESMAN and hides the Commission field when the job is not SALESMAN.
The Dynamic Actions are actually event handlers in HTML. There are several event handlers.
Because these events are HTML (or rather JavaScript) they are handled client side. This has the advantage that the page doesn't have to be reloaded completely when an action is triggered to show or hide items.
|
Event |
Meaning |
|---|---|
|
Item has been refreshed (that is, by page refresh) | |
|
Fires before item has been refreshed (that is, by page refresh) | |
|
User navigates to another item | |
|
User navigates to another item and the value of the item has changed | |
|
User clicks on the item with a pointing device (like a mouse) | |
|
User double-clicks on the item with a pointing device | |
|
User navigates to the item via the tab key or a pointing device | |
|
User clicks on a key on the keyboard | |
|
User clicks on a key on the keyboard (=onkeyDown followed by onkeyUp) | |
|
User releases the key after having pressed it | |
|
The browser loads all content | |
|
User clicks on the mouse button when the mouse pointer is over the item | |
|
User clicks on the item with the pointing device | |
|
User moves away the mouse from the item | |
|
User moves the mouse while the mouse pointer is over the item | |
|
User moves the mouse pointer over the item | |
|
User moves away the mouse pointer from the item | |
|
User releases the mouse button after having it pressed | |
|
Form is submitted | |
|
Document view is resized | |
|
Document view is scrolled | |
|
User selects some text in a text field | |
|
|
Form is submitted |
|
Page is unloaded |
In our example, we use the onchange event handler. So, when the user changes the value in JOB, the onchange is triggered and it calls the action to show or hide the COMMISSION field, depending on the value in JOB. If it is SALESMAN then show commission, else hide commission.
You can control the display of more than one item at a time. Simply separate the items by a comma in the items field.
You can also control the display of regions. So, you can show or hide a complete report. Instead of item, select region and the name of the region in the affected element section when defining an action.
Computations are events that will prepare items with data. As the name implies, computations can "compute" how data is to be shown on screen or how data is handled after submitting. Computations can be triggered during page rendering, but they can also be used in the After Submit process.
This example will show an implementation of a computation, but there are many more possible uses for computations.
Start by creating a normal text item on the form that was created in the second recipe of the first chapter. This is a normal form based on the EMP table. Normally, a user would have to manually enter the commission. What we are going to do is to create a computation that will automatically enter an amount into this field, based on a percentage of the salary and a bonus for people that work in the Sales department.
First, identify the item that holds the commission column.
The next step is to find the moment when we want to execute the computation. In this case, before the page is submitted but after the Save button is pressed. So in this case we will create the computation After Submit.
Right-click on the Computations under After Submit in the Page Processing component and click on Create:
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.
The computation is now done. It can be tested by selecting an existing record from the list of employees and altering its salary and then saving the data. Another option is to create a completely new employee. You will see that an employee under the Sales department with dept no. 30 will receive a 10 percent commission whilst employees from all other departments will receive 5 percent.
Computations can be used for far more situations than preparing an item before saving it to the database.
An example of another implementation can be an item that will show the current date when the page loads, or an item that will show a total amount when certain other amount items are entered. Used with dynamic actions, computations can be very powerful.
When you create a Single Record form, you probably would like to see that the form automatically retrieves data on startup. You can do that with an automated row fetch. In this section, we will create a page process with an automated row fetch. We're going to make a form where users can update their data.
We will use the EMP table so make sure it exists, together with the Primary Key. Create a simple form page:
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.
The page is now ready and we can start creating a region with items.
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 namedP11_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
EMPNOin the Item Source Value textarea. Mind the capitals, otherwise it will not work.Click on the Create Item button.
The item has now been created.
Next, create another item. Let's say Ename.
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
enameand click on Next.In the Source dialog, in the Source Type listbox, select Database Column and enter
ENAMEin the item Source Value textarea. Mind the capitals, otherwise it will not work.Click on the Create Item button.
The item has now been created.
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
EMPand 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 isEMPNO. 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.
The automated row fetch is executed when the page is run but after the page header section has been generated by APEX. A row is fetched by using the Primary Key value stored in the page item P11_EMPNO. If this item is empty in the session state, no row will be fetched at all.
When a row is fetched, the column values are put in internal arrays (memory cache), but will not be committed into session state itself. The session state is only populated when the page is submitted (using the values stored in the page items). This has to do with performance (insert/delete session state) for example, clicking a "cancel" link.
In this case, running the page without P11_EMPNO having a value will produce a page with empty items.
When you fill in a form, the entered data must be validated, just to make sure that it is correct. Date fields, number fields where the number should not exceed some defined limit, items with a certain format like a telephone number or an e-mail address, all this data has to be validated. In APEX you can use validations to check the user input.
For this recipe we will use a user profiles form where the user can enter some personal information. Make sure you have access to the app_user_profiles table and the app_ups_seq sequence. You can create a user profiles form using these steps:
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.
Let's put some validation on the items. We are going to put validation on birthday, e-mail, and a Twitter account. For the check of the Twitter account you must first create the following procedure:
create or replace procedure app_search_user (p_search in varchar2
,p_result out varchar2)
is
l_request utl_http.req;
l_response utl_http.resp;
l_tweet_url varchar2(255) := 'http://api.twitter.com/1/users/lookup.xml';
l_content varchar2(255) := 'screen_name='||p_search;
l_line varchar2(1024);
l_result varchar2(100) := 'no user';
l_user varchar2(100) := 'your user name';
l_password varchar2(100) := 'your password';
begin
-- build the request statement
l_request := utl_http.begin_request(url => l_tweet_url
,method => 'POST');
-- set header
utl_http.set_header(r => l_request
,name => 'Content-Length'
,value => length(l_content));
-- authenticate the user
utl_http.set_authentication(r => l_request
,username => l_user
,password => l_password);
-- write the content
utl_http.write_text(r => l_request
,data => l_content);
-- get the response
l_response := utl_http.get_response(r => l_request);
begin
loop
utl_http.read_line(r => l_response
,data => l_line
,remove_crlf => true);
if instr(l_line,'<screen_name>') > 0
then
l_result := 'user found';
end if;
end loop;
exception
when utl_http.end_of_body
then
null;
end;
utl_http.end_response(r => l_response);
p_result := l_result;
exception
when others then
utl_http.end_response(r => l_response);
p_result := 'request failed';
raise;
end app_search_user;
/
[9672_01_16.txt]This procedure makes a call to the Twitter API and searches for the Twitter username which was passed through. The request sent looks like the following URL:
http://api.twitter.com/1/users/lookup.xml?screen_name=<twittername>
Here, <twittername> is the Twitter username you are checking. The result is an XML or JSONresponse. In this case, if the Twitter username exists, the procedure gets an XML response with a tag <screen_name>, which holds the username. If the Twitter username does not exist, the procedure gets an XML response with an error tag. The procedure makes use of the utl_http package so the database user must be granted execute rights to this package. Also, it is important to define the Access Control List (ACL) if your database version is 11g. To grant access, log in as SYS user and execute the following procedure:
begin
dbms_network_acl_admin.create_acl (
acl => 'utl_http.xml',
description => 'HTTP Access',
principal => '<oracle username>',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
dbms_network_acl_admin.add_privilege (
acl => 'utl_http.xml',
principal => '<oracle username>',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl (
acl => 'utl_http.xml'
host => 'api.twitter.com'
lower_port => 80
upper_port => 80 );
commit;
end;
/
[9672_01_17.txt]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]
if :Pxx_BIRTHDAY > (sysdate – numtoyminterval(13,'YEAR'))
then
raise_application_error (-20001,'You must be at least 13 years old to register.');
end if;xx is the page number. This code checks if the entered date is greater than the current system date minus 13 years. If so, the person is younger than 13 years and is not allowed to register. In that case an error message should be issued. You can enter the error message in the Error Message textarea. In the next step, optionally you can specify the conditions when the validation should take place.
The first validation is ready now. The next validation is the e-mail.
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}/?
With regular expressions you can force a user to conform to a certain format when entering data. You can, for example, check on the format of telephone numbers, URLs, dates and, in this case, correct e-mail addresses. E-mail addresses should at least have the at sign (@) and a dot (.), such as abcd@abcd.com. But an e-mail address can have more dots, and numbers are also allowed. [[:alnum:]] indicates that characters and numbers are accepted. The + sign means that it can match 1 or more times. The dot followed by the question mark indicates that a dot can match 0 or more times. The {2} indicates that it must match at least two times. Behind the at sign again, numbers, characters, and dots are allowed.
In the Error Message textarea, enter the error message:
The email address is not valid.Skip the condition and click on the Create button.
The second validation has now been created. Now let's go to the validation of the Twitter account.
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]
This PL/SQL code calls the stored procedure with the Twitter username as a parameter and gets a result back. If the Twitter username exists, "user found" is returned, otherwise "no valid user" is returned. In the latter case, an error message should be issued. You can enter the error message in the Error Message textarea.
In the Conditions dialog, leave the options as they are and click on the Create button.
On submitting the form, APEX validates the items. In the case of the birthday, it executes the PL/SQL code where the entered birthday is checked. In the case of the e-mail address, the item containing the e-mail address is checked against the regular expression.
You can also validate multiple rows of an item in a tabular form. If one or more rows fail validation, APEX indicates this by showing the concerned items in red with an error message in the Notification area. Also, you can validate at page level.
There are different validation methods. See the following table:
|
Validation method |
Meaning |
|---|---|
|
SQL |
Enter a |
|
PL/SQL |
Enter a PL/SQL expression, PL/SQL error ( |
|
Item not null |
Item should not be empty |
|
Item string comparison |
Compare the value of the item with a predefined string |
|
Regular expression |
Item value should meet a certain format, like a date format ( |
For more information on regular expressions, go to http://psoug.org/reference/regexp.html
APEX offers many item types and templates to use when designing an application. Sometimes this isn't enough, for example, when you have an existing web application built with mod PL/SQL that you want to reuse.
It's possible by using built-in packages such as HTP or HTF, or by using some native APEX code to create these types of pages directly in PL/SQL.
In this example, we are going to build a report without using any items, but only PL/SQL code.
On an empty page, create a new region:
Choose a PL/SQL Dynamic Content region type.
Name it
Employees.
The PL/SQL Source is the most important part of this region. With this, we will be building up the report. To show how this works, we will first create the region without any layout.
Enter the following as the PL/SQL and click on Next, and then on Create Region:
declare
cursor c_emp
is
select apex_item.hidden(1,emp.id) id
, apex_item.display_and_save(2,emp.firstname) firstname
, apex_item.display_and_save(2,emp.lastname) lastname
, apex_item.display_and_save(2,emp.username) username
, apex_item.display_and_save(2,dept.name) department
, apex_item.display_and_save(2,job.abbreviation) job
, apex_item.display_and_save(2,job.description) job_desc
from app_employees emp
, app_departments dept
, app_jobs job
where emp.dept_id = dept.id
and emp.job_id = job.id;
begin
for r_emp in c_emp
loop
htp.p(r_emp.id);
htp.p(r_emp.firstname);
htp.p(r_emp.lastname);
htp.p(r_emp.username);
htp.p(r_emp.department);
htp.p(r_emp.job);
htp.p(r_emp.job_desc);
end loop;
end;
[9672_01_20.txt]When the page is run, all data from each employee is placed on one continuous line. To change this and make it look more like a report, we will add some HTML encoding, using the htp package.
Change the code inside the begin-end to the following:
htp.tableopen;
for r_emp in c_emp
loop
htp.tablerowopen;
htp.tabledata(r_emp.id);
htp.tabledata(r_emp.firstname);
htp.tabledata(r_emp.lastname);
htp.tabledata(r_emp.username);
htp.tabledata(r_emp.department);
htp.tabledata(r_emp.job);
htp.tabledata(r_emp.job_desc);
htp.tablerowclose;
end loop;
htp.tableclose;
[9672_01_21.txt]This looks a lot better and gives us a starting point to apply a better layout:
Applying layout can now be done on multiple levels. For starters, the APEX_ITEM package can be used to select different item types in the cursor query. We have now used APEX_ITEM.DISPLAY_AND_SAVE to show the data as text only, but we could also use item type TEXT to make it a text field.
On a second level, we can start using classes from the CSS that is used by the application. These can be applied to the items, tables, tablerows, and so forth. How this can be done as explained in Chapter 2, Themes and Templates.
