Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

oracle search

Wednesday, August 6, 2008

SP2-0552: Bind variable not declared ....oracle

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.)

No comments: