Sometimes you want to keep statistics on your web application, for example, to measure the level of interest in the application. Or you may want to log when a user clicks a certain link in the application. In that case, you could use the APEX_UTIL.COUNT_CLICK function. We will show you how to use this function in the following recipe.
We will create a section on a page, for example page 1, with the latest news. Just a few words of the news article is displayed. Below the text is a link, [read more], which redirects the user to the complete news article. Make sure you have a page where you can create a news section.
- In the application builder, go to the page where you want to put the news section on.
- In the regions section, click the Add icon.
- Select PL/SQL dynamic content.
- Enter a title for the region, for example, news. Click Next.
- In the PL/SQL source text area, enter the following:
declare cursor c_nws is select id , title , date_published , substr(text,1,300)||'...' text from app_news; -- l_url VARCHAR2(255); l_cat VARCHAR2(30); l_workspace_id VARCHAR2(30); begin for r_nws in c_nws loop l_url := 'http://host:port/apex/f?p=&APP_ID.:2:&APP_SESSION.::NO::P2_ID:'||r_nws.id; l_workspace_id := TO_CHAR(APEX_UTIL.FIND_SECURITY_GROUP_ID('WS')); l_cat := 'news'; -- htp.print('<h1>'||r_nws.title||'</h1>'), htp.print(r_nws.date_published); htp.print(r_nws.text); htp.print('<br>'), htp.print('<a href="http://host:port/apex/z?p_url=' || l_url || '&p_cat=' || l_cat || '&p_workspace=' || l_workspace_id || '">[read more]</a>'), end loop; end;[1346_07_08.txt]
- In the code there is a cursor with a query on the APP_NEWS table. The code loops through the records found and displays the first 300 characters. This is done using the htp.print function. After that, a link with the text [read more] will be displayed. Again, this is done using the htp.print function. However, in the link you can find the APEX function, which is used to count the clicks. In the code you see the link:
http://host:port/apex/z?p_url=...
Where host is the name of the host where APEX resides and port is the port number used by APEX. Furthermore, you see "z?p_url=". Z is the shortcut name for APEX_UTIL.COUNT_CLICK. Behind the function call you can see a number of parameters which can be used to distinguish the different links. The most important parameters are p_url, which passes the URL APEX should navigate to, and p_cat, which passes the category (you can choose any name). In this case, the URL is a link to another page in the application. We use the &APP_ID and &SESSION_ID to dynamically determine the application ID and session ID. The ID of the page as well as the other parameters you might possibly need must be entered by yourself. In this case, the news article ID is passed in the URL. WS is the name of the workspace.
- In the code there is a cursor with a query on the APP_NEWS table. The code loops through the records found and displays the first 300 characters. This is done using the htp.print function. After that, a link with the text [read more] will be displayed. Again, this is done using the htp.print function. However, in the link you can find the APEX function, which is used to count the clicks. In the code you see the link:
- Click Next.
- Click Create region.
The region is now ready. Run the page and hover with the mouse over the [read more] link below a news article. In the bottom status bar of your browser you will see the link to the news article. You will see that this link starts with the call to "z", or the APEX_UTIL.COUNT_CLICK function. Try to click on a link. At first sight nothing special happens except that APEX redirects to the page where the news article will be shown. However, use SQLplus or an IDE like SQL developer and query on the APEX_WORKSPACE_CLICKS view. This view is accessible for all Oracle users so you should be able to query it. You can use the following query:
select workspace , category , apex_user , to_char(clickdate,'DD-MM-YYYY HH24:MI:SS') , click_id , clicker_ip , workspace_id from apex_workspace_clicks order by clickdate desc;
[1346_07_09.txt]
This query retrieves all rows from the view and displays the data in date descending order. The date is shown together with the timestamp to be able to see exactly when a link has been clicked.
APEX_UTIL.COUNT_CLICK is actually a function which can be called either via the URL or via a call in a PL/SQL procedure. You can also use z as a shortcut. The function actually inserts a line in the apex_workspace_clicks view. The function has more parameters:
|
Parameter |
Meaning |
|---|---|
|
|
The URL to which to navigate |
|
|
A category to distinguish between different links |
|
|
Secondary id to associate with the click |
|
|
The application user ID |
|
|
The workspace name |
You can also use a PL/SQL procedure to directly call the count_clicks function:
declare
p_name varchar2(100) := 'apx_usr';
begin
apex_util.count_click(
p_url => 'http://host:port/apex/f?p=<app>:<page>: &SESSION_ID.',
p_cat => p_name,
p_id => null,
p_user => owa_util.get_cgi_env('REMOTE_ADDR'),
p_company => apex_util.find_security_group_id('YOURWORKSPACE'));
end;
[1346_07_10.txt]
Where<app> is the id of your application and<page> is the id of the page. Make a page process and use this code. Use a button to call this page process. APEX will redirect to the link in p_url and insert a line in APEX_WORKSPACE_CLICKS.
If you use a list to use as a menu, you can use the built-in count click mechanism:
- Go to shared components and click on the lists link in the navigation section.
- Click on an existing list. You will get an overview of links included in this list. Click on a link.
- Click on the Click Counting tab.
- In the Count Clicks list box, select Yes.
- In the Click Count Category text field, enter a category name. You can choose any name, as long as it is unique within the application so that you can find it back in the
APEX_WORKSPACE_CLICKSview. This view shows a row for each call toAPEX_UTIL.COUNT_CLICK. You can view the workspace, the workspace ID, the category, the date and time of the moment the user clicked, the APEX user, and the IP address.