How to run UTL_RECOMP.RECOMP_PARALLEL from a procedure?

Question

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!

From the documentation - "This package must be run using SQL*PLUS." — OldProgrammer, Apr 10 '20 at 02:10

score 1 · Accepted Answer · answered Apr 10 '20 at 16:47

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.

score 0 · Answer 2 · answered Apr 10 '20 at 07:02

0

You can use it in a stored procedure:

SQL> set serveroutput on
SQL> --
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   UTL_RECOMP.RECOMP_PARALLEL(4);
  5   EXCEPTION
  6     WHEN OTHERS
  7     THEN
  8     DBMS_OUTPUT.PUT_LINE(SQLERRM);
  9  END;
 10  /

Procedure created.

SQL> --
SQL> show errors
No errors.
SQL> --
SQL> set timing on
SQL> exec compile;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> --
SQL> exit

answered Apr 10 '20 at 07:02

pifor

7,419
2
8
16

Its not working for me. Just the same Error. But maybe its due to Procedure which is inside a package. Sorry, I forgot to say this... THe procedure itself is inside a package which is not in the SYS Schema, but in ontehr one. But I am connected as sys/sysdba – digestBen Apr 10 '20 at 09:41
So, i thing i have found the Reason for this problem. I need to grant the rights to the other Schema which contains the Package to execute this sys.Utl_recomp. GRANT EXECUTE ON SYS.UTL_RECOMP TO **YOUR_SCHEMA** – digestBen Apr 10 '20 at 10:05
I just checked that this is not really working. I have no errors but id does nothing. If I just put **SYS.UTL_RECOMP.RECOMP_PARALLEL(4)** and execute this proceure, nothing happens. Objects are not compiled. Any other ideas or approaches? – digestBen Apr 10 '20 at 15:19

How to run UTL_RECOMP.RECOMP_PARALLEL from a procedure?

2 Answers2