How to specify a list of variable expressions in a Pro * C request?

Question

I have a problem with a Pro * C request that I am trying to optimize.

To explain, our application searches for strings in a huge database. These strings exist in several languages, and the old code selects a string for each language in the array. Now that these queries are the most expensive part of our application, I wanted to make only one request, which is written directly to the array.

Language codes are 2-letter ISO-639 codes (en for English, fr for French).

Old way (this is just a simplified code to show intent)

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 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 for obfuscation only */ INTO :arr:arr_ind FROM table WHERE id=:uniqid AND language IN (:LanCodes); 

but I don’t know how I should define LanCodes.

It works with constant (compile time) like this

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

but this is not useful, as languages ​​can 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 for obfuscation only */ INTO :arr:arr_ind FROM table WHERE id=:uniqid AND language IN (:LanCodes); 

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

So my question is: does anyone know how to make this work? The Oracle documentation is so great, I don't know what to look at. I tried different ways, but no one worked.

EDIT Well, I found a solution that works. He is not elegant, he does not advance, but works well. I put the list of OR clauses in my query and return what I need in the form I need.

 EXEC SQL SELECT * /* Don't look at the *, it 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]); 

This is faster when there are more than two languages, so I call this option or the old one depending on the number of languages ​​to extract.

Answer 1

Perhaps this AskTom article may help you.

Answer 2

You cannot do this without Oracle Dynamic SQL . You will need to create your IN section at runtime and EXECUTE IMMEDIATE. At least you can use method 1 based on your queries.

Answer 3

I used the table before compiling the identifier and the rowset, where the rows are a permutation of the possible values ​​in the "in" list. Then I join the table based on ID and this gives me the results that 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 is select the correct "ID" value 2, 3, 4 ... and put it in to join.

Answer 4

... Main String: = 'Select * FROM table WHERE id =: uniqid AND language IN'; - can be divided into two parts: uniqd ... Select Language_code in v_string from x_table; loop Copy and Concat v_string to LanCode_String and with ``,; end of cycle; .. Concat Lancode to Main String. .. Prepare and execute the main line.