Get Coded Value Domains from a Oracle geodatabase using PLSQL

Question

i'm trying to get all coded value domains from a Oracle geodatabase. I dont have access to arcmap. I only have access to oracle 10g database.

SELECT
 I.NAME Domain, EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code') AS Code, EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name') AS Value FROM SDE.GDB_ITEMS_VW I 
JOIN SDE.GDB_ITEMTYPES IT ON I.Type = IT.UUID, TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues 
WHERE IT.Name = 'Coded Value Domain'

i saw this SQL statement on Return information on coded value domains using ArcSDE 10.x for SQL Server, when i try to run it occurs:

ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 1339
ORA-06512: at "SDE.ST_GEOM_UTIL", line 760
28575. 00000 - "unable to open RPC connection to external procedure agent"
*Cause: Initialization of a network connection to the extproc agent did
 not succeed. This problem can be caused by network problems,
 incorrect listener configuration, or incorrect transfer code.
*Action: Check listener configuration in LISTENER.ORA and TNSNAMES.ORA, or
 check Oracle Names Server.

Is there another way to get coded value domains without configure the LISTENER.ORA? I don't have any chance to configure such file. Should i need a external procedure to get this coded value domains?

• nef001: I have seen this error if the ST_Geometry libraries are not correctly set up on the Oracle server. resources.arcgis.com/en/help/main/10.2/#/…
• nef001: Also relevant: resources.arcgis.com/en/help/main/10.2/#/…

Answer 1

Yes, you will need to use the external procedure agent if you want to access the XML columns in the SDE.GDB_ITEMS_VW views.

By default, the XML columns in the GDB_* tables are stored using ESRI's XML implementation. The functionality to convert these to a CLOB is included in the ST_Geometry library, which is accessed using the external procedure agent.

You could get around this issue by recreating your geodatabase using Oracle's XML storage. This is done by setting the XML_COLUMN_STORAGE dbtune parameter to DB_XML. I am guessing it would be easier to set up the external procedure agent though.

Answer 2

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.

Look into this. I think this will solve your problem.

http://arcscripts.esri.com/details.asp?dbid=15501

• blah238: This will not work with SDE 10.x+ geodatabases due to changes in the geodatabase schema: blogs.esri.com/esri/arcgis/2010/03/15/… -- also link-only answers are not well received on this site because they tend to become useless when the link breaks.