updatable view: where the data lives (part 2)

Here is the solution I came up with to solve this puzzle. Hope curious minds would be happy. The script uses dbms metadata related API as well as XMLType/XPath processing combined with the queries against the data dictionary. It's tested on 11.1.0.7/11.2.0.2. It may not work properly on 11.1.0.7 having DB NLS character set set to AL32UTF8. Other multi-byte character sets may have similar issue. Not everything you find in the script is documented however it was too tempting to solve the task using "plain" (or somewhat plain) SQL. :-) Have fun.

REM
REM This script is provided as a possible solution for the puzzle.
REM
REM It is not recommended for production usage.
REM

SET DEFINE "^" VERIFY OFF TAB OFF PAGES 1000 LINES 1000 TRIMSPOOL ON ECHO OFF
DEFINE owner = '^^1'
DEFINE view_name = '^^2'

COLUMN alias FORMAT A30
COLUMN owner FORMAT A15
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN resolved_from FORMAT A20

SELECT alias
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN (
              SELECT table_owner
                FROM dba_synonyms
               WHERE owner = x.owner
                 AND synonym_name = x.table_name
              )
         ELSE owner
       END owner
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN (
              SELECT table_name
                FROM dba_synonyms
               WHERE owner = x.owner
                 AND synonym_name = x.table_name
              )
         ELSE table_name
       END table_name
     , column_name
     , insertable
     , updatable
     , deletable
     , object_type
     , CASE
         WHEN object_type = 'SYNONYM'
         THEN '"' || owner || '"."' || table_name || '"'
       END resolved_from
  FROM (
       SELECT x.*
            , (
              SELECT object_type
                FROM dba_objects
               WHERE owner = x.owner
                 AND object_name = x.table_name
              ) object_type
         FROM (
              SELECT c.column_name alias
                   , COALESCE(
                       x.owner
                     , (
                       SELECT '^^owner'
                         FROM dba_objects
                        WHERE owner = '^^owner'
                          AND object_name = x.table_name
                          AND object_type <> 'TRIGGER'
                       )
                     , (
                       SELECT table_owner
                         FROM dba_synonyms
                        WHERE owner = 'PUBLIC'
                          AND synonym_name = x.table_name
                       )
                     , NVL2(x.table_name, '^^owner', '')
                     ) owner
                   , x.table_name
                   , x.column_name
                   , c.insertable
                   , c.updatable
                   , c.deletable
                FROM (
                     SELECT (
                            SELECT column_name
                              FROM dba_tab_cols
                             WHERE table_name = '^^view_name'
                               AND owner = '^^owner'
                               AND column_id = rn
                            ) column_alias
                          , owner
                          , table_name
                          , column_name
                       FROM (
                            SELECT ROWNUM rn
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/SCHEMA'
                                   ) owner
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/TABLE'
                                   ) table_name
                                 , EXTRACTVALUE(
                                     column_value
                                   , '/SELECT_LIST_ITEM/COLUMN_REF/COLUMN'
                                   ) column_name
                              FROM TABLE(
                                     XMLSequence(
                                       EXTRACT(
                                         sys.dbms_metadata_util.parse_query(
                                           '^^owner'
                                         , dbms_xmlgen.convert(
                                             XMLType.GetClobVal(
                                               EXTRACT(
                                                 XMLtype(
                                                   dbms_metadata.get_xml(
                                                     'VIEW'
                                                   , '^^view_name'
                                                   , '^^owner'
                                                   )
                                                 )
                                               , '/ROWSET/ROW/VIEW_T/TEXT/text()'
                                               )
                                             )
                                            , 1
                                            )
                                         )
                                       , '//QUERY/SELECT/SELECT_LIST/SELECT_LIST_ITEM'
                                       )
                                     )
                                   )
                            )
                     ) x
                   , dba_updatable_columns c
               WHERE c.table_name = '^^view_name'
                 AND c.owner = '^^owner'
                 AND c.column_name = x.column_alias
              ) x
      ) x
/

The solution of the puzzle may not cover all possible cases; but with extra polishing and tailoring it can be extended. For instance the hierarchy traversal still has to be implemented. I also have to warn on the usage of undocumented features: don't use them in production.