How to configure an Oracle listener to use SQL operations St_geometry

Regarding this content, actually launches st_geometry from ArcSDE9.2 to let the user feel very attractive, and especially after ArcSDE9.3, the user uses the SQL operation St_geometry more and more, But this is always the case when configuring Oracle snooping, and here's a summary of configuring the Oracle listener to use SQL Operation St_geometry.

First of all: If your ARCSDE version is 9.2 It is best not to use this method, because there are more bugs.

Example: http://support.esri.com/en/knowledgebase/techarticles/detail/33918

1: We need to use SQL Operation St_geometry, then our data storage should be st_geometry, but a bit easy to be ignored by the user is the original data may be ArcSDE9.2 they are stored as blob or long raw, And then upgrade to ArcSDE9.3 but the storage is not upgraded, so how to configure the SQL operation, this is often overlooked by the user problem.

Fix the problem: Use the Migrate tool to migrate storage.

2: Check Oracle's Listening state, EXTPORC (CLREXTPROC) state must be static (Unknow), if the ready state is a problem, and the Oracle listener must listen to both TCP and IPC

2. C:\Users\Administrator>lsnrctl Status
6. Lsnrctl for 64-bit windows:version 11.2.0.1.0-production on 2 June-December-2011 10:2
8. 9:44
12. Copyright (c) 1991, Oracle. All rights reserved.
16. Connecting to (description= (address= (protocol=IPC) (key=EXTPROC1521 ))
18. The STATUS of LISTENER
20. ------------------------
22. Alias LISTENER
24. Version Tnslsnr for 64-bit windows:version 11.2.0.1.0-produ
26. Ction
28. Start date 2 June-December-2011 10:28:14
30. Uptime 0 days 0 hours 1 minutes 34 seconds
32. Trace level off
34. Security On:local OS Authentication
36. SNMP OFF
38. Listener parameter File D:\app\Administrator\product\11.2.0\dbhome_1\network\a
40. Dmin\listener.ora
42. Listener log File d:\app\administrator\diag\tnslsnr\lish\listener\alert\
44. Log.xml
46. Listener Endpoint Summary ...
48. (description= (address= (protocol=IPC) (pipename=\\.\pipe\extproc1521ipc )))
50. (description= (address= (protocol=tcp) (host=192.168.100.111) (port=1521))
52. Service Summary:
54. The service "Clrextproc" consists of 1 instances.
56. The instance "Clrextproc", State UNKNOWN, contains 1 handlers for this service ...
58. The service "ORCL" consists of 2 instances.
60. The instance "ORCL", State UNKNOWN, contains 1 handlers for this service ...
62. Instance "ORCL", state ready, contains 1 handlers for this service ...
64. The service "Orclxdb" consists of 1 instances.
66. Instance "ORCL", state ready, contains 1 handlers for this service ...
68. Command execution succeeded

3: In the case of a distributed installation

A: such as server-installed 64Bit Oracle server, ARCSDE machine installed 32Bit software, both Windows operating system

B: such as server-installed 64Bit Oracle Server, AIX operating system, ARCSDE machine installed 32Bit software, Windows operating system

Then this will need to depend on the number of bits of Oracle on the server side and therefore the operating system to set the associated DLL or so the number of bits, that is, this configuration must be with the Oracle server side, we depend on the server's operating system and the number of bits to

Solve:

A: On the server side to install a 64Bit of ARCSDE software can, in fact, the most important thing is to need 64Bit of related DLLs

B: Install a 64Bit arcsde software for the AIX operating system on the server side.

4: Referencing related DLLs Take Windows as an example, not just one st_ Shapelib.dll also includes Pe.dll,sg.dll, so you need to be aware of whether the three DLLs are present in the process, especially in non-Windows environments, and also pay attention to the read and write operations of these three files.

5: Configuration is complete, we can use the following command to check

Note: This is under the SDE user

1. SQL> select * from User_libraries;
3. Library_name
4. ------------------------------
5. File_spec
6. ------------------------------------------------------------------------------
7. --------------------
8. D STATUS
9. - -------
10. St_shapelib
11. C:\Program Files\arcgis\arcsde\ora11gexe\bin\st_shapelib.dll
12. Y VALID

If not,

1. SQL> Create or replace library St_shapelib
2. 2 as ' D:\Oracle\product\11.2.0\dbhome_1\BIN\st_shapelib.dll ';
3. 3/
5. The library has been created.
7. SQL> Alter package SDE.ST_GEOMETRY_SHAPELIB_PKG compile reuse settings;
9. The package has changed.

6: In fact configuration, that is, the configuration of Listener.ora and Tnsname.ora files

Listener.ora: The file is mainly related to the reference of the St_shapelib.dll, the user can also copy the three DLLs I referred to Oracle_home/bin folder inside, note the relevant permissions, and then Extproc_dlls=any, This method can also be used.

2. # Listener.ora Network Configuration File:d:\app\administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
4. # Generated by Oracle configuration tools.
8. Sid_list_listener =
10. (sid_list =
12. (Sid_desc =
14. (sid_name = clrextproc)
16. (oracle_home = D:\app\Administrator\product\11.2.0\dbhome_1)
18. (Program= extproc)
20. (Envs = "Extproc_dlls=any")
22. )
24. (Sid_desc =
26. (global_dbaname = orcl)
28. (oracle_home = D:\app\Administrator\product\11.2.0\dbhome_1)
30. (sid_name = orcl)
32. )
34. )
38. LISTENER =
40. (description_list =
42. (DESCRIPTION =
44. (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
46. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.111) (PORT = 1521)) /c7>
48. )
50. )
54. Adr_base_listener = D:\app\Administrator

7:tnsname.ora file is that two files have a key value, to ensure that the key values of the two files are consistent, and in my experience, the installation of the latest version of Oracle, for example, my machine is the environment of Oracle 11.2.0.1, his tnsname file does not appear extproc_connection_data keyword, so we need to modify this keyword to be able. ESRI also has articles about ORACLE11GR2-related configuration st_geometry: http://support.esri.com/en/knowledgebase/techarticles/detail/38043

2. # Tnsnames.ora Network Configuration File:d:\app\administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
4. # Generated by Oracle configuration tools.
8. LISTENER_ORCL =
10. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.111) (PORT = 1521))
16. Extproc_connection_data =
18. (DESCRIPTION =
20. (address_list =
22. (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
24. )
26. (connect_data =
28. (SID = clrextproc)
30. (PRESENTATION = RO)
32. )
34. )
38. ORCL =
40. (DESCRIPTION =
42. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.111) (PORT = 1521))
44. (connect_data =
46. (SERVER = dedicated)
48. (service_name = orcl)
50. )
52. )

