Dynamically calling a private procedure from within a package

Question

Say I have a package that exposes just one procedure publicly. The purpose of this procedure, however, is to call private procedures within the package, based on the argument that's passed to it.

Ideally, I would want to define a hash table (e.g., an associative array or even a table) that maps arguments to internal procedures, and then do something like:

execute immediate 'begin ' || internalProc(myArgument) || '; end;';

However, this won't work because the dynamic begin ... end PL/SQL block is executed outside the scope of the package. I can't even do 'begin myPkg.' || internalProc... because the internal procedures are all private.

As such, is the only way I can achieve this (without exposing private procedures) is by having a massive, hard-coded switch?

case myArgument
  when 'something' then someProc;
  when 'foo'       then fooProc;
  when 'bar'       then barProc;
  ...
  else raise_application_error('-20001, 'No such process.')
end case;

Given that you have to explicitly map the arguments to the internal procedures somewhere why is a massive case statement so problematic? — APC
@APC Anecdotally, I would say that a table that maps arguments to procedures is slightly easier to maintain. One could also use a naming convention for the internal procedures, such that there's a direct correspondence (e.g., 'something' -> somethingProc, 'foo' -> fooProc, etc.) — Xophmeister
I guess we have a different definition of "maintainable". I prefer to have my control logic in front of me rather than having to look elsewhere. Also, chaining the parameter value to the name of the internal procedure breaks the Law of Demeter: en.wikipedia.org/wiki/Law_of_Demeter That means re-organising the internal structure of the called program may have an impact on the calling programs, which is the opposite of maintainable (in my experience, YMMV). — APC
+1 for LoD: Never heard of that before, but reading the Wikipedia article, it seems like a good principle to follow (and one that I generally do, FWIW)... OK: You've convinced me. I'll [vote to] close my question :) — Xophmeister
This is a horrible design. Let the compiler do the work of matching calls to called procedures. — vonbrand

APC APC · Accepted Answer · 2013-08-06T14:06:57

PL/SQL is a procedural language and is not particularly good at executing itself dynamically. It has nothing analogous to Java's Reflection capability. So implementing what you propose will be at best clunky.

The next question is whether what you propose is a good idea. This is a matter of personal taste. Myself, I prefer to have my control logic in front of me rather than having to look elsewhere. Configuring control flow in some external object smacks of soft coding: "the practice of removing things that should be in source code from source code and placing them in some external resource." Find out more.

At best, soft coding just moves complexity to some other place, at worst it increases the overall complexity of the system. Applications are built entirely out of such stuff, but they use Rules Engines to manage things (and anecdotally some such applications seem to be beyond human comprehension).

Chaining the parameter value to the name of the internal procedure would break the Law of Demeter. This is a very sensible design principle which holds that calling programs should not have to understand the internal structure of a called program. Violating the Law of Demeter means that a re-organisation of the internal structure of the called program may have an impact on the calling programs. This has a detrimental affect on maintainability (in my experience, YMMV).

tl;dr
Large CASE statements are a bit like democracy: they are worst solution except for all the others.

Dynamically calling a private procedure from within a package

1 Answers