How to create an Oracle APEX Dynamic List of Values (LOV)

In Oracle Apex go to the Shared Component page and select List of Values.

Home>Application Builder>Application ###>Shared Components>Lists of Values

Enter your SQL as shown in Screen 1. Another example with conditions is shown in Screen 2.

In the form where the list needs to be displayed add an item with display as: Select List (Screen 3).

Select the relevant list as show in Screen 4.