If the environment is consistent with me, you can also copy the two files and modify the relevant parameters of the user. Related parameters Reference

2. <span style="FONT-SIZE:13PX;" >sid_list_listener
4. This callout is the beginning of the list of SIDS to be processed by the listener named LISTENER (the default listener name).
8. Sid_list and Sid_desc
10. The Sid_desc entry under heading sid_list is used to define the service. The first one in the list? Stagger look? Extproc request, the second entry handles the client session.
14. Sid_name
16. Its value must correspond to the SID specified in the file Tnsnames.ora for Extproc.
20. Oracle_home
22. Defines the location of the Oracle home page for this service. Loads the Extproc program file from a folder under this location.
26. Program
28. Specifies the file name of the Extproc executable file. The case-sensitive name may be Extproc or extproc.exe, depending on the type of operating system. The file is located in Oracle_home/bin.
32. Envs
34. This is the list of environment variables that are used by Extproc at run time. The list is separated by a colon. This list must include the definition of the environment variable extproc_dlls and any other environment variables that EXTPROC requires at run time, and other environment variables typically include Ld_library_path, Shlib_path, or LIBPATH in UNIX and Linux systems. Or PATH on a Windows server. Path variables typically include the location of the geometry and projection engine libraries.
38. Extproc_dlls
40. This environment variable is used to define a list of libraries that EXTPROC can directly load and invoke functions from. The path to the file that contains the St_shapelib and Libst_raster libraries must be specified here.
44. For Oracle servers installed on UNIX, the list is separated by colons, and the list is semicolon-delimited for Oracle servers installed on Windows. All paths must be absolute and point to a local directory, or a UNC path that is accessible to Oracle system users. This also means that if you have ArcSDE installed on a non-Oracle server, you must copy the shapes and raster libraries to the Oracle server. For more information, see Installing Oracle and ArcSDE on different servers.
48. No environment variable substitution exists.
52. Optional keywords any and only can be used to relax or limit the way Extproc uses library files. If you put any in front of the path, Oracle can load any library from the specified path and do not have to provide the library name. If only is placed in front of the path, Oracle will use only the specific libraries provided in the path.
56. KEY
58. The value set for this entry links this listener to the corresponding service entry in the file Tnsnames.ora. This value distinguishes this listener from other IPC listeners that may exist on the same database server. Key can be any short name, but must be the same in file Listener.ora and Tnsnames.ora. Key is case-sensitive. </span><span style="FONT-SIZE:18PX;" >
60. </span>

8: Test is normal

1. SQL> select Sde.st_astext (Shape) from AA where Objectid<3;
3. Sde. St_astext (SHAPE)
4. ------------------------------------------------------------------
6. Point (-13625889.61620000 4541020.47880000)
7. Point (-13627070.41230000 4548787.04140000)

How to configure an Oracle listener to use SQL operations St_geometry