2

I have created a database view in an Oracle-backed Enterprise Geodatabase. I can see data in both of them when I view them using SQL Developer. However, when I look at the view in ArcCatalog (version 10.8), there are no records.

I have attempted to create this view in both SQL Developer using the CREATE VIEW command (and registering with the database), as well as creating it in ArcCatalog directly.

The view, which shows which fields have domains for all feature classes and tables, has the following definition:

SELECT
i.Name AS "Object",
EXTRACTVALUE (xVal.COLUMN_VALUE, 'GPFieldInfoEx/Name') AS "Field",
EXTRACTVALUE (xVal.COLUMN_VALUE, 'GPFieldInfoEx/DomainName') AS "Domain"
FROM SDE.GDB_ITEMS_VW  i JOIN SDE.GDB_ITEMTYPES it ON i.Type = it.UUID,
TABLE (XMLSEQUENCE (XMLType (Definition).EXTRACT('//GPFieldInfoEx'))) xVal
WHERE i.NAME IS NOT NULL AND i.NAME LIKE 'AMIS%' AND EXTRACTVALUE (xVal.COLUMN_VALUE, 
'GPFieldInfoEx/DomainName') IS NOT NULL
ORDER BY "Object", "Field"

Results when viewing in SQL Developer:

enter image description here

Results when viewing attribute table in ArcGIS: enter image description here

The user that created the view has read privileges to the SDE tables that are being queried in the SELECT statement. I'm connecting to the database in ArcGIS using that same user account.

Why can't I see the results in ArcGIS?

1

2 Answers 2

1

I noticed that ArcGIS Desktop 10.7.1 was doing something strange with the text fields in the attribute table: the fields were way too wide. And when I looked at the view properties in ArcCatalog, I confirmed that ArcCatalog was interpreting the fields as being too long. (Maybe due to the underlying field type being CLOB or BLOB?)

  • OBJECT: 257
  • FIELD: 4000
  • DOMAIN: 4000

enter image description here


So I took a look at what the max field length was (at least for my data):

select
    max(length(object)) as max_length_object,
    max(length(field))  as max_length_field,
    max(length(domain)) as max_length_domain
from
    (
    ...
    )

MAX_LENGTH_OBJECT MAX_LENGTH_FIELD MAX_LENGTH_DOMAIN
----------------- ---------------- -----------------
               39               24                32

And then I CAST() the text fields appropriately — nvarchar2(50):

create or replace view a_test_vw as (
select 
    cast(rownum as number(38,0)) as rownum_,
    cast(i.name as nvarchar2(50)) as Object,
    cast(extractvalue (xval.column_value, 'GPFieldInfoEx/Name') as nvarchar2(50)) as Field,
    cast(extractvalue (xval.column_value, 'GPFieldInfoEx/DomainName') as nvarchar2(50)) as Domain
from 
    sde.gdb_items_vw i
join sde.gdb_itemtypes it on i.type = it.uuid,
table (xmlsequence (xmltype (definition).extract ('//GPFieldInfoEx'))) xval
where
    i.name is not null
    and extractvalue (xval.column_value, 'GPFieldInfoEx/DomainName') is not null
)

--Note: I removed part of the WHERE clause to suit my data

That seemed to work better in ArcGIS Desktop:

enter image description here

enter image description here


Finally, ensure that the view is registered with the geodatabase.

4
  • This didn't work. Initially, when I opened the view in ArcGIS, I selected the three rows as the unique identifier. But this time, I select rownum_ as the unique identifier, but the resulting view in ArcGIS is empty. It does work in SQL Developer.
    – Fezter
    Apr 22, 2021 at 22:36
  • Are you able to get the SQL statement that ArcGIS submits to read from the view ? Apr 23, 2021 at 16:57
  • 1
    What may be happening is that ArcGIS expects to find out about the view from the Oracle Spatial metadata (USER_SDO_GEOM_METADATA). That has such info as the coordinate system of the data. Can you try inserting manually a row in there? It will have the same content as the same info for the base table - except for TABLE_NAME that will have the name of your view. Apr 23, 2021 at 17:01
  • 2
    Sorry for the delay in responding. This answer did not work. However, it certainly helped. I did modify the code to cast an objectid as well as shorten the fields to a more reasonable length. However, ultimately, all I had to do was register the view with the database. Something that I thought would happen automatically when I created the view in Catalog. I've added that bit at the end of your answer and accepted for you.
    – Fezter
    May 6, 2021 at 2:47
1

I wonder if the query in the original question could be simplified:

  1. Remove the [inner] join (link), since as far as I can tell, it doesn't do anything.
    • In other words, remove this: JOIN SDE.GDB_ITEMTYPES it ON i.Type = it.UUID,
  2. Remove TABLE (), since that doesn't seem to be necessary in modern versions of Oracle:

A simplified version of the query might look like this:

select
    i.name as object,
    extractvalue(xval.column_value, 'GPFieldInfoEx/Name') as field,
    extractvalue(xval.column_value, 'GPFieldInfoEx/DomainName') as domain
from 
    sde.gdb_items_vw i 
cross join
    xmlsequence(xmltype(definition).extract('//GPFieldInfoEx')) xval
where 
    i.name is not null 
    and i.name like 'AMIS%'
    and extractvalue(xval.column_value, 'GPFieldInfoEx/DomainName') is not null
order by 
    object, 
    field

I only mention that because I wondered if you might have gotten parts of your original query from one of my old posts on GIS SE. In hindsight, I realize that a few of my domain/SQL/XML posts were misguided. So if you did get the SQL from one of my posts, then I'd feel guilty about that, and thought I'd try to right that wrong.

But you might have gotten the SQL from somewhere else too. Either way, I thought I'd provide my thoughts.


Edit:

The EXTRACTVALUE function has been deprecated by Oracle. We ought to use XMLTABLE instead.

1
  • I appreciate your answer, thank you. I actually don’t recall where I got the code. But regardless, it’s been working for a while now.
    – Fezter
    Jun 26, 2022 at 21:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.