Set Oracle bind variables when using st_geometry operators

Summary

When building an application with Oracle, which executes SQL statements containing dynamic predicate filters, developers must incorporate the binding of predicate values (verses literal strings) to ensure that the performance and scalability of the system is not impacted.

Without binding the predicate values in a SQL statement, each execution of the statement will be parsed by the database. Frequent parsing can quickly lead to poor performance and the potential of the application not being able to scale and support a large number of concurrent users.

Instructions provided describe how to bind the literal values for a SQL statement using the st_within relational operator and the constructor operator st_polyfromtext.

Procedure

1. Declare the variables that are going to be used in the SQL statement.
   
   

   Code:
   SQL> VARIABLE poly_shp VARCHAR2(512)
   SQL> VARIABLE srid NUMBER

   
   Once the variables have been created, each variable's values can be set.
2. Assign values to each variable. The first example demonstrates assigning a string to the variable poly_shp. The value represents the literal string argument to the constructor st_polyfromtext.
   
   

   Code:
   SQL> EXECUTE :poly_shp := 'POLYGON (( 1505000 -1072000, 1505000 -1076000, 1512500 -1076000, 1512500 -1072000, 1505000 -1072000))';
   
   PL/SQL procedure successfully completed.

   
   The next variable to assign is the spatial reference number. This is, again, a required value for the st_polyfromtext operator.
   
   

   Code:
   SQL> EXECUTE :srid := 16;
   
   PL/SQL procedure successfully completed.

   
   With the variables declared and values set, the SQL statement can be executed using the variables as the input values verses literal strings.
   
   

   Code:
   SQL> SELECT count(*) FROM street 
     2  WHERE sde.st_within(shape,sde.st_polyfromtext(:poly_shp, :srid)) = 1;