Generate sample data in oracle 11g

Question

I have a problem with the following procedure, it just does not select a random street and city. The problem is

streets(SYS.DBMS_RANDOM.VALUE(1,50)) 

and

towns(SYS.DBMS_RANDOM.VALUE(1,50)) 

It looks like random.value is evaluated only once, how can I get it to select random varray elements ??

CREATE OR REPLACE PROCEDURE GENERATE_ADDRESSES AS
    type streetType is varray(50) of addresses.street%TYPE;
    streets streetType:= streetType('Rebel Loop East', 'Everleigh Rise', 'Double Oak,Pig Mount West', 'Byam Byway', 'South Damour Trail', 'Chart Hill Northwest', 'West Down Turnpike', 'Southeast Lawyers Hill Mount',
    'East Jibbon Road', 'Old Browns Valley Viaduct', 'Queensbury Viaduct', 'Northeast Hards Plaza', 'Northwest Cushing Promenade', 'North Queens Wood', 'South Oakton Plantation', 'East Redeker Terrace',
    'Stanbaugh Mount', 'Huse Nook', 'East Savine Grade', 'Bardo Manor', 'West Mina Rosa Place', 'West Oldarker Mall', 'West Oakgrove Lane', 'Woodleigh Row', 'Southwest Stoney Ridge Passage',
    'Cucumber Mews', 'Stoffa Trace North', 'West Echo Bay Alley', 'North Monkhams Terrace', 'Weller Grove West', 'Estate Walk', 'Doneraile Rise', 'North Yunga Burra Manor', 'Boundaries Square', 'Windsor Hill Row West',
    'South Silver Maple Close', 'West Back Westminster', 'East Bibsworth Causeway', 'Widdop Dell', 'Sawyer Hill', 'East Minehurst Street', 'East Ecclesbridge Close', 'North Clouston Court', 'Southwest Towradgi Alley',
    'Northeast Barkdoll Promenade', 'Southwest Icklingham Quay', 'North Fanum Quadrant', 'Nerbonne Croft', 'West Montee Alley', 'East Burra Street');

    type townType is varray(50) of addresses.town%TYPE;
    towns townType:= townType('Linland','Havenmoor','Fallbank','Marshbush','Whitenesse','Crystalfort','Dorhaven','Spellhall','Northbell','Westermerrow','Butterbeach','Fairbarrow','Violetbush','Westbeach','Landness','Rosemaple','Lochbush',
'Coastfield','Westmarsh','Golddale','Violetford','Elfacre','Brightmill','Bypine','Starryfox','Barrowmeadow','Ashbridge','Swynpond','Eribourne','Wintermill','Eribourne','Bridgebeach',
'Roselyn','Summerwinter','Fairviolet','Ashvale','Dordale','Osthaven','Deephaven','Whiteflower','Welledge','Snowbeach','Marblenesse','Witchnesse','Bluewell','Shorelake','Coldfalcon','Strongbush','','Freyholt');

    TYPE Addresses_type IS TABLE OF addresses%ROWTYPE INDEX BY BINARY_INTEGER;
    adb Addresses_type;
  BEGIN
    --fill streets and towns varrays
    insert into addresses(id, street, streetNo, town, countries_id)
    select rownum, streets(SYS.DBMS_RANDOM.VALUE(1,50)), floor(SYS.DBMS_RANDOM.VALUE(1,10000)) || '/' || floor(SYS.DBMS_RANDOM.VALUE(1,1000)), towns(SYS.DBMS_RANDOM.VALUE(1,50)), SYS.DBMS_RANDOM.VALUE(1,500)
    from dual
    connect by level <= 500;
  END GENERATE_ADDRESSES;
/

Answer 1

Cities () and streets () are constructors, but they are just functions. I don’t know why this is so (perhaps this is due to connecting via double internal work), but even docs warn that you cannot be sure how many times the function is called in the SQL statement:

Calling Saved PL / SQL Functions from SQL Statements

SQL - , ( ), , , SQL , PL/SQL, . , , SQL. .

, , , . - .

, dbms_random :

for k in (select level as lev from dual connect by level <= 500)
loop
 insert into addresses(
   id, 
   street, 
   streetNo, 
   town, 
   countries_id
   )
values 
(
  k.lev, 
  streets(SYS.DBMS_RANDOM.VALUE(1,50)), 
  floor(SYS.DBMS_RANDOM.VALUE(1,10000)) || '/' || floor(SYS.DBMS_RANDOM.VALUE(1,1000)), 
  towns(SYS.DBMS_RANDOM.VALUE(1,50)), 
  SYS.DBMS_RANDOM.VALUE(1,500)
);
end loop;

:

dbms_random. :

declare 
type townType is varray(50) of varchar2(1000);
    towns townType:= townType('Linland','Havenmoor','Fallbank','Marshbush','Whitenesse','Crystalfort','Dorhaven','Spellhall','Northbell','Westermerrow','Butterbeach','Fairbarrow','Violetbush','Westbeach','Landness','Rosemaple','Lochbush',
'Coastfield','Westmarsh','Golddale','Violetford','Elfacre','Brightmill','Bypine','Starryfox','Barrowmeadow','Ashbridge','Swynpond','Eribourne','Wintermill','Eribourne','Bridgebeach',
'Roselyn','Summerwinter','Fairviolet','Ashvale','Dordale','Osthaven','Deephaven','Whiteflower','Welledge','Snowbeach','Marblenesse','Witchnesse','Bluewell','Shorelake','Coldfalcon','Strongbush','','Freyholt');
v  varchar2(2000);

begin

   dbms_output.put_line('start!');

   select listagg(towns(level+1), ',') within group (order by 1) 
   into v 
   from dual
   connect by level < 5;

   dbms_output.put_line(v);

end loop;
end;
/

:

Linland,Linland,Linland,Linland

Answer 2

, Toad oracle, , seq, varchar, ..... - fk pk .

GOTO schema_browser → → " "