It is not often that something like the following happens on Google while searching for a topic (-undocumented word removed-)…in this case database XML/ACL related.
Only one link to word file, created by Tanel.
Okay, I was busy with some deep-dive-searching in internal Oracle database stuff, so chances are that there is not even a hit on Google, but, apparently, such a “wordfile” like Tanel (a while ago I guess created) can be useful…
…(also as I reminder to myself) a small (XML) addition/alteration on the statement used by Tanel…
--
CREATE OR REPLACE VIEW DB_TP_WORDLIST_VW
AS
SELECT NAME
FROM
(SELECT UNIQUE
name,
origin
FROM
( SELECT lower(keyword) name, '1' origin FROM SYS.v_$reserved_words
UNION ALL
SELECT upper(table_name) name, '2' origin FROM dict
UNION ALL
SELECT upper(column_name) name, '3' origin FROM dict_columns
UNION ALL
SELECT object_name name, '4' origin FROM dba_objects
UNION ALL
SELECT upper(object_name||'.'||procedure_name) name,'5' origin FROM dba_procedures
UNION ALL
SELECT '"'||table_name||'"."'||column_name||'"' name, '6' origin FROM dba_tab_columns
UNION ALL
SELECT ksppinm name, '7' origin FROM SYS.x$ksppi
UNION ALL
SELECT name, '8' origin FROM v$sql_hint
UNION ALL
SELECT view_name name, '9' origin FROM v$fixed_view_definition where view_name NOT IN (SELECT object_name name FROM dba_objects where object_type = 'VIEW') and view_name not in (SELECT upper(table_name) name FROM dict)
UNION ALL
select name, '10' origin from v$fixed_table
UNION ALL
SELECT t.DisplayName name, '666' origin FROM XDB.XDB$RESOURCE r, XMLTable( xmlNamespaces ( DEFAULT 'http://xmlns.oracle.com/xdb/XDBResource.xsd' ),'$p/Resource' PASSING r.object_value as "p" COLUMNS DisplayName PATH 'DisplayName', Creator PATH 'Creator', ContentType PATH 'ContentType' ) t where t.contenttype <> 'application/octet-stream'
UNION ALL
SELECT unique t.cname name, '667' origin FROM XDB.XDB$CONFIG c, XMLTable( xmlNamespaces ( DEFAULT 'http://xmlns.oracle.com/xdb/XDBConfig.xsd' ), '$p//*' PASSING c.object_value as "p" COLUMNS cname VARCHAR2(128) PATH 'name()' ) t
)
WHERE LENGTH(name) > 2
ORDER BY origin,
nls_upper(name,'NLS_SORT=generic_m')
);
--
SQL statement and output:
HTH/M
