Avoiding Coincidental Cohesion

Given that Coincidental Cohesion is bad for our code base so obviously we want to avoid writing utilities packages. Fortunately it is mostly quite easy to do so. It requires vigilance on our part. Utilities packages are rarely planned. More often we are writing a piece of business functionality when we find ourselves in need of some low level functionality. It doesn't fit in the application package we're working on, perhaps we suspect that it might be more generally useful, so we need somewhere to put it.

The important thing is to recognise and resist the temptation of the Utilities package. The name itself (and similarly vague synonyms like helper or utils) should be a red flag. When we find ourselves about to type create or replace package utilities we need to stop and think: what would be a better name for this package? Consider whether there are related functions we might end up needing? Suppose we're about to write a function to convert a date into Unix epoch string. It doesn't take much imagine to think we might need a similar function to convert a Unix timestamp into a date. We don't need to write that function now but let's start a package dedicated to Time functions instead of a miscellaneous utils package.

Looking closely at the programs which comprise the DBMS_UTILITY package it is obviously unfair to describe them as a random selection. In fact that there seven or eight groups of related procedures.

DB Info

• INSTANCE_RECORD Record Type
• DBLINK_ARRAY Table Type
• INSTANCE_TABLE Table Type
• ACTIVE_INSTANCES Procedure
• CURRENT_INSTANCE Function
• DATA_BLOCK_ADDRESS_BLOCK Function
• DATA_BLOCK_ADDRESS_FILE Function
• DB_VERSION Procedure
• GET_ENDIANNESS Function
• GET_PARAMETER_VALUE Function
• IS_CLUSTER_DATABASE Function
• MAKE_DATA_BLOCK_ADDRESS Function
• PORT_STRING Function

Runtime Messages

• FORMAT_CALL_STACK Function
• FORMAT_ERROR_BACKTRACE Function
• FORMAT_ERROR_STACK Function

Object Management

• COMMA_TO_TABLE Procedures
• COMPILE_SCHEMA Procedure
• CREATE_ALTER_TYPE_ERROR_TABLE Procedure
• INVALIDATE Procedure
• TABLE_TO_COMMA Procedures
• VALIDATE Procedure

Object Info (Object Management?)

• INDEX_TABLE_TYPE Table Type
• LNAME_ARRAY Table Type
• NAME_ARRAY Table Type
• NUMBER_ARRAY Table Type
• UNCL_ARRAY Table Type
• CANONICALIZE Procedure
• GET_DEPENDENCY Procedure
• NAME_RESOLVE Procedure
• NAME_TOKENIZE Procedure

Session Info

• OLD_CURRENT_SCHEMA Function
• OLD_CURRENT_USER Function

SQL Manipulation

• EXPAND_SQL_TEXT Procedure
• GET_SQL_HASH Function
• SQLID_TO_SQLHASH Function

Statistics (deprecated))

• ANALYZE_DATABASE Procedure
• ANALYZE_PART_OBJECT Procedure
• ANALYZE_SCHEMA Procedure

Time

• GET_CPU_TIME Function
• GET_TIME Function
• GET_TZ_TRANSITIONS Procedure

Unclassified

• WAIT_ON_PENDING_DML Function
• EXEC_DDL_STATEMENT Procedure
• GET_HASH_VALUE Function
• IS_BIT_SET Function

We can see an alternative PL/SQL code suite, with several highly cohesive packages. But there will be some procedures which are genuinely unrelated to anything else. The four procedures in the Unclassified section above are examples. But writing a miscellaneous utils package for these programs is still wrong. There are better options.

1. Find a home. It's worth considering whether we already have a package which would fit the new function. Perhaps WAIT_ON_PENDING_DML() should have gone in DBMS_TRANSACTION; perhaps IS_BIT_SET() properly belongs in UTL_RAW.
2. A package of their own. Why not? It may seem extravagant to have a package with a single procedure but consider DBMS_DG with its lone procedure INITIATE_FS_FAILOVER(). The package delivers the usual architectural benefits plus it provides a natural home for related procedures we might discover a need for in the future.
3. Standalone procedure. Again, why not? We are so conditioned to think of a PL/SQL program as a package that we forget it can be just a Procedure or Function. Some programs are suited to standalone implementation.

So avoiding the Utilities package requires vigilance. Code reviews can help here. Preventing the Utilities package becoming entrenched is crucial: once we have a number of packages dependent on a Utilities package it is pretty hard to get rid of it. And once it becomes a fixture in the code base developers will consider it more acceptable to add procedures to it.

Part of the Designing PL/SQL Programs series