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

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.

Check out this question / answer ( stackoverflow.com/questions/1745811/… ) — pmg
– pmg, Commented Nov 17, 2009 at 15:52
You're still going to have to parse the IN clause at runtime using dynamic SQL. — DCookie
– DCookie, Commented Nov 17, 2009 at 15:55

Egor Rogov · Accepted Answer · 2009-11-17 18:43:10Z

1

Probably this AskTom article can help you.

answered Nov 17, 2009 at 18:43

Egor Rogov

5,48828 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Schlüter Over a year ago

Thank you, that looks like something to try out. Follow up on friday to see if it works (and if it is faster).

DCookie · Accepted Answer · 2009-11-17 15:53:26Z

1

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.

edited Nov 17, 2009 at 15:53

answered Nov 17, 2009 at 15:46

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

4 Comments

Patrick Schlüter Over a year ago

I'm not sure, with method 3 it would boil down to the same problem. If I make a OPEN C USING :LanCodes will it work? One thing I forgot to mention, is that I cannot build a dynamic statement in a string with changing language codes as this would explode the statement cache of the database.

DCookie Over a year ago

Then I think you need to build a temp table and populate it with your IN values, and join to that.

Patrick Schlüter Over a year ago

Thank you, but that's even worse (it would take months to get something like that through to our DBAs, we're here in an administration, this would involve a lot of paperwork). I think I will have to live with our current solution (which is not bad btw.). It was only to gain a little on the selects which are the dominant part of the application.

DCookie Over a year ago

Plus, you lose something by having to insert the values in the temp table as well.

Philip Schlump · Accepted Answer · 2009-11-18 17:08:24Z

0

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.

answered Nov 18, 2009 at 17:08

Philip Schlump

3,1542 gold badges22 silver badges17 bronze badges

1 Comment

Patrick Schlüter Over a year ago

That won't be possible. It's for the EU, so we have to handle 23 official languages (+2 others in the near future), so we have 23! possibilities, that's 25x10^7, the table would be almost as big (or even bigger) as our main database.

Lovell · Accepted Answer · 2009-11-19 16:33:12Z

0

... 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.

answered Nov 19, 2009 at 16:33

Lovell

1

1 Comment

Patrick Schlüter Over a year ago

Yes, that's basically what DCookie suggested. The problem with that approach is that it has a bad influence on the statement cache of Oracle. Overflowing that cache degrades the performance globally (had the effect some time ago for something else). My question was only to save a little on round-trips.

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related