直接使用SQL操作Oracle空间数据的原理以及配置方法

最近一直接到售前的请求，客户现场成功部署SDE for Oracle后，在部署SQL

 

ORA-28595: Extproc 代理: DLL 路径无效
ORA-06512: 在 "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 70
ORA-06512: 在 "SDE.ST_GEOMETRY_OPERATORS", line 68

View program sources of error stack?
--------------------------------------------------------------
listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
      (ENVS="EXTPROC_DLLS=E:\ArcGIS\ArcSDE\ora10gexe\bin\st_shapelib.dll" )---这个“E:\ArcGIS\ArcSDE\ora10gexe\bin\st_shapelib.dll”是可以在本机打开找到文件的
    )
    (SID_DESC =
       (GLOBAL_DBNAME = webgis)
       (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
       (SID_NAME = webgis )
   )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.238.185.71)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
--------------------------------------------------------
tnsname.ora:

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
WEBGIS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.238.185.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = webgis)
    )
  )

---------------

C:\Documents and Settings\Administrator>tnsping EXTPROC_CONNECTION_DATA ---这里也是通的
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 07-9月 -
2011 12:30:55
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
已使用的参数文件:
F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)
(KEY = EXTPROC0))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))
OK (0 毫秒)

 

 

直接操作空间数据的时候始终无法配置成功，现把SQL访问空间数据的原理以及配置过程中经常碰到的问题详细的介绍一下。

 

原理

从ArcSDE9.2 for oracle开始，提供了使用直接调用SQL来操作空间数据的方法，但是得进行配置才能使用，而且空间数据的存储方式只能是ST_GEOMETRY才支持直接SQL。

由于这些空间操作的函数都是GIS的核心算法，并且这些算法都是非常消耗CPU的算法，所以ESRI是以动态库而不是PL/SQL(适用于批量数据处理)的方式提供的这部分函数，然后在oracle中使用C的external procedure对动态库进行调用。

调用的结构图如下所示:

 

调用过程如下:

1. 用户进程执行SQL,在sqlplus执行select sde.st_astext(shape) from sde.test,在数据库中调用的顺序是:

 

上述三个对象的定义如下

sde.astext:

 

CREATE OR REPLACE PACKAGE BODY "SDE"."ST_GEOMETRY_OPERATORS"

IS

--st_Astext

Function st_astext_f(prim SDE.st_geometry)

Return clob

IS

spref SDE.spx_util.spatial_ref_record_t;

temp varchar2(1);

text_clob clob := empty_clob();

rc number;

Begin

If prim IS NULL THEN

Return NULL;

End If;

If prim.numpts = 0 and prim.entity = 0 Then

text_clob := 'EMPTY';

return(text_clob);

End If;

spref.srid := prim.srid;

rc := SDE.st_spref_util.select_spref(spref);

If rc != SDE.st_type_user.se_success THEN

raise_application_error (SDE.st_type_util.st_no_srid,'srid '||spref.srid||

' does not exist in st_spatial_references table.');

End If;

text_clob := ' ';

SDE.st_geometry_shapelib_pkg.astext(spref.srid,spref.x_offset,spref.y_offset,spref.xyunits,spref.z_offset,

spref.z_scale,spref.m_offset,spref.m_scale,spref.Definition,prim.numpts,

prim.entity,prim.points,text_clob);

Return(text_clob);

End st_astext_f;

Sde.st_geometry_shapelib_pkg.st_astext的存储过程的代码如下：

CREATE OR REPLACE PACKAGE BODY "SDE"."ST_GEOMETRY_SHAPELIB_PKG" AS

Procedure astext (srid IN number,

false_x IN number,

false_y IN number,

xyunits IN number,

false_z IN number,

zunits IN number,

false_m IN number,

munits IN number,

proj_str IN varchar2,

pls_numpts IN number,

pls_entity IN number,

points IN blob,

shptxt IN Out clob

)

AS

language c

name "AsText"

library st_shapelib

WITH CONTEXT

parameters (

CONTEXT,

srid ocinumber,

false_x ocinumber,

false_y ocinumber,

xyunits ocinumber,

false_z ocinumber,

zunits ocinumber,

false_m ocinumber,

munits ocinumber,

proj_str string, proj_str Indicator short, proj_str length int,

pls_numpts ocinumber,

pls_entity ocinumber,

points ociloblocator, points Indicator short,

shptxt ociloblocator, shptxt Indicator short

)

