Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Austin, TX
    Posts
    6

    Unanswered: Pro*C: where col in (:c_array) ?

    Hello,
    I want to do something seemingly simple in my Pro*C code:

    Code:
    select *
    from some_table
    where some_col in (:c_array);
    Is that possible? It seems like it should be since it seems like it would be a fairly common operation.

    Thanks for the help.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    No.

    From the pro*c manual:
    "Using Arrays in SQL Statements
    You can use host arrays as input variables in the INSERT, UPDATE, and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements."

    The problem is that each array value could result in zero to N rows being returned. Therefore, oracle cannot tell you which returned row(s) belong to which input value, so they just made the whole thing illegal.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Apr 2004
    Location
    Austin, TX
    Posts
    6
    Thanks for the reply.

    I don't understand why one would care which array values resulted in which rows returned. Admittedly, I'm very inexperienced with this stuff though.

    Any suggestions on how to accomplish my goal? (Which is I want to select all rows from a table where a column in each row matches up to any element in a c array).

    Thanks.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    It's been a long time since I've done pro*c (I don't even have a c compiler anymore), so the first one is just a wild guess.

    change the where clause to a subquery:
    select *
    from some_table
    where some_col in (select :c_array from dual);

    if that doesn't work (I wouldn't be surprised), you can use a temp table and make it a 2 step process:
    insert into temp_table(col) values(:c_array);

    select *
    from some_table
    where some_col in (select col from temp_table);
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •