CREATE OR REPLACE PROCEDURE PR_RS_GET_ACC_DETAILS (
    PA_IN_ACCOUNTID           IN       NUMBER,
    PA_IN_SITEID              IN       NUMBER,
    PA_OUT_RETCURSOR          OUT      RS_GENERAL.REFCURSOR
)
IS

/BEGIN
    OPEN PA_OUT_RETCURSOR FOR
        SELECT ACC.ACCOUNT_ID AS ACCOUNT_ID,
               ACC.STATUS AS ACC_STA,
               DEV.D_ID AS D_ID,
               DEV.STA AS D_STA,
               DEV.CRT_DATE AS CRT_DATE,
               DEV.NK_NA AS NK_NA,
               AUT.AUT_ID AS AUT_ID,
               AUT.STA AS AUT_STA,
               AUT.EXP_DATE AS EXP_DATE,
               AUT.LT_FRDIGIT_SN AS LTFR_DIGIT_SN,
               AUT.PT_DATA AS PT_DATA,
               AUT.SL_NUM
        FROM   RS_ANT_MST AC,
               RS_DE_MST DEV,
               RS_AUT_MST AUT
        WHERE  ACC.ACC_ID = DEV.ACC_ID
        AND    AUT.AUT_ID = DEV.AUT_ID
        AND    DEV.ACC_ID = PA_IN_ACCOUNTID
        AND    ACC.SITE_ID = PA_IN_SITEID;
END PR_RS_GET

create type intArray as integer array[100] % 
create type stringArray as varchar(10) array[100] %  

create table persons (id integer, name varchar(10)) % 
insert into persons values(2, 'Tom') % 
insert into persons values(4, 'Jill') % 
insert into persons values(1, 'Joe') % 
insert into persons values(3, 'Mary') %  

create procedure processPersons(out witho stringArray) 
begin 
declare ids intArray; 
declare names stringArray;  

set ids = ARRAY[5,6,7]; 
set names = ARRAY['Bob', 'Ann', 'Sue'];  

insert into persons(id, name) 
(select T.i, T.n from UNNEST(ids, names) as T(i, n));  

set witho = (select array_agg(name order by id) 
from persons 
where name like '%o%'); 
end %