Re: Have keep recompiling procedure

From: jimbo <jcorey_at_charter.net>
Date: 24 May 2001 06:59:34 -0700
Message-ID: <1a69708a.0105240559.23b3c28f@posting.google.com>

Here is my procedure. Again, I have about 10 similar procedures that work fine. The only difference I see is that this one includes a self-join.

...

CREATE OR REPLACE
Procedure FUL_RULES
(
WORK_BATCH_ID varchar2
)
AS
BEGIN begin  

    begin

      update printinfo_ful
      set cust_type = 'Retail'
      where s_org_ext_name is null
      and batch_id = WORK_BATCH_ID;

    end;
    begin

      update printinfo_ful
      set cust_type = 'Business'
      where s_org_ext_name is not null
      and batch_id = WORK_BATCH_ID;

    end;

    begin

        update printinfo_ful
        set selection_product = 'Multiple',processing_group = 1
        where fulf_comm_id in
          (select a.fulf_comm_id  from printinfo_ful a,printinfo_ful b
                     where a.batch_id = WORK_BATCH_ID
                        and a.fulf_comm_id = b.fulf_comm_id
                        and b.batch_id = a.batch_id
                        and a.selection_lob <> b.selection_lob
          )
        and batch_id = WORK_BATCH_ID;
	end;

       begin
        update printinfo_ful
        set selection_product = 'Multiple/'||selection_lob
        where fulf_comm_id in
                (select fulf_comm_id from
                    (select  fulf_comm_id,count(*) mycount  from
printinfo_ful
                     where batch_id = WORK_BATCH_ID
                     and processing_group = 0
                    group by fulf_comm_id HAVING count(*) > 1))
        and batch_id = WORK_BATCH_ID;
	end;

   begin
    update printinfo_ful f
    set documentgroup =

             (select documentgroup from docgroups_fulfillment
                where product = f.selection_product
                   and cust_type = f.cust_type
                )
           where batch_id = WORK_BATCH_ID;

    end;     

   begin
    update printinfo_ful
    set documentgroup = 'No Document Group'

             where documentgroup is null
             and  batch_id = WORK_BATCH_ID;

    end;

end;
END; -- Procedure FUL_RULES

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:<3B0C974B.6A77BCF6_at_exesolutions.com>...
> jimbo wrote:
>
> > Hello,
> >
> > I have a script that runs every few hours and calls 3 stored
> > procedures.
> > Periodically it fails with this message:
> >
> > ORA-06553: PLS-707: unsupported construct or internal error [2601]
> > ORA-06550
> >
> > and I have to recompile. Then it runs fine. No other objects have
> > been changed. It's a simple proc. This happens in both a test and
> > production schema. This seems to happen even if it's not run; somehow
> > it gets reset, although it's still marked as valid. We're thinking
> > (for lack of any other ideas) that a nightly back-up has something to
> > do with it.
> >
> > Any ideas?
>
> I'm thinking that we need to see it to really provide any useful help.
>
> Daniel A. Morgan
Received on Thu May 24 2001 - 08:59:34 CDT