HOW TO
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.
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.
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.
Article ID:000010122
Get help from ArcGIS experts
Download the Esri Support App