how to select into an array in pl/sql?

Question

I'm trying to add some ids into an array:

CREATE OR REPLACE TYPE array_of_numbers AS VARRAY(10)OF NUMBER(10);

declare
    student_ids array_of_numbers;
begin
    select nrleg BULK COLLECT into student_ids from student where nrleg = 123458;
    FOR i IN 1..student_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(student_ids(i));
    END LOOP;
end;

but I get the following errors:

--------- -------------------------------------------------------------
3/1       PLS-00103: Encountered the symbol "DECLARE" 
10/4      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge json_exists json_value json_query    json_object json_array 
Errors: check compiler log

Could someone explain what I did wrong?

Remove DECLARE; it is not to be used in named PL/SQL procedures. — Littlefoot
– Littlefoot, Commented Jun 3, 2021 at 14:45
@Littlefoot but if I remove the declare part I won't have an array to save the data into (?) — user14769698
– user14769698, Commented Jun 3, 2021 at 14:49
I didn't say to remove the whole section, but the DECLARE keyword. — Littlefoot
– Littlefoot, Commented Jun 3, 2021 at 14:50
Ah, I'm sorry, blinded by how-knows-what. This is an anonymous PL/SQL block. The only thing that's missing is a slash that terminated the CREATE TYPE statement. Sorry. — Littlefoot
– Littlefoot, Commented Jun 3, 2021 at 14:55

Alex Poole · Accepted Answer · 2021-06-03 15:15:38Z

2

Some DDL needs to be terminated with a /; a type can have a PL/SQL body so this is one of them. To run that all at once as a script do:

CREATE OR REPLACE TYPE array_of_numbers AS VARRAY(10)OF NUMBER(10);
/

declare
 student_ids array_of_numbers;
begin
select nrleg BULK COLLECT into student_ids from student where nrleg = 123458;
    FOR i IN 1..student_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(student_ids(i));
    END LOOP;
end;
/

You don't need the semicolon on the end of the CREATE here, but it doesn't hurt; but for some other commands (including other DDL like create table) having both would cause it to try to execute the statement twice, which could cause an error.

SQL Developer won't complain about the lack of a / after the last PL/SQL block in a script, but other tools will, so it's better to always include that one too.

db<>fiddle

Incidentally, another way to see the contents of the array in SQL Developer is with a ref cursor:

var rc refcursor

declare
  student_ids array_of_numbers;
begin
  select nrleg BULK COLLECT into student_ids from student where nrleg = 123458;
  open :rc for select * from table(student_ids);
end;
/

print rc

... but then you might as well just select directly from the table, without any PL/SQL.

edited Jun 3, 2021 at 15:15

answered Jun 3, 2021 at 14:51

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user14769698 Over a year ago

Now I'm trying to print the numbers from the array like this: for i in student_ids.first..student_ids.last loop DBMS_OUTPUT.PUT_LINE(student_ids(i)); end loop; but I get this :end loop Error report - Unknown Command

Alex Poole Over a year ago

@user14769698 - that error is from the client; so are you trying to do that outside a PL/SQL block? it works inside one.

user14769698 Over a year ago

I added BEGIN and END; but it I get the same thing.

Alex Poole Over a year ago

It's unclear what you're doing (or doing wrong); the fiddle shows that it works if you run it properly.

user14769698 Over a year ago

Aaah thank you, it worked with the cursor!

Collectives™ on Stack Overflow

how to select into an array in pl/sql?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related