I'm not making it up

This gem is used at times of particularly high throughput to monitor the size of queues.

PROCEDURE getqueuecount (
   queuename                  IN       VARCHAR2
  ,agentname                  IN       VARCHAR2
  ,queuecount                 OUT      INTEGER
)
IS
   queueoptions        DBMS_AQ.dequeue_options_t;
   messageproperties   DBMS_AQ.message_properties_t;
   MESSAGE             connect_db_external_q_type;
   messageid           RAW (16);
   nodequeuewaiting    EXCEPTION;
   dequeuewaiting      BOOLEAN                      := TRUE;
   nolock              EXCEPTION;
   PRAGMA EXCEPTION_INIT (nolock, -054);
   PRAGMA EXCEPTION_INIT (nodequeuewaiting, -25228);
BEGIN
   LOCK TABLE q_lock_table IN EXCLUSIVE MODE NOWAIT;
   queuecount                 := 0;
   queueoptions.WAIT          := 1;
   queueoptions.consumer_name := agentname;
   queueoptions.navigation    := DBMS_AQ.first_message;

   BEGIN
      WHILE (dequeuewaiting)
      LOOP
         DBMS_AQ.dequeue (queuename
                         ,queueoptions
                         ,messageproperties
                         ,MESSAGE
                         ,messageid
                         );
         queuecount  :=   queuecount  + 1;
      END LOOP;
   EXCEPTION
      WHEN nodequeuewaiting
      THEN
         dequeuewaiting             := FALSE;
   END;

   ROLLBACK;
EXCEPTION
   WHEN nolock
   THEN
      queuecount                 := -1;
END;

Nesting instinct

It is said that the human brain can retain only seven facts in short term memory at one time. Or something like that. Test yourself anyway with the following example, which occurs in the middle of a 175-line INSERT statement, in a 1200-line package body, all written in much the same self-explanatory style:

INSERT INTO some_unsuspecting_table
WITH all_dn AS (SELECT ... FROM ...)
SELECT ...
       CASE WHEN SUBSTR(all_dn.dn_data,6,4) IN ('PODN','UNDN')
           THEN SUBSTR(all_dn.dn_data,6,4)
           ELSE
               NVL2
               ( NVL(l.len, substr(a.features,16,15)),
                 DECODE(MAX(CASE WHEN all_dn.dn_data LIKE '%MSN%'
                                 THEN 2
                                 ELSE
                                     CASE WHEN all_dn.dn_data LIKE '%TWDN%'
                                          THEN 1
                                     END
                            END)
                        OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) ),
                        2, 'MSN',
                        1, 'TWDN', 'LINE' ),
                 CASE WHEN all_dn.dn_data LIKE '%MSN%'
                      THEN 'MSN'
                      ELSE all_dn.dn_data
                 END )
       END main_service_type
     , ...and so on...
FROM   etc etc;

Now, how many levels of nesting was that? What, you passed out somewhere around OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) )? Shame on you.

The little things that snowball

There's nothing more I can say about this.

CREATE TABLE INVOICE_LINEITEM 
(
...
...
MONTH_OF_YEAR   CHAR(1)
...
)

Avoiding the overhead of SQL

Ok, I'll go first, even though this is more of a "whyTF". Have a look at this piece of genius. I've only pulled the bare bones because these scripts are HUGE. Names are changed for the usual reasons. dumpdb.sh follows:

#!/bin/ksh

function showtables
{
  cat << EOF
acc:client_account
amt:client_model_type
...
700 lines later
...
ugp:ultimate_group_parameter
vap:validation_parameter
EOF
}

function execSQL
{
eval "echo \"$SQL_HEADER$1$SQLTRAILER\" \
  |sqlplus -S $ORACONNECT   |path=/usr/xpg4/bin:"$PATH" awk -F: $AWK_PROG \
  | sed 's/::/: :/g' | sed 's/~:/:/g' \
  $OUT_FILTER \
  $ OUTPUT"
}
.
. 700 functions like this, one per table.
.
function fDump_client
{
    execSQL "select * from client;"
}
.

for table in $tables_to_dump
do
    if [[ "$TO_TAB_FILE" == "Y" ]]; then
        ## Send output to file named <table>.csv
        export OUTPUT=" > $table.csv"
        echo "Dumping $table to $table.csv"
    fi
    # Get filter if required

    if [[ $? -eq 0 ]]; then
        # There is a specific function for this table so use it
        fDump_${table}

    else
        # No function - just use default SELECT
        execSQL "select * from ${table};"
    fi
done

which is called by "application support scripts" as demonstrated:

#!/bin/ksh

function select_client_access_type
{
    
    # dbselect gives:
    #
    # 1 CLIENT_KEY         NOT NULL CHAR(3)
    # 2 MEMBER_KEY         VARCHAR2(4)
    # 3 NAME               VARCHAR2(50)
    # 4 PHONE              VARCHAR2(22)
    # 5 ACCESS_ENABLED     VARCHAR2(1)
    # 6 ACCESS_TYPE        CHAR(1)
    # 7 MM_AND_OR_CLIENT   VARCHAR2(1)
    # 8 RIGHTS_GROUP_ID    NUMBER(4)
    # 9 MANAGER_ID         NUMBER(38)

    IFS=:
    if ! set -- $(dumpdb | grep "^${CLIENT_KEY}:")
    then
        echo "CLIENT '$CLIENT_KEY' not found"
        exit $EXIT_NO_CLIENT
    fi

    MEMBER_KEY="$2"
    ACCESS_ENABLED="$5"
    ACCESS_TYPE="$6"
    MM_AND_OR_CLIENT="$7"
    RIGHTS_GROUP_ID="$8"

    if [[ $ACCESS_ENABLED != Y ]]
    then
    if [[ $ACCESS_TYPE != T ]]
    then
        echo "CLIENT ACCESS_TYPE is $ACCESS_TYPE"
    fi
    fi
}