CODEWITHSUNDEEP
sqloracle-databaseplsql
user3269631
user3269631

Reputation: 21

Oracle procedure execute immediate

I have a stored procedure under SYS, which uses the EXECUTE IMMEDIATE command to execute alter object compile. But I am getting the below error. 

CREATE OR REPLACE PROCEDURE comp_inv
is   
   CURSOR empCursor IS 
      SELECT object_type, 
             owner,object_name 
        from dba_objects
       where status = 'INVALID' 
         and object_type in ('PACKAGE','FUNCTION','PROCEDURE');    
BEGIN
   FOR i_rec IN empCursor LOOP       
      execute immediate 'alter ' ||i_rec.object_type|| ' ' || 
                         i_rec.owner || '.' || i_rec.object_name || ' compile';  
   END LOOP;
END;
/

SQL> exec comp_inv;
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "SYS.COMP_INV", line 8
ORA-06512: at line 1


Warning: PL/SQL compilation errors.

SQL> show errors procedure comp_inv;
No errors.

Upvotes: 2

Views: 3406

Answers (4)

Nick Krasnov
Nick Krasnov

Reputation: 27251

In your situation the error simply means that some schema objects cannot be recompiled, for some reason - procedure is executed successfully, but some objects you are trying to recompile cannot be recompiled. For example:

create table t1(col1 number);

create or replace procedure p2(p_in in number) is
  l_res number;
begin
  select count(1)
    into l_res
    from t1;
end;

Determine if there is something "INVALID" there:

column object_type format a10;
column object_name format a10;
column status      fromat a10;

select object_type
     , object_name 
     , status
  from dba_objects
 where status = 'INVALID' 
   and object_type in ('PACKAGE','FUNCTION','PROCEDURE'); 

 no rows selected

Invalidate P2 procedure:

alter table t1 add (col2 number);

column object_type format a10;
column object_name format a10;
column status      fromat a10;

select object_type
     , object_name 
     , status
  from dba_objects
 where status = 'INVALID' 
   and object_type in ('PACKAGE','FUNCTION','PROCEDURE');

OBJECT_TYP OBJECT_NAM STATUS
---------- ---------- -------
PROCEDURE  P2         INVALID

Recompiling:

exec comp_inv

anonymous block completed

Recompilation went successfully:

column object_type format a10;
column object_name format a10;
column status      fromat a10;

select object_type
     , object_name 
     , status
  from dba_objects
 where status = 'INVALID' 
   and object_type in ('PACKAGE','FUNCTION','PROCEDURE');

 no rows selected

Invalidate our P2 procedure so it cannot be compiled:

create or replace procedure p2(p_in in number) is
  l_res number;
begin
  invalid_operator;
end;
/

PROCEDURE P2 compiled
Errors: check compiler log

Check for invalid objects:

column object_type format a10;
column object_name format a10;
column status      fromat a10;

select object_type
     , object_name 
     , status
  from dba_objects
 where status = 'INVALID' 
   and object_type in ('PACKAGE','FUNCTION','PROCEDURE');

OBJECT_TYP OBJECT_NAM STATUS
---------- ---------- -------
PROCEDURE  P2         INVALID

Trying to recompile that procedure:

exec comp_inv

ORA-24344: success with compilation error
ORA-06512: at "SYS.COMP_INV", line 11
ORA-06512: at line 1
24344. 00000 -  "success with compilation error"

As it has already been said by @OracleUser check one of the *_errors data dictionary view, to see if there is something in it. It'll provide you with information needed to eliminate the threat. 

column owner format a10;
column name  format a10;
column type  format a10;
column text  format a60;

select owner
     , name
     , type
     , text
  from dba_errors

OWNER      NAME       TYPE      TEXT  
------ ---------- ------------------------------------------------------------
NK         P2         PROCEDURE  PLS-00201: identifier 
                                             'INVALID_OPERATOR' must be declared 
NK         P2         PROCEDURE  PL/SQL: Statement ignored

Secondly, as @Frank Schmitt correctly pointed out in his comment to your question never, ever create user objects of any kind in the SYS schema. If you need to store some administrative stored procedures or other objects somewhere, dedicate a different user for that with appropriate privileges and roles granted, or as a last resort use SYSTEM(which is not recommended either) schema.

Upvotes: 3

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

You can use the standard Oracle package DBMS_UTILITY and it's procedure COMPILE_SCHEMA() which gives you standard way to recompile invalidated objects. It's worth to use it for many reasons, in particular it allows you to keep compile settings.

Upvotes: 3

shinobi92
shinobi92

Reputation: 127

this might be what you need.

    exec dbms_utility.compile_schema('SCOTT');

off course you need proper rights to execute that

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

CREATE OR REPLACE PROCEDURE comp_inv
is   
   CURSOR empCursor IS 
      SELECT object_type, 
             owner,object_name 
        from dba_objects
       where status = 'INVALID' 
         and object_type in ('PACKAGE','FUNCTION','PROCEDURE');    
BEGIN
   FOR i_rec IN empCursor LOOP
      BEGIN
        execute immediate 'alter ' ||i_rec.object_type|| ' ' || 
                         i_rec.owner || '.' || i_rec.object_name || ' compile';
      EXCEPTION
      WHEN OTHERS THEN
         FOR I IN (select * from DBA_ERRORS where NAME = i_rec.object_name and owner = i_rec.owner AND type = i_rec.object_type)
         LOOP
                DBMS_OUPT.PUT_LINE(I.line || ' ' || I.text);
         END LOOP;
      END;

   END LOOP;
END;
/

Upvotes: 2

Related Questions