O'Reilly logo

Oracle PL/SQL Best Practices by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

PKG-10: Consolidate the implementation of related overloaded modules.

Synopsis

In most cases, when you build overloaded programs, each program performs a similar operation, with variations that are usually related to different combinations of parameters. If you aren’t careful about how you implement these overloadings, you will end up with a mess of code that’s difficult to maintain and enhance.

The most important step you can take is to isolate behavior/features common to all overloadings and then move that common code into a separate, usually private program. All the overloadings then call that internal program.

You should also take care to organize the overloaded headers contiguously in the package specification so that they are easily identified.

Example

Suppose that I have decided to build an encapsulation package around the book table. Developers will not write an INSERT statement to add a book; they will call an insert procedure. I can think of several different ways to perform that insert:

  • Pass an individual value for each column.

  • Pass a record containing a value for each column.

  • Pass a collection of multiple records of book data.

Here’s the package specification corresponding to these approaches:

CREATE OR REPLACE PACKAGE te_book IS TYPE book_tt IS TABLE OF book%ROWTYPE; PROCEDURE ins ( isbn_in IN book.isbn%TYPE, title_in IN book.title%TYPE DEFAULT NULL, summary_in IN book.summary%TYPE DEFAULT NULL, author_in IN book.author%TYPE DEFAULT NULL, date_published_in IN book.date_published%TYPE ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required