Creating an LOV from Database Collection Object in ApEx

0
votes

I am trying to create a select list in ApEx based off of values stored inside of a collection object.

Basically I am running a stored procedure that calls a java class, which sets the values of an array of varchar2.

To get the values and print them to dbms output, I can run the following PLSQL block:

DECLARE
    vArray testschema.stringarray_type := testschema.stringarray_type();
BEGIN
    testschema.getLOV(vArray);

    FOR i IN 1..vArray.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Counter: ' || i || ' Value: "' || vArray(i) || '".');
    END LOOP;

END;

Now I just need to get all of those values into a select list instead of printing them out.

How can I rewrite this PLSQL code to instead work for the LOV source of a select list?

Is there a way that I change the code to do something like this:

FOR i IN 1..vArray.COUNT LOOP
    :P1_SELECT_LIST.append(vArray(i));
END LOOP;

and run that in a dynamic action or something?

Thanks

EDIT:

I made a temporary workaround where now I use a dynamic action on page load to run some PLSQL. I append each value from the object to a varchar2 variable in my plsql block, delimited by commas. I then set a hidden page item equal to the value of that variable, so it holds a value of something like:

item1,item2,item3,item4,item5

and then through a javascript dynamic action, I split the item value on the commas, and then replace the html of the select list with a custom-built html string to have all of my items listed as options.

This isn't my ideal solution, so I would still like to figure out if there is a better way. Or perhaps part of this workaround can be used in a better solution.

3
plsqloracle-apex
How to do display those values? shuttle ? Checkbox ? Text field? - Jestem_z_Kozanowa
I would like them to be in a Select List (html combo box) - Sandoichi

3 Answers

0
votes

If you modify your procedure to be a function that returns a varray then you can create a dynamic APEX List of Values object with the query defined like this:

BEGIN
    return 'select column_value d, column_value r from table(testschema.getLOV())';
END;
0
votes

Wrap your procedure with a function as Tony proposed:

create 
function apx_getLOV
  return testschema.stringarray_type
as
  l_result testschema.stringarray_type;
begin
  testschema.getLOV(l_result);

  return l_result;
end apx_getLOV;
/

And your query in LOV source will be next:

select column_value d, column_value r from table(apx_getLOV())

You can read about table collection expression here.