Skip to Main Content
  • Questions
  • Pro*C: FOR clause in SELECT statements is no longer an error

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 29, 2024 - 10:21 am UTC

Last updated: November 11, 2024 - 9:29 am UTC

Version: 19c

Viewed 100+ times

You Asked

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.

and Connor said...

I don't see what you're seeing

Pro*C/C++: Release 21.0.0.0.0 - Production on Mon Nov 11 04:27:40 2024
Version 21.13.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /u01/app/oracle/product/21/precomp/admin/pcscfg.cfg

Semantic error at line 92, column 10, file sql_for.pc:
EXEC SQL FOR :nrows select empno into :output from emp;
.........1
PCC-S-02372, FOR clause not allowed in SELECT statement



Rating

  (1 rating)

Comments

It works for me using 19c

A reader, November 11, 2024 - 4:46 pm UTC

Hi Connor, thank you for your reply.

I'm using Pro*C 19 and it works fine for me. For example this works as intented and print the right thing (I work with C++ though but this is a C compatible example):

    EXEC SQL BEGIN DECLARE SECTION;
        int* data;
        int nrows;
    EXEC SQL END DECLARE SECTION;
    nrows = 10;
    data = (int*)malloc(sizeof(int) * nrows);

    EXEC SQL FOR :nrows SELECT level INTO :data from dual connect by level <= 10;

    for (int i = 0; i < 10; ++i)
        printf("%d\n", data[i]);


and it works for fine. The version I'm using is

Pro*C/C++: Release 19.0.0.0.0 - Production on Mon Nov 11 17:11:14 2024
Version 19.3.0.0.0


Was there any bug regarding the support for the FOR clause that has been fixed in v21?

That's unfortunate because the FOR clause has worked nicely for years since I tried it the first time in multitude of contexts and the semantics was ok: store each row in each separate element of the array up to nrows. It even worked for pointers to structs and everything was awesome, and remove a lot of lines of code: no need to create/open/close these annoying cursors that breaks RAII semantics and forbid early returns and exceptions when in 99% of cases you just want to fetch all and don't need to "cursor anything".

So, if that's the case that the support for that syntax was accidental but now is fixed in 21c, and I shouldn't rely on the FOR statement anymore as the docs says, is there any way to get that cursor-agnostic syntax by any other route?

What I want to achieve is basically avoid to depend on these manual create/destroy pairs (like cursors) that messed up everything and doubles or triples the places where you have to check for errors. It really makes functions double or triple in size when you use cursors and it's horrible. A pandora box of technical noise.