CODEWITHSUNDEEP
c++arraysoracle-databaseplsql
HeywoodFloyd
HeywoodFloyd

Reputation: 186

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.

Upvotes: 0

Views: 324

Answers (2)

user272735
user272735

Reputation: 10648

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.

Upvotes: 1

Josh
Josh

Reputation: 51

Your objective is unclear; what are you really trying to accomplish?

If you're just trying to get values out of the database table, a simple select will do. No need for an ad-hoc procedure. That could actually cause more overhead as the procedure needs to be sent to the database to be compiled before you could use it.

I don't know the C++ syntax off hand, but you are missing the FROM table specifier on your second code set.

SQL EXECUTE SELECT dob INTO :birthdays FROM driver_license WHERE dl_number < 10;

Upvotes: 1

Related Questions