14-7. Creating a Sortable Web Report
Problem
You need a report that displays data that is sorted by a field the user selects.
Solution
Create a package that prompts the user for a sort field, then generates the sorted output using the sort field parameter in the ORDER BY section of the SELECT statement. In this example the user is prompted to select a sort option on the EMPLOYEEs table. The options are to sort by last name, hire date, salary, or employee ID.
Note See Recipe 14-1 to define a DAD and direct your browser to run your procedure.
Note See Recipe 14-2 for more information on the common package, which is used in this recipe.
CREATE OR REPLACE PACKAGE sorted AS
PROCEDURE html;
PROCEDURE rpt (sort_order VARCHAR2);
END sorted;
CREATE OR REPLACE PACKAGE BODY sorted AS
PROCEDURE html IS
BEGIN
common.header ('Sorted Report'),
htp.formOpen ('sorted.rpt', 'POST'),
htp.formSelectOpen ('sort_order', 'Select a Sort Order: '),
htp.formSelectOption ('Last Name'),
htp.formSelectOption ('Hire Date'),
htp.formSelectOption ('Salary'),
htp.formSelectOption ('Employee ID'),
htp.formSelectClose;
htp.formSubmit;
htp.formClose;
common.footer;
END html;
PROCEDURE rpt (sort_order VARCHAR2) IS
CURSOR driver IS
SELECT *
FROM employees
ORDER BY DECODE (sort_order,
'Last Name', last_name,
'Hire Date', TO_CHAR (hire_date, 'YYYYMMDD'),
'Salary', TO_CHAR (salary, '00000'),
'Employee ID', TO_CHAR (employee_id, '00000') );
BEGIN
common.header ('Sorted Report by '||sort_order); -- See recipe 14-2.
htp.tableOpen ('BORDER'),
htp.tableRowOpen ('LEFT', 'BOTTOM'),
htp.tableHeader ('Name'),
htp.tableHeader ('Hired'),
htp.tableHeader ('Salary'),
htp.tableHeader ('ID'),
htp.tableRowClose;
FOR rec IN driver LOOP
htp.tableRowOpen ('LEFT', 'TOP'),
htp.tableData (rec.last_name);
htp.tableData (rec.hire_date);
htp.tableData (rec.salary);
htp.tableData (rec.employee_id);
htp.tableRowClose;
END LOOP;
htp.tableClose;
common.footer;
END rpt;
END sorted;
How It Works
Users access the web page using the URL http://node.mycompany.com/DAD_NAME/sorted.html.
Note See Recipe 14-1 for more on how to to define the DAD_NAME.
The package specification is defined by exposing two procedures, html and rpt. You must define these procedures in the specification to make the PL/SQL procedures available within Apache.
Next, the package body is defined. The html procedure generates the data entry form. It generates the opening HTML code by calling the common.header routine defined in Recipe 14-2. Next, it calls the htp.formOpen to set the form's action when the user clicks the Submit button. The calls to htp.formSelectOpen, htp.formSelectOption and htp.formSelectClose procedures create the dropdown list for the user to select a sort order.
Note See Recipe 14-6 for more information on how to create dropdown lists.
A call to form.submit, form.close and common.footer complete the necessary HTML code. The form generated is shown in Figure 14-5.
The rpt procedure accepts the sort_order parameter, which is used in the cursor to dynamically determine the sort order on the EMPLOYEES table. The order by option in the select statement uses the decode function to return the proper string needed for ordering based on the user's input.
The first set of parameters sent to the decode function, namely the first_name field, defines the data type returned by the decode function. This is important to note as the remaining data types returned from the decode function will be converted to strings to match the first_name. It is necessary to convert the numeric and date fields to strings that sort properly. For example, if the default date string format is dd-Mon-yy, then the hire dates will sort by the day of the month first, then by the month's abbreviation and year. The desired sort order is year, month, then day.
Figure 14-5. Initial data entry screen showing the sort options