A Database Wordfile…

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

Marco Gralike Written by: