Today I was working on a data table, output in HTML generated by PL/SQL and I needed to implement a feature that would allow the user to sort the table based on the column header they clicked on.

Basically I have a procedure which takes a parameter (p_sort_by) and establishes which column to sort by based on this parameter. It does this using the DECODE function, which has similar functionality to IF-THEN-ELSE statements. I could have alternatively used CASE statements here, which are easier to understand, but they are not supported in older versions of Oracle. An example of this procedure is:

PROCEDURE display_table(p_sort_by IN NUMBER DEFAULT 1) IS
    CURSOR c_get_data IS
        SELECT name, city, dept, dob
        FROM person
        ORDER BY
        DECODE(p_sort_by, 1, name, 
                  2, city, 
                  3, dept, 
                  4, to_char(dob, 'yyyymmdd')
            );
BEGIN
    FOR x IN c_get_data LOOP
        htp.p('<tr>');
        htp.p('<td>'||x.name||'</td>');
        htp.p('<td>'||x.city||'</td>');
    htp.p('<td>'||x'dept||'</td>');
    htp.p('<td>'||x.dob||'</td>');
        htp.p('</tr>');
    END LOOP;
END display_table;

To sort the table by name, pass 1 as a parameter to the display_table procedure. To sort by city, pass 2, and so on.