REM dbdrv:none

REM
REM $Header: adgrants.sql 115.54 2007/01/13 00:43:46 hxue ship $
REM +-------------------------------------------------------------------------+
REM |
REM | Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
REM | All rights reserved.
REM |
REM | NAME
REM | adgrants.sql
REM |
REM | DESCRIPTION
REM |
REM | SQL script to grant necessary privileges on selected SYS
REM | objects when the Init.ora parameter O7_DICTIONARY_ACCESSIBILITY
REM | is set to FALSE.
REM | Also this script calls profload.sql and proftab.sql which
REM | create PL/SQL profiler objects.
REM |
REM | This script is for Unix only!
REM |
REM | USAGE
REM |
REM | sqlplus '/ as sysdba' @adgrants.sql <APPLSYS schema name>
REM |
REM | NOTES
REM |
REM | This SQL script must be run as SYS user, from the ORACLE_HOME
REM | on the Database Server.
REM |
REM | ARGUMENTS
REM | You must pass the following argument.
REM | APPLSYS schema name.
REM +-------------------------------------------------------------------------+
set verify off;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;

REM
REM this connect will generate an ORACLE error and cause the
REM script to exit if they don't have privileges to run as SYS
REM

connect / as sysdba

whenever sqlerror continue;

select '--- adgrants.sql started at '||

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
from dual;

REM
REM Create PL/SQL profiler objects.
REM Bug: 3448207.CREATE PL/SQL PROFILER OBJECTS IN 11.5.10 RELEASE.
REM

prompt
prompt Creating PL/SQL profiler objects.
prompt

select '--- profload.sql started at '||

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
from dual;

@?/rdbms/admin/profload.sql

select '--- profload.sql completed at '||

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
from dual;

select '--- proftab.sql started at '||

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
from dual;

@?/rdbms/admin/proftab.sql

select '--- profltab.sql completed at '||

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||' ---' " "
from dual;

REM
REM End of Creating PL/SQL profiler objects.
REM

commit

set termout on

DECLARE

type TableNameType is table of varchar2(100)

index by binary_integer;

object_list TableNameType;
grant_type_list TableNameType;

list_count number;

--
-- Procedure
-- add_list
--
-- Purpose
-- Create tables which are used later by the procedure give_grants.
--
-- Arguments
-- X_sys_object name of SYS object
-- X_grant_type type of grants such as select , execute etc.
--
-- Example
-- none
procedure add_list
( X_sys_object in varchar2 ,
X_grant_type in varchar2 )
is

begin

list_count := list_count + 1 ;
object_list(list_count) := upper (X_sys_object );
grant_type_list(list_count) := upper(X_grant_type);

exception
when others then
raise_application_error(-20000, sqlerrm || 'Error in ad_grants.add_list');

end add_list;

procedure load_table_list
is

begin

