goodspeed goodspeed - 6 months ago 116
SQL Question

How to SELECT INTO array of Numbers in Oracle PL/SQL?

I'm trying to save set of ids in array:

declare
cities_ids array_of_numbers;
begin
select id into cities_ids from objects where id = 1115464;
FOR i IN 1..cities_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(cities_ids(i));
END LOOP;
end;


After execution, I got next error:

ORA-00932: inconsistent datatypes. Expected UDT, got NUMBER.


Please explain what I did wrong...

Answer

Very simple: BULK COLLECT is missing.

declare
 cities_ids arrayofnumbers;
begin
select object_id BULK COLLECT into cities_ids from nc_objects where object_id = 1000;
    FOR i IN 1..cities_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(cities_ids(i));
    END LOOP;
end;