English

How To: 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

Instructions provided demonstrate how to create a table containing both sde.st_geometry and mdsys.sdo_geometry, use the Oracle supplied dbms_sql package to describe the table, and how to print out if the attribute is either st_geometry or sdo_geometry.

  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.