list_count := 0;
--
-- SYS tables on which Apps needs privileges
--
-- (please keep in alphabetical order)
--
add_list('AD_EXTENTS','SELECT');
add_list('ALL_COL_COMMENTS','SELECT');
add_list('ALL_IND_COLUMNS','SELECT');
add_list('ALL_OBJECTS','SELECT');
add_list('ALL_SEQUENCES','SELECT');
add_list('ALL_SOURCE','SELECT');
add_list('ALL_TABLES','SELECT');
add_list('ALL_TAB_COLUMNS','SELECT');
add_list('ARGUMENT$','SELECT');
add_list('DBA_CONSTRAINTS','SELECT');
add_list('DBA_CONS_COLUMNS','SELECT');
add_list('DBA_CONTEXT','SELECT');
add_list('DBA_DATA_FILES','SELECT');
add_list('DBA_DEPENDENCIES','SELECT');
add_list('DBA_ERRORS','SELECT');
add_list('DBA_EXTENTS','SELECT');
-- Bug 4748813 VPALAKUR, 21-NOV-2005
add_list('DBA_EXTERNAL_TABLES','SELECT');
add_list('DBA_FREE_SPACE','SELECT');
add_list('DBA_INDEXES','SELECT');
add_list('DBA_IND_COLUMNS','SELECT');
add_list('DBA_IND_EXPRESSIONS','SELECT');
add_list('DBA_IND_PARTITIONS','SELECT');
add_list('DBA_JOBS','SELECT');
add_list('DBA_LOB_PARTITIONS','SELECT');
add_list('DBA_LOBS','SELECT');
add_list('DBA_MVIEWS','SELECT');
add_list('DBA_MVIEW_DETAIL_RELATIONS','SELECT');
add_list('DBA_MVIEW_LOGS','SELECT');
add_list('DBA_OBJECTS','SELECT');
add_list('DBA_PART_INDEXES','SELECT');
--Bug 3696123, CBHATI, 17-JUN-04
add_list('DBA_PART_KEY_COLUMNS','SELECT');
add_list('DBA_PART_TABLES','SELECT');
add_list('DBA_POLICIES','SELECT');
add_list('DBA_QUEUES','SELECT');
add_list('DBA_QUEUE_TABLES','SELECT');
--Bug 3651346, HXUE, 08-JUN-04
add_list('DBA_QUEUE_SCHEDULES','SELECT');
add_list('DBA_REGISTERED_SNAPSHOTS','SELECT');
add_list('DBA_ROLE_PRIVS','SELECT');
add_list('DBA_ROLLBACK_SEGS','SELECT');
add_list('DBA_RSRC_CONSUMER_GROUPS','SELECT');
add_list('DBA_RSRC_CONSUMER_GROUP_PRIVS','SELECT');
add_list('DBA_RSRC_MANAGER_SYSTEM_PRIVS','SELECT');
add_list('DBA_RSRC_PLANS','SELECT');
add_list('DBA_RSRC_PLAN_DIRECTIVES','SELECT');
add_list('DBA_SEGMENTS','SELECT');
add_list('DBA_SEQUENCES','SELECT');
add_list('DBA_SNAPSHOTS','SELECT');
add_list('DBA_SNAPSHOT_LOGS','SELECT');
add_list('DBA_SOURCE','SELECT');
add_list('DBA_SYNONYMS','SELECT');
add_list('DBA_SYS_PRIVS','SELECT');
add_list('DBA_TABLES','SELECT');
add_list('DBA_TABLESPACES','SELECT');
add_list('DBA_TAB_COLUMNS','SELECT');
add_list('DBA_TAB_HISTOGRAMS','SELECT');
add_list('DBA_TAB_MODIFICATIONS','SELECT');
add_list('DBA_TAB_PARTITIONS','SELECT');
add_list('DBA_TAB_PRIVS','SELECT');
add_list('DBA_TEMP_FILES','SELECT');
add_list('DBA_TRIGGERS','SELECT');
add_list('DBA_TS_QUOTAS','SELECT');
add_list('DBA_TYPES','SELECT');
add_list('DBA_TYPE_ATTRS','SELECT');
add_list('DBA_USERS','SELECT');
add_list('DBA_VIEWS','SELECT');
--Bug 3777732, HXUE, 19-JUL-04
add_list('DBMS_UPG_CURRENT_STATUS','SELECT');
add_list('DBMS_UPG_DEBUG','SELECT');
add_list('DBMS_UPG_STATUS','SELECT');
add_list('DBMS_UPG_STATUS$','SELECT');
add_list('DEPENDENCY$','SELECT');
add_list('DUAL','SELECT');
add_list('ERROR$','SELECT');
add_list('EXPDEPOBJ$','SELECT');
add_list('GV_$INSTANCE','SELECT');
add_list('GV_$PROCESS','SELECT');
add_list('GV_$SESSION','SELECT');
add_list('GV_$SESSION_EVENT','SELECT');
add_list('GV_$SYSTEM_PARAMETER','SELECT');
add_list('GV_$AQ','SELECT');
add_list('GV_$LOGFILE','SELECT');
add_list('IND$','SELECT');
add_list('OBJAUTH$','SELECT');
 add_list('PLSQL_PROFILER_DATA','SELECT');
