ssdownloader

I recently purchased some electronic media from Simon & Schuster.

Apparently the technology guys at Simon & Schuster believe in "Java Over Functionality." Unlike most companies that sell DRM-free digital media, I was not given a link to download zip file containing the contents of my purchase.

Instead, I was given a JNLP file, which is nothing more than an XML file. A little Googling revealed that these files should be openable by javaws, a Java Web Starter application. After getting the file to successfully run, I was greeted with a custom download manager. This is great because why offer a simple download that will be managed with my browser's download manager when I can have some half-rate, custom download manager to fudge things up?

So the download manager asked me where I would like to save the file, downloaded each file individually for about twenty minutes, and then told me it was done. There was a big problem, my files where not in the location I specified. In fact, a hard drive search revealed they didn't exist anywhere on my machine.

I tried again, no dice.

So, if Mother Necessity if the driver of invention, then welcome her new baby, ssdownloader. I wrote this Python app up in a little over an hour. It was a good chance to strengthen my weak command of Python, and to actually get the stuff I paid for.

It simply parses the JNLP file to find the other XML file that defines where the actual downloads are. It then loops through that file, grabbing each file and saving it locally. If anyone out there gets frustrated trying to make good on their Simon & Schuster purchases, feel free to use this to make your life happy.

Copy and paste from the system clipboard with vim

Often, when using vim, highlighting text in the terminal to copy-and-paste it around is plausible.  This is definitely true when on a true terminal.  I use the rnu option so that I have relative line numbers on each line.  So copying multiple lines with the mouse grabs the line number with unwanted indentation.  When I need to move code around it is annoying to have to manually remove the numbers.

To understand how to get vim's clipboard to match your system's you need to understand vim registers.  On a computer we usually only get one clipboard.  Every time we ctrl-c, the contents of the clipboard are discarded and replaced with whatever is highlighted.  We don't have any option to copy multiple objects and then paste them around.  However vim has multiple "registers" where text can be copied and pasted from.  To see the registers type :reg in command mode.

The register that we are interested in is register +.  To test it out, copy some text from another application then run :reg and see the contents displayed in register +.

So now we just need to know how to access the contents of register +.  Register access is done with ".  To paste from they keyboard we type "+p  To copy, for example an entire line, into the system clipboard we type "+yy  To delete the current line and store it in the system clipboard we type "+dd

We can also use registers with visual mode.  If we wanted to copy the line underneath the cursor we would type V"+y  We could copy the next three lines into the system clipboard by typing Vjj"+y

To sum it up, to use the system clipboard to copy and paste in vim simply do what you would usually do in vim, then prepend "+ to your y (yank) or p (paste) command.

Update:
Depending on the version of vim you are using, the register for the clipboard may work differently. In some cases the * and + registers are the same register. If you want to find out which register is for your system clipboard, simply copy some text from anywhere into your clipboard, then run :reg. Whichever register has the text you copied is the register that holds the contents of your clipboard.

cx_Oracle on Ubuntu 11.04 Natty

I used the following to install cx_Oracle on Ubuntu 11.04 for use with Python 3.2, though they should work with any version of Python supported by cx_Oracle.  This will basically involve using alien to convert lots of rpms into debian package.  Go ahead and install alien as well as some other libraries we will need.

sudo apt-get install alien libaio1 python3.2 libpython3.2

Change the above to reflect the version of Python you want to use.

First we need the Oracle Instant Client software.  Go to http://www.oracle.com/technetwork/topics/linuxsoft-082809.html or http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html (for 64-bit) and download either the basic or basiclite rpm.  I did not try the basiclite client, but I think they should both work.  You can also grab sqlplus and whatever other packages you may need while working with Oracle, but only the basic package is needed for cx_Oracle to work.  Once downloaded convert it to a debian package.

sudo alien -d oracle-instantclient11.2-sqlplus-11.2.0.2.0.i386.rpm

Replace the above line with the appropriate version of the instant client that you downloaded.

Now install it.

sudo dpkg -i oracle-instantclient11.2-basic_11.2.0.2.0-2_i386.deb

