Using Host Array in Open Cursor in Embedded SQL

Question

I have this embedded SQL code:

EXEC SQL
  OPEN MY_CURSOR USING :a1
END-EXEC

Where a1 is Array with 20 items, but I don't know in forward how many Host Variables I will need. It can be from 1 to 20. If I use it in this way, and actually I am using only 5 items from array, I got this error:

SQLCODE: -10000, SQLSTATE: 24000, SQLERRMC: Invalid Cursor State

Is there any possibility how to in OPEN CURSOR section send number of used items in array? Or I must use array with exact number of items?

Answer 1

The host variables in the "OPEN CURSOR" statement refer to parameters in the where clause.

Not data is returned on OPEN.

Instead you get each individual row by issuing a "FETCH INTO :var1, :var2 ..." much the same a single row select.

See docs here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/fetch-transact-sql?view=sql-server-ver15