When Packages Need to Lose Weight

Slim your PL/SQL with some reorganization and a new Oracle Database 12c feature.

By Steven Feuerstein

January/February 2015

Over several months, the development team members at (the fictional) extremememe.info had tackled their worst performance issues and bugs. I’d helped them upgrade to Oracle Database 12c Release 1, and I’d trained them on best practices for PL/SQL development. Sandra, the team lead, then informed me that it was time to switch focus and improve the maintainability of the extremememe.info codebase.

“Over the last couple of years,” Sandra began, “our codebase has gotten larger and more convoluted. A ‘small’ bad design decision from the early days has demanded more workarounds, and now we have big problems understanding and enhancing our code.”

To extremememe’s credit, Sandra had obtained approval from her manager to put new development and all noncritical bug fixes on hold for a month so the team could engage in a targeted cleanup of its code.

“First up,” said Sandra, “we need to do something about the biggest, fattest, most critical package in our entire application: em_central.”

“Well, you know what they say: dream no small dreams,” I mused. “Subprograms in that package are used all over the application, and you want to start with that?”

“We don’t really have any choice,” explained Sandra. “You’re right: lots of other program units call procedures and functions in em_central, and multiple developers often need to work on the package body. Sometimes programmers are queued up, waiting for their turn. And then there’s the size of the package. Its scope has gradually increased over the years. So now the package specification has close to 100 subprograms, covering five different functional areas, and the package body is running close to 10,000 lines long.”

Now there’s a story line many of us will find familiar. Most applications feature configuration or utility packages that are widely used. They then become convenient dumping grounds for new functionality that doesn’t fit anywhere else. Next, that new functionality expands, and before you know it, every developer calls and needs to work on that package.

“OK,” I told Sandra, “You’re the boss: em_central it is. I’ll review the package and come up with a set of recommendations for changing things.”

“Actually, I prefer to take a different approach. You’ve taught us a lot, Steven. Now it’s time for my team to take the lead. So Lakshmi and Wanda are going to take on the em_central refactoring project. They will present their efforts to you, and you can give them feedback.”

Pay a consultant to sit back and comment on other people’s work? Gotta love it!

I am exploring how to reorganize massive amounts of code, but I cannot show all that code in this article. I will, instead, use highly artificial names and “empty” procedures (simply executing NULL;) as placeholders for the real thing. Because the focus of this article is on constructing and changing interfaces, rather than the code inside, this simplification should not interfere with understanding the key points.

So here’s my simplified representation of em_central:

CREATE PACKAGE em_central
IS
   PROCEDURE a_do_this;
   PROCEDURE a_do_that;
   PROCEDURE b_do_more;
   PROCEDURE b_do_less;
END;
/
CREATE PACKAGE BODY em_central
IS
   PROCEDURE a_do_this
   IS
   BEGIN
      NULL;
   END;
   PROCEDURE a_do_that
   IS
   BEGIN
      NULL;
   END;
   PROCEDURE b_do_more
   IS
   BEGIN
      NULL;
   END;
   PROCEDURE b_do_less
   IS
   BEGIN
      NULL;
   END;
END;
/

I use the prefixes “a” and “b” to represent two different major areas of functionality, with two procedures “in” each area. (I put the word “in” in quotation marks because the procedures are together only by virtue of their shared prefix.) In a real package, there would be dozens of subprograms for each area.

A week went by, and then I got a request for a meeting with Lakshmi and Wanda. They were ready to show me their reworking of em_central. We grabbed a conference room, and Lakshmi showed me this code:

DROP PACKAGE em_central
/
CREATE PACKAGE em_central_a
IS
   PROCEDURE a_do_this;
   PROCEDURE a_do_that;
END;
/
CREATE PACKAGE em_central_b
IS
   PROCEDURE b_do_more;
   PROCEDURE b_do_less;
END;
/

She was about to show me the package bodies, but I stopped her: “I don’t need to see the implementations. This is definitely not the approach you want to take.”

Wanda protested, “But we broke up the big package into smaller packages. Lakshmi is responsible for area a, and I’m responsible for area b. So now we can edit and compile simultaneously—we don’t have to wait for the other to finish.”

“That’s true,” I nodded, “and that’s all good. But you’ve violated your contract with the developers on your team. And establishing a new contract will be expensive and time-consuming.”

“Contract?” asked Lakshmi. “What are you talking about? We never signed a contract with our teammates.”

“Oh yes, you did. As soon as you compiled em_central and made it available for use, you entered into a contract with everyone who uses it—including yourself—and now you must respect it.”

Package specifications define what you can do with the functionality implemented in the package body. Each subprogram has a header, with a name and parameter list. When you invoke a subprogram, you must conform to this header (most importantly in the way you specify arguments for your parameters).

“That’s pretty obvious, right?” I asked Wanda and Lakshmi. They nodded. “What is less obvious is that the person who wrote the package specification also needs to conform to that header. That’s the contract.

“This means that if you need to make changes to a subprogram in the future, you should ensure that the header remains consistent or backward-compatible with all current invocations of that program. Otherwise, you will break existing, tested, stable code.”

