oracle-databaseobjectpackagerecompile

How to run UTL_RECOMP.RECOMP_PARALLEL from a procedure?


I have a simple question: Is it possible to run UTL_RECOMP.RECOMP_PARALLEL from a procedure? I have a Package with a procedure which should recompile all invalid objects. It looks like this:

    PROCEDURE Compile ()
    IS
    BEGIN
        EXECUTE IMMEDIATE ('BEGIN SYS.UTL_RECOMP.RECOMP_PARALLEL(4,); END;');
        EXCEPTION
            WHEN OTHERS
            THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);

    END; 

However, I always get the Error PLS-00201: identifier 'UTL_RECOMP.RECOMP_PARALLEL' must be declared I am logged in as sys/sysdba user. That's not the problem.

Any ideas how to get this working?

Thanks!


Solution

  • Actually it works if the procedure is owned by SYS and you grant EXECUTE privilege to another user (doc says "You must be connected AS SYSDBA to run this script").

    SQL> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
    SQL> show user;
    USER is "SYS"
    SQL> --
    SQL> CREATE OR REPLACE PROCEDURE Compile
      2  IS
      3  BEGIN
      4   SYS.UTL_RECOMP.RECOMP_PARALLEL(4);
      5  END;
      6  /
    
    Procedure created.
    
    SQL> --
    SQL> grant execute on compile to c##test;
    
    Grant succeeded.
    
    SQL> --
    SQL> connect c##test/c##test
    Connected.
    SQL> show user
    USER is "C##TEST"
    SQL> --
    SQL> drop table t purge;
    
    Table dropped.
    
    SQL> create table t(x int);
    
    Table created.
    
    SQL> create or replace procedure p
      2  is
      3  v int;
      4  begin
      5   select x into v from t;
      6  end;
      7  /
    
    Procedure created.
    
    SQL> --
    SQL> show errors
    No errors.
    SQL> --
    SQL> drop table t;
    
    Table dropped.
    
    SQL> --
    SQL> select object_name, object_type, status
      2  from user_objects
      3  where object_name='P';
    
    OBJECT_NAM OBJECT_TYP STATUS
    ---------- ---------- ----------
    P      PROCEDURE  INVALID
    
    SQL> --
    SQL> create table t(x int);
    
    Table created.
    
    SQL> --
    SQL> select object_name, object_type, status
      2  from user_objects
      3  where object_name='P';
    
    OBJECT_NAM OBJECT_TYP STATUS
    ---------- ---------- ----------
    P      PROCEDURE  INVALID
    
    SQL> --
    SQL> exec sys.compile;
    
    PL/SQL procedure successfully completed.
    
    SQL> --
    SQL> select object_name, object_type, status
      2  from user_objects
      3  where object_name='P';
    
    OBJECT_NAM OBJECT_TYP STATUS
    ---------- ---------- ----------
    P      PROCEDURE  VALID
    
    SQL> --
    

    These kind of procedures should be run only by SYS (like utlrp.sql) - so this is for DBA only - as documented to avoid unexpected behaviour.