problem using Oracle parameters in SELECT IN

by

To pass a set of values, you need to use Oracle’s table or array types.

At first, you create a table type (e.g. for NUMBER):

CREATE TYPE number_table AS TABLE OF NUMBER;

When you create the parameter for the query, declare it as an associative PL/SQL array:

OracleParameter param1 = new OracleParameter(); 
param1.OracleDbType = OracleDbType.Int32; 
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

Then assign some values:

param1 = new int[] { 3857, 3858, 3863, 3285 };

And your query needs a cast:

SELECT * FROM tablename a 
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun

More Related Contents:

  1. Oracle Parameters with IN statement?
  2. How to execute an .SQL script file using c#
  3. Difference between Parameters.Add(string, object) and Parameters.AddWithValue
  4. C# parameterized queries for Oracle – serious & dangerous bug!
  5. C# constructing parameter query SQL – LIKE %
  6. Store value in Yes/No field in Access using C#
  7. How to save leap date ’02/29/2006′ to sql server database? [duplicate]
  8. how to get date difference between two dates using DateDiff Excluding the weekends [duplicate]
  9. C# Data Connections Best Practice?
  10. Is there a good LINQ way to do a cartesian product?
  11. How do I assign by “reference” to a class field in C#?
  12. Read SQL Table into C# DataTable
  13. LINQ to SQL – Left Outer Join with multiple join conditions
  14. How to save image in database using C# [closed]
  15. Code to validate SQL Scripts
  16. How to access ssis package variables inside script component
  17. C# SqlCommand – cannot use parameters for column names, how to resolve?
  18. Application can’t scaffold items
  19. use decimal values as attribute params in c#?
  20. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  21. Why is a SQL float different from a C# float
  22. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  23. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  24. Anonymous type result from sql query execution entity framework
  25. I got error “The DELETE statement conflicted with the REFERENCE constraint”
  26. Execute multiple queries in single Oracle command in C#
  27. Enabling Foreign key constraints in SQLite
  28. no overload for method “ToString” takes 1 arguments
  29. How do I extract data from a DataTable?
  30. Oracle Entity Framework – Generate Entity From Tables in Different Schema

Leave a Comment