CODEWITHSUNDEEP
oracle-databaseplsql
Seb
Seb

Reputation: 11

ORA-04021 when trying to recompile

I am trying to implement a mock library for plsql. I run into an error ORA-04021 when trying to dynamically recompile stored objects.

The code is

create or replace function perform return varchar2 is begin return 'zero'; end;
/

create or replace procedure recompile(source in clob)
is
begin
  execute immediate source;
end;
/

create or replace procedure recompile_usage
is
begin
  recompile('create or replace function perform return varchar2 is begin return ''one''; end;');

  dbms_output.put_line(perform);
  --execute immediate 'begin dbms_output.put_line(perform); end;';

  recompile('create or replace function perform return varchar2 is begin return ''two''; end;');
  dbms_output.put_line(perform);

end;
/

exec recompile_usage;

And here is the output

Function created.

Procedure created.

Procedure created.

one
BEGIN recompile_usage; END;

*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
ORA-06512: at "UTP.RECOMPILE", line 4
ORA-06512: at "UTP.RECOMPILE_USAGE", line 9
ORA-06512: at line 1

It takes 5 to 10 minutes approximately for the timeout to occur.

How can we unlock the object to be recompiled ?

Upvotes: 1

Views: 2705

Answers (1)

Avrajit Roy
Avrajit Roy

Reputation: 3303

As stated in the comments perfectly it is not possible by the way you are trying to accomplish the job. I would suggest that you should directly call the RECOMPILE procedure rather than recompile_usage, Hope below snippet helps.

set sqlbl on;
set define off;

DECLARE
  lv_sql VARCHAR2(32676);
BEGIN
  RECOMPILE(
  'create or replace function perform return varchar2 is begin return ''one''; end;'
  );
  recompile(
  'create or replace function perform return varchar2 is begin return ''two''; end;'
  );
END;

-----------------------------OUTPUT--------------------------------------------

anonymous block completed

SELECT OBJECT_NAME,OBJECT_TYPE,STATUS FROM ALL_OBJECTS
WHERE object_name = 'PERFORM';


**OBJECT_NAME   OBJECT_TYPE STATUS**
PERFORM           FUNCTION  VALID

Upvotes: 1

Related Questions