Random string selection in Oracle

Question

I need to randomly select values ​​from a single table, for example. tableA.a_idwhich is VARCHAR2, and use the value to insert into another table. For example, it is assumed that three columns should be inserted in 100 rows tableX(serial number, random number between 100 and 999 and values tableA.a_id):

insert into tableX
select
    rownum,
    dbms_random.value(100,999), 0),
    (select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
    where rownum = 1)
from
   (select level from dual connect by level <= 100);

However, instead of selecting a random row from tableA.a_idfor each row, it selects the same value for all rows, for example:

1 129 A-ID-48
2 849 A-ID-48
3 367 A-ID-48

However, if I execute a subquery several times, I get a new value every time (for obvious reason), for example:

select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
where rownum = 1;

The result will be after each execution:

A-ID-7
A-ID-48
A-ID-74

, tableA a_id ? :

1 129 A-ID-7
2 849 A-ID-48
3 367 A-ID-74

1

mathguy answer :

insert into tableX
select
    rownum,
    round(dbms_random.value(100,999), 0),
    a_id
from
    (
      select 
        round(dbms_random.value(1, (select count(*) from tableA)), 0) tableX_rand_num
      from tableX
    ) x
join 
    (
      select
        a_id, 
        dbms_random.value() rnd,
        rownum tableA_rownum
      from tableA
      order by rnd
    ) a
on x.tableX_rand_num = a.tableA_rownum
where rownum <= 100;

. , (tableX). , , , tableX. tableX 200 , 1000, 200 .

Answer 1

:

select a_id, dbms_random.value() rnd from tableA order by rnd

100 , rownum <= 100.

:

insert into tableX
select
    rownum,
    round(dbms_random.value(100,999), 0),
    a_id
from
    (
      SELECT a_id, dbms_random.value() rnd
      FROM tableA
      ORDER BY rnd
    )
where rownum <= 100;