【ArcSDE/Oracle】配置Oracle使SQL可以对ST_GEOMETRY类型操作

下面要使用sde中的相关的动态链接库，因此需要事先安装好arcsde和oracle。

1，测试用例

select sde.ST_AsText(SDE.ST_Geometry(‘POINT (10 10)’, 0)) from dual;

2，配置listener.ora、tnsname.ora和extproc.ora

如果出现以下错误：
ORA-28595: Extproc 代理: DLL 路径无效
ORA-06512: 在 “SDE.ST_GEOMETRY_SHAPELIB_PKG”, line 70
ORA-06512: 在 “SDE.ST_GEOMETRY_OPERATORS”, line 68

那么说明listener.ora和tnsname.ora没有配置好，主要是key和sid_name要一样。
具体见：
http://blog.csdn.net/zhaojian1988/article/details/42739551

此外还要在 extproc.ora中添加一句话
SET EXTPROC_DLLS=ANY

配置好的几个文件示例如下：
tnsnames.ora

# tnsnames.ora Network Configuration File: F:\app\KingWang\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-L5ENP5D)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora

# listener.ora Network Configuration File: F:\app\KingWang\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = F:\app\KingWang\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-L5ENP5D)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

ADR_BASE_LISTENER = F:\app\KingWang

extproc.ora

# 
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
# 
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs 
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the 
# following values:
# 
# * ONLY (maximum security) 
#
#   When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
#   When EXTPROC_DLLS=, only the DLL(s) in $ORACLE_HOME/bin and ORACLE_HOME/lib
#   can be loaded.
#
#   Syntax: SET EXTPROC_DLLS=
#
# * Colon-seperated list of the DLLs
#
#   When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in 
#   $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
# 
#   Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY 
#   When EXTPROC_DLLS=ANY, DLL checking is disabled.
# 
#   Syntax: SET EXTPROC_DLLS=ANY
#
SET EXTPROC_DLLS=ANY

过程中，如果出现
ORA-28575:无法打开与外部过程代理程序的RPC连接
那么多半说明没有上面的几个文件没有配置好

配置完成之后需要重启一下监听服务和数据库服务。

3，在dos窗口下运行以下命令：

CREATE OR REPLACE LIBRARY st_shapelib AS ‘C:\Program Files\ArcGIS\ArcSDE\ora11gexe\bin\st_shapelib.dll’

这一步骤是为了当使用sql调用外部dll文件能够正确调用，不然就会出现调用失败的错误，而上面的st_shapelib.dll这个文件是安装完ArcSDE之后安装路径下的文件。需要使用这个文件才能对ST_GEOMETRY类型的数据进行操作。

查看user_libraries表，st_shapelib 指向的dll文件是否正确。

再利用步骤1中的测试用例进行测试，看是否可以对ST_GEOMETRY类型字段进行操作，如果失败，请重新按照上面的配置检查自己的环境配置。

几个参考网址：
http://resources.arcgis.com/en/help/main/10.2/#/Configuring_the_Oracle_listener_to_use_ST_Geometry_and_ST_Raster/006z0000001w000000/
http://resources.arcgis.com/en/help/main/10.2/index.html#/ST_Geometry_function_calls/006z000000qz000000/

---

补充知识点：
version，delta table,base table，default version等概念：
http://edndoc.esri.com/arcsde/9.2/concepts/versioning/basicprinciples/deltatables.htm#BaseTable
在数据库中的表现就是，如果sde中的数据表注册了版本，就会有base table,add table,delta table。那么如果insert一条记录，那么数据库的add table中就会出现一条新增记录；
如果是删除一条记录，那么就是在delta table中记录一条删除的记录。

至于怎么使用sql来操作st_geometry类型数据请参考：

使用 SQL 编辑 Oracle 中的版本化数据