Home » SQL & PL/SQL » SQL & PL/SQL » How to Document PL/SQL Code (9i, 10g, 11g)
How to Document PL/SQL Code [message #409119] Fri, 19 June 2009 08:33 Go to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi all, I am looking for you to give me ideas on how to document my PL/SQL code.

I am currently working on documentation for one PL/SQL package used as an API to a java front end. I would be interested in knowing what documentation artifacts my fellow experts here on OraFAQ think would add value to a documentation package.

For example:

A CRUD matrix: will show what row source objects are referenced by each packaged procedure/function and how it is used. Additionally an overall CRUD will show what the package does as a whole.

So... I am open to your suggestions as to what would help someone (or possible make me look good), with respect to documenting a piece of PL/SQL code. Please consider who would be able to use this documentation and how. Consider the people who would be reviewing the doc... DBAs, Developers, and BAs in particular.

I recognize there are two types of documentation: 1) what can be extracted by looking at the code directly, and 2) what is locked up inside a developers head. Commentary and suggestions for either type are being solicited.

Thanks, Kevin

[Updated on: Fri, 19 June 2009 08:45]

Report message to a moderator

Re: How to Document PL/SQL Code [message #409141 is a reply to message #409119] Fri, 19 June 2009 11:03 Go to previous messageGo to next message
Messages: 3202
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Usually when I write a package the first (or the least) thing I do is add a description of each procedure/function and of the in/out parameters in the package head.

If the APIs of that package are made available to others then those descriptions will be mad a little more detailed, and I would add some sort of overview on how the code is interconnected and/or how the data usually flows.

What is also important in my opinion is to give an overview on how errors that I have expected (and unexpected ones) are handled in general, and how they can be rectified. Which is important, because if you have documented that well it is less likely that the night/weekend hotline will have to call you because of an error. Very Happy

For example, "If there is an format error in one of the input files, this file will be copied to the defined revision directory after the error is logged in the error table, and then processing continues with then next file."
Re: How to Document PL/SQL Code [message #409177 is a reply to message #409141] Fri, 19 June 2009 18:25 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't write much (production) PL/SQL these days, but I'm not sure the technology should affect the content - just the location and method.

In PL/SQL you have three points at which you can add documentation:
- In the Package Spec
- At the beginning of each component in the body
- In-line

People reading comments at each of those points are looking for different things:

In the Package Spec - which contains all of the external interfaces with the package - you should explain how to use each of those interfaces properly, what happens when the interface is called (i.e. changes to the DB state or package state) and what exceptions the package could raise (and their meaning).

A short note at the top of the package spec should explain the overall conceptual grouping of the package. i.e. What's in and what's out.

At the beginning of each component in the body (cursor, proc/func) you don't explain WHAT the component does (i.e. don't explain the functionality) - that is covered in the spec. Anyone looking at the body is interested in the coding - i.e. the algorithm - and the rationale for that algorithm. There are many ways to skin a cat - why did the programmer/designer choose this particular approach. This type of documentation should be limited to the non-trivial stuff.

Programmers reading in-line documentation are confused and wondering what the heck this line of code is doing. In-line documentation contains micro-algorithm details (e.g. a description of a devilishly tricky couple of lines), and tips/clarifications of no more than three or four words - such as the purpose of each variable.

It's personal preference, but I don't like CRUD within a package. That is something that belongs at a higher level. When you want to know what-changes-what, you need a definitive source of information that crosses package boundaries. i.e. You want to know everything that CRUDs a particular table, or you want to know every package/proc run and table CRUDed by a particular business process.

Ross Leishman
Previous Topic: Different Equals !!!
Next Topic: Need to generate unique number for the combination of values (merged)
Goto Forum:

Current Time: Thu Jul 27 13:58:02 CDT 2017

Total time taken to generate the page: 0.13712 seconds