add_list('PLSQL_PROFILER_DATA','INSERT');
add_list('PLSQL_PROFILER_DATA','UPDATE');
add_list('PLSQL_PROFILER_DATA','DELETE');
add_list('PLSQL_PROFILER_RUNS','SELECT');
add_list('PLSQL_PROFILER_RUNS','INSERT');
add_list('PLSQL_PROFILER_RUNS','UPDATE');
add_list('PLSQL_PROFILER_RUNS','DELETE');
add_list('PLSQL_PROFILER_RUNNUMBER','SELECT');
add_list('PLSQL_PROFILER_UNITS','SELECT');
add_list('PLSQL_PROFILER_UNITS','INSERT');
add_list('PLSQL_PROFILER_UNITS','UPDATE');
add_list('PLSQL_PROFILER_UNITS','DELETE');
add_list('PROCEDURE$','SELECT');
add_list('PRODUCT_COMPONENT_VERSION','SELECT');
add_list('SEG$','SELECT');
add_list('SOURCE$','SELECT');
add_list('TAB$','SELECT');
add_list('TRIGGER$','SELECT');
add_list('TS$','SELECT');
add_list('UNDO$','SELECT');
add_list('USER$','SELECT');
add_list('USER_OBJECTS','SELECT');
add_list('USER_TAB_COLUMNS','SELECT');
add_list('V_$DATABASE','SELECT');
add_list('V_$INSTANCE','SELECT');
add_list('V_$LOCK','SELECT');
add_list('V_$MYSTAT','SELECT');
add_list('V_$NLS_PARAMETERS','SELECT');
add_list('V_$PARAMETER','SELECT');
add_list('V_$PARAMETER2','SELECT');
add_list('V_$PQ_SYSSTAT','SELECT');
add_list('V_$PROCESS','SELECT');
add_list('V_$ROLLSTAT','SELECT');
add_list('V_$ROWCACHE','SELECT');
add_list('V_$SESSION','SELECT');
add_list('V_$SESSTAT','SELECT');
add_list('V_$STATNAME','SELECT');
add_list('V_$SQLAREA','SELECT');
add_list('V_$SQLTEXT','SELECT');
add_list('V_$SYSTEM_PARAMETER','SELECT');
add_list('V_$THREAD','SELECT');
add_list('V_$TRANSACTION','SELECT');
add_list('V_$TYPE_SIZE','SELECT');
add_list('V_$VERSION','SELECT');
--
-- SYS packages on which Apps needs privileges
--
-- (please keep in alphabetical order)
--
add_list('DBMS_ALERT','EXECUTE');
add_list('DBMS_APPLICATION_INFO','EXECUTE');
add_list('DBMS_AQ','EXECUTE');
add_list('DBMS_AQADM','EXECUTE');
add_list('DBMS_AW','EXECUTE');
add_list('DBMS_DESCRIBE','EXECUTE');
add_list('DBMS_JAVA','EXECUTE');
add_list('DBMS_JOB','EXECUTE');
add_list('DBMS_LOB','EXECUTE');
 add_list('DBMS_LOCK','EXECUTE');
add_list('DBMS_MVIEW','EXECUTE');
add_list('DBMS_OBFUSCATION_TOOLKIT','EXECUTE');
add_list('DBMS_OUTPUT','EXECUTE');
add_list('DBMS_PIPE','EXECUTE');
add_list('DBMS_PROFILER','EXECUTE');
add_list('DBMS_RANDOM','EXECUTE');
add_list('DBMS_REPCAT','EXECUTE');
add_list('DBMS_RLS','EXECUTE');
--Bug 3777732, HXUE, 19-JUL-04
add_list('DBMS_SCHEMA_COPY','EXECUTE');
add_list('DBMS_SESSION','EXECUTE');
add_list('DBMS_SQL','EXECUTE');
add_list('DBMS_SPACE','EXECUTE');
add_list('DBMS_SPACE_ADMIN','EXECUTE');
add_list('DBMS_STATS','EXECUTE');
add_list('DBMS_SYSTEM','EXECUTE');
add_list('DBMS_SYS_SQL','EXECUTE');
add_list('DBMS_TRANSACTION','EXECUTE');
add_list('DBMS_UTILITY','EXECUTE');
add_list('DBMS_XMLGEN','EXECUTE');
add_list('DBMS_XMLQUERY','EXECUTE');
add_list('DBMS_XMLSAVE','EXECUTE');
add_list('UTL_RECOMP','EXECUTE');
--
-- End of list marker (do not delete)
--
add_list(null,null);