We need to create a configuration file pointing to the oracle library folder.

sudo vi /etc/ld.so.conf.d/oracle.conf

Depending on the version of the instant client installed you should have a folder like /usr/lib/oracle/11.2/client/lib.  This folder should be the first line to the oracle.conf file we create here.

Finally, for our Oracle client install, we need to define our ORACLE_HOME.  We need to create a new file.

sudo vi /etc/profile.d/oracle.sh

Add the following line, tweaking it as necessary to point to the version of Oracle you installed.

export ORACLE_HOME=/usr/lib/oracle/11.2/client
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

On 64-bit installs then do the following line instead.

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Now that our client software is setup, we can install the python library to connect to Oracle.

Download cx_Oracle from http://cx-oracle.sourceforge.net/  I want the 11gR2 client for my x86 (32-bit) machine, and I want to use it with Python 3.2, so here I download the line labeled CentOS 5 i386 RPM (Oracle 11g, Python 3.2)  Make sure NOT to download the Unicode version unless you know for sure you need it.  Download the version for the version of Oracle and Python that you plan on using.  Usually, regardless of the version of Oracle you plan on using, downloading the latest version will work with any of the older versions of Oracle.

After downloading we use alien again to created a debian package.

sudo alien -d cx_Oracle-5.1-11g-py32-1.i386.rpm

sudo dpkg -i cx-oracle_5.1-2_i386.deb

The way our debian installs cx_Oracle isn't quite what Ubuntu needs, so run the following command to set things right.

cd /usr/lib/python3.2

sudo mv site-packages/cx_Oracle* dist-packages/

sudo rmdir site-packages/

sudo ln -s dist-packages site-packages

If the dist-packages folder does not already exist, you may need to run the following.

sudo mkdir dist-packages

Now you should be able to import the cx_Oracle module into Python and start creating connection.

david@fink:/usr/lib/python3.2$ python3.2

Python 3.2 (r32:88445, Mar 25 2011, 19:28:28) 

[GCC 4.5.2] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> import cx_Oracle

Profit!

Introducing Git-Notifier

This evening I finished the preliminary base code for a project I have been working on called Git-Notifier.  Inspired by Gitifer for OSX, Git-Notifier provides pop-up notifications for updates of a remote git repository using libnotify in Ubuntu.

As it stands, the program is currently a bash script, and configuration is a manual process.

To get started, download the come from http://github.com/maxolasersquad/Git-Notifier/archives/v0.1 in either tar.gz or .zip format.  Extract the .gitnotifier folder to your home directory.  Extract the gitnotify.sh file to wherever you would like to execute it from.  I keep this file in my home directory as well.

Next cd into the ~/.gitnotify directory and clone the remote repository using the -n switch.  For example, to get notified of updates to the Git-Notifier application, do the following

cd ~/.gitnotify

git clone -n git://github.com/maxolasersquad/Git-Notifier.git

Finally, edit the file ~/.gitnotify/gitnotify.ini file.  To the right of repos=, list out each git project you have cloned.  Using the same example from above, the line would look like this.

repos=Git-Notifier

If you have cloned any more repositories, simply list them all in the repos line, separated by a space.

Finally you can change how often the program looks for updates, and the pretty style to display the results. To see the different options for the pretty styel, run man git show.

Once you have cloned the directories you want, with the -n switch, and you have configured the ini file appropriately, simply run the gitnotify.sh script.  As commits are made to the remote repository, you should get notifications on your deskop.

The code could probably use some love, and a few more features would be nice as well.  Once I am happy with how this script works, I'd like to port it over to python.  I'd also like to have better support for non-Ubuntu flavors of Linux, though I'm not sure what other notifications are out there that can be easily programmed from Bash.

Any bugs, fixes, improvements, etc. would be appreciated on the github site.

Migrating from subversion to git

At work we have been migrating from subversion to git.  The projects that we still have in subversion I check out using git-svn.  This allows me to use much of the git happiness while we make the transition.  It also performs another very nice function; it allows us to migrate our repositories to git keeping our full version control history.

