My team maintains an application suite. Some of the applications are almost 15 years old. A lot of the code in the older applications has not been modified in a long time. The older code accesses the database using Oracle Pro*C. This sits well with me because I am a C programmer at heart. Recently we ported the application suite from using the Oracle 8 client to the Oracle 10g client. We encountered a number of problems with the Pro*C code. Right now we have resolved most of the problems.While debugging some infrequently used code, I discovered all types of disturbing Pro*C code. I should have known something was amiss when I found some Pro*C functions that were very long. This was a clue that somebody was not following good design. Then I found a lot of SQL which was executed. By itself this is not a problem. However the developer created separate string variables to hold each of the SQL statements. The result was a huge declaration section in the function.
It was the last issue that brought the most alarm to me. Many of the variables used to hold the dynamic SQL strings were huge. We had code that looked like this:
char sql_string1[10000];
char sql_string2[10000];
char sql_string3[10000];
char sql_string4[10000];
…
This seemed crazy. There were huge arrays for strings all being put on the stack. I always assumed this would not work since there is a limited stack size set apart by the compiler. In fact there were a couple such functions which just did not work. So I converted them to be strings with memory allocated on the heap. Maybe these were amateur C programmers that initially coded the application. The amazing thing is that this has worked for such a long time.
Perhaps the true solution is to move all the Pro*C to a more modern database access technique. However the C programmer inside of me advises against such a move.
