Thursday, April 06, 2006

SP2-0552: Bind variable not declared

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

2 comments:

ksogor said...

Oracle understand : as bind variable symbol.

Try
IntArray IntArrays;

P.S.: All variables after "DECLARED" are bind variables, there is no need to use :.

William Robertson said...

I thought I mentioned that in 2006, in the bit where it says "hint", but thanks for joining us.