Esri ArcSDE 10.3.1 Query Inspire Metadata from Oracle (11g) Enterprise GeoDB via SQL

Question

As the title says - I want to create a query in Oracle SQL which will read all dates from the geoprocessing history which can be found using ArcCatalog to show the "Description"-tab from a feature. (You need to change the settings of ArcCatalog to use the following "Metadata Style")

If possible I just want to use SQL only - no python or arcpy.

The first approach I used was to query the "cdate" field within "sde.layers" table with the following query:

SELECT Upper(sde.layers.owner 
         || '.' 
         || sde.layers.table_name)                                    AS 
   feature_name, 
   ( To_date('1970-01-01', 'YYYY-MM-DD') + sde.layers.cdate / 86400 ) AS 
   last_update 
FROM   sde.layers 
WHERE  sde.layers.owner LIKE 'SDE_V_%' 
ORDER  BY last_update DESC; 

The query above will list the creation date of every layer within the ArcSDE which is not what I need.

I tried querying the SDE_XML_DOC2 table which should contain the information I described above but wasn't successful because oracle developer always outputs the following error:

Query:

SELECT Xmltype(xml_doc, 871) 
FROM   sde.sde_xml_doc2 
WHERE  sde_xml_id = 8641; 

Error:

LPX-00217 invalid character error

I tried different character sets (1 US7ASCII, 170 WIN1250, 171 WIN1251, 178 WIN1252, 871 UTF-8) within the XMLType function but all of them returned the same error.

I'am stuck at this point and need some help. The general idea is to get the last modified date of all layers within the sde (vector and raster).

Answer 1

SQL-only / database-only possible solutions:

A. - add a timestamp column to every layer using systimestamp as default value, then query for latest timestamp

B. - add a trigger ON INSERT and ON UPDATE to every layer table, that updates last modified date value for each layer, on a table of your own.