The use and misuse of %TYPE and %ROWTYPE attributes in PL/SQL APIs

PL/SQL provides two attributes which allow us to declare a data structure with its datatype derived from a database table or a previously declared variable.

We can use %type attribute for defining a constant, a variable, a collection element, record field or PL/SQL program parameters. While we can reference a previously declared variable, the most common use case is to tie the declaration to a table column. The following snippet declares a variable with the same datatype and characteristics (length, scale, precision) as the SAL column of the EMP table.

l_salary emp.sal%type;

We can use the %rowtype attribute to declare a record variable which matches the projection of a database table or view, or a cursor variable. The following snippet declares a variable with the same projection as the preceeding cursor.

cursor get_emp_dets is
  select emp.empno
         , emp.ename
         , emp.sal
         , dept.dname
  from   emp
  inner join dept on dept,deptno = emp.deptno;        
l_emp_dets get_emp_dets%rowtype;

Using these attributes is considered good practice. PL/SQL development standards will often mandate their use. They deliver these benefits:

1. self-documenting code: if we see a variable with a definition which references emp.sal%type we can be reasonably confident this variable will be used to store data from the SALARY column of the EMP table.
2. datatype conformance: if we change the scale or precision of the the SALARY column of the EMP table all variables which use the %type attribute will pick up the change automatically. If we add a new column to the EMP table, all variables defined with the %rowtype attribute will be able to handle that column without us needing to change those programs.

That last point comes with an amber warning: the automatic conformance only works when the %rowtype variable is populated by SELECT * FROM queries. If we are using an explicit projection with named columns then we have now broken our code and we need to fix it. More generally, this silent propagation of changes to our data structures means we need to pay more attention to impact analysis. Is it right that we can just change a column's datatype or amend a table's projection without changing the code which depends on them? Maybe it's okay, maybe not. By shielding us from the immediate impact of broken code, using these attributes also withholds the necessity to revisit our programs: so we have to remember to do it.

Overall I think the benefits listed above outweigh the risks, and I think we should always use these attributes whenever it is appropriate, for the definition of local variables and constants. However, complications arise if we use them to declare PL/SQL program parameters, specifically for procedures in package specs and standalone program units. It's not so bad if we're writing an internal API but it becomes a proper headache when we are dealing with a public API, one which will be called by programs owned by another user, one whose developers are in another team or outside our organisation, or even using Java, dotNet or whatever. So why is the use of these attributes so bad for those people?

1. obfuscated code: these attributes are only self-documenting when we have a familiarity with the underlying schema, or have easy access to it. This will frequently not be the case for developers in other teams (or outside the organisation) who need to call our API. They may be able to guess at the datatype of SALARY or HIREDATE, but they really shouldn't have to. And, of course, a reference to emp%rowtype is about as unhelpful as it could be. Particularly when we consider ...
2. loss of encapsulation: one purpose of an API is to shield consumers of our application from the gnarly details of its implementation. However, the use of %type and %rowtype is actually exposing those details. Furthermore, a calling program cannot define their own variables using these attributes unless we grant them SELECT on the tables. Otherwise the declaration will hurl PLS-00201. This is particularly problematic for handling %rowtype, because we need to define a record variable which matches the row structure.
3. breaking the contract: an interface is an agreement between the provider and the calling program. The API defines input criteria and in return guarantees outcomes. It forms a contract, which allows the consumer to write code against stable definitions. Automatically propagating changes in the underlying data structures to parameter definitions creates unstable dependencies. It is not simply that the use of %type and %rowtype attributes will cause the interface to change automatically, the issue is that there is no mechanism for signalling the change to an API's consumers. Interfaces demand stable dependencies: we must manage any changes to our schema in a way which ideally allows the consumers to continue to use the interface without needing to change their code, but at the very least tells them that the interface has changed.

Defining parameters for public APIs

The simplest solution is to use PL/SQL datatypes in procedural signatures. These seem straightforward. Anybody can look at this function and understand that input parameter is numeric and the returned value is a string.

function get_dept_manager (p_deptno in number) return varchar2;

So clear but not safe. How long is the returned string? The calling program needs to know, so it can define an appropriately sized variable to receive it. Likewise, in this call, how long is can a message be?

procedure log_message (p_text in varchar2);

Notoriously we cannot specify length, scale or precision for PL/SQL parameters. But the calling code and the called code will write values to concretely defined types. The interface needs to communicate those definitions. Fortunately PL/SQL offers a solution: subtypes. Here we have a substype which explicitly defines the datatype to be used for passing messages:

subtype st_message_text is varchar2(256);

procedure log_message (p_text in st_message_text);

Now the calling program knows the maximum permitted length of a message and can trim its value accordingly. (Incidentally, the parameter is still not constrained in the called program so we can pass a larger value to the log_message() procedure: the declared length is only enforced when we assign the parameter to something concrete such as a local variable.)

We can replace %rowtype definitions with explicit RECORD defintions. So a function which retrieves the employee records for a department will look something like this:

subtype st_deptno is number(2,0);

type r_emp is record(
  empno          number(4,0),
  ename          varchar2(10),
  job            varchar2(9),
  mgr            number(4,0),
  hiredate       date
  sal            number(7,2),
  comm           number(7,2),
  deptno         st_deptno
);
  
type t_emp is table of r_emp;  

function get_dept_employees (p_deptno in st_deptno) return t_emp;

We do this for all our public functions.

subtype st_manager_name is varchar2(30);

function get_dept_manager (p_deptno in st_deptno) return st_manager_name;

Now the API clearly documents the datatypes which calling programs need to pass and which they will receive as output. Crucially, this approach offers stability: the datatype of a parameter cannot be changed invisibly, as any change must be implemented in a new version of the publicly available package specification. Inevitably this imposes a brake on our ability to change the API but we ought not to be changing public APIs frequently. Any such change should arise from either new knowledge about the requirements or a bug in the data model. Wherever possible we should try to handle bugs internally within the schema. But if we have to alter the signature of a procedure we need to communicate the change to our consumers as far ahead of time as possible. Ideally we should shield them from the need to change their code at all. One way to achieve that is Edition-Based Redefinition. Other ways would be to deploy the change with overloaded procedures or even using a different procedure name, and deprecate the old procedure. Occasionally we might have no choice but to apply the change and break the API: sometimes with public interfaces the best we can do is try to annoy the fewest number of people.

Transitioning from a private to a public interface

There is a difference between internal and public packages. When we have procedures which are intended for internal usage (i.e. only called by other programs in the same schema) we can define their parameters with %type and %rowtype attributes. We have access and - it is to be hoped! - familiarity with the schema's objects, so the datatype anchoring supports safer coding. But what happens when we have a package which we wrote as an internal package but now we need to expose its functionality to a wider audience? Should we re-write the spec to use subtypes instead?

No. The correct thing to do is to write a wrapper package which acts as a facade over the internal one, and grant EXECUTE privileges on the wrapper. The wrapper package will obviously have the requisite subtype definitions in the spec, and procedures declared with those subtypes. The package body will likely consist of nothing more than those procedures, which simply call their equivalents in the internal package. There may be some affordances for translating data structures, such as populating a table %rowtype variable from the public record type, but those will usually be necessary only for the purposes of documentation (this publicly defined subtype maps to this internally defined table column). There is an obvious overhead to writing another package, especially one which is really just a pass-through to the real functionality, but there are clear benefits which justify the overhead:

• Stability. Not re-writing an existing package is always a good thing. Even if we are mechanically just replacing one set of datatype definitions with a different set which have the same characteristics we are still changing the core system, and that's a chunk of regression testing we've just added to the task.
• Least privilege escalation. Even if the internal package has been written with a firm eye on the SOLID principles, the chances are it contains more functionality than we need to expose to other consumers. Writing a wrapper package gives us the opportunity to grant access to only the required procedures.
• Composition. It is also likely that the internal package doesn't have the exact procedure the other team needs. Perhaps there are actually two procedures they need to call, or there's one procedure but it has some confusing internal flags in its signature. Instead of violating the Law of Demeter we can define one simple procedure in the wrapper package spec and handle the internal complexity in the body.
• Future proofing. Writing a wrapper package gives us an affordance where we can handle subsequent changes in the internal data model or functionality without affecting other consumers. By definition a violation of YAGNI, but as it's not the main reason why we're doing this I'm allowing this as a benefit.