exception
when others then
raise;
end load_table_list;

--
-- Procedure
-- give_grants.
--
-- Purpose
-- Grants necessary preveliges on sys objects by using the data stored in
-- the tables schema_list , object_list , grant_type_list and
-- with_grant_option_list
--
-- Arguments
-- none
-- Example
-- none
--

procedure give_grants
is
i number;
--Bug:3457610:sshivara.
cursor schema_list is
select ORACLE_USERNAME from &&1..FND_ORACLE_USERID FOU ,DBA_USERS DU
where FOU.ORACLE_USERNAME = DU.USERNAME and
FOU.READ_ONLY_FLAG in ('U', 'K' )
union select 'SYSTEM' from dual;
 object_exists exception;
object_not_exists exception;
plsql_not_exists exception;

pragma exception_init(object_exists, -955);

pragma exception_init(plsql_not_exists, -4042);
pragma exception_init(object_not_exists, -942);

begin

load_table_list;

for c_schema in schema_list loop

i := 1;
while object_list(i) is not null loop

if( c_schema.ORACLE_USERNAME = 'SYSTEM' ) or (object_list(i) = 'DUAL') or

(c_schema.ORACLE_USERNAME = 'APPS' and object_list(i) = 'DBA_USERS') or
(c_schema.ORACLE_USERNAME = 'APPS' and object_list(i) = 'GV_$SESSION')
then
begin
execute immediate 'grant ' || grant_type_list(i) || ' on ' ||'SYS.'||
object_list(i) || ' to ' || c_schema.ORACLE_USERNAME || ' with grant option ' ;

