Can I drop PL/SQL procedure in Anonymous block just after called the same procedure?

user_4219683 0
Viewed 134

I have created a PL/SQL stored procedure which is used to insert row into related table, then in another PL/SQL Anonymous block, I can call and drop it separatly. But when put these two statements togegher, Sql developer was just continuously running there forever. Below are three code snippets:

-- Below code works well

BEGIN
  MyProcedure1 (1,70,'Tommy'); 
END;

-- Below code works well

BEGIN
  EXECUTE IMMEDIATE 'drop procedure MyProcedure1';
END;

-- Below code was stuck there forever

BEGIN
  MyProcedure1 (1,70,'Tommy'); 
  EXECUTE IMMEDIATE 'drop procedure MyProcedure1';
END;
plsql stored-procedures oracle
2 Answers

David, I can still see no reason why after the data is inserted you need to immediately drop the procedure.... what if you want to insert more data tomorrow? Why can you not just manually drop it afterwards if it is just a once off thing?

However if you really must do so...

BEGIN
  MyProcedure1 (1,70,'Tommy'); 
END;
/
drop procedure MyProcedure1;

Then click run as script button in SqlDeveloper... Runs no issue, looking at session manager what is happening is that the anonymous block is putting the procedure in the cache / pinning it (meaning nothing else can compile / drop it) until that block finishes. This is why moving the drop outside the Begin / End allows the code to work.

user_28334010

If you query v$session, this is what you can see (of course, while the anonymous PL/SQL block is still "running" - actually, it is waiting):

SQL> select
  2    a.blocking_session,
  3    b.username blocking_username,
  4    b.osuser blocking_osuser,
  5    a.sid,
  6    a.serial#,
  7    a.seconds_in_wait,
  8    a.username,
  9    a.wait_class,
 10    a.state
 11  from v$session a join v$session b on a.blocking_session = b.sid;

BLOCKING_SESSION BLOCKING_USERNAME BLOCKING_OSUSER  SID  SERIAL# SECONDS_IN_WAIT USERNAME WAIT_CLASS  STATE
---------------- ----------------- --------------- ---- -------- --------------- -------- ----------- -------
             707 SCOTT             littlefoot       707     1245             329 SCOTT    Concurrency WAITING

SQL>

Note the last two columns:

  • state = waiting
  • wait_class = concurrency

Who caused it? Me, myself. So yes - we have met the enemy, and they is us.

Looks like you can't do it the way you wanted but separately:

  • call the procedure first
  • once you're done, drop it

Though, what a strange requirement ... why would you want to drop a procedure? Will you create it again tomorrow? If so, why drop it at all? Saying that "customer requires it" - what the heck customer knows about it? Let them think twice; if that's not enough, think again.

user_90979060
Related