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:
SELECT Xmltype(xml_doc, 871) FROM sde.sde_xml_doc2 WHERE sde_xml_id = 8641;
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).