HeywoodFloyd HeywoodFloyd - 2 months ago 7
C++ Question

Getting Data into Host Arrays from Oracle PL/SQL

I'd like to pull a column of data in a table into a host array. In my table, DOB is an integer type. My C++ code looks like this:

EXEC SQL BEGIN DECLARE SECTION;
int birthdays[10];
EXEC SQL END DECLARE SECTION;

...//Code to connect with the database

EXEC SQL EXECUTE
DECLARE
...
BEGIN
SELECT DOB INTO :birthdays FROM DRIVER_LICENSE WHERE DL_NUMBER < 10;
END


The DOB column is of integer type, and the DL_NUMBERS in the DRIVER_LICENSE table are numbered from 0. When I try to compile this, I get the error "PLS-S-00597, expression "BIRTHDAYS' in the INTO list is of wrong type"

I can run the select if it's not in an EXECUTE. The C++ code:

EXEC SQL BEGIN DECLARE SECTION;
int birthdays[10];
EXEC SQL END DECLARE SECTION;

SQL EXEC SELECT DOB INTO :birthdays FROM DRIVER_LICENSE WHERE DL_NUMBER < 10;


Gives me the numbers from the DOB column of the DRIVER_LICENSE table.

I'm trying to use PL/SQL to consolidate a lot of SQL calls to minimize communication with the server. I can get the information into the C++ birthdays array by looping through a cursor and assign values to the birthday array elements one at a time, but that seems really inefficient.

Answer

I know nothing about your C++ library/framework/whatever calling context, but in PL/SQL select into expects a single row result set and the bind variables have to be scalar variables. int birthdays[10] looks like an array so the PL/SQL compiler error message in your first example makes perfectly sense.

I don't think your second example could work unless the magic chant SQL EXEC somehow implicitly runs in a PL/SQL context (and raise the same PL/SQL compiler error than the first example). select into is a PL/SQL-only and should raise a SQL parser error in SQL context.

It looks like you expect to obtain a multi row result set, so in PL/SQL you should use select into bulk collect instead.

You should study your C++ calling context SQL co-operation documentation more to find out how it expects to bind into arrays.

Comments