Re: PLS-00123: Program too large

Wes Riding wrote:
>
> We are using Designer/2000 to generate WebServer packages. Some of the
> packages for our modules are too large to be compiled (error
> PLS-00123). We get this error when we try to install the packages
> through the Web Generator or right on our unix server.
>
> Oracle support tells us that this problem is not going away any time
> soon. Our only solution is to manually break the offending packages
> into smaller units.

[Quoted] And there is a problem with this???

[Quoted] There are many, many ways to do this. So many that I can only give you a hint or two and let you take it from there.

Look for duplicated code. Even if it is only duplicated twice, see if you can't place it as a function/procedure in another package. Along this line, look for cursors that are similar. Maybe you can make one global, parameterized cursor.

package body xxx is
  procedure p1 is
    ...
    cursor c is
      ...

  procedure p2 is
    ...
    cursor cc is
      ...

  procedure p3 is
    ...
    cursor ccc is
      ...
end;

Turn the above into:

package body xxx is
  cursor c (p1 integer, p2...) is
    ....

  procedure p1 is
    ...
    open c (v1, v2, ...);
      ...

  procedure p2 is
    ...
    open c (v1, v1, ...);
      ...

  procedure p3 is
    ...
    open c (v2, v1, ...);
      ...
end;

I have seen packages where essentially the same cursor was redefined dozens of times throughout the code!

Always keep in mind the three keys to writing good, yet compact, code: modularize, modularize, modularize!

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"