What follows demonstrates, from a Linux machine, to migrate a repository from http://svn.foo.com/bar to ssh://git.foo.com/repos/bar.git.  This assumes you already have git, subversion, and git subversion installed.  It also assumes that you have read access to the subversion repository, and a git repository you have direct access to.

git svn clone http://svn.foo.com/bar/trunk bar

Next we need to log into the machine hosting our git repository.  We do this over ssh.

ssh foo.com

cd ~/repos

mkdir bar.git

cd bar.git

git init --bare

Back on our machine.

cd bar

git remote add origin ssh://git.foo.com/repos/bar.git

git push origin master

You should now have a central git repository at ssh://git.foo.com/repos/bar.git  You can create your branches, and do whatever git-style version controlling that you wish.  The final step, of course, is to remove the old subversion repository so that others do not accidentally make changes that never find themselves in the real copy of the code.

One limitation is that this process does not import any branches or tags from the subversion repository.  I'm not sure if there is a clean way to do this.

Shuffling cards in PHP

The following demonstrates what I believe to be an efficient means of shuffling a deck of cards, using PHP.




$cards = array(array('suit'=>'Spade', 'rank'=>'01'), array('suit'=>'Spade', 'rank'=>'02'), array('suit'=>'Spade', 'rank'=>'03'), array('suit'=>'Spade', 'rank'=>'04'), array('suit'=>'Spade', 'rank'=>'05'), array('suit'=>'Spade', 'rank'=>'06'), array('suit'=>'Spade', 'rank'=>'07'), array('suit'=>'Spade', 'rank'=>'08'), array('suit'=>'Spade', 'rank'=>'09'), array('suit'=>'Spade', 'rank'=>'10'), array('suit'=>'Spade', 'rank'=>'11'), array('suit'=>'Spade', 'rank'=>'12'), array('suit'=>'Spade', 'rank'=>'13'), array('suit'=>'Spade', 'rank'=>'14'),
               array('suit'=>'Club', 'rank'=>'01'), array('suit'=>'Club', 'rank'=>'02'), array('suit'=>'Club', 'rank'=>'03'), array('suit'=>'Club', 'rank'=>'04'), array('suit'=>'Club', 'rank'=>'05'), array('suit'=>'Club', 'rank'=>'06'), array('suit'=>'Club', 'rank'=>'07'), array('suit'=>'Club', 'rank'=>'08'), array('suit'=>'Club', 'rank'=>'09'), array('suit'=>'Club', 'rank'=>'10'), array('suit'=>'Club', 'rank'=>'11'), array('suit'=>'Club', 'rank'=>'12'), array('suit'=>'Club', 'rank'=>'13'), array('suit'=>'Club', 'rank'=>'14'),
               array('suit'=>'Diamond', 'rank'=>'01'), array('suit'=>'Diamond', 'rank'=>'02'), array('suit'=>'Diamond', 'rank'=>'03'), array('suit'=>'Diamond', 'rank'=>'04'), array('suit'=>'Diamond', 'rank'=>'05'), array('suit'=>'Diamond', 'rank'=>'06'), array('suit'=>'Diamond', 'rank'=>'07'), array('suit'=>'Diamond', 'rank'=>'08'), array('suit'=>'Diamond', 'rank'=>'09'), array('suit'=>'Diamond', 'rank'=>'10'), array('suit'=>'Diamond', 'rank'=>'11'), array('suit'=>'Diamond', 'rank'=>'12'), array('suit'=>'Diamond', 'rank'=>'13'), array('suit'=>'Diamond', 'rank'=>'14'),
               array('suit'=>'Heart', 'rank'=>'01'), array('suit'=>'Heart', 'rank'=>'02'), array('suit'=>'Heart', 'rank'=>'03'), array('suit'=>'Heart', 'rank'=>'04'), array('suit'=>'Heart', 'rank'=>'05'), array('suit'=>'Heart', 'rank'=>'06'), array('suit'=>'Heart', 'rank'=>'07'), array('suit'=>'Heart', 'rank'=>'08'), array('suit'=>'Heart', 'rank'=>'09'), array('suit'=>'Heart', 'rank'=>'10'), array('suit'=>'Heart', 'rank'=>'11'), array('suit'=>'Heart', 'rank'=>'12'), array('suit'=>'Heart', 'rank'=>'13'), array('suit'=>'Heart', 'rank'=>'14'));