Backward compatibility for subprogram invocations is achieved in three main ways:

1. Add new trailing IN parameters with default values. Because they have default values, any existing invocations of the subprogram will not need to be changed.
2. Add an overloading of the subprogram. You can always add another subprogram with the same name in the package, but with substantially different parameters, such as different numbers, types, or names.
3. Add an entirely new subprogram. Avoid conflicts with current usages by adding subprograms with new names.

“So,” I concluded, “you cannot drop the em_central package and replace it with two new packages. That will invalidate virtually all your program units and require team members to change dozens, maybe hundreds, of programs.

“You’ve got to keep the em_central package specification in place so all those existing programs don’t have to change. What you need to focus on is the package body: how does that need to change to utilize those two new packages, em_central_a and em_central_b?”

So Wanda and Lakshmi got back to work. A week later, they asked for a meeting to share their revised implementation. First they showed me the em_central_a and em_central_b package bodies (the specifications were unchanged from the last meeting):

CREATE PACKAGE BODY em_central_a
IS
   PROCEDURE private_stuff
   IS
   BEGIN
      NULL;
   END;
   PROCEDURE a_do_this
   IS
   BEGIN
      private_stuff;
   END;
   PROCEDURE a_do_that
   IS
   BEGIN
      private_stuff;
   END;
END;
/
CREATE PACKAGE BODY em_central_b
IS
   PROCEDURE private_stuff
   IS
   BEGIN
      NULL;
   END;
   PROCEDURE b_do_more
   IS
   BEGIN
      private_stuff;
   END;
   PROCEDURE b_do_less
   IS
   BEGIN
      private_stuff;
   END;
END;
/

Then it was time for the em_central package body. Wanda explained, “Rather than dropping em_central, we rebuilt the package body of em_central so that it redirects to the helper packages.”

CREATE PACKAGE BODY em_central
IS
   PROCEDURE a_do_this
   IS
   BEGIN
      em_central_a.a_do_this;
   END;
   PROCEDURE a_do_that
   IS
   BEGIN
      em_central_a.a_do_that;
   END;
   PROCEDURE b_do_more
   IS
   BEGIN
      em_central_b.b_do_more;
   END;
   PROCEDURE b_do_less
   IS
   BEGIN
      em_central_b.b_do_less;
   END;
END;
/

“Nice work!” I declared. “You’ve respected the contract and changed only the package body. That enables you now to focus your attention on the approach you took when implementing those redirects. I have only two concerns, and that’s not bad at all.”

1. Redundant information in subprogram names. Wanda and Lakshmi changed the implementation of em_central.b_do_more to

em_central_b.b_do_more;

Because the name of the package now includes the “b” suffix (and context), it is no longer necessary to include “b” as a prefix on the subprogram. When all the code was in em_central, the “b” prefix was used to partition the single package into functional areas.

Now they have separate packages for each functional area, a much better approach. I therefore recommended to Wanda and Lakshmi that the “a” and “b” prefixes be dropped from all subprograms in the em_central_a and em_central_b packages.

FUNCTION i_was_called_by (program_in IN VARCHAR2)
   RETURN BOOLEAN
IS
   c_stack   CONSTANT VARCHAR2 (32767)
      := DBMS_UTILITY.format_call_stack;
BEGIN
   RETURN INSTR (SUBSTR (c_stack,
       INSTR (c_stack,
              CHR (10),1,5)+ 1,
              INSTR (c_stack, CHR (10),1,6)
                - INSTR (c_stack,CHR (10),1,5)
                         + 1),
       program_in) > 0;
END;

PROCEDURE protected_subprogram
IS
BEGIN
   IF i_was_called_by (‘PKG1’)
   THEN
      /* All is fine. */
      ... your code here ...
   ELSE
      RAISE PROGRAM_ERROR;
   END IF;
END;

2. Redundant code in the two helper packages. This is a much more serious problem. The original em_central package contained a procedure, private_stuff, that was called by all the public subprograms but that did not appear in the specification of em_central. Anything defined in the package body but left out of the specification is not accessible from outside of the package.

As a result, when the implementations of those public subprograms were moved to their own packages, they could no longer access the private_stuff procedure in em_central. This problem can be resolved in a few ways:

• Add private_stuff to the em_central package specification, and then call that procedure from both em_central_a and em_central_b. In other words, change private_stuff into a publicly available procedure.

This is a very bad idea. First, private subprograms should stay private. Otherwise, you risk exposing functionality that should not be called directly. Second, extremememe would then have a very tangled set of dependencies between these packages, which could cause serious maintenance headaches.

The “main” em_central package should depend on its helper packages, but those helpers should not, in turn, depend on em_central.

• Copy the contents of private_stuff to each of the helper packages.

Wanda nodded. “Yep, that’s what we did. So no cross-dependencies, and both helper package bodies compile.”

“That’s all true,” I replied, “but do you really want to copy the private_stuff logic? What happens if it needs to change?”

