Dogs Eat Dogs

Sun to buy MySQL AB for USD 1B
Oracle to buy BEA for USD 8.5B

Give Me The Last X Records Ordered by These Criteria

While writing a script to analyze application log, written into Oracle database, I found that it is fairly hard to get the last X records using rownum after the records are ordered based on certain criteria.

Trupti Rajparia says:

[...] one should be careful when using the Order By clause along with Rownum. When Order By is used with Rownum to restrict query results, it works only if Ordered By is the primary key of the table.

Since I am not a great fan of PL/SQL (clunky POS), I decided to use Perl instead to slice and dice the matching criteria.

1. Get the INDEXED_CRITERIA from Oracle

SELECT
   INDEXED_CRITERIA
FROM logTable
WHERE
   filter criteria
ORDER BY
   INDEXED_CRITERIA


2. Get the last X INDEXED_CRITERIA (easily processed by Perl with three standard functions)
   

push each INDEXED_CRITERIA into array tempA
pop the last X records from array tempA into array tempB.
produce CRITERIA_TEXT by joining tempB with ',' as separator


3. Get the data from Oracle
   

SELECT
   REQUIRED_FIELDS
FROM logTable
WHERE
   CRITERIA_TEXT are met
ORDER BY
   INDEXED_CRITERIA


4. Process the data returned.

I Hate .*LOB

Today's lesson : I hate .*LOB.

First pain: there is no working DBI-Oracle PM for Perl 5.6. Now I have to switch to my stand alone Perl 5.8 (according to IPhang: it should work).

Second pain: the ppm GUI decided to die after buffer overflow. I know: oraociei10.dll is 91MB and gzoraociei10.dll is 30MB. So I have to fall back to the olde ppm command line.

Third pain: Oracle connection string, which one to choose? There are tons of non-working possibilities, in the end I picked tnsname.

Fourth pain: DB schema hell - each legal document type has it's own table (whiskey-tango-hotel?). Must insert additional logic here.

Last pain: getting the CLOB value (thank god - no inserting for today). Reading through the pod, tons of non-working examples and hints, I realized that:

1. Must know the length of the CLOB in advance. (select length(CLOB) from table)
   
2. Set the LongReadLen to the length of the CLOB
3. Get the CLOB (select CLOB from table) - voilà - the magic works.