Skip to Main Content
  • Questions
  • Passing array size for a structure dynamically in PRO*C using a select query in a function.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Alagu.

Asked: October 10, 2016 - 5:09 am UTC

Last updated: October 11, 2016 - 12:33 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,
I have declared a C variable in declare section and trying to get its value using a select query from a function as follwing,

Note: I have mentioned only relevant codelines.
----------------------------------------------------------------
#include <stdio.h>
#include <stdlib.h>
int array_size; /* C variable declaration */

EXEC SQL BEGIN DECLARE SECTION;
int max_rec; /* Bind variable */
EXEC SQL END DECLARE SECTION;

typedef struct {
char custNo[20];
int feeOrder;
char feeCode[20];
double dtlTotal;
}dtlRecord;

dtlRecord dtlRec[array_size];

int getmaxrec()
{
EXEC SQL
select max(count(*)) into :max_rec from main_table where main_month=to_date('20160822','YYYYMMDD')and cust_no in (SELECT cust_no FROM dup_table WHERE GROUP_ID = 'A') group by cust_no;
array_size = max_rec;
return array_size;
}

/***********************MAIN FUNCTION***********************/
int main (int argc, char *argv[])

/* other initializing functions */
getmaxrec();

if (!function_that_uses_structure_variables())
{
printf("\nReports completed at time");
}

exit (0);
}

------------------------------------------------------------
When I compile I get compilation error ": can not declare variably modified type at file scope" at line
-------------------------------
dtlRecord dtlRec[array_size];
-------------------------------

Is that possible to give the array size dynamically ? We have also tried the same by initializing C variable but getting same error. Or any other way to get this ?

Please let me know your suggestion.
Thanks in advance !!!

and Connor said...

My advice is pretty simple..... *dont* dynamically set it.

Life just gets complicated when you do this...When "select max(count(*)) into :max_rec" returns a HUGE number, then splat, your array is about to kill your RAM. So you can't just cater for *any* number of entries.

So now we have to have a limit per fetch...which means we have to code the processing in a loop (ie, process the first batch, then fetch the next batch, etc). And if I'm going that, then what do I really gain by having it dynamic ? I may as well pick a largest size I'm happy to use, and go with that.

In particular, you get a diminishing set of returns as the arraysize gets large. So do a little benchmarking, and pick a number that works best for your environment and dont make things complex when they dont need to be.

Commonly optimal array sizes for fetching are (say) between 100 and 500. Try them and see how you go.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Calrification

Alagu Raja, October 10, 2016 - 7:57 am UTC

This array size does have anything to do with batch fetching. This is just a maximum number of records which we wish to initialize dynamically which is not going to change after execution of script. This is to avoid future expansion of array size due to increase in data volume.
Connor McDonald
October 11, 2016 - 12:33 am UTC

That is my point.

"This is to avoid future expansion of array size due to increase in data volume."

is asking for trouble. Because data volume is potentially infinite, and array sizes definitely arent.