Design is always a trade off

The use of these attributes is an example of the nuances which Coding Standards often lack. In many situations their use is good practice, and we should employ them in those cases. But we also need to know when their use is a bad practice, and why, so we can do something better instead.

Part of the Designing PL/SQL Programs series

How PL/SQL Development Standards work

I have been gigging at a place which has documented PL/SQL Development Standards. This is not so unusual: most Oracle shops have such a document. What makes it unusual is that they enforce the standards. With code reviews. And I mean properly enforce: programs fail QA for egregious breaches of the standards or a sufficient accumulation of minor breaches. This is less common than it ought to be.

Many coders are sceptical about development standards; I have been in the past. Standards generally focus on things which are easy to standardise (indentation, case, naming conventions) rather functional correctness or design principles. They frequently codify arbitrary or outdated practices (mandating explicit cursors is a particular bugbear of mine). They either go into so much detail that they are unreadably long (and dull) or are so sketchy that they operate as easy-to-ignore guidelines. But I think many experienced developers' objections boil down to: I don't like being told how to write my code; my style is the best style; my code is clean, clear and readable.

The catch is, readability is not simply a function of personal style: it emerges from consistency across the entire codebase. Just because I find my personal coding style clear doesn't mean everbody else will. At the very least a colleague reading my program will have to invest time in understanding how I name my variables, how I use table aliases, and a dozen other things, none of them important individually but all together adding friction to the crucial task of understanding how a program works (or does not work).

This particular set of standards certainly had a lot to say about layout. Many strictures fitted with my natural coding style (all lower case, one column per line in a SQL projection, comma before the column name rather than after it). Others were rather tiresome: the rules for clause alignment entail a lot of spacing and backspacing to ensure elements line up. There are a few strictures I actively disagree with (notably mandatory use of SQL-89 syntax i.e. impicit joins). But here's the rub: I didn't get to pick and choose which of the standards I followed. I just had to knuckle down and follow them all. Because the discipline of the code review meant my programs failed QA when I hadn't applied the standards.

There's more to consistency than just layout and naming conventions. There's also functional consistency: use of SQL and PL/SQL idioms, how to organise programs within a package, and so forth. Too many things to cover in a single document. But again, code reviews enforce standardisation of these aspects, by applying undocumented conventions with the same rigour as documented standards. A couple of times I tripped over such an undocumented convention and it didn't feel fair: my code failed the review because I wrote something which was wrong even though not explicitly covered by the standards. One of these times it was something awry in the layout. "That's wrong", the reviewer said. It was a difference I hadn't even noticed, and probably you wouldn't have noticed either, and even if I had have noticed it I wouldn't have thought it was wrong. But it was different from what everybody else was doing. That made it wrong.

Everybody undertakes code reviews and everybody's code is reviewed. Thus code reviews shape the codebase, by enforcing documented standards and undocumented conventions. As a result this is the most readable codebase I have ever worked on. It's almost impossible to tell who wrote any given program, because all programs look the same. It's easy to reason about a piece of code because it follows rigorous naming conventions and consistent architectural principles. The code is habitable. A colleague can read a program I wrote and feel comfortable doing so. The layout, the naming conventions, the consistent selection of one approach in situations where PL/SQL offers more than one way of doing something, all these factors mean my program looks just like the program anybody else would have written. So the reader is freer to understand what the program actually does and how it works. Standardisation reduces friction.

It is a virtuous circle. Code reviews enforce a consistent programming style, which eliminates trivial (i.e. non-functional) differences in the program. In turn this makes the program easier to review: all the programs look basically the same which highlights the things which need to be different, the business logic and the data structures.

Readability is a feature

Readility is a feature. It's a feature our code must have. We all know readability makes code easier to maintain, easier to re-use, easier to debug. Yet still many developers bridle at the suggestion that their PL/SQL must look like everybody else's PL/SQL. I get this. It's not that I think the way I write PL/SQL is intrinsically correct, it just looks the way I have evolved to write it over the years. A new set of coding standards, rigorously applied, disrupts my flow. I must slow down to correct the variable names or fix the layout. It's tedious.

Tedious but also necessary. A sofware system is a shared enterprise. It's not "my" code, it's the project's code; I am just the person checking it into source control. As a discipline, programming is a craft not an art. PL/SQL is simply a device for turning data into business value. It's more important that other people on the team can work with our code than that it has our signature style. So let's not be precious about appearance. We must follow the rules, and save our self-expression for our poems and our tweets.

Above all, know this: there are no development standards without code reviews.

The Single Responsibility principle

The Single Responsibility principle is the foundation of modular programming, and is probably the most important principle in the SOLID set. Many of the other principles flow from it.

It is quite simple: a program unit should do only one thing. A procedure should implement a single task; a package should gather together procedures which solve a set of related tasks. Consider the Oracle library package UTL_FILE. Its responsibility is quite clear: it is for working with external files. It implements all the operations necessary to work with OS files: opening them, closing them, reading and writing, etc. It defines a bespoke suite of exceptions too.

Each procedure in the package has a clear responsibility too. For instance, fclose() closes a single referenced file whereas fclose_all() closes all open files. Now, the package designers could have implemented that functionality as a single procedure, with different behaviours depending on whether the file parameter was populated or unpopulated. This might seem a simpler implementation, because it would be one fewer procedure. But the interface has actually become more complicated: essentially we have a flag parameter, which means we need to know a little bit more about the internal processing of fclose(). It would have made the package just a little bit harder to work with without saving any actual code.

Of course, it's pretty easy to define the Single Responsibility of a low level feature like file handling. We might think there are some superficially similarities with displaying information to the screen but it's fairly obvious that these are unrelated and so we need tow packages, UTL_FILE and DBMS_OUTPUT. When it comes to our own code, especially higher level packages, it can be harder to define the boundaries. At the broadest level we can define domains - SALES, HR, etc. But we need more than one package per domain: how do we decide the responsibilities of indvidual pacakages?

Robert C Martin defines the Single Responsibility principle as: "A class should have only one reason to change." Reasons for change can be many and various. In database applications dependence on tables is a primary one. So procedures which work a common set of table may well belong together. But there are at least two sets of privileges for data: reading and manipulating. So it's likely we will need a package which gathers together reporting type queries which can be granted to read-only users and a package which executes DML statements which can be granted to more privileged users. Maybe our domain requires special processing, such as handling sensitive data; procedures for implementing that business logic will belong in separate packages.

Single responsibility becomes a matrix, with dependencies along one access and audience of users along another.

The advantages of Singel Responsibility should be obvious. It allows us to define a cohesive package, collecting together all the related functionality which makes it easy for others reuse it. It also allows us to define private routines in a package body, which reduces the amount of code we have to maintain while giving us a mechanism for preventing other developers from using it. Restricting the features to a single responsibility means unrelated functions are not coupled together. This gives a better granularity for granting the least privileges necessary to users of our code.

Part of the Designing PL/SQL Programs series

Data Access Layer vs Table APIs

One of the underlying benefits of PL/SQL APIs is the enabling of data governance. Table owners can shield their tables behind a layer of PL/SQL. Other users have no access to the tables directly but only through stored procedures. This confers many benefits:

• Calling programs code against a programmatic interface. This frees the table owner to change the table's structure whenever it's necessary without affecting its consumers.
• Likewise the calling programs get access to the data they need without having to know the details of the table structure, such as technical keys.
• The table owner can use code to enforce complicated business rules when data is changed.
• The table owner can enforce sophisticated data access policies (especially for applications using Standard Edition without DBMS_RLS).

So naturally the question arises, is this the same as Table APIs?

Table APIs used to be a popular approach to encapsulating tables. The typical Table API comprised two packages per table; one package provided methods for inserting, updating and deleting records, and the other package provided query methods. The big attraction of Table APIs was that they could be 100% generated from the data dictionary - both Oracle Designer and Steven Feuerstein's QNXO library provided TAPI generators. And they felt like good practice because, y'know, access to the tables was shielded by a PL/SQL layer.