2。 在执行的Sde.st_geometry_shapelib_pkg.st_astext程序过程中，调用了一个astext函数(该函数在SDEHOME/bin/st_shapelib.dll(windows),$SDEHOME/lib/libst_shapelib.so(unix,linux)定义的)。这里需要借助别名库（Alias Library）来表示动态库路径。别名库是数据库里的一个对象，在SDE中就是st_shapelib对象(该对象的类型为library对象，其定义在下面)，用来描述一个外部函数所在的动态链接库的路径和名称。通过别名库，从而可以知道被调用的外部函数在哪个文件里。

Library对象的定义:

Windows:

 

Linux,Unix:

 

Astext函数的定义：

Windows:

 

从上图可以看出，空间运算的所有的核心算法都在这17个函数中。

Linux,Unix:

 

3.当某个session调用某个sql的时候，监听器会生成一个ext(extproc.exe windows,extproc(linux))该进程专门用来处理对外部函数的调用。并且在整个session生命周期里，extproc进程会一直存在,只有当所有的session断开后，该进程才会退出。

4. Extproc进程负责将别名库所指定的动态链接库文件加载到内存，(windows:st_shapelib.dll,unix,linux:libst_shapelib.so必须放在数据库服务器端，并且由于windows:st_shapelib.dll要调用sg.dll和pe.dll两个动态库, unix,linux:libst_shapelib.so要调用libsg.so,libpe.so两个动态库,所以相关的两个动态库也要放到服务器端)

5. Extproc进程执行指定的外部函数，并将结果返回给服务器进程，进而服务器进程返回给用户进程。

 

从上述原理可以看到，使用外部存储过程，既保证了调用的效率，又很好的隐藏了源代码。

配置

 

st_shapelib对象的配置

该对象位于sde的schema下，类型为library,以sde用户登陆后可以通过查询user_libraries字典进行详细信息的查询。

分以下几种情况进行说明：

1. ArcSDE和数据库服务器安装在一台机器。

在post过程中会自动创建该对象，而且该对象指向的外部的动态库的路径一定正确的，对这种情况不用做特别的设置。

2. ArcSDE和数据库服务器是分开部署的并且数据库服务器和sde服务器是同类型，同厂商的操作系统，如都是windows操作系统或者都是solairs操作系统。

在post过程中也会自动创建该对象，但是该对象所指向的外部的动态库为sde服务器上的路径，如sde安装在d:/program/arcgis/arcsde目录中，则该对象指向d:/program files/arcgis/arcsde/ora10gexe/bin/st_shapelib.dll,但是该路径在数据库服务上并不存在，此时可以用以下两种方法进行处理。

A. 在数据库服务器上也建立d:/program files/arcgis/arcsde/ora10gexe/bin/目录，然后把sde服务器上的st_shapelib.dll文件拷贝到该目录中。

B. 在数据库服务器上随便建立一个目录，如d:/arcsde/bin，然后把sde服务器上的st_shapelib.dll文件拷贝到该目录中，然后到数据库中修改st_shapelib对象所指向的动态库，

 

3. ArcSDE和数据库服务器是分开部署的，并且数据库服务器和sde服务器不是同类型的操作系统。

如数据库服务器是linux，sde服务器是window操作系统。

如sde安装在d:/program files/arcgis/arcsde目录中，做完post后，数据库中的st_shapelib对象所指向的动态库为d:/program files/arcgis/arcsde/ora10gexe/bin/st_shapelib.dll。该路径在linux实际上不存在。此时需要在数据库服务器上把ArcSDE的软件安装上，然后通过sql到数据库中把st_shapelib对象指向正确的动态库。

 

Oracle外部存储过程的配置

配置方法

数据库服务器端：

Windows操作系统下通过菜单打开net manager, linux, unix操作系统执行netmgr，启动的界面如下图所示:

1.

选中已经存在的某个listener(一般情况下只会有一个listener),选择监听位置，并添加一个监听地址，协议选择IPC,如下图所示：

2. 然后选择数据库服务，并填写相关信息，如下:

 

 

3. 选择其他服务，

在环境处填写：

"EXTPROC_DLLS=d:/program files/arcgis/arcsde/ora10gexe/bin/st_shapelib.dll"

在程序名处填写:

extproc

SID处填写:

PLSExtProc

 

3. 选择文件à保存网络配置后，退出，完成监听器的配置。

4. 修改tnsnames.ora文件，添加如下一行:

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.222)(PORT = 1521))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

 

常见问题

1. Linux,Unix下没有把$SDEHOME/lib目录添加到oracle用户下的.profile或者.bash_profile的相关环境变量中。

2. 监听器除了监听TCP协议，一定还要要监听IPC协议，可以通过lsnrctl status命令来查看。

3. St_shapelib对象所指向的动态库的路径不正确，可以通过select * from user_libraries查看指向的动态库的路径是否正确，如果不正确可以通过create or replace library st_shapelib as ‘your lib path’来修改。