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?

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

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

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