SQL Statement IN Condition Oracle

Permanently deleted user

12. Oktober 2017 19:49

I'm trying to pass a SQL Query of ObjectIDs and returning the geometry to select values in the HTML5Viewer. The reason I'm not passing the Where Clause into a Query Task is that the list can have over 1000 items. So a tuple is a way to get around that restriction. I'm getting an ORA-00920 Invalid Relational Operator error. The Sql statement runs flawlessly in Toad. My Sql statement is below:

 

  SELECT distinct OBJECTID, SHAPE FROM CULVERTS WHERE (1,OBJECTID) IN (:OBJ)

 

Where OBJ is a paramter passed in as a String from values collected before the SqlQuery is fired. The values are a tuple:

 

(1,7253),(1,10772),(1,12920),(1,15843),(1,18783),(1,18823),(1,16839).

 

Am I missing syntax with the above statement? Thank you in advance!

0

• 

  Permanently deleted user

  12. Oktober 2017 23:48

  Cody,

   

  That activity does not support SQL 'IN' clauses, and the Product won't change. The same request came in long ago too.

   

   Here is the comment from the Product developer for the same kind of request

   

  "An activity like this that accepts a string argument that is then inserted directly into a non-parameterized SQL statement would open us SQL injection attacks. That is why we opted to have the SQL activities use a property for the command.

   

  While the above code is perfectly well written... this type of activity is extremely risky. We should not recommend this to customers unless they sanitize the SQL statement or ensure that the statement does not contain any user supplied values."

   

  However, you can implement another way though.

   

  Refer the followings:

   

  1. https://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S

   

  2. https://stackoverflow.com/questions/5401641/passing-a-sql-parameter-to-an-in-clause-using-typed-datasets-in-net

   

  3. https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement

  0
• 

  Permanently deleted user

  13. Oktober 2017 13:59

  Hi Munhwan-

   

  Thank you for the reply. I will run through the examples you sent and see how to adjust my string to fit those parameters. Thanks again for the resources!

  0
• 

  Permanently deleted user

  18. Oktober 2017 15:09

  I ended up creating an Assign activity and making my query_string = "OBJECTID in ("+string.Join(") OR OBJECTID in (",listOIDs)+")"

   

  Where listOIDs is my list of ObjectIDs returned from the sql query. It's not the cleanest or most efficient query, but it works without issues while returning more than 1000 records.

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.