After reading your latest comments I understand that what you want is not an LOV but rather list item. Although it can be an LOV too. The first list item/lov will have all products only that user selects from it, e.g. Prod1, Prod2, Prod3... The second list item will have all versions converted from comma separated values as in your example to table as in my examples below. Because in my understanding user may pick only a single value per product from this list. Single product may have many values, e.g. Prod1 has values 1,2,3, 4. But user needs to select only one. Correct? This is why you need to convert comma values to table. The first query select is smth lk this:
SELECT prod_id
FROM your_prod_table
/
id
--------
myProd1
myProd2
.....
The second query should select all versions where product_id is in your_prod_table:
SELECT version FROM your_versions_table
WHERE prod_id IN (SELECT prod_id FROM your_prod_table)
/
Versions
--------
1,2,3,4 -- myProd1 values
a,b,c,d -- myProd2 values
.....
The above will return all versions for the product, e.g. all values for myProd1 etc...
Use my examples converting comma sep. values to table. Replace harcoded '1,2,3,4' with your value column from your table. Replace dual with your table name
If you need products and versions in a single query and single result then simply join/outer join (left, right join) both tables.
SELECT p.prod_id, v.version
FROM your_prod_table p
, your_versions_table v
WHERE p.prod_id = v.prod_id
/
In this case you will get smth lk this in output:
id | Values
------------------
myProd1 | 1,2,3,4
myProd2 | a,b,c,d
If you convert comma to table in above query then you will get this - all in one list or LOV:
id | Values
------------------
myProd1 | 1
myProd1 | 2
myProd1 | 3
myProd1 | 4
myProd2 | a
myProd2 | b
myProd2 | c
myProd2 | d
I hope this helps. Again, you may use LOV or list values if available in APEX. Two separate list of values - one for products other for versions - make more sense to me. In case of list items you will need two separate queries as above and it will be easier to do comma to table conversion for values/versions only. But is is up to you.
Comma to table examples:
-- Comma to table - regexp_count --
SELECT trim(regexp_substr('1,2,3,4', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= regexp_count('1,2,3,4', ',')+1
/
-- Comma to table - Length -
SELECT trim(regexp_substr('1,2,3,4', '[^,]+', 1, LEVEL)) token
FROM dual
CONNECT BY LEVEL <= length('1,2,3,4') - length(REPLACE('1,2,3,4', ',', ''))+1
/
-- Comma to table - instr --
SELECT trim(regexp_substr('1,2,3,4', '[^,]+', 1, LEVEL)) str_2_tab
FROM dual
CONNECT BY LEVEL <= instr('1,2,3,4', ',', 1, LEVEL - 1)
/
The output of all that above is the same:
STR_2_TAB
----------
1
2
3
4
Comma to table - PL/SQL-APEX example. For LOV you need SQL not PL/SQL.
DECLARE
v_array apex_application_global.vc_arr2;
v_string varchar2(2000);
BEGIN
-- Convert delimited string to array
v_array:= apex_util.string_to_table('alpha,beta,gamma,delta', ',');
FOR i in 1..v_array.count LOOP
dbms_output.put_line('Array: '||v_array(i));
END LOOP;
-- Convert array to delimited string
v_string:= apex_util.table_to_string(v_array,'|');
dbms_output.put_line('String: '||v_string);
END;
/
