0

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 domain codes to domain descriptions.

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

select
    substr(extractvalue(codedvalues.column_value,'CodedValue/Code'),1,255) as code,
    substr(extractvalue(codedvalues.column_value,'CodedValue/Name'),1,255) as description
from
    sde.gdb_items_vw i 
cross join
    xmlsequence(xmltype(definition).extract('/GPCodedValueDomain2/CodedValues/CodedValue')) codedvalues 
where
    i.name = 'ASSET_STATUS'

Question:

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?

Improve this question
4
  • 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
    Commented Oct 11, 2019 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
    Commented Oct 17, 2019 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?
    – User1974
    Commented Dec 15, 2019 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
    Commented Dec 15, 2019 at 16:37

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.