generate random words from a table to insert into another table( PLSQL)

Question

Table with word I want to generate

create table words
( word varchar(100));

insert into words values ('Main street');
insert into words values ('Patrick Street');
insert into words values ('Red Street');
insert into words values ('Green street');
insert into words values ('Blue street');
insert into words values ('Yellow street');
insert into words values ('Silver street');
insert into words values ('Gold street');
insert into words values ('Brown street');

PLSQL code for insert

    declare  
randTemp number(10); 
tempCounty VARCHAR(20); 
streetaddress VARCHAR(100);
Begin 
For i in 1..9 
Loop 
randTemp := dbms_random.value(1,5);
SELECT c.countyname INTO tempCounty FROM county C WHERE c.countyid = randTemp;
SELECT w.word INTO streetaddress FROM words w ORDER BY dbms_random.random limit 1; 
Insert into BRANCH values(i,streetaddress,tempCounty,randTemp); 
End loop; 
Commit; 
End; 
/

Everything works besides this select statement

SELECT w.word INTO streetaddress FROM words w ORDER BY dbms_random.random limit 1;

I get this error

ORA-06550: line 10, column 75: PL/SQL: ORA-00933: SQL command not properly ended

I think the limit might be the problem...???

Alex Poole Alex Poole · Accepted Answer · 2013-04-03T11:23:59

You're right, limit is the problem because that is not something Oracle supports.

You can do something like this instead:

SELECT word INTO streetaddress
FROM (
    SELECT word
    FROM words
    ORDER BY dbms_random.random
)
WHERE rownum = 1;

The inner query is ordering the potential words randomly, and the out query uses the rownum pseudocolumn to pick the first one returned, similar to what limit would do.

Repeating that query in a loop doesn't seem very efficient though; you might be better off doing something like:

DECLARE
    tempCounty VARCHAR(20);
    streetaddress VARCHAR2(100);
    cur sys_refcursor;
BEGIN
    OPEN cur FOR
        SELECT word, countyname
        FROM (
            SELECT w.word, c.countyname
            FROM words w
            CROSS JOIN county c
            ORDER BY dbms_random.random
        )
        WHERE rownum <= 9;
    LOOP
        FETCH cur INTO streetaddress, tempCounty;
        EXIT WHEN cur%NOTFOUND;
        dbms_output.put_line(streetaddress ||', '|| tempCounty);
    END LOOP;
END;
/

That is, generating all possible combinations of the fields from the two tables, ordering that randomly, and restricting it to the first 9 results.

But it depends what you're dong with it - if you're simply populating another table as the question suggests, then you can use that kind of query as part of a insert into ... select ... construct, avoiding PL/SQL and loops entirely:

INSERT INTO branch
SELECT rownum, word, countyname, countyid
FROM (
    SELECT w.word, c.countyname, c.countyid
    FROM words w
    CROSS JOIN county c
    ORDER BY dbms_random.random
)
WHERE rownum <= 9;

If you're doing more processing in PL/SQL and have a lot of rows then using bulk collect might speed things up further.

generate random words from a table to insert into another table( PLSQL)

1 Answers