But there are several problems with Table APIs.

The first is that they entrench row-by-agonising-row processing. Table APIs have their roots in early versions of Oracle so the DML methods only worked with a single record. Even after Oracle 8 introduced PL/SQL collection types TAPI code in the wild tended to be RBAR: there seems to something in the brain of the average programmer which predisposes them to prefer loops executing procedural code rather than set operations.

The second is that they prevent SQL joins. Individual records have to be selected from one table to provide keys for looking up records in a second table. Quite often this leads to loops within loops. So-called PL/SQL joins prevent the optimizer from choosing good access paths when handling larger amounts of data.

The third issue is that it is pretty hard to generate methods for all conceivable access paths. Consequently the generated packages had a few standard access paths (primary key, indexed columns) and provided an dynamic SQL method which accepted a free text WHERE clause. Besides opening the package to SQL injection this also broke the Law of Demeter: in order to pass a dynamic WHERE clause the calling program needed to know the structure of the underlying table, which defeats the whole objective of encapsulation.

Which leads on to the fourth, more philosophical problem with Table APIs: there is minimal abstraction. Each package is generated so it fits very closely to the structure of the Table. If the table structure changes we have to regenerate the TAPI packages: the fact that this can be done automatically is scant recompense for the tight coupling between the Table and the API.

So although Table APIs could be mistaken for good practice in actuality they provide no real benefit. The interface is 1:1 with the table structure so it has no advantage over granting privileges on the table. Combined with the impact of RBAR processing and PL/SQL joins on performance and the net effect of Table APIs is disastrous.

We cannot generate good Data Access APIs: we need to write them. This is because the APIs should be built around business functions rather than tables. The API packages granted to other users should comprise procedures for executing transactions. A Unit Of Work is likely to touch more than one table. These have to be written by domain experts who understand the data model and the business rules.

Part of the Designing PL/SQL Programs series

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

Utilities - the Coincidental Cohesion anti-pattern

One way to understand the importance of cohesion is to examine an example of a non-cohesive package, one exhibiting a random level of cohesion. The poster child for Coincidental Cohesion is the utility or helper package. Most applications will have one or more of these, and Oracle's PL/SQL library is no exception. DBMS_UTILITY has 37 distinct procedures and functions (i.e. not counting overloaded signatures) in 11gR2 and 38 in 12cR1 (and R2). Does DBMS_UTILITY deliver any of the benefits the PL/SQL Reference says packages deliver?

Easier Application Design?

One of the characteristics of utilities packages is that they aren't designed in advance. They are the place where functionality ends up because there is no apparently better place for it. Utilities occur when we are working on some other piece of application code; we discover a gap in the available functionality such as hashing a string. When this happens we generally need the functionality now: there's little benefit to deferring the implementation until later. So we write a GET_HASH_VALUE() function,x stick it in our utilities package and proceed with the task at hand.

The benefit of this approach is we keep our focus on the main job, delivering business functionality. The problem is, we never go back and re-evaluate the utilities. Indeed, now there is business functionality which depends on them: refactoring utilities introduces risk. Thus the size of the utilities package slowing increases, one tactical implementation at a time.

Hidden Implementation Details?

Another characteristic of utility functions is that they tend not to share concrete implementations. Often a utilities package beyond a certain size will have groups of procedures with related functionality. It seems probable that DBMS_UTILITY.ANALYZE_DATABASE(), DBMS_UTILITY.ANALYZE_PART_OBJECT() and DBMS_UTILITY.ANALYZE_SCHEMA() share some code. So there are benefits to co-locating them in the same package. But it is unlikely that CANONICALIZE() , CREATE_ALTER_TYPE_ERROR_TABLE() and GET_CPU_TIME() have much code in common.

Added Functionality?

Utility functions are rarely part of a specific business process. They are usually called on a one-off basis rather than being chained together. So there is no state to be maintained across different function calls.

Better Performance?

For the same reason there is no performance benefit from a utilities package. Quite the opposite. When there is no relationship between the functions we cannot make predictions about usage. We are not likely to call EXPAND_SQL_TEXT() right after calling PORT_STRING(). So there is no benefit in loading the former into memory when we call the latter. In fact the performance of EXPAND_SQL_TEXT() is impaired because we have to load the whole DBMS_UTILITY package into the shared pool, plus it uses up a larger chunk of memory until it gets aged out. Although to be fair, in these days of abundant RAM, some unused code in the library cache need not be our greatest concern. But whichever way we bounce it, it's not a boon.

Grants?

Privileges on utility packages is a neutral concern. Often utilities won't be used outside the owning schema. In cases where we do need to make them more widely available we're probably granting access on some procedures that the grantee will never use.

Modularity?

From an architectural perspective, modularity is the prime benefit of cohesion. A well-designed library should be frictionless and painless to navigate. The problem with random assemblages like DBMS_UTILITY is that it's not obvious what functions it may contain. Sometimes we write a piece of code we didn't need to.

The costs of utility packages

Perhaps your PL/SQL code base has a procedure like this:

create or replace procedure run_ddl
  ( p_stmt in varchar2)
is
  pragma autonomous_transaction;
  v_cursor number := dbms_sql.open_cursor;
  n pls_integer;
begin
  dbms_sql.parse(v_cursor, p_stmt, dbms_sql.native);
  n := dbms_sql.execute(v_cursor);
  dbms_sql.close_cursor(v_cursor);
exception
  when others then
    if dbms_sql.is_open(v_cursor) then
      dbms_sql.close_cursor(v_cursor);
    end if;
    raise;
end run_ddl;
/

It is a nice piece of code for executing DDL statements. The autonomous_transaction pragma prevents the execution of arbitrary DML statements (by throwing ORA-06519), so it's quite safe. The only problem is, it re-implements DBMS_UTILITY.EXEC_DDL_STATEMENT().

Code duplication like this is a common side effect of utility packages. Discovery is hard because their program units are clumped together accidentally. Nobody sets out to deliberately re-write DBMS_UTILITY.EXEC_DDL_STATEMENT(), it happens because not enough people know to look in that package before they start coding a helper function. Redundant code is a nasty cost of Coincidental Cohesion. Besides the initial wasted effort of writing an unnecessary program there are the incurred costs of maintaining it, testing it, the risk of introducing bugs or security holes. Plus each additional duplicated program makes our code base a little harder to navigate.

Fortunately there are tactics for avoiding or dealing with this. Find out more.

Part of the Designing PL/SQL Programs series

The importance of cohesion

"Come on, come on, let's stick together" - Bryan Ferry

There's more to PL/SQL programs than packages, but most of our code will live in packages. The PL/SQL Reference offers the following benefits of organising our code into packages:

Modularity - we encapsulate logically related components into an easy to understand structure.

Easier Application Design - we can start with the interface in the package specification and code the implementation later.

Hidden Implementation Details - the package body is private so we can prevent application users having direct access to certain functionality.

Added Functionality - we can share the state of Package public variables and cursors for the life of a session.

Better Performance - Oracle Database loads the whole package into memory the first time you invoke a package subprogram, which makes subsequent invocations of any other subprogram quicker. Also packages prevent cascading dependencies and unnecessary recompilation.

Grants - we can grant permission on a single package instead of a whole bunch of objects.

However, we can only realise these benefits if the packaged components belong together: in other words, if our package is cohesive.  

The ever reliable Wikipedia defines cohesion like this: "the degree to which the elements of a module belong together"; in other words how it's a measure of the strength of the relationship between components. It's common to think of cohesion as a binary state - either a package is cohesive or it isn't - but actually it's a spectrum. (Perhaps computer science should use  "cohesiveness" which is more expressi but cohesion it is.)

Cohesion

Cohesion owes its origin as a Comp Sci term to Stevens, Myers, and Constantine.  Back in the Seventies they used the terms "module" and "processing elements", but we're discussing PL/SQL so let's use Package and Procedure instead. They defined seven levels of cohesion, with each level being better - more usefully cohesive - than its predecessor.

Coincidental

The package comprises an arbitrary selection of procedures and functions which are not related in any way. This obviously seems like a daft thing to do, but most packages with "Utility" in their name fall into this category.

