Re: Stored Procedure Locked
Date: 1998/01/11
Message-ID: <34ba181c.14041260_at_inet16>#1/1
On 10 Jan 1998 23:07:54 GMT, Jeff Wyant <wyant_at_wcoil.com> wrote:
>I'm having a problem with replacing stored procedures in an Oracle
>database running on a HP9000 Unix machine. I have a stored procedure
>that calls many other stored procedures. I want to be able to replace
>one of the called stored procedure without stopping the calling stored
>procedures. Every time I try I get a locked message. Is there a way to
>force Oracle to unlock the stored procedure so I can replace it? Thanks.
No, not really. In order to to replace that procedure, it must invalidate all of the procedures that call it -- that means they cannot be running.
This is where packages come in really handy. They break the dependency chain. If you separate the code into a specification and a body, you can change the body (implementation) without changing the specification. Other chunks of code are dependent on the specification -- not the body. This allows you to change the implementation of the logic without invalidating all of the dependent code in the database (and will avoid your problem as well). Only if you recompile the specification will the dependent objects need to be invalidated.
so, instead of coding:
create or replace procedure my_procedure
as
....
end;
Code:
create or replace packge my_pkg
as
procedure my_procedure;
end;
/
create or replace package body my_pkg
as
procedure my_procedure
is
begin
....
end;
end;
/
then, you can rebuild the package body my_pkg much more freely then the standalone procedure my_procedure....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jan 11 1998 - 00:00:00 CET