$shuffled_cards = array();
while (sizeof($cards) != 0)
{
  mt_srand((double)microtime()*1000000);
  $card = mt_rand(0, sizeof($cards) - 1);
  $shuffled_cards[] = $cards[$card];
  unset($cards[$card]);
  $cards = array_values($cards);
}
print_r($shuffled_cards);

We start by initializing our deck of fifty-two cards.  Each member of our array is an associative array holding the suit and rank of each card.  We then initialize a blank array where we will store our shuffled cards.
The while loop will execute until all of the cards have been popped out of our initial array.
At the beginning of each iteration we start a new pseudo-random seed.  Then, we get a random number between zero, and the number of cards we have left to shuffle, minus one (because arrays are zero indexed.)  Next, we append the randomly selected card to our shuffled array and remove it from our cards array.
When we remove a card from our numerically indexed array, the array becomes sparse.  So if the random number was four, then we are left with indexes one, two, three, five, six and so on.  If we hit four through another iteration, there would be no value to add to our shuffled array.
To make our array dense again, we can simply call the array_values function on our cards array and assign it back to cards.
This solution buys us a few niceties, compared to other solutions I have come across.  First, at any given time a card is only in one array (except the split moment that we are doing the move).  The second is that we don't have to worry about our random number being the same as a previously picked random number, and thus waste time picking another number until we hit a unique number.
Finally, this solution is really just an exercise in efficiently shuffling an array.  In a real application I would not use this solution at all.  PHP comes with a nice little function named, easy enough to remember, shuffle().  So let's take a look at how one should really implement the solution.


$cards = array(array('suit'=>'Spade', 'rank'=>'01'), array('suit'=>'Spade', 'rank'=>'02'), array('suit'=>'Spade', 'rank'=>'03'), array('suit'=>'Spade', 'rank'=>'04'), array('suit'=>'Spade', 'rank'=>'05'), array('suit'=>'Spade', 'rank'=>'06'), array('suit'=>'Spade', 'rank'=>'07'), array('suit'=>'Spade', 'rank'=>'08'), array('suit'=>'Spade', 'rank'=>'09'), array('suit'=>'Spade', 'rank'=>'10'), array('suit'=>'Spade', 'rank'=>'11'), array('suit'=>'Spade', 'rank'=>'12'), array('suit'=>'Spade', 'rank'=>'13'), array('suit'=>'Spade', 'rank'=>'14'),
               array('suit'=>'Club', 'rank'=>'01'), array('suit'=>'Club', 'rank'=>'02'), array('suit'=>'Club', 'rank'=>'03'), array('suit'=>'Club', 'rank'=>'04'), array('suit'=>'Club', 'rank'=>'05'), array('suit'=>'Club', 'rank'=>'06'), array('suit'=>'Club', 'rank'=>'07'), array('suit'=>'Club', 'rank'=>'08'), array('suit'=>'Club', 'rank'=>'09'), array('suit'=>'Club', 'rank'=>'10'), array('suit'=>'Club', 'rank'=>'11'), array('suit'=>'Club', 'rank'=>'12'), array('suit'=>'Club', 'rank'=>'13'), array('suit'=>'Club', 'rank'=>'14'),
               array('suit'=>'Diamond', 'rank'=>'01'), array('suit'=>'Diamond', 'rank'=>'02'), array('suit'=>'Diamond', 'rank'=>'03'), array('suit'=>'Diamond', 'rank'=>'04'), array('suit'=>'Diamond', 'rank'=>'05'), array('suit'=>'Diamond', 'rank'=>'06'), array('suit'=>'Diamond', 'rank'=>'07'), array('suit'=>'Diamond', 'rank'=>'08'), array('suit'=>'Diamond', 'rank'=>'09'), array('suit'=>'Diamond', 'rank'=>'10'), array('suit'=>'Diamond', 'rank'=>'11'), array('suit'=>'Diamond', 'rank'=>'12'), array('suit'=>'Diamond', 'rank'=>'13'), array('suit'=>'Diamond', 'rank'=>'14'),
               array('suit'=>'Heart', 'rank'=>'01'), array('suit'=>'Heart', 'rank'=>'02'), array('suit'=>'Heart', 'rank'=>'03'), array('suit'=>'Heart', 'rank'=>'04'), array('suit'=>'Heart', 'rank'=>'05'), array('suit'=>'Heart', 'rank'=>'06'), array('suit'=>'Heart', 'rank'=>'07'), array('suit'=>'Heart', 'rank'=>'08'), array('suit'=>'Heart', 'rank'=>'09'), array('suit'=>'Heart', 'rank'=>'10'), array('suit'=>'Heart', 'rank'=>'11'), array('suit'=>'Heart', 'rank'=>'12'), array('suit'=>'Heart', 'rank'=>'13'), array('suit'=>'Heart', 'rank'=>'14'));
