Use the Oracle dbms_sql package to fetch st_geometry attributes

Summary

Oracle developers using PL/SQL require the ability to use the Oracle dbms_sql package for constructing cursors to execute SQL statements and fetching st_geometry attributes.

The Oracle dbms_sql package provides the ability to fetch individual rows or to array fetch SQL result sets. When iterating over large result sets, array fetching provides the best performance.

Unfortunately, the dbms_sql package does not provide a mechanism to array fetch user defined types (UDT), such as an st_geometry attribute. Because of this limitation, the instructions provided demonstrates how to array fetch an st_geometry's native attributes and reconstruct the st_geometry object.

Procedure

1. The first example shows a basic example executing a SELECT statement with a where clause for a specific neighborhood and fetching two attributes, one of which is an st_geometry attribute.
   
   

   Code:
   SQL> DECLARE
     2
     3    st_cursor      INTEGER;
     4    sqlstr         VARCHAR2(512);
     5    cursor_status  INTEGER;
     6    oid            NUMBER(38);
     7    st_shape       sde.st_geometry;
     8
     9   BEGIN
    10
    11     st_cursor := dbms_sql.open_cursor;
    12
    13     sqlstr := 'SELECT objectid, shape FROM neighborhoods WHERE name = ''WESTWOOD''';
    14
    15     dbms_sql.parse(st_cursor,sqlstr,dbms_sql.native);
    16
    17     dbms_sql.define_column(st_cursor,1,oid);
    18     dbms_sql.define_column(st_cursor,2,st_shape);
    19
    20     cursor_status := dbms_sql.execute(st_cursor);
    21     cursor_status := dbms_sql.fetch_rows(st_cursor);
    22
    23     IF cursor_status = 1 THEN
    24       dbms_sql.column_value(st_cursor,1,oid);
    25       dbms_sql.column_value(st_cursor,2,st_shape);
    26     END IF;
    27
    28     dbms_sql.close_cursor(st_cursor);
    29
    30   END;
    31   /
   
   PL/SQL procedure successfully completed.

   
   The above example demonstrates creating the cursor using dbms_sql.open_cursor, defining the SQL statement using the variable named sqlstr, parsing the statement using dbms_sql.parse, defining the two columns that are in the SQL statements SELECT list using dbms_sql.define_column, executing the SQL statement with dbms_sql.execute, and fetching the rows with dbms_sql.fetch_rows. The IF block verifies one row is returned from the query and sets the local variables for the results fetched using dbms_sql.column_value. The last step is to close the cursor using dbms_sql.close_cursor.
2. The second example shows how to fetch multiple attributes using dbms_sql.
   
   

   Code:
   SQL> DECLARE
     2
     3    oid_tab        dbms_sql.number_table;
     4    entity_tab     dbms_sql.number_table;
     5    numpts_tab     dbms_sql.number_table;
     6    srid_tab       dbms_sql.number_table;
     7    minx_tab       dbms_sql.number_table;
     8    miny_tab       dbms_sql.number_table;
     9    maxx_tab       dbms_sql.number_table;
    10    maxy_tab       dbms_sql.number_table;
    11    points_tab     dbms_sql.blob_table;
    12
    13    search_shape   sde.st_geometry;
    14    st_shape       sde.st_geometry := sde.st_geometry(0,0,0,0,0,0,0,0,0,0,0,0,0,empty_blob());
    15
    16    st_cursor      INTEGER;
    17    cursor_status  INTEGER;
    18    sqlstr         VARCHAR2(512);
    19
    20   BEGIN
    21
    22     st_cursor := dbms_sql.open_cursor;
    23     sqlstr := 'SELECT shape FROM neighborhoods WHERE name = ''WESTWOOD''';
    24     dbms_sql.parse(st_cursor,sqlstr,dbms_sql.native);
    25     dbms_sql.define_column(st_cursor,1,search_shape);
    26     cursor_status := dbms_sql.execute(st_cursor);
    27     cursor_status := dbms_sql.fetch_rows(st_cursor);
    28     IF cursor_status = 1 THEN
    29       dbms_sql.column_value(st_cursor,1,search_shape);
    30     END IF;
    31     dbms_sql.close_cursor(st_cursor);
    32
    33     st_cursor := dbms_sql.open_cursor; 
    34     sqlstr := 'SELECT a.objectid, a.shape.entity, a.shape.numpts, a.shape.minx, a.shape.miny, a.shape.maxx,
    35                   a.shape.maxy, a.shape.srid, a.shape.points 
    36                FROM parcels a WHERE sde.st_intersects(shape,:shape) = 1
    37                ORDER BY a.objectid';
    38     dbms_sql.parse(st_cursor,sqlstr,dbms_sql.native);
    39 
    40     dbms_sql.define_array(st_cursor,1,oid_tab,100,1);
    41     dbms_sql.define_array(st_cursor,2,entity_tab,100,1);
    42     dbms_sql.define_array(st_cursor,3,numpts_tab,100,1);
    43     dbms_sql.define_array(st_cursor,4,minx_tab,100,1);
    44     dbms_sql.define_array(st_cursor,5,miny_tab,100,1);
    45     dbms_sql.define_array(st_cursor,6,maxx_tab,100,1);
    46     dbms_sql.define_array(st_cursor,7,maxy_tab,100,1);
    47     dbms_sql.define_array(st_cursor,8,srid_tab,100,1);
    48     dbms_sql.define_array(st_cursor,9,points_tab,100,1);
    49
    50     dbms_sql.bind_variable(st_cursor,':shape',search_shape);
    51
    52     cursor_status := dbms_sql.execute(st_cursor);
    53
    54     LOOP
    55
    56       cursor_status := dbms_sql.fetch_rows(st_cursor);
    57       dbms_sql.column_value(st_cursor,1,oid_tab);
    58       dbms_sql.column_value(st_cursor,2,entity_tab);
    59       dbms_sql.column_value(st_cursor,3,numpts_tab);
    60       dbms_sql.column_value(st_cursor,4,minx_tab);
    61       dbms_sql.column_value(st_cursor,5,miny_tab);
    62       dbms_sql.column_value(st_cursor,6,maxx_tab);
    63       dbms_sql.column_value(st_cursor,7,maxy_tab);
    64       dbms_sql.column_value(st_cursor,8,srid_tab);
    65       dbms_sql.column_value(st_cursor,9,points_tab);
    66
    67       EXIT WHEN cursor_status != 100;
    68     END LOOP;
    69
    70     dbms_sql.close_cursor(st_cursor);
    71
    72     FOR i IN 1..entity_tab.COUNT LOOP
    73
    74       st_shape.entity := entity_tab(i);
    75       st_shape.numpts := numpts_tab(i);
    76       st_shape.minx := minx_tab(i);
    77       st_shape.miny := miny_tab(i);
    78       st_shape.maxx := maxx_tab(i);
    79       st_shape.maxy := maxy_tab(i);
    80       st_shape.minz := 0;
    81       st_shape.maxz := 0;
    82       st_shape.minm := 0;
    83       st_shape.maxm := 0;
    84       st_shape.area := 0;
    85       st_shape.len := 0;
    86       st_shape.srid := srid_tab(i);
    87       st_shape.points := points_tab(i);
    88
    89     END LOOP;
    90
    91     oid_tab.delete;
    92     entity_tab.delete;
    93     numpts_tab.delete;
    94     minx_tab.delete;
    95     miny_tab.delete;
    96     maxx_tab.delete;
    97     maxy_tab.delete;
    98     srid_tab.delete;
    99     points_tab.delete;
   100
   101   END;
   102   /
   
   PL/SQL procedure successfully completed.

   
   Again, the example above demonstrates how to create a cursor, define the SQL statement, define the arrays for fetching the attributes using dbms_sql.define_array, how to bind a variable (:shape) used in the where clause using dbms_sql.bind_variable, and most importantly, how to fetch each st_geometry attribute into a dbms_sql type and reconstruct the local variable st_shape geometry. A user extending this example would then implement their own code to work with the local st_shape st_geometry attribute on line 88. The final steps on lines 91 through 99 delete the attributes from the dbms_sql type objects.