Spot the difference

Prog rock

Anyone who remembers The Ramones will have happy memories of their toetappingly monolithic slabs of pure punk pop. After a couple of albums of two-chord magic, however, there were those who said it was starting to sound samey, and I believe it was Joey Ramone's dad who commented in an interview that perhaps the boys might want to "complicate it up a bit".

It seems the authors of Oracle Database 10g PL/SQL Programming (Oracle Press, 2004) chose to follow similar advice in their chapter on working with 8i, er, I mean 10g collections. Anyone looking for a funkier way to DELETE FROM addresses WHERE address_id = 11, for example, need look no further:
www.peakretrieval.com/plsql/Chapter6/varray_dml1.sql

And while we're at it,

SELECT column_value
FROM   THE (SELECT CAST(emp_address AS varray_nested_table )
            FROM   emp
            WHERE  empno = 11);

(love that type name btw) could also be written as

SELECT column_value
FROM   emp, TABLE(emp_address)
WHERE  empno = 11;

but where's the fun in that?

You can never have too many tables

There is no problem that cannot be solved with a loop and some dynamic SQL.

Problem: Your report can return rows for more than one feature code. How on Earth are you going to store the results?

Solution: Loop through the FEATURES table, creating three dynamically named tables for each feature code. Problem solved.

BEGIN
FOR rec IN
    ( SELECT f.short_code ft FROM features f
      WHERE  f.chargeable = 'Y' )
LOOP
    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t8_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t8_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t7_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t7_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t9_' || rec.ft ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF rec.ft <> 'ZZ' THEN
        EXECUTE IMMEDIATE 'create table r_t9_' || rec.ft || '(' ||
        'report_type varchar2(50),' ||
        'report_date date,' ||
        'switch_id number,' ||
        'feat_count number,' ||
        'rental_rate number,' ||
        'discount number,' ||
        'rental_rating number)' ;
    END IF;
END LOOP;
END;
/

Now, how to write those reports so they populate the right tables? I know...

Watching the Watcher-Watchers

I've just found a process that uses on-update triggers to insert rows into a table:

Queue_Monitor_Table
( source_table_name varchar2(30)
  rowid             varchar2(16) /*lovely,  but not the point*/
  enqueued_yn       char(1)
  dequeued_yn       char(1) )

This is processed by a pl/sql "queue_monitor_listener" that is started in a background process. A "queue_monitor_listener" if you can't guess, repeatedly scans the table for rows where enqueued_yn = 'N' and then it enqueues the rowid to an AQ queue, called the "row_update_queue". When it's happy that a row has been enqueued, it updates the enqueued_yn flag to "Y". As you might fear, there is also dequeue process. This also runs in a background job. When this has successfully dequeued from the row_update_queue, and done its task ( which could be the subject of a new WTF ), it updates the Queue_Monitor_Table to set dequeued_yn = 'Y'. Nice. The fun really seems to have begun when, in order to improve performance, it was decided that several "queue_monitor_listener" processes should be started in parallel background processes.