rem rem $Header: catrpc.sql,v 1.5 1995/03/16 12:20:49 mramache Exp $ rem Rem Copyright (c) 1991, 1996 by Oracle Corporation Rem NAME Rem catrpc.sql Rem DESCRIPTION Rem Creates internal views for RPC. Rem Rem These views are needed only for databases with the procedural option Rem that are accessed by remote databases Rem Rem NOTES Rem Must be run as SYS. Rem MODIFIED (MM/DD/YY) Rem mramache 09/09/96 - Add obj# to KGLR7_IDL views Rem mramache 02/17/95 - merge changes from branch 1.4.720.1 Rem mramache 02/02/95 - bug 256246 - tuned ORA_KGLR7_DEPENDENCIES Rem jbellemo 12/17/93 - merge changes from branch 1.3.710.1 Rem jbellemo 11/09/93 - #170173: change uid to userenv schemaid Rem jwijaya 02/22/93 - merge changes from branch 1.2.312.1 Rem jwijaya 02/12/93 - heterogeneous/nls rpc bug Rem tpystyne 10/28/92 - use create or replace view Rem glumpkin 10/20/92 - Renamed from KGLR.SQL Rem jwijaya 09/09/92 - also select from v$fixed_table Rem jwijaya 07/17/92 - remove database link owner from name Rem jwijaya 02/20/92 - v$enabledroles -> x$kzsro Rem jwijaya 10/18/91 - support portable IDL Rem jwijaya 06/19/91 - add ORA_KGLR7_DB_LINKS Rem jwijaya 05/09/91 - Creation Rem create or replace view ORA_KGLR7_OBJECTS (OWNER, NAME, LINK_NAME, OWNER_ID, OBJECT_ID, TYPE) as select decode(o.linkname, null, u.name, o.remoteowner), o.name, o.linkname, u.user#, o.obj#, o.type from sys.obj$ o, sys.user$ u where o.owner# = u.user# and (o.namespace = 1 /* TABLE/PROCEDURE */ or o.namespace = 2 /* BODY */ and o.owner# = userenv('SCHEMAID')) union select 'SYS', name, null, 0, object_id, decode(type, 'TABLE', 2, 'VIEW', 4, 2) from sys.v$fixed_table / create or replace view ORA_KGLR7_DEPENDENCIES (OWNER, NAME, TYPE, PARENT_OWNER, PARENT_NAME, PARENT_TYPE, PARENT_LINK_NAME, PARENT_TIMESTAMP, ORDER_NUMBER, OBJ#) as select u.name, o.name, o.type, decode(o2.linkname, null, u2.name, o2.remoteowner), o2.name, o2.type, o2.linkname, d.p_timestamp, d.order#, o.obj# from sys.obj$ o, sys.dependency$ d, sys.user$ u, sys.obj$ o2, sys.user$ u2 where o.obj# = d.d_obj# and o.owner# = u.user# and o.status = 1 /* VALID/AUTHORIZED WITHOUT ERRORS */ and (o2.obj# = d.p_obj# and o2.owner# = u2.user# and (o2.namespace = 1 /* TABLE/PROCEDURE */ or o2.namespace = 2 /* BODY */ and o2.owner# = userenv('SCHEMAID'))) and (o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */) or (o.namespace = 1 /* TABLE/PROCEDURE */ and (o.obj# in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# = 12 /* EXECUTE */) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# = -144 /* EXECUTE ANY PROCEDURE */)))) union select u.name, o.name, o.type, 'SYS', po.name, decode(po.type, 'TABLE', 2, 'VIEW', 4, 2), null, d.p_timestamp, d.order#, o.obj# from sys.obj$ o, sys.v$fixed_table po, sys.dependency$ d, sys.user$ u where o.obj# = d.d_obj# and o.owner# = u.user# and o.status = 1 /* VALID/AUTHORIZED WITHOUT ERRORS */ and po.object_id = d.p_obj# and (o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */) or (o.namespace = 1 /* TABLE/PROCEDURE */ and (o.obj# in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# = 12 /* EXECUTE */) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# = -144 /* EXECUTE ANY PROCEDURE */)))) / drop public synonym ORA_KGLR7_DEPENDENCIES / create public synonym ORA_KGLR7_DEPENDENCIES for ORA_KGLR7_DEPENDENCIES / grant select on ORA_KGLR7_DEPENDENCIES to PUBLIC with grant option / create or replace view ORA_KGLR7_IDL_UB1 (OWNER, NAME, TYPE, PART, VERSION, PIECE#, LENGTH, PIECE, OBJ#) as select /*+ index(i i_idl_ub11) +*/ o.owner, o.name, o.type, i.part, i.version, i.piece#, i.length, i.piece, o.object_id from sys.ora_kglr7_objects o, sys.idl_ub1$ i where o.object_id = i.obj# and (o.type = 5 /* SYNONYM */ or o.owner_id in (userenv('SCHEMAID'), 1 /* PUBLIC */) or o.object_id in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# in (3 /* DELETE */, 6 /* INSERT */, 7 /* LOCK */, 9 /* SELECT */, 10 /* UPDATE */, 12 /* EXECUTE */)) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and (o.type in (7 /* PROCEDURE */, 8 /* FUNCTION */, 9 /* PACKAGE */) and privilege# = -144 /* EXECUTE ANY PROCEDURE */ or o.type in (2 /* TABLE */, 4 /* VIEW */) and privilege# in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) or o.type = 6 /* SEQUENCE */ and privilege# = -109 /* SELECT ANY SEQUENCE */))) / drop public synonym ORA_KGLR7_IDL_UB1 / create public synonym ORA_KGLR7_IDL_UB1 for ORA_KGLR7_IDL_UB1 / grant select on ORA_KGLR7_IDL_UB1 to PUBLIC with grant option / create or replace view ORA_KGLR7_IDL_CHAR (OWNER, NAME, TYPE, PART, VERSION, PIECE#, LENGTH, PIECE, OBJ#) as select /*+ index(i i_idl_char1) +*/ o.owner, o.name, o.type, i.part, i.version, i.piece#, i.length, i.piece, o.object_id from sys.ora_kglr7_objects o, sys.idl_char$ i where o.object_id = i.obj# and (o.type = 5 /* SYNONYM */ or o.owner_id in (userenv('SCHEMAID'), 1 /* PUBLIC */) or o.object_id in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# in (3 /* DELETE */, 6 /* INSERT */, 7 /* LOCK */, 9 /* SELECT */, 10 /* UPDATE */, 12 /* EXECUTE */)) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and (o.type in (7 /* PROCEDURE */, 8 /* FUNCTION */, 9 /* PACKAGE */) and privilege# = -144 /* EXECUTE ANY PROCEDURE */ or o.type in (2 /* TABLE */, 4 /* VIEW */) and privilege# in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) or o.type = 6 /* SEQUENCE */ and privilege# = -109 /* SELECT ANY SEQUENCE */))) / drop public synonym ORA_KGLR7_IDL_CHAR / create public synonym ORA_KGLR7_IDL_CHAR for ORA_KGLR7_IDL_CHAR / grant select on ORA_KGLR7_IDL_CHAR to PUBLIC with grant option / create or replace view ORA_KGLR7_IDL_UB2 (OWNER, NAME, TYPE, PART, VERSION, PIECE#, LENGTH, PIECE, OBJ#) as select /*+ index(i i_idl_ub21) +*/ o.owner, o.name, o.type, i.part, i.version, i.piece#, i.length, i.piece, o.object_id from sys.ora_kglr7_objects o, sys.idl_ub2$ i where o.object_id = i.obj# and (o.type = 5 /* SYNONYM */ or o.owner_id in (userenv('SCHEMAID'), 1 /* PUBLIC */) or o.object_id in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# in (3 /* DELETE */, 6 /* INSERT */, 7 /* LOCK */, 9 /* SELECT */, 10 /* UPDATE */, 12 /* EXECUTE */)) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and (o.type in (7 /* PROCEDURE */, 8 /* FUNCTION */, 9 /* PACKAGE */) and privilege# = -144 /* EXECUTE ANY PROCEDURE */ or o.type in (2 /* TABLE */, 4 /* VIEW */) and privilege# in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) or o.type = 6 /* SEQUENCE */ and privilege# = -109 /* SELECT ANY SEQUENCE */))) / drop public synonym ORA_KGLR7_IDL_UB2 / create public synonym ORA_KGLR7_IDL_UB2 for ORA_KGLR7_IDL_UB2 / grant select on ORA_KGLR7_IDL_UB2 to PUBLIC with grant option / create or replace view ORA_KGLR7_IDL_SB4 (OWNER, NAME, TYPE, PART, VERSION, PIECE#, LENGTH, PIECE, OBJ#) as select /*+ index(i i_idl_sb41) +*/ o.owner, o.name, o.type, i.part, i.version, i.piece#, i.length, i.piece, o.object_id from sys.ora_kglr7_objects o, sys.idl_sb4$ i where o.object_id = i.obj# and (o.type = 5 /* SYNONYM */ or o.owner_id in (userenv('SCHEMAID'), 1 /* PUBLIC */) or o.object_id in (select obj# from sys.objauth$ where grantee# in (select kzsrorol from x$kzsro) and privilege# in (3 /* DELETE */, 6 /* INSERT */, 7 /* LOCK */, 9 /* SELECT */, 10 /* UPDATE */, 12 /* EXECUTE */)) or exists (select null from sys.sysauth$ where grantee# in (select kzsrorol from x$kzsro) and (o.type in (7 /* PROCEDURE */, 8 /* FUNCTION */, 9 /* PACKAGE */) and privilege# = -144 /* EXECUTE ANY PROCEDURE */ or o.type in (2 /* TABLE */, 4 /* VIEW */) and privilege# in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) or o.type = 6 /* SEQUENCE */ and privilege# = -109 /* SELECT ANY SEQUENCE */))) / drop public synonym ORA_KGLR7_IDL_SB4 / create public synonym ORA_KGLR7_IDL_SB4 for ORA_KGLR7_IDL_SB4 / grant select on ORA_KGLR7_IDL_SB4 to PUBLIC with grant option / create or replace view ORA_KGLR7_DB_LINKS (OWNER, NAME, USERNAME) as select u.name, l.name, l.userid from sys.link$ l, sys.user$ u where l.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */) and l.owner# = u.user# / drop public synonym ORA_KGLR7_DB_LINKS / create public synonym ORA_KGLR7_DB_LINKS for ORA_KGLR7_DB_LINKS / grant select on ORA_KGLR7_DB_LINKS to PUBLIC with grant option /