“We’ll change it in both places. What’s the big deal?”

“Are you so sure you will remember to fix it in both places? And what happens when someone else has to maintain your code? How will they even know, much less remember?”

“I’ve got it!” piped up Lakshmi. “Let’s add a comment before each private_stuff program, like this:”

/* Any changes here should be applied*/
/* to em_central_b as well. */

I nodded. “Sure, you can do that, and I confess: I have done that. But it’s not the best solution, because anything that involves copying rather than reusing code is suboptimal. How could you avoid copies of private_stuff code?”

They were both silent for a moment, and then Wanda declared: “Put it in its own package!”

Which brings us to the third, and best, way to avoid redundant code in the helper packages, and the approach we ended up taking at extremememe.

• Move private_stuff into its own package

Always aim for a single point of definition for the logic in your application. As long as you are adding two more helper packages, why not add a third? A helper, em_central_private, for the helpers:

CREATE PACKAGE em_central_private
IS
  PROCEDURE private_stuff;
END;
/
CREATE PACKAGE BODY em_central_private
IS
  PROCEDURE private_stuff
    IS
    BEGIN
      NULL;
    END;
END;
/

So we removed the private_stuff procedure from em_central_a and em_central_b and changed the calls to private_stuff to em_central_private.private_stuff:

CREATE PACKAGE BODY em_central_b
IS
  PROCEDURE b_do_more
  IS
    BEGIN
     em_central_private.private_stuff;
    END;
  PROCEDURE b_do_less
  IS
    BEGIN
     em_central_private.private_stuff;
    END;
END;
/

After so many iterations, Wanda, Lakshmi, and I decided that it was time to recap what we’d accomplished so far:

1. We respected the contract with existing invocations of em_central subprograms. Those programs neither have to change nor be recompiled, because the em_central specification hasn’t changed one bit.
2. The body of em_central shrinks to a fraction of its former self, because the body of each procedure is simply a redirect into the em_central_a and em_central_b packages. The subprograms in these packages should be invoked only by em_central.
3. We’ve avoided repeating the common implementation logic in both em_central_a and em_central_b, by creating a fourth package, em_central_private, to hold that common code. It should be invoked only by em_central_a and em_central_b.

“So are we done?” asked Lakshmi.

“Yes and no,” I replied. “The word should is an interesting one for programmers and software. Should is all about doing the right thing and about intention. Programming is all too often just about doing whatever it takes to get the job done on time.”

“But everyone can just keep on doing what they did before: calling em_central subprograms.”

“Sure,” I agreed. “They can do that, but they can also write code like this:”

BEGIN
   em_central_a.do_this;
   em_central_private.private_stuff;
END;
/

“Only if they have EXECUTE privileges on those packages,” noted Wanda.

“If each of your developers worked in his or her own schema,” I said, “you could use grants to control access. But all your developers connect to the same schema.”

After a moment of silent thought, Lakshmi spoke up: “Could we add code to each of the subprograms to raise an exception if the ‘wrong’ program calls it?”

“Yes, you could. You can obtain the execution call stack and then parse it to determine which program invoked a ‘private’ subprogram. [Editor’s note: Refer to the “Protect at Runtime” sidebar for this solution.] But because you have upgraded to Oracle Database 12c, there is a better way: whitelisting with ACCESSIBLE BY.”

Before Oracle Database 12c, PL/SQL could not prevent a session from using any and all subprograms in packages to which that session’s schema had been granted EXECUTE privileges. Developers had to accept that violations of their subprograms would or could occur or write code to restrict access (as shown in the “Protect at Runtime” sidebar).

As of Oracle Database 12c, you can now include a clause in the header of your program unit that specifies a “whitelist” of other PL/SQL units that can access the PL/SQL unit you are creating or altering.

As I explained to Wanda and Lakshmi: “All you have to do is add the ACCESSIBLE BY clause and include a comma-delimited list of the program units that can call it.”

We applied the change to em_central_ private:

CREATE PACKAGE em_central_private
   ACCESSIBLE BY (em_central_a,
                  em_central_b)
IS
   PROCEDURE private_stuff;
END;
/

If anything besides a subprogram in those two packages calls private_stuff, it will see this compilation error:

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00904: insufficient privilege to
access object EM_CENTRAL_PRIVATE
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Wanda and Lakshmi were both excited about the power and implementation of ACCESSIBLE BY.

“It reminds me of SQL,” said Lakshmi. “I am not telling PL/SQL how it should restrict access. I just describe what I need done, and the underlying engine does all the work for me.”

“And I love,” Wanda chimed in, “that I am able to do it with such a small change in my code.”

CREATE PACKAGE BODY em_central_private
   ACCESSIBLE BY (em_central_a),
   ACCESSIBLE BY (em_central_b)
IS

Next Steps DOWNLOAD Oracle Database 12c LEARN more about ACCESSIBLE BY TEST your PL/SQL knowledge READ more Feuerstein bit.ly/omagplsql stevenfeuersteinonplsql.blogspot.com READ more about PL/SQL