Possible values
identifiers:all | |
identifiers:none | |
identifiers:public | |
identifiers:sql | |
identifiers:plsql | |
statements:all | |
statements:none | |
The setting for identifiers and statements can be combined:
alter session set plscope_settings = 'identifiers:all, statements:all';
Show current plscope values for PL/SQL objects
select
owner,
name,
type,
plscope_settings
from
dba_plsql_object_settings
where
owner in (select username from dba_users where oracle_maintained = 'N')
order by
owner,
name,
type;
Modify PLSCOPE settings for all PL/SQL objects of a given schema
alter session set plscope_settings = 'identifiers:all, statements:all';
begin
for p in (
select
owner,
object_type,
object_name
from
all_objects
where
owner = user and
object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION', 'TYPE')
) loop
begin
execute immediate 'alter ' || p.object_type || ' ' || p.owner || '.' || p.object_name || ' compile';
exception when others then
if sqlcode = -24344 then -- ORA-24344: success with compilation error
dbms_output.put_line('Ok with error: ' || p.object_type || ' ' || p.object_name);
end if;
end;
end loop;
end;
/