PLSQL Selecting from table using an array into a variable

Question

I am trying to select some records from a table using an array into the variable init_av_days but its not working out. init_av_days is to be committed to another table after this select query. How best do you suggest i do this?

declare

    myarray  APEX_APPLICATION_GLOBAL.VC_ARR2;
    init_av_days varchar2(10);

begin

    myarray := APEX_UTIL.STRING_TO_TABLE(:P592_AVAILABILITY_DAYS);

    For i in 1.. myarray.count loop

        select  availability_days 
        into init_av_days 
        from sl_available_days 
        where location_code = :P592_LOCATION_CODE 
        and availability_days = myarray(i);

    end loop;

end;

Not working means? are you getting any error or it is not working as expected? — Popeye
– Popeye, Commented Mar 26, 2020 at 7:42

Littlefoot · Accepted Answer · 2020-03-26 07:29:30Z

1

init_av_days is to be committed to another table after this select query

Best? Skip PL/SQL entirely, I'd say. Something like this:

insert into another_table (some_column)
select availability_days 
from sl_available_days 
where location_code in = (select regexp_substr(:P592_LOCATION_CODE, '[^:]+', 1, level)
                          from dual
                          connect by level <= regexp_count(:P592_LOCATION_CODE, ':') + 1
                         )
  and availability_days = <I don't know what APEX_APPLICATION_GLOBAL.VC_ARR2 contains>

This code should be fixed because I don't know the last condition; if you know how, do it. If not, explain what's in vcc_arr2, maybe we can assist.

answered Mar 26, 2020 at 7:29

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLSQL Selecting from table using an array into a variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related