Search code examples
sqloracle-databaseplsqlvarray

I want to create a VARRAY that is on the schema level, so that I can pass it as an input parameter into a procedure


This is what I have been trying-

create or replace type persons_list is varray(10) of varchar(1) not null;

Declare
peoplesList persons_list := persons_list(1, 2, 3, 4, 5, 6, 7, 8, 9, 'a');

Begin
dbms_output.put_line('hello');

End;
/

but I keep getting this error-

Type PERSONS_LIST compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
3/1       PLS-00103: Encountered the symbol "DECLARE" 
10/0      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 
Errors: check compiler log

Solution

  • It is enought to replase first semicolon with a slash at a new line:

    create or replace type persons_list is varray(10) of varchar(1) not null
    /
    Declare
        peoplesList persons_list := persons_list(1, 2, 3, 4, 5, 6, 7, 8, 9, 'a');
    Begin
        dbms_output.put_line('hello');
    End;
    /