I'm trying to CREATE a function that will generate different combinations of first and last names.

As you can see from my example below I have 5 distinct first and last names but I want to make this a generic function or a package where I can wrap around something like CONNECT by level <=N.

How can I fix this code to accomplish that task or do I need to have the same amount of WHEN clauses that will = N?

 with fn as (select rownum r, first_name
               from (
                     select case rownum
                             WHEN 1 THEN 'Faith'
                             WHEN 2 THEN 'Cheryl'
                             WHEN 3 THEN 'Madison'
                             WHEN 4 THEN 'Lisa'
                             WHEN 5 THEN 'Beth'
                            end as first_name
                     from  (select level l from dual connect by level <= 5)
                     order by dbms_random.value()
                    )
              )
       ,ln as (select rownum r, last_name
               from (
                     select case rownum
                             WHEN 1 THEN 'Ashcroft'
                             WHEN 2 THEN 'Saladino'
                             WHEN 3 THEN 'Stein'
                             WHEN 4 THEN 'Ford'
                             WHEN 5 THEN 'Cooper'
                            end as last_name
                     from  (select level l from dual connect by level <= 5)
                     order by dbms_random.value()
                    )
              )   select fn.r, fn.first_name, ln.last_name
   from   fn
          join ln on (fn.r = ln.r);
 

CodePudding user response：

As long as you don't care about the R column which was holding the ROWNUM, I would recreate your "random name generator" using two SYS.ODCIVARCHAR2LIST objects, joining them together, then doing a random sort and returning the first 5 or however many rows you want.

  SELECT fn.COLUMN_VALUE AS first_name, LN.COLUMN_VALUE AS last_name
    FROM TABLE (sys.odcivarchar2list ('Faith',
                                      'Cheryl',
                                      'Madison',
                                      'Lisa',
                                      'Beth')) fn
         CROSS JOIN TABLE (sys.odcivarchar2list ('Ashcroft',
                                                 'Saladino',
                                                 'Stein',
                                                 'Ford',
                                                 'Cooper')) LN
ORDER BY DBMS_RANDOM.VALUE
   FETCH FIRST 5 ROWS ONLY;

If you do not want every possible combination and do not want to have any possibly repeating first names or last names, you can use the query below.

SELECT fn.COLUMN_VALUE AS first_name, LN.COLUMN_VALUE AS last_name
  FROM (SELECT ROWNUM r, COLUMN_VALUE
          FROM (  SELECT COLUMN_VALUE
                    FROM TABLE (sys.odcivarchar2list ('Faith',
                                                      'Cheryl',
                                                      'Madison',
                                                      'Lisa',
                                                      'Beth'))
                ORDER BY DBMS_RANDOM.VALUE)) fn
       JOIN (SELECT ROWNUM r, COLUMN_VALUE
               FROM (  SELECT COLUMN_VALUE
                         FROM TABLE (sys.odcivarchar2list ('Ashcroft',
                                                           'Saladino',
                                                           'Stein',
                                                           'Ford',
                                                           'Cooper'))
                     ORDER BY DBMS_RANDOM.VALUE)) LN
           ON (fn.r = LN.r)
 FETCH FIRST 5 ROWS ONLY;