0

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.

Old Junior
  • 1
  • 2
  • Does it have to be a PL/SQL function rather than a SQL query? You could certainly write a pipelined table function that returned 20 rows. But a SQL query is going to involve a lot less code. – Justin Cave Feb 04 '21 at 19:02
  • Yes, you are right. If I create a table which is include two column, first for id and second for numbers then I can write an SQL query like this: select number d, id r from numbers; but I dont want to create a table for just numbers. I want to use loop or something like that. – Old Junior Feb 05 '21 at 06:23
  • You can write a SQL query that generates the numbers 1-20 without creating a `numbers` table. Is that acceptable? Or does it need to use PL/SQL? – Justin Cave Feb 05 '21 at 07:25
  • Yes it is acceptable. Could you write it please? – Old Junior Feb 05 '21 at 07:43

1 Answers1

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
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
  • And some more explanation is here: https://www.geeksforgeeks.org/display-sequence-of-numbers-in-sql-using-level/#:~:text=LEVEL%20must%20be%20used%20with,database%20along%20with%20data%20dictionary. – Old Junior Feb 05 '21 at 10:00