How to selected list values from the page two multilist

Answered

How can we retrieve selected list values from the pag two multilist.

For example if multilist  has selected list values like cochin,mumbai,simla and these values are stored as seperate entryid’s in listentry table for the selected values .but how can we get as a consolidated one.

Select entryvalue from listentry l,page_two p2 where l.entryid=p2.multilist23

But multilist23 has all the selected values like 465,678,568 but the entryid has a single selected value like 465 in listentry table.but how to get all selected values

 

Add Comment
2 Answer(s)
Best answer

Hi Keerthana,

A very crude and quickly written procedure to help you out, hope this serves your purpose:

DECLARE
l_variable_name PAGE_TWO.MULTILIST02%TYPE;
type string_array is varray(10) of varchar2(10);
l_result_name string_array;
BEGIN
    select multilist02 into l_variable_name from page_two where id in (select id from item where item_number = 'PART-123456');
    select entryvalue bulk collect into l_result_name from listentry where entryid in (select regexp_substr(l_variable_name,'[^,]+', 1, level) from dual connect by            regexp_substr(l_variable_name,'[^,]+', 1, level) is not null);
    for i in l_result_name.first..l_result_name.last loop
       dbms_output.put_line(l_result_name(i));
    end loop;
END;

Regards,

Swagoto

Agile Expert Answered on June 3, 2021.

Thank you Swagoto..How can I get multilist32 for all the items

on June 4, 2021.

I mean how can I get multilist selected values for all the items

on June 4, 2021.

if you want to get multilist32’s value for all items, then do a minor modification on the select query, I hope this should work, if it doesn’t let me know

type string_array is varray(100000) of varchar2(10);
select multilist32 into l_variable_name from page_two where id in (select id from item);

I have set the array size as 100,000, you can set it as per the volume of data in your system i.e. number of items in your db

on June 4, 2021.
Add Comment

Swagoto,

I tried as suggested by you but I am getting below error

ORA-01422: exact fetch returns more than requested number of rows

even I increased the type string_array is varray(2000000)

also how can I move the data  into the file instead of dbmsoutput put line  in the format as below for all the items in the db

item_number,selected MultilistValue

 

Agile User Answered on June 6, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.