December 5, 2007 • 13 minute read
PL/SQL Pagination and Sorting Example
I've put together a comprehensive example for paginating and sorting a table of data in a web page using PL/SQL. This example uses the all_objects table, so should be usable on any Oracle database. It is fully commented and should be pretty self-explanatory. The example uses two small images for the column sorting links, asc.gif and desc.gif. You should put these wherever you store your images and update the code to reflect that location.
jl_example.pak
/***********************************
* Pagination and Sorting Example *
* by Joe Lennon *
* Date: 05-DEC-2007 *
* http://www.joelennon.com *
***********************************
*/
CREATE OR REPLACE PACKAGE jl_example AS
PROCEDURE create_stylesheet;
PROCEDURE create_javascript;
PROCEDURE create_header(p_title IN VARCHAR2 DEFAULT NULL);
PROCEDURE create_table_header;
PROCEDURE create_table_body(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10);
PROCEDURE create_table_footer;
PROCEDURE create_hidden_form(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10);
PROCEDURE create_footer;
PROCEDURE create_page(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10);
END cp_cpw_example;jl_example.bod
/***********************************
* Pagination and Sorting Example *
* by Joe Lennon *
* Date: 05-DEC-2007 *
* http://www.joelennon.com *
***********************************
*/
CREATE OR REPLACE PACKAGE BODY jl_example AS
/* This procedure simply prints the styles for the example.
Alternatively, you could import an external style sheet. */
PROCEDURE create_stylesheet IS
BEGIN
htp.p('<style type="text/css">');
htp.p('body {');
htp.p(' font-family: Arial, Verdana, sans-serif;');
htp.p('}');
htp.p('table.data {');
htp.p(' border: 1px solid #000;');
htp.p(' border-right: none;');
htp.p(' border-bottom: none;');
htp.p(' width: 550px;');
htp.p(' font-size: 12px;');
htp.p('}');
htp.p('table.data tr.odd {');
htp.p(' background-color: #d6cbbb;');
htp.p('}');
htp.p('table.data tr.even {');
htp.p(' background-color: #fcefdc;');
htp.p('}');
htp.p('table.data th {');
htp.p(' background-color: #a5a5a5;');
htp.p(' border-right: 1px solid #000;');
htp.p(' border-bottom: 1px solid #000;');
htp.p(' color: #fff;');
htp.p('}');
htp.p('table.data td {');
htp.p(' border-right: 1px solid #000;');
htp.p(' border-bottom: 1px solid #000;');
htp.p('}');
htp.p('td.number {');
htp.p(' text-align: right;');
htp.p('}');
htp.p('td.pagination {');
htp.p(' background-color: #737373;');
htp.p(' color: #fff;');
htp.p('}');
htp.p('td.pagination a:link,');
htp.p('td.pagination a:visited,');
htp.p('td.pagination a:active,');
htp.p('td.pagination a:hover {');
htp.p(' color: #fff;');
htp.p('}');
htp.p('</style>');
END create_stylesheet;
/* This procedure simply prints the JavaScript functions used in
the example. Alternatively, you could link to an external
JavaScript file. */
PROCEDURE create_javascript IS
BEGIN
htp.p('<script type="text/javascript">');
htp.p('<!--');
/* This function submits the selected column and direction to sort */
htp.p('function orderBy(column, direction) {');
htp.p(' document.all_objects.p_order_by.value = column;');
htp.p(' document.all_objects.p_direction.value = direction;');
htp.p(' document.all_objects.submit();');
htp.p('}');
/* This function submits the page to move to */
htp.p('function goToPage(page) {');
htp.p(' if(page > 0) {');
htp.p(' document.all_objects.p_page.value = page;');
htp.p(' document.all_objects.p_show_all.value = 0;');
htp.p(' document.all_objects.submit();');
htp.p(' } else {');
htp.p(' document.all_objects.p_page.value = 1;');
htp.p(' document.all_objects.p_show_all.value = 1;');
htp.p(' document.all_objects.submit();');
htp.p(' }');
htp.p('}');
/* This function submits the results to display per page */
htp.p('function resultsPerPage(num) {');
htp.p(' document.all_objects.p_results.value = num;');
htp.p(' document.all_objects.p_page.value = 1;');
htp.p(' document.all_objects.submit();');
htp.p('}');
htp.p('//-->');
htp.p('</script>');
END create_javascript;
/* This procedure simply prints the opening section of the XHTML Document */
PROCEDURE create_header(p_title IN VARCHAR2 DEFAULT NULL) IS
BEGIN
htp.p('<?xml version="1.0" encoding="utf-8"?>');
htp.p('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"');
htp.p('"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">');
htp.p('<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">');
htp.p('<head>');
htp.p('<title>'||p_title||'</title>');
-- Include Stylesheet
create_stylesheet;
-- Include Javascript
create_javascript;
htp.p('</head>');
htp.p('<body>');
-- Print the parameter p_title as a level 1 heading
htp.p('<h1>'||p_title||'</h1>');
END create_header;
/* This procedure opens the main data table for this example and prints the top row,
which includes the column headers and links for sorting the table */
PROCEDURE create_table_header IS
BEGIN
htp.p('<table class="data" cellspacing="0" cellpadding="4" border="0">');
htp.p('<tr>');
htp.p('<th width="50">Row:</th>');
htp.p('<th>Object Type');
htp.p('<a href="#" onclick="orderBy(1,1);" title="Sort by Object Type (Asc)">');
htp.p('<img src="/images/asc.gif" border="0" />');
htp.p('</a>');
htp.p('<a href="#" onclick="orderBy(1,2);" title="Sort by Object Type (Desc)">');
htp.p('<img src="/images/desc.gif" border="0" />');
htp.p('</a>');
htp.p('</th>');
htp.p('<th width="70">Count');
htp.p('<a href="#" onclick="orderBy(2,1);" title="Sort by Count (Ascending)">');
htp.p('<img src="/images/asc.gif" border="0" />');
htp.p('</a>');
htp.p('<a href="#" onclick="orderBy(2,2);" title="Sort by Count (Descending)">');
htp.p('<img src="/images/desc.gif" border="0" />');
htp.p('</a>');
htp.p('</th>');
htp.p('</tr>');
END create_table_header;
/* This procedure outputs the data to the main data table. All pagination
and column sorting is performed here, based on the parameters
passed to the procedure */
PROCEDURE create_table_body(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10) IS
/* This cursor retrieves the data, sorted by the selected column
header, and limits the rows retrieved based on the current pagination
selection (or retrieves all if Show All Results has been selected. This
particular cursor is used if data is to be sorted ascending. */
CURSOR c_object_types_asc(p_start NUMBER DEFAULT 1, p_end NUMBER DEFAULT 10) IS
SELECT * FROM
(SELECT row_.*, rownum rownum_ FROM
(SELECT COUNT(*) total, INITCAP(LOWER(object_type)) object_type
FROM all_objects
GROUP BY object_type
ORDER BY DECODE(p_order_by, '1', object_type,
'2', TO_CHAR(total, '99999999')
) ASC)
row_ WHERE rownum <= p_end
)
WHERE rownum_ >= p_start;
/* This cursor retrieves the data, sorted by the selected column
header, and limits the rows retrieved based on the current pagination
selection (or retrieves all if Show All Results has been selected. This
particular cursor is used if data is to be sorted descending. */
CURSOR c_object_types_desc(p_start NUMBER DEFAULT 1, p_end NUMBER DEFAULT 10) IS
SELECT * FROM
(SELECT row_.*, rownum rownum_ FROM
(SELECT COUNT(*) total, INITCAP(LOWER(object_type)) object_type
FROM all_objects
GROUP BY object_type
ORDER BY DECODE(p_order_by, '1', object_type,
'2', TO_CHAR(total, '99999999')
) DESC)
row_ WHERE rownum <= p_end
)
WHERE rownum_ >= p_start;
/* This cursor simply counts the total number of rows in our required
table. It is used to determine how many pages of results we will have. */
CURSOR c_count_rows IS
SELECT COUNT(*) tot
FROM (
SELECT COUNT(*) total, object_type
FROM all_objects
GROUP BY object_type
);
v_page NUMBER := 1;
v_start NUMBER := 1;
v_end NUMBER := 10;
v_limit NUMBER := 10;
v_num_rows NUMBER := 0;
v_num_pages NUMBER := 0;
v_row_num NUMBER := 0;
BEGIN
/* We begin by counting the total number of rows and storing this value in
the v_num_rows variable */
OPEN c_count_rows;
FETCH c_count_rows INTO v_num_rows;
CLOSE c_count_rows;
IF p_page IS NOT NULL THEN
v_page := p_page;
END IF;
IF p_results IS NOT NULL THEN
v_limit := p_results;
END IF;
/* To determine the starting point of the data we need to print in the table,
we use a formula ((Page Number - 1) * Results Per Page) + 1. For example,
if we are displaying 10 results per page, the starting point for page 1
should be 1, for page 2 should be 11 and so on. If we apply the formula
to these examples we will see how the formula works:
Page 1: ((1 - 1) * 10) + 1 = (0 * 10) + 1 = 0 + 1 = 1
Page 2: ((2 - 1) * 10) + 1 = (1 * 10) + 1 = 10 + 1 = 11
Page 3: ((3 - 1) * 10) + 1 = (2 * 10) + 1 = 20 + 1 = 21
and so on... */
v_start := ((v_page - 1) * v_limit) + 1;
/* Similarly, for the ending point, we also need to use a formula. This is one
a little easier to grasp, as it is simply Page Number * Results Per Page.
If we use the same example, we'd expect the upper limit for page 1 to be 10,
page 2 to be 20 and so on. Applying the formula we see:
Page 1: 1 * 10 = 10
Page 2: 2 * 10 = 20
Page 3: 3 * 10 = 30
and so on... */
v_end := v_page * v_limit;
/* To calculate the number of pages, we simply take the number of
rows and divide it by the number of results we are displaying per
page. We then use the CEIL function to ensure that the value is a
whole number (CEIL rounds up to the nearest whole number) */
v_num_pages := CEIL(v_num_rows / v_limit);
v_row_num := v_start - 1;
/* If the user wants to see all results (i.e. no pagination) we then
need to set the start point to the first row and the end point
to the last row of the result set. Because we have earlier found
out how many rows the result set has, we simply set this value
as our upper limit. */
IF p_show_all = 1 THEN
v_start := 1;
v_end := v_num_rows;
END IF;
/* This IF statement determines whether we should display data in
ascending or descending order */
IF p_direction = '2' THEN
/* Loop through the data */
FOR x IN c_object_types_desc(v_start,v_end) LOOP
v_row_num := v_row_num + 1; -- Current row number (for display purposes)
/* In order to alternate the colours of the rows, I used
the MOD function to determine every second row, and then
apply a style based on whether the row is odd or even */
IF MOD(v_row_num, 2) = 0 THEN
htp.p('<tr class="even">');
ELSE
htp.p('<tr class="odd">');
END IF;
htp.p('<td width="50" class="number">'||v_row_num||'.</td>');
htp.p('<td>'||x.object_type||'</td>');
htp.p('<td width="70" class="number">'||x.total||'</td>');
htp.p('</tr>');
END LOOP;
ELSE
/* Loop through the data */
FOR x IN c_object_types_asc(v_start,v_end) LOOP
v_row_num := v_row_num + 1; -- Current row number (for display purposes)
/* In order to alternate the colours of the rows, I used
the MOD function to determine every second row, and then
apply a style based on whether the row is odd or even */
IF MOD(v_row_num, 2) = 0 THEN
htp.p('<tr class="even">');
ELSE
htp.p('<tr class="odd">');
END IF;
htp.p('<td width="50" class="number">'||v_row_num||'.</td>');
htp.p('<td>'||x.object_type||'</td>');
htp.p('<td width="70" class="number">'||x.total||'</td>');
htp.p('</tr>');
END LOOP;
END IF;
/* The bottom row of the table is used to display pagination
information and options. It will show the number of results
returned by the query. If the user has selected to paginate the
results (default) they will see which page they are currently on,
and how many pages there are in total, followed by links to
each page and Prev/Next links if relevant. They will also see a
link to Show All Results and links for changing the number of
results displayed per page. If the user has selected to show
all results (no pagination), they will be given a link to paginate
the results. */
htp.p('<tr>');
htp.p('<td colspan="3" class="pagination">');
htp.p(v_num_rows||' result(s).');
/* If pagination enabled */
IF p_show_all != 1 THEN
htp.p('Page '||v_page||' of '||v_num_pages||' | '); -- Current Page
/* If we are not on the first page, display a link to the Previous Page */
IF p_page > 1 THEN
htp.p('<a href="#" onclick="goToPage('||(v_page - 1)||');">Prev</a>');
END IF;
/* Loop through each page number */
FOR x IN 1..v_num_pages LOOP
/* If the page number is the current page, show in bold (no link) */
IF x = p_page THEN
htp.p('<strong>'||x||'</strong>');
/* Otherwise, show as a link */
ELSE
htp.p('<a href="#" onclick="goToPage('||x||');">'||x||'</a>');
END IF;
END LOOP;
/* If we are not on the last page, display a link to the Next page */
htp.p('<a href="#" onclick="goToPage('||(v_page + 1)||');">Next</a>');
IF p_show_all = 0 THEN
htp.p(' | <a href="#" onclick="goToPage(0);">Show All Results</a>');
END IF;
/* Links for changing results per page */
htp.p(' | Results Per Page:');
IF p_results = 5 THEN
htp.p('<strong>5</strong>');
ELSE
htp.p('<a href="#" onclick="resultsPerPage(5);">5</a>');
END IF;
IF p_results = 10 THEN
htp.p('<strong>10</strong>');
ELSE
htp.p('<a href="#" onclick="resultsPerPage(10);">10</a>');
END IF;
IF p_results = 15 THEN
htp.p('<strong>15</strong>');
ELSE
htp.p('<a href="#" onclick="resultsPerPage(15);">15</a>');
END IF;
IF p_results = 20 THEN
htp.p('<strong>20</strong>');
ELSE
htp.p('<a href="#" onclick="resultsPerPage(20);">20</a>');
END IF;
/* If no pagination (showing all rows on a single page) */
ELSE
htp.p('<a href="#" onclick="goToPage(1);">Paginate Results</a>');
END IF;
htp.p('</td>');
htp.p('</tr>');
END create_table_body;
/* This procedure closes the main data table */
PROCEDURE create_table_footer IS
BEGIN
htp.p('</table>');
END create_table_footer;
/* This procedure creates the hidden form we use to post parameter values to the
server. We could have alternatively used GET requests built into links, but by
using POST instead we are limiting the scope for user error */
PROCEDURE create_hidden_form(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10) IS
BEGIN
htp.p('<form name="all_objects" method="post" action="jl_example.create_page">');
htp.p('<input type="hidden" name="p_order_by" value="'||p_order_by||'" />');
htp.p('<input type="hidden" name="p_direction" value="'||p_direction||'" />');
htp.p('<input type="hidden" name="p_page" value="'||p_page||'" />');
htp.p('<input type="hidden" name="p_show_all" value="'||p_show_all||'" />');
htp.p('<input type="hidden" name="p_results" value="'||p_results||'" />');
htp.p('</form>');
END create_hidden_form;
/* This procedure simply closes the XHTML page */
PROCEDURE create_footer IS
BEGIN
htp.p('</body>');
htp.p('</html>');
END create_footer;
/* This procedure is the main procedure called to display the page. It calls
other procedures as required to display relevant sections of the page. */
PROCEDURE create_page(p_order_by IN VARCHAR2 DEFAULT '1',
p_direction IN VARCHAR2 DEFAULT '1',
p_page IN NUMBER DEFAULT 1,
p_show_all IN NUMBER DEFAULT 0,
p_results IN NUMBER DEFAULT 10) IS
BEGIN
create_header('Database Object Types');
create_table_header;
create_table_body(p_order_by, p_direction, p_page, p_show_all, p_results);
create_table_footer;
create_hidden_form(p_order_by, p_direction, p_page, p_show_all, p_results);
create_footer;
END create_page;
END jl_example;Feel free to use this code as you please. If you have any comments, suggestions or are having problems with this code, please leave a comment on this blog entry. I will try to help anyone who is struggling with the code, but I can't guarantee anything (remember this code is provided free of charge).