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;
/
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments