Oracle SQL (Pro*C): knowing number of rows of a select beforehand
In a Pro*C file, I have a fetch pattern like this one:
EXEC SQL BEGIN DECLARE SECTION;
typedef struct my_row_t
{
unsigned field1;
unsigned field2;
} my_row;
EXEC SQL END DECLARE SECTION;
void do_query()
{
EXEC SQL BEGIN DECLARE SECTION;
struct my_row* buffer;
unsigned buffer_size;
EXEC SQL END DECLARE SECTION;
buffer_size = /* some tied upper bound on the number of rows to fetch */;
buffer = (my_row*)malloc(buffer_size * sizeof(my_row));
// Create my_cursor based on some select query
// ...
// and now:
EXEQ SQL FOR :buffer_size FETCH my_cursor INTO :buffer;
unsigned number_of_fetched_rows = sqlca.sqlerrd[2];
// At this point, with just one fetch I have all the rows
}
The problem is that, sometimes, you can’t estimate a proper upper bound. I’m talking about queries that won’t return more than a few thousand rows, but I don’t want to allocate storage for, for example, 5000 rows, when a particular query for a particular input will only give you 500.
Is there a way to know in an efficient way, before doing your first fetch (for example, just after opening the cursor), the number of rows to fetch, without executing any count(*)
query (for performance reasons)?
My goal is to “fetch all” in a single pass in the most performant way as possible.
I think it can be done with PL/SQL tables/records, but I don’t know how to “save” or extract the content of a PL/SQL table in a buffer of my own using Pro*C syntax.