Oracle – Code source des vues

Exemple de récupération du code source de la vue ALL_PROCEDURES (version 11.2.0.4.0) :

set pagesize 100
set linesize 200
set long 50000
select text from dba_views where view_name = 'ALL_PROCEDURES';

TEXT
--------------------------------------------------------------------------------
(select u.name, o.name, pi.procedurename, o.obj#, pi.procedure#,
decode(pi.overload#, 0, NULL, pi.overload#),
decode(o.type#, 7, 'PROCEDURE',
 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY',
 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
 22, 'LIBRARY', 28, 'JAVA SOURCE', 29, 'JAVA CLASS',
 30, 'JAVA RESOURCE', 87, 'ASSEMBLY', 'UNDEFINED'),
decode(bitand(pi.properties,8),8,'YES','NO'),
decode(bitand(pi.properties,16),16,'YES','NO'),
u2.name, o2.name,
 decode(bitand(pi.properties,32),32,'YES','NO'),
 decode(bitand(pi.properties,512),512,'YES','NO'),
decode(bitand(pi.properties,256),256,'YES','NO'),
decode(bitand(pi.properties,1024),1024,'CURRENT_USER','DEFINER')
from sys."_CURRENT_EDITION_OBJ" o, user$ u, procedureinfo$ pi,
 sys."_CURRENT_EDITION_OBJ" o2, user$ u2
where u.user# = o.owner# and o.obj# = pi.obj#
and (o.type# in (7, 8, 9, 11, 12, 14, 22, 28, 29, 30, 87) or
 (o.type# = 13 and o.subname is null))
and pi.itypeobj# = o2.obj# (+) and o2.owner# = u2.user# (+)
and (o.owner# = userenv('SCHEMAID')
 or exists
 (select null from v$enabledprivs where priv_number in (-144,-141))
 or o.obj# in (select obj# from sys.objauth$ where grantee# in
 (select kzsrorol from x$kzsro) and privilege# = 12)))
union all
(select tabobj.owner, tabobj.object_name, NULL,
 tabobj.object_id,
 case tabobj.object_type
 when 'TRIGGER' then 1
 else 0
 end,
 NULL, tabobj.object_type, 'NO', 'NO', NULL, NULL, 'NO', 'NO', 'NO',
 case tabobj.object_type
 WHEN 'TRIGGER' then 'DEFINER'
 else
 case pi.properties
 WHEN NULL then NULL
 else
 decode(bitand(pi.properties,1024),
 NULL, NULL,
 1024,'CURRENT_USER',
 'DEFINER')
 end
 end case
 from all_objects tabobj, procedureinfo$ pi
 where
 ((tabobj.object_id = pi.obj# (+)) AND
 (tabobj.object_type IN ('TRIGGER', 'PACKAGE')) AND
 ((pi.procedure# is null) OR (pi.procedure# = 1))))