st_geometry syntax for Oracle Sql

Question

I'm trying to pass in a geometry(polygon) collected as an ESRI.ArcGIS.Client.Geometry.Geometry type on a map and passing it as an IN parameter into an oracle sql query. I'm also trying to use st_intersects to find features that intersect the passed-in geometry.

I can't seem to get my syntax correct. Here is my sql query:

WITH cons
     AS (SELECT DISTINCT culvertgid , condition AS condition_code,
                         value     AS condition_literal
         FROM   culvert_inspect
                LEFT OUTER JOIN (SELECT Extractvalue (CodedValues.column_value,
                                        'CodedValue/Code') AS Code,
                                        Extractvalue (CodedValues.column_value,
                                        'CodedValue/Name') AS VALUE
                                 FROM   sde.gdb_items_vw items
                                        inner join sde.gdb_itemtypes itemtypes
                                                ON items.TYPE = itemtypes.uuid,
                                        TABLE (
        Xmlsequence (Xmltype (DEFINITION).EXTRACT (
                     '/GPCodedValueDomain2/CodedValues/CodedValue')))
                        CodedValues
                 WHERE  itemtypes.name = 'Coded Value Domain'
                        AND items.name = 'OVERALL_CONDITION')
                coded_values
             ON culvert_inspect.condition = coded_values.code)
SELECT DISTINCT ci.condition_code, condition_literal
FROM   culverts c
       INNER JOIN  cons ci
               ON ( c.globalid_1 = ci.culvertgid )
WHERE sde.st_intersects(c.shape, sde.ST_Polygon (-10685820.278500814 5060357.6856116494, -10685820.278500814 5101327.9327725023, -10645461.527566245 5101327.9327725023, -10645461.527566245 5060357.6856116494, -10685820.278500814 5060357.6856116494)) = 1;
ORDER  BY condition_code

The code before the WHERE statement gets the domain descriptions from a table called culvert_inspect. What I'm trying to do is return only the domain values that are in the features that are within the geometry that is getting passed in. Everything works up until the WHERE statement. I've tested this same query and instead of passing in geometry, I pass in a value from a field and return expected results.

Answer 1

I figured out the syntax.

WITH cons
 AS (SELECT DISTINCT
            culvertgid,
            condition AS condition_code,
            VALUE AS condition_literal
       FROM culvert_inspect
            LEFT OUTER JOIN
            (SELECT EXTRACTVALUE (CodedValues.COLUMN_VALUE,
                                  'CodedValue/Code')
                       AS Code,
                    EXTRACTVALUE (CodedValues.COLUMN_VALUE,
                                  'CodedValue/Name')
                       AS VALUE
               FROM sde.gdb_items_vw items
                    INNER JOIN sde.gdb_itemtypes itemtypes
                       ON items.TYPE = itemtypes.uuid,
                    TABLE (
                       XMLSEQUENCE (
                          Xmltype (DEFINITION).EXTRACT (
                             '/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues
              WHERE     itemtypes.name = 'Coded Value Domain'
                    AND items.name = 'OVERALL_CONDITION') coded_values
               ON culvert_inspect.condition = coded_values.code)
SELECT DISTINCT ci.condition_code, condition_literal
FROM culverts c INNER JOIN cons ci ON (c.globalid_1 = ci.culvertgid)
WHERE sde.ST_Intersects (c.shape, sde.st_polygon ('polygon ((-10643359.509288402 5078836.3371996051, -10643359.509288402 5082008.4738734355, -10640378.465185285 5082008.4738734355, -10640378.465185285 5078836.3371996051, -10643359.509288402 5078836.3371996051))', SRID)) = 1
ORDER BY condition_code;