first stab at retreiving all coded Domain values in SDE 10.0 from Oracle

GilScholl — Wed, 23 Jan 2013 17:19:13 GMT

This was my first stab at retrieving coded domain descriptions for all domains in SDE 10.0.

The SQL Server equivalent was out there already in the link http://support.esri.com/en/knowledgebase/techarticles/detail/39962 .

This is what I came up with for Oracle. I used a view for the first part, to make the transition out of the way domain values are stored in XML in a Clob field instead of a XML datatype. This simplified the ultimate query. Am sure others will come up with more cool way of doing this, but this got my question answered. Suggest running this on the database server if your domain list is very large.

I have 3 items, a view, putting 5 tables in KEEP POOL that is already set up in Oracle (for speed), and a query .

1-The view needs to be created in SDE owner (or other "user geodatabase"), (one-time).
It is based on the SDE.GDB_ITEMS_VW that is already installed in sde schema.
The view and query assume you are logged into the database as the user that owns the SDE system table schema...so if a "user geodatabase" logon to that user schema.

2-As a DBA account, run these 5 commands. These are a one-time thing. They helped with speed.
alter table sde.gdb_items storage (BUFFER_POOL KEEP);
alter table sde.gdb_itemtypes storage (BUFFER_POOL KEEP);
alter table sde.sde_xml_doc1 storage (BUFFER_POOL KEEP);
alter table sde.sde_xml_doc2 storage (BUFFER_POOL KEEP);
alter table sde.sde_xml_doc3 storage (BUFFER_POOL KEEP);

3-The query is setup as follows, it has two lines commented out with --,
and WHERE clause is there in case you want to restrict by whatever, such as an objectid, or a domain owner, or domain, etc. ***I recommend you uncomment those lines and try a small query***...sample had GEOHOME as the data owner. Another option for restricting to a single domain name is just to put a WHERE clause like this   WHERE objectid=321    you can do a quick check of the objected you want in the view.

--begin View

CREATE OR REPLACE FORCE VIEW SDE.DOMAIN_NEWXMLTYPE
(
   OBJECTID,
   NAME,
   DOMAINXML
)
AS
   SELECT objectid,
          name,
          EXTRACT (xmltype (definition), '/GPCodedValueDomain2') domainXML
     FROM sde.gdb_items_vw items
        WHERE items.objectid IN (SELECT items.objectid
                               FROM    SDE.GDB_ITEMS items
                                    INNER JOIN
                                       SDE.GDB_ITEMTYPES itemtypes
                                    ON items.TYPE = itemtypes.UUID
                              WHERE itemtypes.Name = 'Coded Value Domain')
;
--end of view

--QUERY to run in SQL*Plus or other tool for Oracle
set serveroutput off
set echo off
set linesize 1000
set pagesize 60
set feedback off
set termout off

COLUMN D_Owner FORMAT A25
COLUMN DomainName FORMAT A25
COLUMN Description FORMAT A50
COLUMN FieldType FORMAT A30
COLUMN MergePolicy FORMAT A30
COLUMN Splitpolicy FORMAT A30
COLUMN Splitpolicy FORMAT A30
COLUMN code FORMAT A50
COLUMN name FORMAT A50

spool d:\temp\domainoutput.txt
SELECT /*+FIRST_ROWS (100) */
objectid,
extractvalue (domainxml,'/GPCodedValueDomain2/Owner') D_Owner,
extractvalue (domainxml,'/GPCodedValueDomain2/DomainName') DomainName,
extractvalue (domainxml,'/GPCodedValueDomain2/Description') Description,
c.code,
c.name,
extractvalue (domainxml,'/GPCodedValueDomain2/FieldType') FieldType,
extractvalue (domainxml,'/GPCodedValueDomain2/MergePolicy') MergePolicy,
extractvalue (domainxml,'/GPCodedValueDomain2/SplitPolicy') SplitPolicy
from SDE.DOMAIN_NEWXMLTYPE x,
xmltable('/GPCodedValueDomain2/CodedValues/CodedValue' passing x.domainxml
   columns
   name   varchar2(50) path './Name', code varchar2(50) path './Code') c
--where extractvalue (domainxml,'/GPCodedValueDomain2/Owner')='GEOHOME'
--AND x.name='2.99_WaterUtilityNode'
;
spool off

Re: first stab at retreiving all coded Domain values in SDE 10.0 from Oracle

ForrestJones — Thu, 24 Jan 2013 15:28:12 GMT

Hi Gil,

It sounds like you don't have a question with the queries, but are posting for informational purposes. There is also more info in the 10.1 doc with examples for SQLServer and Oracle. These are just a couple examples:

ArcGIS Documentation: Discovering domain usage

ArcGIS Documentation: Resolving domain codes to values

topic first stab at retreiving all coded Domain values in SDE 10.0 from Oracle in Data Management Questions

first stab at retreiving all coded Domain values in SDE 10.0 from Oracle

Re: first stab at retreiving all coded Domain values in SDE 10.0 from Oracle