CODEWITHSUNDEEP
sqlplsql
user14769698
user14769698

Reputation: 27

how to select into an array in pl/sql?

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?

Upvotes: 0

Views: 907

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

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.

Upvotes: 2

Related Questions