How do I list numbers -for example from 1 to 20- in a LOV item by using pl/SQL function? Return values are same as display values

Question

I'm trying to list numbers -for example from 1 to 20- in a LOV item by using a loop in a pl/SQL function. It must be possible but I haven't succeeded yet. Thanks for your precious helps.

Answer 1

No need to do pl/sql, this can be achieved using the pseudo column LEVEL and the CONNECT BY clause in pure SQL. Very useful for selects like date lists, number lists, etc. For a list of numbers from 1 to 20 you could do this:

SELECT
  level  AS display_value,
  level  AS return_value
  FROM
  dual
CONNECT BY
  level <= 20