exception
when object_exists then null;
when object_not_exists then null;
when plsql_not_exists then null;
when others then
raise_application_error(-20000, sqlerrm ||'Error in
ad_grants.Give_grants');

end;

else
begin
execute immediate 'grant ' || grant_type_list(i) || ' on ' ||'SYS.'||
object_list(i) || ' to ' || c_schema.ORACLE_USERNAME ;

exception
when object_exists then null;
when object_not_exists then null;
when plsql_not_exists then null;
when others then
raise_application_error(-20000, sqlerrm ||'Error in
ad_grants.Give_grants');

end;

end if;

i := i + 1;

end loop; -- End object_list loop

end loop; -- End Schema loop

-- Give grants on special objects to special schemas.

 -- Hardcoding the execute statements since such requests
-- should be rare.

-- Request1, Bug 3658242, sgadag, 04-JUN-04

begin
execute immediate 'grant EXECUTE on DBMS_LOCK to CTXSYS';
exception
when others then
null;
end;

begin
execute immediate 'grant EXECUTE on DBMS_PIPE to CTXSYS';
exception
when others then
null;
end;

begin
execute immediate 'grant EXECUTE on DBMS_REGISTRY to CTXSYS';
exception
when others then
null;
end;

-- Request2, Bug 5630057, vlim, 08-JAN-07

-- this change is only required for 11i and not for R12

begin
execute immediate
'grant EXECUTE on AQ$_JMS_TEXT_MESSAGE to APPLSYS with GRANT OPTION';
exception
when others then
null;
end;

end give_grants;

--
-- For bug 3447980, create a view using the huge select tested by
-- APPS Performance team. This is one of the steps in replacing the
-- Rule optimization in ad_parallel_updates_pkg() package.
--

procedure create_ad_extents
is

l_stmt varchar2(20000);

begin

l_stmt := 'create or replace view ad_extents as

SELECT owner,
segment_name,
partition_name,
segment_type,
data_object_id,
 relative_fno, block_id, blocks
from
(select
ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
e.block# BLOCK_ID,
e.length BLOCKS, e.file# RELATIVE_FNO,
ds.DATA_OBJECT_ID
from sys.uet$ e,
( select
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select
''TABLE'' OBJECT_TYPE,
2 OBJECT_TYPE_ID,
5 SEGMENT_TYPE_ID,
t.obj# OBJECT_ID,
t.file# HEADER_FILE,
t.block# HEADER_BLOCK,
t.ts# TS_NUMBER
from sys.tab$ t
where bitand(t.property, 1024) = 0
and bitand(t.property, 8192) != 8192
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
UNION ALL
select /*+ USE_NL(U O SO) */
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select /*+ INDEX(TP) */
''TABLE PARTITION'' OBJECT_TYPE,
 19 OBJECT_TYPE_ID,
5 SEGMENT_TYPE_ID,
tp.obj# OBJECT_ID,
tp.file# HEADER_FILE,
tp.block# HEADER_BLOCK,
tp.ts# TS_NUMBER
from sys.tabpart$ tp
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
UNION ALL
select
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select /*+ INDEX(TSP) */
''TABLE SUBPARTITION'' OBJECT_TYPE,
34 OBJECT_TYPE_ID,
5 SEGMENT_TYPE_ID,
tsp.obj# OBJECT_ID,
tsp.file# HEADER_FILE,
tsp.block# HEADER_BLOCK,
tsp.ts# TS_NUMBER
from sys.tabsubpart$ tsp
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
) ds, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and bitand(NVL(ds.segment_flags,0), 1) = 0
 union all
select /*+ ordered use_nl(e) use_nl(f) */
ds.owner, ds.segment_name, ds.partition_name, ds.segment_type,
e.ktfbuebno BLOCK_ID,
e.ktfbueblks BLOCKS, e.ktfbuefno RELATIVE_FNO,
ds.DATA_OBJECT_ID
from (
select
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select
''TABLE'' OBJECT_TYPE,
2 OBJECT_TYPE_ID,
5 SEGMENT_TYPE_ID,
t.obj# OBJECT_ID,
t.file# HEADER_FILE,
t.block# HEADER_BLOCK,
t.ts# TS_NUMBER
from sys.tab$ t
where bitand(t.property, 1024) = 0
and bitand(t.property, 8192) != 8192
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
UNION ALL
select /*+ USE_NL(U O SO) */
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select /*+ INDEX(TP) */
''TABLE PARTITION'' OBJECT_TYPE,
19 OBJECT_TYPE_ID,
 5 SEGMENT_TYPE_ID,
tp.obj# OBJECT_ID,
tp.file# HEADER_FILE,
tp.block# HEADER_BLOCK,
tp.ts# TS_NUMBER
from sys.tabpart$ tp
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
UNION ALL
select
u.name OWNER, o.name SEGMENT_NAME, o.subname PARTITION_NAME,
so.object_type SEGMENT_TYPE, ts.ts# TABLESPACE_ID, s.block#
HEADER_BLOCK, s.file# RELATIVE_FNO,
NVL(s.spare1,0) SEGMENT_FLAGS,
o.dataobj# DATA_OBJECT_ID
from
sys.user$ u,
sys.obj$ o,
sys.ts$ ts,
sys.seg$ s,
sys.file$ f,
(
select /*+ INDEX(TSP) */
''TABLE SUBPARTITION'' OBJECT_TYPE,
34 OBJECT_TYPE_ID,
5 SEGMENT_TYPE_ID,
tsp.obj# OBJECT_ID,
tsp.file# HEADER_FILE,
tsp.block# HEADER_BLOCK,
tsp.ts# TS_NUMBER
from sys.tabsubpart$ tsp
) so
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
) ds,
sys.x$ktfbue e,
sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and e.ktfbuesegtsn = f.ts#
and e.ktfbuefno = f.relfile#
 and bitand(NVL(ds.segment_flags, 0), 1) = 1 )';

execute immediate l_stmt;

exception
when others then
raise_application_error(-20000, sqlerrm ||': ' ||
'Error in creating view');

end create_ad_extents;

BEGIN
begin
create_ad_extents;
commit;
exception
when others then
raise;
end;

begin
give_grants;
commit;
exception
when others then
raise;
end;

END;
/

begin
execute immediate 'revoke all on sys.dual from public';
execute immediate 'grant select on sys.dual to public';
exception
when others then
raise;
end;
/
COMMIT
/
EXIT;