Skip to Main Content
  • Questions
  • How to Compile Code on all Connections

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jerome.

Asked: July 31, 2017 - 1:48 am UTC

Last updated: July 31, 2017 - 9:48 am UTC

Version: Version 4.0.0.13

Viewed 1000+ times

You Asked

Hi,

Scenario: We are using 6 different schemas and we are compiling codes on all of them. So first, I connect to those schemas, then when I need to compile my codes, what i do is: Select Schema, Compile, Repeat.

I was wondering if there's a technique where I can compile my codes on all connected connections (or a series of selected connected connections).
I tried searching it in google, stackoverflow, and asktom.oracle.com, i found nothing.

Thanks in advance.



Regards,
Lazy Programmer

and Chris said...

You mean you want to compile all the (PL/SQL) objects in several schemas in your database?

If so you can do this by calling dbms_utility.compile_schema. As the name implies, this will compile all the objects in the specified schema:

select user from dual;

USER                          
------------------------------
CHRIS

create table t (
  x int 
);

grant create session to u identified by u;
grant select on t to u;

create or replace procedure u.p is
begin
  for rws in (
    select * from chris.t
  ) loop
    null;
  end loop;
end p;
/

alter table t add ( y int );

select object_name, status from all_objects
where  owner = 'U';

OBJECT_NAME                    STATUS 
------------------------------ -------
P                              INVALID

exec dbms_utility.compile_schema('U');
      
select object_name, status from all_objects
where  owner = 'U';

OBJECT_NAME                    STATUS 
------------------------------ -------
P                              VALID  


You can use the compile_all parameter to say if you want to compile everything (the default) or only the invalid objects (pass false). Note you need privileges to compile objects in the schema in question!

http://docs.oracle.com/database/122/ARPLS/DBMS_UTILITY.htm#GUID-A279BC5C-6CF5-4467-931D-0917C984DAB2

Or, if you have access to the DB server, you can recompile all objects in the database by running $ORACLE_HOME/rdbms/admin/utlrp.sql.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database