Logical

The package contains procedures which all belong to the same logical class of functions. For instance, we might have a package to collect all the procedures which act as endpoints for REST Data Services.

Temporal

The package consists of procedures which are executed at the same system event. So we might have a package of procedures executed when a user logs on - authentication, auditing, session initialisation - and similar package for tidying up when the user logs off. Other than the triggering event the packaged functions are unrelated to each other.

Procedural

The package consists of procedures which are executed as part of the same business event. For instance, in an auction application there are a set of actions to follow whenever a bid is made: compare to asking price, evaluate against existing maximum bid, update lot's status, update bidder's history, send an email to the bidder, send an email to the user who's been outbid, etc.

Communicational

The package contains procedures which share common inputs or outputs. For example a payroll package may have procedures to calculate base salary, overtime, sick pay, commission, bonuses and produce the overall remuneration for an employee.

Sequential

The package comprises procedures which are executed as a chain, so that the output of one procedure becomes the input for another procedure. A classic example of this is an ETL package with procedures for loading data into a staging area, validating and transforming the data, and then loading records into the target table(s).

Functional

The package comprises procedures which are focused on a single task. Not only are all the procedures strongly related to each other but they are fitted to user roles too. So procedures for power users are in a separate package from procedures for normal users. The Oracle built-in packages for Advanced Queuing are a good model of Functional cohesion.

How cohesive is cohesive enough?

The grades of cohesion, with Coincidental as the worst and Functional as the best, are guidelines. Not every package needs to have Functional cohesion. In a software architecture we will have modules at different levels. The higher modules will tend to be composed of calls to lower level modules. The low level modules are the concrete implementations and they should aspire to Sequential or Functional cohesion.

The higher level modules can be organised to other levels. For instance we might want to build packages around user roles - Sales, Production, HR, IT - because Procedural cohesion makes it easier for the UI teams to develop screens, especially if they need to skin them for various different technologies (desktop, web, mobile). Likewise we wouldn't want to have Temporally cohesive packages with concrete code for managing user logon or logoff. But there is a value in organising a package which bundles up all the low level calls into a single abstract call for use in schema level AFTER LOGON triggers.    

Cohesion is not an easily evaluated condition. We need cohesion with a purpose, a reason to stick those procedures together. It's not enough to say "this package is cohesive". We must take into consideration how cohesive the package needs to be: how will it be used? what is its relationships with the other packages?

Applying design principles such as Single Responsibility, Common Reuse, Common Closure and Interface Segregation can help us to build cohesive packages. Getting the balance right requires an understanding of the purpose of the package and its place within the overall software architecture.  

Part of the Designing PL/SQL Programs series

Working with the Interface Segregation Principle

Obviously Interface Segregation is crucial for implementing restricted access. For any given set of data there are three broad categories of access:

• reporting 
• manipulation 
• administration and governance 

So we need to define at least one interface - packages - for each category in order that we can grant the appropriate access to different groups of users: read-only users, regular users, power users.

