I have tables in an Oracle 18c GDB that I integrate to a work order management system.

I create views on the tables to translate the data. For example, I translate codes to domain descriptions.

I do this by joining to a view that extracts the domain values from the GDB system tables:

    substr(extractvalue(codedvalues.column_value,'CodedValue/Code'),1,255) as code,
    substr(extractvalue(codedvalues.column_value,'CodedValue/Name'),1,255) as description
    sde.gdb_items_vw i 
    sde.gdb_itemtypes it 
    on i.type = it.uuid, table(xmlsequence(xmltype(definition).extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) codedvalues 
    i.name = 'ASSET_STATUS'


In addition to getting the domain's description, I would also like to select the date that the domain was last updated.

However, I don't see any sort of date field in sde.gdb_items_vw and I don't think scn_to_timestamp(ora_rowscn) will work.

How can I select a domain's last update date?

  • 1
    Creating a view on an XML column is not likely to be adequately performant. If you make this a materialized view, then you won't have to pay all that overhead with each query, and can add a virtual column with the timestamp for the last update. – Vince Oct 11 '19 at 14:13
  • Self-answering is encouraged. I haven't had Oracle loaded on the last three laptops, so I'm not going to answer this. – Vince Oct 17 '19 at 2:33
  • @Vince Regarding the materialized view on SDE.GDB_ITEMS idea: Is it recommended to put such a view in the SDE owner/schema? Or would that get squirrelly come upgrade time? – User1973 Dec 15 '19 at 14:17
  • 1
    I'd use a different schema, just on principle, but I'd also undo any changes, possibly saving any data in a temporary table, before attempting upgrade (no dangling dependencies) – Vince Dec 15 '19 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.