Hi Tom! In Pro*C, when working with pointers to dynamic arrays, you can specify the FOR clause to indicate what is the size of your dynamic array, like:
EXEC SQL BEGIN DECLARE SECTION;
int* ids = (int*)malloc(sizeof(int) * n); // n is some runtime variable
int nrows = n;
EXEC SQL END DECLARE SECTION;
EXEC SQL FOR :nrows insert into some_table (id) values (:ids);
However, the Pro*C documentation says that you can't use the FOR syntax for SELECT statements, because you will get an error:
PCC-E-0056: FOR clause not allowed on SELECT statement at ...
This sentence (and all other sentences stating the same thing in different points of the document) has been in the Pro*C and Oracle Precompiler docs since at least version 8.
However, at least in 19c (and I think in 11 as well, but I'm not fully sure), that's no longer the case and you can use FOR in SELECT statements without problems.
EXEC SQL BEGIN DECLARE SECTION;
int* output = (int*)malloc(sizeof(int) * n); // n is some runtime variable
int nrows = n;
EXEC SQL END DECLARE SECTION;
EXEC SQL FOR :nrows select id into :output from some_table;
and it works just fine and do what I pretend without issues. However, I wonder, starting from which version was the restriction relaxed?
I have two choices here: assume the FOR clause working in SELECTs is actually a "bug" (it shouldn't work), or it's a feature and the docs are buggy, and I'm inclined for the latter because there's plenty of instances in the Pro*C docs that are outdated:
- The doc claims you need to use CODE=CPP if you want to use C++-style comments (//...). That's false. Pro*C accepts that kind of comments just fine with PARSE=ALL. I guess the reason is that the claim is there in the docs from times when Pro*C only cared about C89 and C90 (because the C++-style comments were added to the C standard in C99), even if that's no longer the case. At some points they increased the support for C90 but forgot to update the docs.
- In the release notes of version 12, it says they have added a new option called trim_password. However, the option is totally missing from the documented list of options all the way up to 23c (but using the option on the command line "compiles", at least in 19c).
- It claims that host variables of type `char` and `char[n]` uses the same default mapping demanded by `CHAR_MAP`. False, host variables of type `char` are always mapped to the external type `VARCHAR2` (discovered first by trying to solve a bug in my code, and then confirmed by looking at the type code assigned to each host variable in the sqlcud0 array of the generated code).
All of this tells me you that you have to rely on your own experiments on top of what the doc says, because the docs are, at the very least, marginally maintained.
But even if my experiments say otherwise, I would like to have at least "something", "somewhere" (some Oracle post, some AskTom question, some release notes, something...), stating that the FOR clause in SELECT statements is supported, even if the official, actual docs fails to do so. However, I haven't been able to find
any single comment on all the gargantuously extent of the whole mighty Internet about it.
So, is it supported? Is it not? What is buggy about the FOR clause, the compiler or the docs? I hope it's supported because it's an awesome feature. It removes you in a highly percentage of cases the need to do an iterative processing of rows and get all you need in one go, in a very straightforward simple sentence, without cursors or anything.