In st_geometry overview I gone over some important issues concerning st_geometry.

You saw all kind of cool things you can do in simple SQL queries. In order to use these queries you need to do some additional configurations to your database after you install SDE.

In ESRI's documentation you can find the specific instructions of how to do this.

Example of Net configuration of st_geometry in Oracle on linux:
listener.ora:

LSNRMYDB =
    (DESCRIPTIONLIST =
        (DESCRIPTION = 
            (ADDRESSLIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = myDbSeverUrl)(PORT = 1234))
            )
            (ADDRESSLIST =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC01))
            )
        )
    )
 
SIDLISTLSNRMYDB =
    (SIDLIST =
        (SIDDESC =
            (SIDNAME = PLSExtProc01)
            (ORACLEHOME = /software/oracle/app/oracle/10203)
            (PROGRAM = extproc)
            (ENV="EXTPROCDLLS=/software/sde/sdeexe/lib/libstshapelib.sp,LDLIBRARYPATH=/software/sde/sdeexe/lib")
        )
        (SIDDESC =
            (GLOBALDBNAME=mydb)
            (ORACLEHOME = /software/oracle/app/oracle/10203)
            (SIDNAME = MYDB)
        )
    )

tnsnames.ora:

EXTPROCCONNECTIONDATA =
    (DESCRIPTION =
        (ADDRESSLIST =
            (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC01))
        )
        (CONNECTDATA =
            (SID = PLSExtProc01)
            (PRESENTATION= = RO)
        )
    )

A couple of things you need to remember

After you configure the listener, you need to restart it.

You MUST recompile the st_shapelib package in the SDE user (you also need to change the url to the appropriate url in your server, and according to your OS – see the table in the next section). Do that after you’ve restarted the listener.

In the listener definition under SID_LIST=>SID_DESC=>ENV of PLSExtProc01 SID in unix/linux OS you need to add the library path definition to the file location definition. For each OS it’s library path (unix/linux – LD_LIBRARY_PATH,hp-ux – SHLIB_PATH, AIX(IBM 64) – LIBPATH).

Notice the KEY value in ADDRESS of the IPC protocol, in the listener and the tnsnames, must be the same in both definitions.

The SID_NAME in the listener and the SID in CONNECT_DATA in tnsnames, must also be the same name.

Using st_geometry when your sde and db are on different server

As I showed previously st_geometry works with external libraries which contains the operators and methods of the object. These libraries are installed with SDE on the SDE machine.

When sde resides on a different server then your database, you need to copy these files to the database machine in order to do the configuration of the listener and the st_shapelib library.

The files can be found in $SDEHOME/lib directory (Unix) or %SDEHOME%\bin in windows:

Library Name Windows Unix/Linux HP-UX AIX (IBM 64)
st_shapelib st_shapelib.dll libst_shapelib.so libst_shapelib.sl libst_shapelib_64.so
Projection Engine pe.dll libpe.so libpe.sl libpe_64.so
Geometry sg.dll libsg.so libsg.sl libsg_64.so

You need to copy these files to your db machine under your db lib folder, and then aim the listener to these files.

Important to notice

The files must match the OS on your database server!

If your db machine has different OS then your sde server you need to create a dummy installation of a sde on the same OS and then copy the files from there to your db.

When installing service packs you need to recopy the files

If your sde server and db server has different OS you need to do the same thing as you did in the previous section, install the service packs and then recopy the files.

Troubleshooting

There are a couple of errors we encountered while configuring sde

ORA-20003 – Unable to retrieve message

As you can see, this is a very generic error (the kind of error Esri is used to give with ArcObjects – the notorious COM exception).

These errors where published in the first version of st_geometry in sde 9.2. This was done in order to wrap the exceptions that originated from the database. In service pack 4 Esri changed this and the exception that were thrown had a specific meaning

Here, you can find a full list of detailed exceptions.

ORA-28579 – network error during callback from external procedure agent

This is a nice one. We spent about a week and a half in order to solve this.
In my case, the problem arose from an inaccurate definition of the listener.

When we first installed st_geometry we did it on HP-UX. The second installation was on AIX (IBM 64). So, at the advice of our unix and DBA teams we simply copied the definition. But as I described in the first section of this post, you need to change the library path according to the operating system, which we didn’t...

This can cause very weird exceptions and errors, because some of the st_geometry functions are implemented in PL/SQL (like st_srid) and some are in the external library (like st_geomfromtext). This means that some of the function works and some don’t. The properties of the type will also work because they are defined in the User Defined Type.
Also, editing the layer in ArcMap will also work because ArcMap is using ArcObjects and not the SQL API.

In short, all you have to do when encountering this exception is check the listener definition:

  • Are the library files in their place?
  • Are the files match the database OS?
  • Is the path in the listener leads to the files?
  • Is the definition of the listener match the database OS? (SHLIB_PATH for HP_UX,LIBPATH for AIX(IBM 64) and LD_LIBRARY_PATH for all other linux/unix OS).

In conclusion

Configuring your database to work with st_geometry could be very tricky. You need to be precise. From my experience most errors are the result of wrong configuration.