shuffle($cards);
print_r($cards);

Listing invalid indexes in Oracle

I have often seen it asked online many times how to get a list of invalid indexes. The information was mostly incomplete, and often contained misinformation. Determined to solve this problem once and for all I wrote a query to get a list of invalid indexes on our Oracle 10g database.

Before we get on to the query, I'd like to note what makes finding invalid indexes so difficult, and how to overcome these obstacles.

Finding invalid global indexes is rather easy. Global indexes are your standard indexes you use and love every day. They are simply an index on a column, or columns in a standard non-partitioned table. Oracle provides a nice little view called all_indexes, which has a column named status. This view will return a list of all indexes that the user running the query has permissions to view. There is also the dba_indexes view and the user_indexes view. The status column can be one of three values, VALID, INVALID, or N/A. Global indexes will never have a status of N/A.

We run into trouble if we try to query WHERE status != 'VALID' if our database has any partitioned tables with partitioned indexes and potentially bring back valid indexes. Partitioned tables, and their indexes, save the lives of millions of babies every year, so we must give them our full respect, and do the necessary extra work that is required to account for the quirks they sometimes bring to our code.

Partitioned indexes will always have a status of N/A in the all_indexes view. Each of these indexes will have a partitioned index, which we can see by querying the all_ind_partitions view. Again, we also have the dba_ind_partitions and user_ind_partitions views available to us as well. This table contains all partitioned indexes, along with a status column. This status column works a little bit differently. Its possible values are USABLE, UNUSABLE, and N/A.

If our index'es partitioned indexes are marked as usable then we know our index is valid. However, we could again run into a case where our status column is marked as N/A. Partitioned indexes can go a level deeper and have their own subpartitioned indexes. Determined, we pick up our shovel and keep digging deeper.

Oracle provides us with a third view named all_ind_subpartitions. Like the above views, we also have user_ind_subpartitions and dba_ind_subpartitions. Again, we have a status column. This time, though, we can only have two possible values, USABLE, and UNUSABLE. At this point, we can determine if our index is valid or not by looking at its partition's subpartition's status.


Okay, so this is all well and good, but what we really need is a query that will give us the name of indexes that need rebuilding. Global indexes do not have partitions, and not all partitioned indexes have subpartitions, yet we want to know all invalid indexes, regardless of their makeup. The following query accomplishes this.

select
 index_name
from
  all_indexes
where
  owner not in ('SYS', 'SYSTEM')
  and
  status != 'VALID'
  and (
    status != 'N/A'
    or
    index_name in (
      select
        index_name
      from
        all_ind_partitions
      where
        status != 'USABLE'
        and (
          status != 'N/A'
          or
          index_name in (
            select
              index_name
            from
              all_ind_subpartitions
            where
              status != 'USABLE'
          )
        )
    )
);

I'll leave dissecting how this query works as a lesson to the reader. Here I am also leaving out the indexes owned by SYS and SYSTEM, but you can remove that line if you need those indexes listed as well.


I do presently have an anonymous block that makes a cursor out of this query, loops through it, and builds all indexes, partitioned indexes, and subpartitioned indexes, as is appropriate. I am still working on making sure that it is battle hardened. After that I'll post about how I accomplish this. Combined they provide an elegant solution to finding all of your invalid indexes and rebuild them.

