HOW TO

Use the Oracle dbms_sql package to fetch st_geometry attributes

Last Published: April 25, 2020

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.

Article ID:000010122

Software:
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic