Generate sample data in oracle 11g

Question

I have a problem with following procedure, it simply does not choose random street and town. Problem is with

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

and

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

looks it random.value is evaluated only once, how can I get it to choose random elements of varray??

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;
/

`looks it random.value is evaluated only once` , can you explain a bit more on this. This procedure looks good to me. Are you saying, same street/town is inserted 500 times ? — Maheswaran Ravisankar, Oct 08 '15 at 10:43

Florin Ghita · Accepted Answer · 2015-10-08T11:59:50.867

The towns() and streets() are constructors but they are just functions. I don't know exactly why here is the case(maybe it has to do with the connecty by over dual inner works), but even the docs warn that you cannot be sure how many times a function is called in a SQL statement:

Invoking Stored PL/SQL Functions from SQL Statements

Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language. If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead.

For example, if your application requires that a function be called for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This technique guarantees that the number of calls to the function is the number of rows fetched from the cursor.

So, the workaround is to call dbms_random package more than once, for every row:

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;

Later edit:

This has nothing to do with dbms_random package. For example:

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;
/

outputs:

Linland,Linland,Linland,Linland

score 1 · Answer 2 · answered Oct 09 '15 at 05:29

My suggestion is if you are using Toad for oracle tool then you have a option of generating millions of sample data with random values, you can specify a seq, varchar length , number length etc... also a main advantage is you can map a fk and pk relation to child and parent tables respectively.

GOTO schema_browser>>Tables>>Right click on the selected table and click generate data and choose a limit and proceed with your requirment

Generate sample data in oracle 11g

2 Answers2