Please help. Why does SQL*Plus keep giving me the error
SP2-0552: Bind variable "INTARRAY" not declared.
Thanks in advance.
DECLARE BatchSize : constant := 50; subtype IndexRanges is INTEGER range 1 .. BatchSize; type IntArrays is array( IndexRanges) of INTEGER; IntArray : IntArrays; EXEC SQL DECLARE network_cursor CURSOR FOR select pal.provider_id from hold_provider_address_link pal; EXEC SQL OPEN network_cursor; -- establish a local block, with an exception to -- handle the "no data found" condition begin EXEC SQL WHENEVER NOT FOUND raise NO_MORE_DATA; FETCH_LOOP: loop -- fetch the data, 20 rows at a time EXEC SQL FETCH network_cursor INTO :IntArray; for I in 1..20 loop -- process batches of 20 rows ... end loop; commit; end loop FETCH_LOOP; exception -- the exception NO_MORE_DATA is raised when there is -- no more data to FETCH when NO_MORE_DATA => PUT("No more data to fetch. N of rows fetched was "); PUT(ORACLE.SQLROWS); NEW_LINE; -- turn off the error handling EXEC SQL WHENEVER NOT FOUND CONTINUE; end; /
I mentioned this in a comment the other day so apologies if you've seen it before, but I felt it really deserved its own post.
(Hint: SP errors are from SQL*Plus, and INTARRAY is a bind variable from SQL*Plus's point of view because it begins with ":")
By the way I don't mean to laugh too much at the poor guy who posted this problem on a forum, as from his other posts he seems to have had a crappy application dumped on him without much support from anyone at his company. I did ask him what language it was written in but he hasn't replied. Suggestions, anyone? (My guess is Pro*Ada, which in my opinion we don't see enough of these days.)