English

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

The examples below demonstrate performing a single row fetch using dbms_sql and defining an array to perform bulk fetches.

  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.