Collect is not working

delimiter //
create or replace procedure str2arrfnq(indata text,delimiter char(1))
returns array(record(val varchar(255)))
as
declare
delim_cnt int =length(indata)-length(replace(indata,delimiter,’’));
array_size int = delim_cnt + 1;
strs ARRAY(RECORD(val varchar(255)));
offset_start int=1;
q query(val varchar(255));
echo_str text;
begin
for i in 0 … delim_cnt
loop
if (i < delim_cnt) then
q= select substr(indata,offset_start,regexp_instr(indata,’,’,1,i+1) - offset_start);
strs=collect(q);
else
q= select substr(indata,offset_start);
strs=collect(q);
end if;
offset_start= regexp_instr(indata,’,’,1,i+1)+1;
end loop;

 for j in strs
  loop
    echo select j.val strval;
     echo_str=concat(echo_str,j.val);
  end loop;
   
   return strs;

end //

when I query I am able to see the value for the string I am passing, but when iam collecting into an array it’s not working. throwing an error strs is not available. Appreciate for providing a solution
call str2arrfnq(’“TEST1”,“TEST2”,“TEST3”,’,’);
the requirement is converting a given comma separated line into an array.

What’s the full error message?

Also try using a column alias in your query to match the record definition like this:

q= select substr(indata,offset_start,regexp_instr(indata,’,’,1,i+1) - offset_start) as vars;

Also, since your query returns 1 row and 1 column, you can use SCALAR() instead. Moreover, your SELECT statements are not operating on table data at all, just SP variables and parameters, so you could just reference the expressions like

substr(indata,offset_start,regexp_instr(indata,’,’,1,i+1) - offset_start)

anywhere you need them, like on the right-hand side of an assignment statement, like:

myLocaVar = substr(indata,offset_start,regexp_instr(indata,’,’,1,i+1) - offset_start);

Thanks Hanson for the reply. Iam able to fix it using scalar and collect statements.