Question

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.

Answer 1

Probably this AskTom article can help you.

Answer 2

You can t do this without Oracle Dynamic SQL. You will have to build your IN clause at runtime and EXECUTE IMMEDIATE. At least you can use Method 1, based on your queries.

Answer 3

I have used a table before composed of an ID and a set of rows where the rows are the permutation of the possible values in the "in" list. Then I join to the table based on the ID and it gives me the results I need.

create table permute (
  id number,
  lang char(2)
);
create index permute_p1 on permute ( lang, id );
insert into permute ( id, lang ) values ( 1,  en  );
insert into permute ( id, lang ) values ( 2,  en  );
insert into permute ( id, lang ) values ( 2,  fr  );
...

All you have to do then is pick the correct "ID" value 2, 3, 4 ... and put that into the join.

Answer 4

... Main String:= Select * FROM table WHERE id=:uniqid AND language IN ; -- can split into two to accomadate :uniqd ... Select Language_code into v_string from x_table; loop Copy & Concat v_string to LanCode_String and with , ; end loop; .. Concat Lancode to Main String. .. Prepare and execute the Main String.

友情链接