But there's more to Interface Segregation. This example is based on a procedure posted on a programming forum. Its purpose is to maintain medical records relating to a patient's drug treatments. The procedure has some business logic (which I've redacted) but its overall structure is defined by the split between the Verification task and the De-verification task, and flow is controlled by the value of the p_verify_mode parameter.

 
procedure rx_verification
     (p_drh_id in number,
       p_patient_name in varchar2,
       p_verify_mode in varchar2)
as
    new_rxh_id number;
    rxh_count number;
    rxl_count number;
    drh_rec drug_admin_history%rowtype;
begin
    select * into drh_rec ....;
    select count(*) into rxh_count ....;

    if p_verify_mode = 'VERIFY' then

        update drug_admin_history ....;
        if drh_rec.pp_id <> 0 then
            update patient_prescription ....;
        end if;
        if rxh_count = 0 then
            insert into prescription_header ....;
        else
            select rxh_id into new_rxh_id ....;
        end if;
        insert into prescription_line ....;
        if drh_rec.threshhold > 0
            insert into prescription_line ....;
        end if;

    elsif p_verify_mode = 'DEVERIFY' then

        update drug_admin_history ....;
        if drh_rec.pp_id <> 0 then
            update patient_prescription ....;
        end if;
        select rxl_rxh_id into new_rxh_id ....;
        delete prescription_line ....;
        delete prescription_header ....;

    end if;
end;

Does this procedure have a Single Responsibility? Hmmm. It conforms to Common Reuse - users who can verify can also de-verify. It doesn't break Common Closure, because both tasks work with the same tables. But there is a nagging doubt. It appears to be doing two things: Verification and De-verification.

So, how does this does this procedure work as an interface? There is a definite problem when it comes to calling the procedure: how do I as a developer know what value to pass to p_verify_mode?

  rx_management.rx_verification
     (p_drh_id => 1234,
       p_patient_name => 'John Yaya',
       p_verify_mode => ???);

The only way to know is to inspect the source code of the procedure. That breaks the Information Hiding principle, and it might not be viable (if the procedure is owned by a different schema). Clearly the interface could benefit from a redesign. One approach would be to declare constants for the acceptable values; while we're at it, why not define a PL/SQL subtype for verification mode and tweak the procedure's signature to make it clear that's what's expected:

create or replace package rx_management is
 
  subtype verification_mode_subt is varchar2(10);
  c_verify constant verification_mode_subt := 'VERIFY'; 
  c_deverify constant verification_mode_subt := 'DEVERIFY'; 
 
  procedure rx_verification
     (p_drh_id in number,
       p_patient_name in varchar2,
       p_verify_mode in verification_mode_subt);

end rx_management;

Nevertheless it is still possible for a caller program to pass a wrong value:

  rx_management.rx_verification
     (p_drh_id => 1234,
       p_patient_name => 'John Yaya',
       p_verify_mode => 'Verify');

What happens then? Literally nothing. The value drops through the control structure without satisfying any condition. It's an unsatisfactory outcome. We could change the implementation of rx_verification() to validate the parameter value and raise and exception. Or we could add an ELSE branch and raise an exception. But those are runtime exceptions. It would be better to mistake-proof the interface so that it is not possible to pass an invalid value in the first place.

Which leads us to to a Segregated Interface :

create or replace package rx_management is
 
  procedure rx_verification
     (p_drh_id in number,
       p_patient_name in varchar2);
 
  procedure rx_deverification
     (p_drh_id in number);
     
end rx_management;

Suddenly it becomes clear that the original procedure was poorly named (I call rx_verification() to issue an RX de-verification?!) We have two procedures but their usage is now straightforward and the signatures are cleaner (the p_patient_name is only used in the Verification branch so there's no need to pass it when issuing a De-verification).

Summary

Interface Segregation creates simpler and safer controls but more of them. This is a general effect of the Information Hiding principle. It is a trade-off. We need to be sensible. Also, this is not a proscription against flags. There will always be times when we need to pass instructions to called procedures to modify their behaviour. In those cases it is important that the interface includes a definition of acceptable values.

Part of the Designing PL/SQL Programs series

Three more principles

Here are some more principles which can help us design better programs. These principles aren't part of an organized theory, and they're aren't particularly related to any programming paradigm. But each is part of the canon, and each is about the relationship between a program's interface and its implementation.

The Principle Of Least Astonishment

Also known as the Principle of Least Surprise, the rule is simple: programs should do what we expect them to do. This is more than simply honouring the contract of the interface. It means complying with accepted conventions of our programming. In PL/SQL programming there is a convention that functions are read-only, or at least do not change database state. Another such convention is that low-level routines do not execute COMMIT statements; transaction management is the prerogative of the program at the top of the call stack, which may be interacting directly with a user or may be an autonomous batch process.

Perhaps the most common flouting of the Principle Of Least Astonishment is this:

   exception
      when others then
      null; 

It is reasonable to expect that a program will hurl an exception if something as gone awry. Unfortunately, we are not as astonished as we should be when we find a procedure with an exception handle which swallows any and every exception.

Information Hiding Principle 

Another venerable principle, this one was expounded by David Parnas in 1972. It requires that a calling program should not need to know anything about the implementation of a called program. The definition of the interface should be sufficient. It is the cornerstone of black-box programming. The virtue of Information Hiding is that knowledge of internal details inevitably leads to coupling between the called and calling routines: when we change the called program we need to change the caller too. We honour this principle any time we call a procedure in a package owned by another schema, because the EXECUTE privilege grants visibility of the package specification (the interface) but not the body (the implementation).

The Law Of Leaky Abstractions

Joel Spolsky coined this one: "All non-trivial abstractions, to some degree, are leaky." No matter how hard we try, some details of the implementation of a called program will be exposed to the calling programming, and will need to be acknowledged. Let's consider this interface again:

    function get_employee_recs
        ( p_deptno in number ) 
        return emp_refcursor;

We know it returns a result set of employee records. But in what order? Sorting by EMPNO would be pretty useless, given that it is a surrogate key (and hence without meaning). Other candidates - HIREDATE, SAL - will be helpful for some cases and irrelevant for others. One approach is to always return an unsorted set and leave it to the caller to sort the results; but it is usually more efficient to sort records in a query rather than a collection. Another approach would be to write several functions - get_employee_recs_sorted_hiredate(), get_employee_recs_sorted_sal() - but that leads to a bloated interface which is hard to understand. Tricky.

Conclusion

Principles are guidelines. There are tensions between them. Good design is a matter of trade-offs. We cannot blindly follow Information Hiding and ignore the Leaky Abstractions. We need to exercise our professional judgement (which is a good thing).

Part of the Designing PL/SQL Programs series

It's all about the interface

When we talk about program design we're mainly talking about interface design. The interface is the part of our program that the users interact with. Normally discussion of UI focuses on GUI or UX, that is, the interface with the end user of our application.

But developers are users too.

Another developer writing a program which calls a routine in my program is a user of my code (and, I must remember, six months after I last touched the program, I am that other developer). A well-designed interface is frictionless: it can be slotted into a calling program without too much effort. A poor interface breaks the flow: it takes time and thought to figure it out. In the worst case we have to scramble around in the documentation or the source code.

Formally, an interface is the mechanism which allows the environment (the user or agent) to interact with the system (the program). What the system actually does is the implementation: the interface provides access to the implementation without the environment needing to understand the details. In PL/SQL programs the implementation will usually contain a hefty chunk of SQL. The interface mediates access to data.

An interface is a contract. It specifies what the caller must do and what the called program will do in return. Take this example:

function get_employee_recs
     ( p_deptno in number )
     return emp_refcursor;

The contract says, if the calling program passes a valid DEPTNO the function will return records for all the employees in that department, as a strongly-typed ref cursor. Unfortunately the contract doesn't say what will happen if the calling program passes an invalid DEPTNO. Does the function return an empty set or throw an exception? The short answer is we can't tell. We must rely on convention or the document, which is an unfortunate gap in the PL/SQL language; the Java keyword throws is quite neat in this respect.

The interface is here to help

The interface presents an implementation of business logic. The interface is a curated interpretation, and doesn't enable unfettered access. Rather, a well-designed interface helps a developer use the business logic in a sensible fashion. Dan Lockton calls this Design With Intent: Good design expresses how a product should be used. It doesn't have to be complicated. We can use simple control mechanisms which to help other developers use our code properly.

Restriction of access

Simply, the interface restricts access to certain functions or denies it altogether. Only certain users are allowed to view salaries, and even fewer to modify them. The interface to Employee records should separate salary functions from more widely-available functions. Access restriction can be implemented in a hard fashion, using architectural constructs (views, packages, schemas) or in a soft fashion (using VPD or Data Vault). The hard approach benefits from clarity, the soft approach offers flexibility.

Forcing functions

If certain things must be done in a specific order then the interface should only offer a method which enforces the correct order. For instance, if we need to insert records into a parent table and a child table in the same transaction (perhaps a super-type/sub-type implementation of a foreign key arc) a helpful interface will only expose a procedure which inserts both records in the correct order.

Mistake-proofing

A well-design interface prevents its users from making obvious mistakes. The signature of a procedure should be clear and unambiguous. Naming is important. If a parameter presents a table attribute the parameter name should echo the column name: p_empno is better than p_id. Default values for parameters should lead developers to sensible and safe choices. If several parameters have default values they must play nicely together: accepting all the defaults should not generate an error condition.

Abstraction

Abstraction is just another word for interface. It allows us to focus on the details of our own code without need to understand the concrete details of the other code we depend upon. That's why good interfaces are the key to managing large codebases.

Part of the Designing PL/SQL Programs series

Designing PL/SQL Programs: Series home page

Designing PL/SQL Programs is a succession of articles published the articles in a nonlinear fashion. Eventually it will evolve into a coherent series. In the meantime this page serves as a map and navigation aid. I will add articles to it as and when I publish them.

Introduction

Designing PL/SQL Programs
It's all about the interface

Principles and Patterns

Introducing the SOLID principles
Introducing the RCCASS principles
Three more principles
The Single Responsibility principles
The Dependency Inversion Principle: a practical example
Working with the Interface Segregation Principle

Software Architecture

The importance of cohesion
Utilities - the Coincidental Cohesion anti-pattern
Avoiding Coincidental Cohesion

Interface design

Data Access Layer versus Table APIs The use and misuse of %TYPE and %ROWTYPE attributes in PL/SQL APIs

Tools and Techniques

The Dependency Inversion Principle: a practical example

These design principles may seem rather academic, so let's look at a real life demonstration of how applying Dependency Inversion Principle lead to an improved software design.

Here is a simplified version of an ETL framework which uses SQL Types in a similar fashion to the approach described in my blog post here. The loading process is defined using an abstract non-instantiable Type like this:

create or replace type load_t force as object
    ( txn_date date
      , tgt_name varchar2(30)
      , member function load return number
      , final member function get_tgt return varchar2
      )
not final not instantiable;
/

create or replace type body load_t as
    member function load return number
    is
    begin
        return 0;
    end load;
    final member function get_tgt return varchar2
    is
    begin
        return self.tgt_name;
    end get_tgt;
end;
/

The concrete behaviour for each target table in the ABC feed is defined by sub-types like this:

create or replace type load_tgt1_t under load_t
    ( overriding member function load return number
        , constructor function load_tgt1_t
            (self in out nocopy load_tgt1_t
             , txn_date date)
           return self as result
      )
;
/
create or replace type body load_tgt1_t as
    overriding member function load return number
    is
    begin
        insert into tgt1 (col1, col2)
        select to_number(col_a), col_b
        from stg_abc stg
        where stg.txn_date = self.txn_date;
        return sql%rowcount;
    end load;
    constructor function load_tgt1_t
            (self in out nocopy load_tgt1_t
             , txn_date date)
           return self as result
    is
    begin
        self.txn_date := txn_date;
        self.tgt_name := 'TGT1';
        return;
    end load_tgt1_t;
end;
/

This approach is neat because ETL is a fairly generic process: the mappings and behaviour for a particular target table are specific but the shape of the loading process is the same for any and all target tables. So we can build a generic PL/SQL procedure to handle them. This simplistic example does some logging, loops through a set of generic objects and, through the magic of polymorphism, calls a generic method which executes specific code for each target table:

    procedure load  
     (p_txn_date in date
        , p_load_set in sys_refcursor)
    is
        type loadset_r is record (
            tgtset load_t
            );
        lrecs loadset_r;
        load_count number;
    begin
        logger.logm('LOAD START::txn_date='||to_char(p_txn_date,'YYYY-MM-DD'));
        loop
            fetch p_load_set into lrecs;
            exit when p_load_set%notfound;
            logger.logm(lrecs.tgtset.get_tgt()||' start');
            load_count := lrecs.tgtset.load();
            logger.logm(lrecs.tgtset.get_tgt()||' loaded='||to_char(load_count));
        end loop;
        logger.logm('LOAD FINISH');
    end load;

So far, so abstract. The catch is the procedure which instantiates the objects:

    procedure load_abc_from_stg  
         (p_txn_date in date)
    is
        rc sys_refcursor;
    begin
        open rc for
            select load_tgt1_t(p_txn_date) from dual union all
            select load_tgt2_t(p_txn_date) from dual;
       load(p_txn_date, rc);
    end load_abc_from_stg;

On casual inspection it doesn't seem problematic but the call to the load() procedure gives the game away. Both procedures are in the same package:

create or replace package loader as
    procedure load 
     (p_txn_date in date
        , p_load_set in sys_refcursor);
    procedure load_abc_from_stg
         (p_txn_date in date);
end loader;
/

So the package mixes generic and concrete functionality. What makes this a problem? After all, it's all ETL so doesn't the package follow the Single Responsibility Principle? Well, up to a point. But if we want to add a new table to the ABC feed we need to update the LOADER package. Likewise if we want to add a new feed, DEF, we need to update the LOADER package. So it breaks the Stable Abstractions principle. It also creates dependency problems, because the abstract load() process has dependencies on higher level modules. We can't deploy the LOADER package without deploying objects for all the feeds.

Applying the Dependency Inversion Principle.

The solution is to extract the load_abc() procedure into a concrete package of its own. To make this work we need to improve the interface between the load() procedure and programs which call it. Both sides of the interface should depend on a shared abstraction.

The LOADER package is now properly generic:

create or replace package loader as
    type loadset_r is record (
            tgtset load_t
            );
    type loadset_rc is ref cursor return loadset_r;
    procedure load 
        (p_txn_date in date
          , p_load_set in loadset_rc)
         authid current_user
               ;
end loader;
/

The loadset_r type has moved into the package specification, and defines a strongly-typed ref cursor. The load() procedure uses the strongly-typed ref cursor.

Similarly the LOAD_ABC package is wholly concrete:

create or replace package loader_abc as
    procedure load_from_stg
            (p_txn_date in date);
end loader_abc;
/

create or replace package body loader_abc as
    procedure load_from_stg
            (p_txn_date in date)
    is
        rc loader.loadset_rc;
    begin
        open rc for
            select load_tgt1_t(p_txn_date) from dual union all
            select load_tgt2_t(p_txn_date) from dual;
       loader.load(p_txn_date, rc);
    end load_from_stg;
end loader_abc;
/

Both package bodies now depend on abstractions: the strongly-typed ref cursor in the LOADER specification and the LOADER_T SQL Type. These should change much less frequently than the tables in the feed or even the loading process itself. This is the Dependency Inversion Principle in action.

Separating generic and concrete functionality into separate packages produces a more stable application. Users of a feed package are shielded from changes in other feeds. The LOADER package relies on strongly-typed abstractions. Consequently we can code a new feed package which can call loader.load() without peeking into that procedure's implementation to see what it's expecting.

Part of the Designing PL/SQL Programs series

Introducing the RCCASS design principles

Rob C Martin actually defined eleven principles for OOP. The first five, the SOLID principles, relate to individual classes. The other six, the RCCASS principles, deal with the design of packages (in the C++ or Java sense, i.e. libraries). They are far less known than the first five. There are two reasons for this:

• Unlike "SOLID", "RCCASS" is awkward to say and doesn't form a neat mnemonic. 
• Programmers are far less interested in software architecture. 

Software architecture tends to be an alien concept in PL/SQL. Usually a codebase of packages simply accretes over the years, like a coral reef. Perhaps the RCCASS principles can help change that.

The RCCASS Principles

Reuse Release Equivalency Principle 

The Reuse Release Equivalency Principle states that the unit of release matches the unit of reuse, which is the parts of the program unit which are consumed by other programs. Basically the unit of release defines the scope of regression testing for consuming applications. It's an ill-mannered release which forces projects to undertake unnecessary regression testing. Cohesive program units allow consumers to do regression testing only for functionality they actually use. It's less of a problem for PL/SQL because (unlike C++ libraries of Java jars) the unit of release can have a very low level of granularity: individual packages or stored procedures.

Common Reuse Principle 

The Common Reuse principle supports the definition of cohesive program units. Functions which share a dependency belong together, because they are likely to be used together belong together. For instance, procedures which maintain the Employees table should be co-located in one package (or a group of related packages). They will share sub-routines, constants and exceptions. Packaging related procedures together makes the package easier to write and easier for calling programs to use.

Common Closure Principle

The Common Closure principle supports also the definition of cohesive program units. Functions which share a dependency belong together, because they have a common axis of change. Common Closure helps to minimise the number of program units affected by a change. For instance, programs which use the Employees table may need to change if the structure of the table changes. All the changes must be released together: table, PL/SQL, types, etc.

Acyclic Dependencies Principle 

Avoid cyclic dependencies between program units: if package A depends on package B then B must not have a dependency on B. Cyclic dependencies make application hard to use and harder to deploy. The dependency graph shows the order in which objects must be built. Designing a dependency graph upfront is futile, but we can keep to rough guidelines. Higher level packages implementing business rules tend to depend on generic routines which in turn tend to depend on low-level utilities. There should be no application logic in those lower-level routines. If SALES requires a special logging implementation then that should be handled in the SALES subsystem not in the standard logging package.

Stable Dependencies Principle 

Any change to the implementation of a program unit which is widely used will generate regression tests for all the programs which call it. At the most extreme, a change to a logging routine could affect all the other programs in our application. As with the Open/Closed Principle we need to fix bugs. But new features should be introduced by extension not modification. And refactoring of low-level dependencies must not done on a whim.

Stable Abstractions Principle

Abstractions are dependencies, especially when we're talking about PL/SQL. So this Principle is quite similar to Stable Dependencies Principle. The key difference is that this relates to the definition of interfaces rather than implementation. A change to the signature of a logging routine could require code changes to all the other programs in the application. Obviously this is even more inconvenient than enforced regression testing. Avoid changing the signature of a public procedure or the projection of a public view. Again, extension rather than modification is the preferred approach.

Applicability of RCCASS principles in PL/SQL 

The focus of these principles is the stability of a shared codebase, and minimising the impact of change on the consumers of our code. This is vital in large projects, where communication between teams is often convoluted. It is even more important for open source or proprietary libraries.

We we can apply Common Reuse Principle and Common Closure Principle to define the scope of the Reuse Release Equivalency Principle, and hence define the boundaries of a sub-system (whisper it, schema). Likewise we can apply the Stable Dependencies Principle and Stable Abstractions Principle to enforce the Acyclic Dependencies Principle to build stables PL/SQL libraries. So the RCCASS principles offer some most useful pointers towards a stable PL/SQL software architecture.

Part of the Designing PL/SQL Programs series

Introducing the SOLID design principles

PL/SQL programming standards tend to focus on layout (case of keywords, indentation, etc), naming conventions, and implementation details (such as use of cursors).  These are all important things, but they don't address questions of design. How easy is it to use the written code?  How easy is it to test? How easy will it be to maintain? Is it robust? Is it secure?

Simply put, there are no agreed design principles for PL/SQL. So it's hard to define what makes a well-designed PL/SQL program.

The SOLID principles

It's different for object-oriented programming. OOP has more design principles and paradigms and patterns than you can shake a stick at. Perhaps the most well-known are the SOLID principles, which were first mooted by Robert C. Martin, AKA Uncle Bob, back in 1995 (although it was Michael Feathers who coined the acronym).

Although Martin put these principles together for Object-Oriented code, they draw on a broader spectrum of programming practice. So they are transferable, or at least translatable, to the other forms of modular programming. For instance, PL/SQL.

Single Responsibility Principle

This is the foundation stone of modular programming: a program unit should do only one thing. Modules which do only one thing are easier to understand, easier to test and generally more versatile. Higher level procedures can be composed of lower level ones. Sometimes it can be hard to define what "one thing" means in a given context, but some of the other principles provide clarity. Martin's formulation is that there should be just one axis of change: there's just one set of requirements which, if modified or added to, would lead to a change in the package.

Open/closed Principle

The slightly obscure name conceals a straightforward proposal. It means program units are closed to modification but open to extension. If we need to add new functionality to a package, we create a new procedure rather than modifying an existing one. (Betrand Meyer, the father of Design By Contract programming, originally proposed it; in OO programming this principle is implemented through inheritance or polymorphism.) Clearly we must fix bugs in existing code. Also it doesn't rule out refactoring: we can tune the implementation providing we don't change the behaviour. This principle mainly applies to published program units, ones referenced by other programs in Production. Also the principle can be looser when the code is being used within the same project, because we can negotiate changes with our colleagues.

Liskov Substitution Principle

This is a real Computer Science-y one, good for dropping in code reviews. Named for Barbara Liskov it defines rules for behavioural sub-typing. If a procedure has a parameter defined as a base type it must be able to take an instance of any sub-type without changing the behaviour of the program. So a procedure which uses

IS OF

to test the type of a passed parameter and do something different is violating Liskov Substitution. Obviously we don't make much use of Inheritance in PL/SQL programming, so this Principle is less relevant than in other programming paradigms.

Interface Segregation Principle

This principle is about designing fine-grained interfaces. It is a extension of the Single Responsibility Principle. Instead of build one huge package which contains all the functions relating to a domain build several smaller, more cohesive packages. For example Oracle's Advanced Queuing subsystem comprises five packages, to manage different aspects of AQ. Users who write to or read from queues have

DBMS_AQ

; users who manage queues and subscribers have

DBMS_AQADM

.

Dependency Inversion Principle

Interactions between programs should be through abstract interfaces rather than concrete ones. Abstraction means the implementation of one side of the interface can change without changing the other side. PL/SQL doesn't support Abstract objects in the way that say Java does. To a certain extent Package Specifications provide a layer of abstraction but there can only be one concrete implementation. Using Types to pass data between Procedures is an interesting idea, which we can use to decouple data providers and data consumers in a useful fashion.

Applicability of SOLID principles in PL/SQL

So it seems like we can apply SOLID practices to PL/SQL.  True, some Principles fit better than others. But we have something which we might use to distinguish good design from bad when it comes to PL/SQL interfaces.

The SOLID principles apply mainly to individual modules. Is there something similar we can use for designing module groups? Why, yes there is. I'm glad you asked.

Part of the Designing PL/SQL Programs series

Error Wrangling

Last week the OTN SQL and PL/SQL Forum hosted of those threads which generate heat and insight without coming to a firm conclusion: this one was titled WHEN OTHERS is a bug. Eventually Rahul, the OP, complained that he was as confused as ever. The problem is, his question asked for a proof of Tom Kyte's opinion that, well, that WHEN OTHERS is a bug. We can't proof an opinion, even an opinion from a well-respected source like Tom. All we can do is weigh in with our own opinions on the topic.

One of the most interesting things in the thread was Steve Cosner's observations on designing code "to be called by non-database, and ... non-Oracle software (Example: Oracle Forms, or a web process)". He uses WHEN OTHERS to produce a return code. Now return codes are often cited as an example of bad practice. Return codes disguise the error and the allow the calling program to proceed as though nothing had gone wrong in the called unit. Exceptions, on the other hand, cannot be ignored.

But Steve goes on to make a crucial point: we must make allowances for how our code will be called. For instance, one time I was writing PL/SQL packages for an E-Business Suite application and I was doing my usual thing, coding procedures which raised exceptions on failure. This was fine for the low level routines which were only used in other PL/SQL routines. But the procedures which ran as concurrent jobs had to finish cleanly, no matter what happened inside; each passed a return code to the concurrent job manager, and logged the details. Similarly the procedures used by Forms passed back an outcome message, prefixed with "Success" or"Error" as appropriate.

This rankled at first but it is undoubtedly true that exceptions make demands on the calling program. Although the demands are not as high in PL/SQL as in say C++ or Java, raising exceptions still changes how the calling function must be coded. We have to work with the grain of the existing code base when introducing new functionality. (Interestingly, Google has a fiat against exceptions in its C++ code base, and developed its Go language to support return codes as the default error handling mechanism. Find out more.)

The point is APIs are a contract. On its side the called program can enforce rules about how it is called - number and validity of input parameters, return values. But it cannot impose rules about what the calling program does with the outcome. So there's no point in exposing a function externally if its behaviour is unacceptable to the program which wants to call it. When the calling program wants to use return codes there's little point in raising exceptions instead. Sure the coder writing the calling program can ignore the value in the return code, but that is why we need code reviews.

So, is WHEN OTHERS a bug? The answer is, as so often, it depends.

GOTOs, considered

Extreme programming is old hat now, safe even. The world is ready for something new, something tougher, something that'll... break through. You know? . And here is what the world's been waiting for: Transgressive Programming.

The Transgressive Manifesto is quite short:

It's okay to use GOTO.

The single underlying principle is that we value willful controversy over mindless conformity.

I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that GOTOs are "considered harmful". But as Marshall and Webber point out, "the problem lies ... in the loss of knowledge and experience. If something is forbidden for long enough, it becomes difficult to resurrect the knowledge of how to use it."

How many Oracle developers even realise PL/SQL supports GOTO? It does, of course. Why wouldn't it? PL/SQL is a proper programming language.

The standard objection is that there is no role for GOTO because PL/SQL has loops, procedures, CASE, etc. But sometimes we need to explicitly transfer control. In recent times I have have across these examples:

• a loop which raised a user-defined exception to skip to the END LOOP; point when the data was in certain ststes, thus avoiding large chunk of processing. A GOTO would have have been cleaner, because it is poor practice to represent normal business states as exceptions.
• a huge function with more than a dozen separate RETURN statements. GOTOs directing flow to a single RETURN call would have been really helpful, because I needed to log the returned value.
• a condition which set a counter variable to a large number so as to short-circuit a loop. Here a GOTO would simply have been more honest.

These examples are all explicit control transfers: they cause exactly the sort of random paths through the code which Dijkstra inveighed against. But the coders didn't honour the principle underlying his fatwa, they just lacked the moxie to invoke the dread statement. Instead they kludged. I'm not saying that using a GOTO would have redeemed a function with 800 LOC ; clearly there'e a lot more refactoring to be done there. But it would have been better.

Here is a situation I have come across a few times. The spec is to implement a series of searches of increasing coarseness, depending on which arguments are passed; the users want the most focused set of records available, so once a specific search gets some hits we don't need to run the more general searches.

Nested IF statements provide one way to do this:

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);

    if result_set.count() = 0
    then
        result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);

        if result_set.count() = 0
        then
            result_set := sieve_3(p1=>var3, p2=>var5);

            if result_set.count() = 0
            then
                ....
            end if;
        end if;
    end if;      
    return result_set;     