If you find this helpful, or have some additional information to provide, please do so in the comment section below.


Edit: The script is available at Rebuilding invalid indexes in Oracle

Dynamic column ordering and paging in PL/SQL

Update 09/30/2010: There was a problem with how I was doing the dynamic ordering.  I have resolved this and updated the post.

Update 10/08/2013: Oracle 12c finally has support for sane paging without jumping through all of these hoops. Until I get a chance to update this post you can read more at http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

The following demonstrates how a PL/SQL function or procedure can take in parameters, and dynamically sort based on those parameters.

Let's start with an example.  This example accepts two parameters, a VARCHAR2 that will be the column we want to sort on, and a REF CURSOR.  The REF CURSOR is an out variables that the calling program can use to iterate through to get the result set.

TYPE cursortype IS REF CURSOR;

PROCEDURE sort_example

(

  sort_column VARCHAR2,

  result_set OUT cursortype

) AS

BEGIN

  OPEN result_set FOR

    SELECT column_one,

      column_two

      FROM example_table
      ORDER BY DECODE
      (
        UPPER(sort_column),
        'COLUMN_ONE',
        column_one,
        'COLUMN_TWO',
        column_two,
        column_one
      );

EXCEPTION

  WHEN OTHERS THEN

    NULL;//Real error handling should go in this exception block.

END;

The DECODE statement examines the sort_column passed in and matches it to an actual column in the table(s) that we are selecting from.  If the variable passed in is not in our list of columns then it will default, in this example, to column_one.
Using this technique we have to define, up front, all of the columns that we are going to allow the calling procedure to order by.  If we want to allow ordering by any column, we must go through and list them out one-by-one.
It is also a good idea to add some extra validation, beyond the DECODE clause, to make sure that sort_column is an actual column in the table.  In our real packages that do this, we have an out variable named message.  If an invalid column name is passed in we notify the calling program by sticking a message to that effect in our message variable.

To make our procedure even more robust, it is nice to have the ability to decide if the column should be sorted in ascending or descending order.

This gets a little tricker, and to accomplish we need to understand how ROWNUM works in PL/SQL.  When a query is executed, Oracle assigns each returned result a pseudo-column nmaed ROWNUM, with the first returned row having a ROWNUM of 1, the second 2, etc.

Through a subquery, and ROWNUM, we can dynamically sort in either ascending or descending order.

TYPE cursortype IS REF CURSOR;

PROCEDURE sort_example

  (

    sort_column VARCHAR2,

    sort_direction VARCHAR2,

    result_set OUT cursortype

  ) AS

BEGIN

  OPEN result_set FOR

  SELECT *

    FROM

    (

      SELECT column_one,

        column_two

        FROM example_table

        ORDER BY DECODE

        (

          UPPER(sort_column),

          'COLUMN_ONE',

          column_one,

          'COLUMN_TWO',

          column_two,

          column_one

        )

    )

    ORDER BY

      CASE

        WHEN UPPER(sort_direction) = 'DESC' THEN

          ROWNUM * -1

        ELSE

          ROWNUM

      END;

EXCEPTION

  WHEN OTHERS THEN

    NULL;//Real error handling should go in this exception block.

END;

Here we have our original query, initially sorting on the passed in column, that is subqueried and then sorted again, either on the rownum, or the opposite of rownum.  If the calling program passed in 'DESC' for the sort_direction, then we sort descending, if anything else is passed in we sort ascending.

We can take this maddening query a step further, utilizing rownum again, to handle paging.  We use this in our applications to push paging out of our interface code, and onto the database.  It is much quicker for our database to only return ten or so results we want to show on each page, then to return the whole set of data, and have our PHP application parse through the data.

So here is our final procedure, with dynamic sorting and paging.

TYPE cursortype IS REF CURSOR;

PROCEDURE sort_example

  (

    sort_column VARCHAR2,

    sort_direction VARCHAR2,

    start_row NUMBER,

    end_row NUMBER,

    result_set OUT cursortype

  ) AS

