• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

.net equivalent of Java's Statement.setArray

I have oracle table and i am trying to run a select statement -
select * from mytable where id in { .... }

I need to pass the list of IDs as an oracleparameter. Because if i just put the list of  ID as string and if SQL Statement becomes very long it will crash...

So i am trying pass integer array as an oracle parameter
dim myidlist() as integer
Dim oraparobjlist As OracleParameter
oraparobjlist = New OracleParameter("myidlist", OracleType.?????)
But it does not work...

I know in java we can do something like this-
statement stmt;
stmt.setArray(1, myidlist)  
How i can do that in .NET ? is it possible ?

0
at999
Asked:
at999
  • 2
2 Solutions
 
ToddBeaulieuCommented:
I'm not into Oracle, but a search for "OracleParameter array" turned up quite a bit of material that should get you there.

http://forums.oracle.com/forums/thread.jspa?messageID=3650465


cmd.CommandType = CommandType.StoredProcedure;OracleParameter[] 
oparams = new OracleParameter[2];
oparams[0] = cmd.Parameters.Add("param2", OracleDbType.Int32, DBNull.Value, ParameterDirection.ReturnValue);
int[] vals = new int[] { 1, 2, 3, 4, 5 };oparams[1] = cmd.Parameters.Add("param1", OracleDbType.Int32, vals.Length, vals, ParameterDirection.Input);oparams[1].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
cmd.ExecuteNonQuery();

Open in new window

0
 
at999Author Commented:
Thanks ToddBeaulieu,

I want to get the Resultset out of it i donot want to run an executenonquery - i want to put that resultset for my DataGrid.
0
 
caga79Commented:
Since you mentioned that the IID list can become very long you can use the following:

Have your query defined on Oracle  as an Oracle PL/SQL procedure, that would return a t_cursor, and call that query within your code using the OracleDataReader Class. Your could even break it down into two pieces if you wanted to.So your .Net code would be something like:
while (myID.Read())
{

   <set the value of the ID you want for mySelectStatement here>

   while (mySelectStatement.Read())
    {
      <Your code goes here>
    }
}

Open in new window

0
 
at999Author Commented:
Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now