How to specify a variable expression list in a Pro*C query?

I have a problem with a Pro*C query I'm trying to optimise.

To explain, our application searches for rows in a huge database. These rows exist in several languages and the old code selected a row for each language in an array. Now as these queries are the most time consuming part of our app, I wanted to make only one query which writes directly in an array.

The language codes are 2 letter ISO-639 codes (en for english, fr for french).

Old way (this is only a simplified code to show the intention)

struct ROW arr[MAX_LAN];
struct ROW_IND arr_ind[MAX_LAN];
uint_t LanIdx;
for(LanIdx=0; LanIdx<MAX_LAN; LanIdx++) {
  EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
      INTO :arr[LanIdx]:arr_ind[LanIdx]
      FROM table WHERE id=:uniqid AND language=:LanCode[LanIdx];
}

I would like to do something like this:

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN (:LanCodes);

but do not know how I should define LanCodes.

It works with a constant (compile time) list like this

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN ('en','fr','de');

but this is not useful, as the languages may vary from case to case.

If I write something like

char LanCodes[MAX_LANS*5];
sprintf(LanCodes, "%s", LanCode[LanIdx]);

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND language IN (:LanCodes);

it works only if there is 1 language code in the string.

So my question is, does anybody know how to make this work? The Oracle documentation is so big, I don't know where to look at. I tried different ways, but none worked.

EDIT Ok, I found a solution that works. It's not elegant, it's not advanced but it works well. I put a list of OR clauses in my query and it returns what I need in the form that I need.

EXEC SQL SELECT *  /* Don't look at the *, it's for obfuscation only */
    INTO :arr:arr_ind
    FROM table WHERE id=:uniqid AND (
                language=:v1[ 0] OR
                language=:v1[ 1] OR
                language=:v1[ 2] OR
                language=:v1[ 3] OR
                language=:v1[ 4] OR
                language=:v1[ 5] OR
                language=:v1[ 6] OR
                language=:v1[ 7] OR
                language=:v1[ 8] OR
                language=:v1[ 9] OR
                language=:v1[10] OR
                language=:v1[11] OR
                language=:v1[12] OR
                language=:v1[13] OR
                language=:v1[14] OR
                language=:v1[15] OR
                language=:v1[16] OR
                language=:v1[17] OR
                language=:v1[18] OR
                language=:v1[19] OR
                language=:v1[20] OR
                language=:v1[21] OR
                language=:v1[22] OR
                language=:v1[23] OR
                language=:v1[24] OR
                language=:v1[25] OR
                language=:v1[26] OR
                language=:v1[27] OR
                language=:v1[28] OR
                language=:v1[29] OR
                language=:v1[30]);

It's faster when there is more than 2 languages, so I call this variant or the old one depending on number of languages to fetch.

Solution

Probably this AskTom article can help you.