How to selected list values from the page two multilist
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
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
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
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