Obviously as the number of distinct searches increases the nested indentation drives the code towards the right-hand side of the page. Here is an alternative implementation which breaks the taboo and does away with the tabs.

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    
    result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);
    if result_set.count() > 0
    then
        goto return_point;
    end if;

    result_set := sieve_3(p1=>var3, p2=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    ...
    << return_point >>
    return result_set;     

I think the second version has a clearer expression of intent. Did we find any records? Yes we did, job's a good'un, let's crack on.

tl;dr
GOTO: not as evil as triggers.

My PL/SQL Coding Standards

As I mentioned recently a long-running trolling thread in the OTN forum has recently taken a new twist, by reviving the PL/SQL Coding Standards meme. Alas I was wrong in my prediction that the OTN moderators would soon kill the thread. Not only is it still going but the evil genius behind it is still going, and regularly changing their handle. Currently it is my turn to be impersonated.

In order to justify my assertion in that thread I have decided to publish my damn fine standards. So here they are.

---

APC's Damn Fine PL/SQL Coding Standards

1. Your code must implement the requirements correctly and completely.
   
2. Your code must have a suite of unit and integration tests (preferably automated) to prove it implements the requirements correctly and completely.
   
3. Your code must implement the requirements as efficiently and peformantly as possible.
   

---

Is that it?

These standards have much to recommend them. They are easy to read. They won't need revision whenever there's a new version of PL/SQL. And they focus on what is really important in code: correct functionality. Of course things such as layout and naming of variables are important, I'm not saying they're not. But a PL/SQL procedure can be neatly laid out, rigourously capitalised and thoroughly commented and yet be full of bugs. In my experience, most coding standards tend to document in tedious detail the things which are easy to standardise - use of upper and lower case, line indentation, etc - rather than the things which actually matter.

Also the strictures of codings standards are rarely revised. I still get handed coding standards which say things like "Always use explicit cursors". So either these standards were written ten years ago or the author has not coded any PL/SQL in the last ten years or the author should be shot. Whichever it is, if the standards document contains such canards, why should anybody pay it the slightest attention?

I intend to expand upon some of these points in future posts. If you want a more regular set of PL/SQL Coding Standards then you should check out the redoubtable William Robertson's site. Alternatively, invest in a copy of Oracle PL/SQL Best Practices by Steven Feuerstein (Whom God Preserve). I don't agree with everything that Steven writes but I firmly believe that if every PL/SQL coder in the world followed these guidelines the overall quality of the global PL/SQL codebase would increase by several orders of magnitude.

Update

The troll has now reverted to an anonymous userNNNNNN handle and restored the original text. Obviously even they have got tired of the joke. Or been stricken by conscience.

Oracle: Logging in PL/SQL

Logging is one of those little utility functions that we shouldn't be without but generally we never get around to implementing. Logging is not just for errors - important though that is - but for dumping trace messages, diagnostic information and of course debugging. Such instrumentation is crucial to the discovery of when, where and why a program went wrong. Furthermore, properly instrumented code can be a useful tool for assessing the virtues of a prototype program and for catching buggy or poorly performing code before it gets released into production.

Yet most developers still seem to be using DBMS_OUTPUT.PUT_LINE() for these tasks. DBMS_OUTPUT.PUT_LINE() has one virtue - ease of implementation - but it has several drawbacks:

• It requires an interactive session like SQL*Plus to view;
  
• It produces no permanent output (without a spool command);
  
• It may contaminate an application that needs to output information to the actual user;
  
• We only get the output once the PL/SQL program has completed;
  
• It doesn't work with inside a FUNCTION;
  
• There's that pesky 1000000 character buffer limit.
  

So I think we can agree that DBMS_OUTPUT.PUT_LINE() is not suitable for anything other than the most cursory of debugging tasks. Yet, many people have nothing else in their toolkit.

What must be done?

What are the characteristics of a good logging solution?

Persistence

The log must be permanent. At the minimum this means logging to a file or a table, but it is nice to have the option of directing output to other destinations such as syslog.

Structure

The log must be more than a free text field. At the very least we need to be able to search on Who, What and When.

Message levels

We need to be able to distinguish different types of message. The obvious ones are ERROR, INFO and DEBUG, but it is useful to be able to define custom levels.

Filtering

We need to be able to enable the logging by message levels. Typically we would only want to run at DEBUG level in a development environment. In production we would normally log only ERROR and certain types of INFO messages. However, we must be able to change this behaviour easily, so if we want to run production with DEBUG on, we can. Nice to haves include the ability to direct output for different message levels to different destinations and the ability to log at different levels for different subsystems.

Robustness

It is crucial that the logging doesn't fail. If it does fail it must at least report it failure. Furthermore it mustn't raise an exception and cause the program it's logging to fail.

Acceptable impact

We want all this and a small performance hit. Inevitably logging will consume CPU cycles, but provided this is kept to a reasonable level the price is worth the asking, as the benefits of logging are plentiful.

Interface

The public face of the logging solution ought to conceal the workings of the program from the programs that use it. Of course, this is just standard good practice, but it's particularly crucial with something like logging as it is used throughout our application. We want to be able to tweak the logging code without having to rewrite every single application.

Enter stage left LOG4PLSQL

The project I am currently working on consists largely of a fully automated data warehouse: the user interaction is limited to the data mart on the front. The rest of it is long running batch jobs. Logging is crucial: indeed, there is no other feedback for the success, failure or progress of a job. Although I had some scraps of code for logging errors I lacked a full Monty logging implementation that I needed here. So I turned to LOG4PLSQL, an open source package. The LOG4PLSQL developer Guillaume Moulard was inspired by the open source java tool log4j, which is as close to an industry standard as you can get. So much so that even though Sun has introduced its own logging package in JDK1.4 I suspect most projects still use log4j.

Let's assess LOG4PLSQL under those headings...

Persistence: 8.5/10

LOG4PLSQL consists on a table with a PL/SQL package acting as an API. It also provides hooks to write out to the Alert Log or to throw a .trc file. Furthermore it has the ability to integrate with log4j, which adds further options (of which more later). The package uses DBMS_PIPE to connect to a log4j session running in the background. The key aspect of the PLOG API is the CTX PL/SQL record type, which stores metadata for the logging of the message.

Structure: 7.5/10

The TLOG table has columns for user, datetime and context as well as a message text. The datetime is an Oracle DATE column, but it also uses the V$TIMER view to get hundredths of a second. It would be neater if it used the TIMESTAMP data type but the package is meant to be runnable on Oracle 8 installations. I would also like to see TLOG store the SID.

Message levels: 7/10

The package defines five default message FATAL, ERROR, WARN, INFO and DEBUG, plus ALL and NONE as the outer bounds logging. These are actually constants, mapping to numeric values. Despite being "inspired by" log4j LOG4PLSQL has a different scale from that tool. It runs from 0=NONE to 70=ALL whereas log4j runs from 10000=ALL to 50000=NONE; this diverge has a key impact when it come to the integration with log4j later on.

Filtering: 7/10

LOG4PLSQL has two mechanisms for assessing message levels and deciding what to do with them. Firstly, a PLOG.CTX can be associated with each message: this allows us to individually specify the treatment of the message - write to alert log, store in table, send to DBMS_OUTPUT, etc. If no CTX record is specified the package uses a default record (and we can set the values for that default). Additionally LOG4PLSQL allows us to set a default logging level: messages of a message type lower than that level are discarded. The problem is that the logging level is hard-coded in a package specification. This is very efficient from a performance point of view but it makes it awkward to alter the level. Indeed, in a production environment most DBAs would be profoundly reluctant to edit a package spec at all, and certainly not without a change request.

We could also filter log messages by using log4j. However, because the LOG4PLSQL message levels do not map to the log4j levels we cannot use log4j filtering methods to direct different levels of messages to different appenders.

Robustness: 9/10

No complaints on this score with regards to normal running. The only problem has been with the installation scripts. The packages are annotated with PLDOC comments so that HTML documentation can be generated using the Javadoc tool. Unfortunately the annotations are not correct, which leads to error messages. So Moutard has provided two empty scripts param.sql and return.sql which suppress the error messages but throw errors of their own because some versions of SQL*Plus don't like empty files. The correct solution is to ensure that every line of a PLDOC comment starts with an asterisk.

When using log4j we have to be certain that the background instance is running. Otherwise the DBMS_PIPE gets clogged up and no further logging happens until the pipe is flushed.

Acceptable impact: 6.5/10

Writing records to a database is a fairly expensive process; logging TRACE and DEBUG messages to the TLOG table can have a severe impact on the performance of PL/SQL code. Also, the checking of the logging level is fairly cumbersome and is an overhead in its own right.

Interface: 7/10

The PLOG package exposes the workings of LOG4PLSQL to a degree unsuitable for production use. However, I think this is fair enough: the facade ought to be application specific.

Conclusion

So, to sum up, LOG4PLSQL offers a fine starting point as a logging package if you don't have one of your own. It has a solid set of functionality and does pretty much everything you would want. There are a couple of niggling things, but as it is open source, we are free to tweak the program to suit ourselves.

Having said all that I am now working on my own logging package (a man's got to have a hobby). LOG4PLSQL ultimately is engineered (and in some respects over-engineered) to suit Guillaume Moutarde's needs. My needs are somewhat different: particularly I need better performance out of logging to a table, I need to be able to change the logging level dynamically. I want certain types of log message written to V$SESSION_LONGOPS. I would like to be able to have different logging behaviours for discrete subsystems. I need a better fit with the industry standard tools: log4j and syslog.

Finally, having read Michael Feather's thoughts on stunted frameworks, what I want is a simple core logging package that is easy to extend for specific applications. My current application consists of a core of (amended) LOG4PLSQL with three packages over it; it's a horrible gnarly mess of application specific stuff (how subsystems are handled) and general stuff (writing out to syslog). Fortunately the public interface is quite clean, so (theoretically) it will be a cinch to swap in my new logging package - except I'll probably have left the project by the time I get it working properly....