Oracle PL/SQL Best Practices

Name

PKG-03: Freeze and build package specifications before implementing package bodies.

Synopsis

Develop a “specifications first” discipline: put off writing package bodies as long as possible. Instead, sit back, relax, and brainstorm about the kinds of things you want to do with each package (based, of course, on requirements provided by the users). Write out those things-to-do as procedure and function headers in the specification. Do this for a whole bunch of packages you need to build.

Then try them out. Even if you don’t built the package bodies, you can still write programs based on the headers. By doing this, you often uncover errors in the requirements, missing parameters, and so on. Since you haven’t yet written the implementations, it’s easy to clarify what the user wants and modify the package specifications.

Once you are confident that the specifications reflect the application needs, dive into those package bodies!

Example

We are building a telesales call management system. Management just told us that due to the upcoming IPO, we have to get everything done in two months. Yikes! My first inclination is to start writing code madly, but the DBAs haven’t finished designing the tables, and the users are still thrashing. I can’t wait, though, so I take what requirements have been set and brainstorm via package specifications.

I know that I have to do some analysis, so I quickly put together this specification:

CREATE OR REPLACE PACKAGE analysis IS FUNCTION avg_workload ( dept_id ...