Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ankur.

Asked: November 12, 2019 - 12:59 pm UTC

Last updated: November 12, 2019 - 4:23 pm UTC

Version: Release 11.1.0.6.0

Viewed 1000+ times

You Asked

create package pkg
is
v_n number:=777;
End pkg;
/

Begin
dbms_output.put_line(pkg.v_n);
execute immediate 'drop package pkg';
End;
/

Q. Why my anonymous block hangs, if there is some kind of locking issue as we have referenced the package and it has loaded into the memory, what measures can we apply to unload it from memory so that it could be referenced and dropped in a single anonymous block.

Thanks.

and Chris said...

You can't drop a package while someone is executing it.

The code accesses the package variable in the dbms_output call.

So:

Your session is executing it!

So you can't drop it.

To resolve this, remove the dbms_output call in your block. Or any other references to this package in the block:

create package pkg
is
  v_n number:= 777;
End pkg;
/

select count(*) from user_objects
where  object_name = 'PKG';

COUNT(*)   
          1 

begin
  execute immediate 'drop package pkg';
end;
/

select count(*) from user_objects
where  object_name = 'PKG';

COUNT(*)   
          0


Also ensure no other sessions are accessing this package.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ankur Kumar, November 12, 2019 - 3:25 pm UTC

create or replace package pkg
is
v_n number:=777;
End pkg;
/

Begin
pkg.v_n:=25;--Package is being used.
--But here package is not being used--
--Also no dbms_ouput.pu_line used in this anonymous block--
--since for me this package is not being used anymore
--the below line must drop this package but still I am
--unable to drop it.
--apart from me no one is using my package.
execute immediate 'drop package pkg';
End;
/

Thanks Chris for your response. Look I used dbms_ouput.put_line just to draft my question my real requirement does not use anything like this but still I have modified the code and now removed the dbms_ouput.put_line but still I am not able to drop the package within that anonymous block.
Chris Saxon
November 12, 2019 - 4:23 pm UTC

When you use an anonymous block, you're sending ONE statement to the database. So it's all part of the same call.

If you reference the package anywhere in the block, it's still part of the statement. So you can't drop the package in that block.

You must split package access and drop into two separate blocks.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library