Thursday, May 17, 2012

Schema-level Procedure or Package with one Procedure?

I received this question from a PL/SQL developer:

Dear Steven,
I'm an Italian Pl/Sql developer who often follows your precious advices.As you always say, it's useful to modularize your business logic through procedures and functions inside a package, and I always do that way.
My question is this. If I have to write a main module X (procedure or function) which calls further N modules (procedures or functions), how do I have to choose between to develop it by a package or by a procedure? I mean, is it better to write a package with the only X procedure in the specification and the other N modules inside the body or to write directly a procedure X with a main code and the other N modules defined inside it? I've thought that I should ask to myself whether I want to hide the N modules (package) or not, but I'd like to get your opinion.
Thanks in advance!
And my reply:
I suggest you never define a schema-level procedure or function (outside a package). Instead, always start with a package for a very simple reason: everything gets more complicated over time. Users request new features or changes to existing features, developers realize that they need to do more or do it differently, etc.
So there is a very great likelihood that in time you will find that you need to build another procedure or function that is related to the first. Even if they don't share code, they share the same "domain" within your application. And that is one of the best reasons for creating packages: to group together related functionality.

If you don't use a function, you may find yourself defining prefixes for those related schema-level program units, so that you can see by name that they are related.

Bad idea. Better to put those schema-level procedures and functions in a package - but now you have to go back to all the places they are called and add the "package dot" to each invocation (or create a synonym to "redirect" into the package). 

Bad use of time.

Instead, therefore, I suggest that you start with a package, even if the specification contains just a single subprogram.

1 comment:

Jeffrey Kemp said...


The only procedures I sometimes create are convenient one-line wrappers for a package procedure, e.g.: