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.