BEGIN

  IF start_row < 1 THEN

    start_row = 1

  END IF;

  IF start_row > end_row THEN

    end_row = start_row;

  ELSIF end_row IS NULL THEN

    end_row = start_row + 10;

  END IF;

  OPEN result_set FOR

  SELECT *

    FROM

    (

      SELECT ROWNUM rnum,

        column_one,

        column_two

        FROM

        (

          SELECT *

            FROM

            (

              SELECT column_one,

                column_two

                FROM example_table

                ORDER BY DECODE

                (

                  UPPER(sort_column),

                  'COLUMN_ONE',

                  column_one,

                  'COLUMN_TWO',

                  column_two,

                  column_one

                )

            )

            ORDER BY

              CASE

                WHEN UPPER(sort_direction) = 'DESC' THEN

                  ROWNUM * -1

                ELSE

                  ROWNUM

              END

        )

    )

    WHERE rnum >= start_result

      AND rnum <= end_result

    ORDER BY rnum;

EXCEPTION

  WHEN OTHERS THEN

    NULL;//Real error handling should go in this exception block.

END;

Yes, this one is a beast.  Starting at the child-most query, we have our SELECT statement, ordering the data by the column passed in.  In the parent query we then use ROWNUM, and the sort_direction argument to sort either ascending or descending.  We subquery these results, capturing the ROWNUM into an aliased column named rnum, so that in it's parent query we can refer to it's ROWNUM, and not some other ROWNUM from any other part of the query.  In the parent-most query we use our new rnum column to get only the data from the rows asked for.

Before you do any querying, we check the validity of the start_result and end_result arguments to make sure they actually make sense.  If they don't then we do some manual patching.  You may want to handle this differently, such as throwing an exception.  It is really up to you.  We have a DEFAULT_PAGING environmental variable we store in our database.  When end_result is NULL, then we set it to start_result plus our DEFAULT_PAGING variable.

You may look at this and think it is complicated, and probably performs very poorly.  In our testing, as long as your inner-most query has the proper indexes and constraints, the performance is actually pretty good.  In our environment, it is definitely much faster than having our PHP parse through all the results, sort and order the whole thing, and then cut out only the rows we actually want, especially when we have result sets that are well over 100,000 rows long.

There is one final step we take, so that our interface application knows how many results there are in the whole query, so that it knows how many pages it can expect there to be.  This example won't show it, but we actually check for a cached rowcount first, and we have all sorts of logic to try to guarantee integrity of those results, but I'm not going to get into all that here.

TYPE cursortype IS REF CURSOR;

PROCEDURE sort_example

  (

    sort_column VARCHAR2,

    sort_direction VARCHAR2,

    start_row NUMBER,

    end_row NUMBER,

    result_set OUT cursortype,

    rowcount OUT NUMBER

  ) AS

BEGIN

  IF start_row < 1 THEN

    start_row = 1

  END IF;

  IF start_row > end_row THEN

    end_row = start_row;

  ELSIF end_row IS NULL THEN

    end_row = start_row + 10;

  END IF;

  OPEN result_set FOR

  SELECT *

    FROM

    (

      SELECT ROWNUM rnum,

        column_one,

        column_two

        FROM

        (

          SELECT *

            FROM

            (

              SELECT column_one,

                column_two

                FROM example_table

                ORDER BY DECODE

                (

                  UPPER(sort_column),

                  'COLUMN_ONE',

                  column_one,

                  'COLUMN_TWO',

                  column_two,

                  column_one

                )

            )

            ORDER BY

              CASE

                WHEN UPPER(sort_direction) = 'DESC' THEN

                  ROWNUM * -1

                ELSE

                  ROWNUM

              END

        )

    )

    WHERE rnum >= start_result

      AND rnum <= end_result

    ORDER BY rnum;

  SELECT COUNT(*)

    INTO rowcount

    FROM example_table;

EXCEPTION

  WHEN OTHERS THEN

    NULL;//Real error handling should go in this exception block.

END;

With any luck this will help someone out.  With better luck, someone will drop by and offer some tips on how to do this even better.