Re: Dealing with size limitations in PL/SQL

In article <4ci1rl$m0l_at_stsver.scs.com.sg>, Justina Seet <jseet_at_scscc1.scsnet.scs.com.sg> wrote:

<snip>
> Solution
> --------
> Between the BEGIN and END of a program, maximum 64K compiled code is
> allowed. The best solution to this problem is to modularize program by
> defining subprograms or packages.
>
> Question
> --------
> Does anyone know of another solution instead of having to divide my
> package into 2 or more packages ?"

I don't know if the following works. But I read something from Oracle that sounded like this might be a solution. Just break your code into several BEGIN - END pairs. An example:

  begin

     select a from b;
     select c from d;
     select e from f;

Change to:

  begin

     begin
        select a from b;
     end;

     begin
        select c from d;
        select e from f;
     end;

I haven't tried this myself as I haven't hit this limit yet. It seems silly, but if you interpret Oracle's statement literal to break your code into BEGIN-END blocks of less then 64k this should work. It didn't say break it up into separate sub-programs.

-- 
Ed Bruce
edward_at_igate1.hac.com
Key fingerprint =  62 8D FC 8F 27 2F 89 D0  8B 38 7E 34 33 74 C2 36