Alok Shukla Alok Shukla - 1 month ago 7
SQL Question

How to define array/collection in stored procedure?

I have a fixed array of strings on which I want to operate. How should I declare them in stored procedure? Is it even possible?

CREATE OR REPLACE PROCEDURE testing AS

BEGIN

operations...
END;


Can we declare like DECLARE @v1 varchar(15); or something similar for collection of string?(PS "@" throwing error on Oracle SQL Developer )

A newbie in SQL, so expecting a sample code if possible thanks.

Answer

You can use this:

declare
   type array_t is varray(3) of varchar2(2);
   array array_t := array_t('aa', 'bb', 'cc');
begin
   for i in 1..array.count loop
       dbms_output.put_line(array(i));
   end loop;
end;