Describe a table in Oracle with dbms_sql to discover an st_geometry attribute

Summary

Developing applications in Oracle with PL/SQL, which work with native spatial types in the database, at times require the ability to discover an attribute's type definition. An example of this is the ability to describe a table and discover if the spatial attribute is using sde.st_geometry or mdsys.sdo_geometry.

This becomes very important if the user is developing applications, which, at run time, must be knowledgeable of the underlying spatial attribute to construct the proper SQL statements when working with the data.

This article provides examples on how to use the dbms_sql package and describe a table containing st_geometry and sdo_geometry spatial attributes.

Procedure

1. Create a table with both sde.st_geometry and mdsys.sdo_geometry attributes, which will be used in step 2.
   
   

   Code:
   SQL> CREATE TABLE spatial_table (
     2  objectid  NUMBER(38) NOT NULL,
     3  st_shape  sde.st_geometry,
     4  sdo_shape mdsys.sdo_geometry);
   
   Table created.

   
   
2. The example shows an Oracle PL/SQL script, which uses the Oracle supplied dbms_sql package to describe a table and print out the attribute name, the attribute type, and the attribute owner.
   
   If the release is Oracle 10g, the user cannot discover the name of the attribute type, just the owner. Based on this limitation, the user must make an assumption that if the attribute is a user defined type (UDT) and the owner is SDE, then the type must be st_geometry. The same assumption can be made that if the owner of the type is mdsys, then the type must be sdo_geometry.
   
   

   Code:
   SQL> DECLARE
     2
     3    l_Cursor        INTEGER DEFAULT dbms_sql.open_cursor;
     4    l_Stmt          VARCHAR2(4000);
     5    l_colCnt        NUMBER DEFAULT 0;
     6    l_descTbl       dbms_sql.desc_tab;
     7
     8   BEGIN
     9
    10     dbms_sql.parse(l_Cursor, 'SELECT * FROM spatial_table', dbms_sql.native);
    11     dbms_sql.describe_columns(l_Cursor, l_colCnt, l_descTbl);
    12
    13     FOR i IN 1..l_colCnt LOOP
    14
    15       IF l_descTbl(i).col_name = 'ST_SHAPE' OR l_descTbl(i).col_name = 'SDO_SHAPE' THEN
    16         
    17         dbms_output.put_line('col_name: '||l_descTbl(i).col_name);
    18         dbms_output.put_line('col_type: '||l_descTbl(i).col_type);
    19         dbms_output.put_line('col_schema_name: '||l_descTbl(i).col_schema_name);
    20
    21       END IF;
    22
    23     END LOOP;
    24    
    25     dbms_sql.close_cursor(l_Cursor);
    26
    27   END;
    28   /
   
   col_name: ST_SHAPE
   col_type: 109
   col_schema_name: SDE
   col_name: SDO_SHAPE
   col_type: 109
   col_schema_name: MDSYS
   
   PL/SQL procedure successfully completed.

   
   A col_type value of 109 indicates that the attribute is a UDT. Using the value returned from col_schema_name, the user can conclude that the attribute is either st_geometry or sdo_geometry.
   
   Starting with Oracle 11g, the Oracle supplied dbms_sql package now returns the name of the UDT. The user can, therefore, discover the name of the type and subsequently know how to work with the attribute. To leverage this new functionality, consume the describe_columns3 procedure.
   
   

   Code:
   SQL> DECLARE
     2
     3    l_Cursor        INTEGER DEFAULT dbms_sql.open_cursor;
     4    l_Stmt          VARCHAR2(4000);
     5    l_colCnt        NUMBER DEFAULT 0;
     6    l_descTbl       dbms_sql.desc_tab3;
     7
     8   BEGIN
     9
    10     dbms_sql.parse(l_Cursor, 'SELECT * FROM spatial_table', dbms_sql.native);
    11     dbms_sql.describe_columns3(l_Cursor, l_colCnt, l_descTbl);
    12
    13     FOR i IN 1..l_colCnt LOOP
    14
    15       IF l_descTbl(i).col_name = 'ST_SHAPE' OR l_descTbl(i).col_name = 'SDO_SHAPE' THEN
    16         
    17         dbms_output.put_line('col_name: '||l_descTbl(i).col_name);
    18         dbms_output.put_line('col_type: '||l_descTbl(i).col_type);
    19         dbms_output.put_line('col_schema_name: '||l_descTbl(i).col_schema_name);
    20         dbms_output.put_line('col_type_name: '||l_descTbl(i).col_type_name);
    21
    22       END IF;
    23
    24     END LOOP;
    25    
    26     dbms_sql.close_cursor(l_Cursor);
    27
    28   END;
    29   /
   
   col_name: ST_SHAPE
   col_type: 109
   col_schema_name: SDE
   col_type_name: ST_GEOMETRY
   col_name: SDO_SHAPE
   col_type: 109
   col_schema_name: MDSYS
   col_type_name: SDO_GEOMETRY
   
   PL/SQL procedure successfully completed.