0

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. The second SQL call was indeed incomplete. The bulk collect was what I needed.
1

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;
Improve this answer

2 Comments

Thanks. I know for this example a simple select would work, not requiring PL/SQL. However, we've got a lot of places where we pull stuff into arrays, and make decisions about what else to pull based on that. Eventually we'd like to move the logic to the Oracle server and reduce the number of calls to it.
@HeywoodFloyd I recommend creating database packages and moving your business logic into the database tier. What you're doing now isn't reducing anything, it's actually adding unnecessary complexity and compile time. It looks like you're trying to bind a C++ data type to a PLSQL method. Oracle only returns tables. If you want to convert the table results into an array, you're going to have to use the ODP ToArray function. Not sure what this is in C++ but a Google search should provide that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.