Pro*C Tricks

Consider the following Pro*C snippet of code that retrieves all records via cursor.

EXEC SQL DECLARE tol_cursor FOR
SELECT tol_value
FROM tolerances;
EXEC SQL OPEN tol_cursor;
while (bMoreData)
{
EXEC SQL FETCH tol_cursor
INTO v_value;
}
EXEC SQL CLOSE tol_cursor;

Suppose you had a huge amount of data to retrieve. You can split the code into two Pro*C functions. One can initialize the cursor. Another can use the cursor previously initialized and retrieve the data.

void init_query()
{
EXEC SQL DECLARE tol_cursor FOR
SELECT tol_index, tol_value
FROM tolerances;
EXEC SQL OPEN tol_cursor;
}

int get_value()
{
EXEC SQL FETCH tol_cursor
INTO v_value;
return v_value;
}

In other words, you do not have to set up a Pro*C cursor in the same function where you use it. This can come in handy if you want to make multiple calls to a Pro*C function to get small chunks of a large data set.