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