To use the relevant dynamic link libraries in sde, you need to install arcsde and oracle beforehand.

1. Test cases

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

2. Configure listener.ora, tnsname.ora and extproc.ora

If the following error occurs:
ORA-28595: Extproc agent: invalid DLL path
ORA-06512: In "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 70
ORA-06512: In "SDE.ST_GEOMETRY_OPERATORS", line 68

So listener.ora and tnsname.ora are not well configured, mainly key and sid_name are the same.
See:
http://blog.csdn.net/zhaojian1988/article/details/42739551

In addition, add a sentence to extproc.ora
SET EXTPROC_DLLS=ANY

Examples of configurable files are as follows:
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

In the process, if it appears
ORA-28575: Failed to open RPC connection with external process agent
So most of the above documents are not well configured.

After the configuration is complete, you need to restart the listening service and database service.

3. Run the following commands under the dos window:

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

This step is to invoke the external DLL file correctly using sql, otherwise there will be a call failure error, and the above st_shapelib.dll file is installed after the ArcSDE file under the path. This file is required to operate on data of type ST_GEOMETRY.

Check the user_libraries table to see if the dll file pointed to by st_shapelib is correct.

Then use the test case in Step 1 to test whether the ST_GEOMETRY type field can be operated on. If it fails, please re-check your environment configuration according to the above configuration.

Several reference websites:
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/

Supplementary knowledge points:
Concept of version, delta table,base table, default version, etc.
http://edndoc.esri.com/arcsde/9.2/concepts/versioning/basicprinciples/deltatables.htm#BaseTable
In the database, if the tables in the sde are registered, there will be base table,add table,delta table. If insert has a record, then a new record will appear in the add table of the database.
If you delete a record, you record a deleted record in the delta table.

For how to use sql to manipulate st_geometry type data, please refer to:

Editing Versioned Data in Oracle with SQL