SQL*Plus Tip: Splitting Up Package Code

With PL/SQL packages, I run into the perennial problem. Logically, a package works best as a single huge thing, grouping perhaps hundreds of related procedures together. But logistically, source code sometimes works better as small individual files.

First up, always put the package spec and package body in separate files. That way, you can recompile the body without invalidating other procedures that call into the package.

You can take advantage of SQL*Plus's lack of a true parser by splitting big PL/SQL package files (typically the bodies) into smaller ones. The '@' and '@@' commands work inside a package, where you might not expect them to. For example:

create or replace package foo
as
@foo_declarations;
@foo_procedures;
@foo_functions;
end;