Sorry to bother again but I need an answer to this question since I can't seem to come up with one myself. Here's the previous post: Is there any way I can speed up the following insert(s)?.
Consider the following:
CREATE TABLE myTable ( random_value1 NUMBER, random_value2 NUMBER, random_string VARCHAR2(5) ); DECLARE TYPE arrayType IS VARRAY(5) OF VARCHAR2(5); v_my_array arrayType := arrayType('foo', 'bar', 'baz', 'qux', 'quux'); max NUMBER := 1000000; BEGIN FOR j IN 1..max LOOP INSERT INTO myTable VALUES(DBMS_RANDOM.VALUE(1, 500), DBMS_RANDOM.VALUE(1, 500), v_my_array(DBMS_RANDOM.VALUE(1, 5))); END LOOP; END; /
Based on the answers I got on the previous post I can easily insert random values on the columns #1 and #2 using a single insert statement instead of a million(see the example). Now my question is how can I also insert a random string from a list of given strings and avoid using a loop, if that's possible of course. If I try something like:
INSERT INTO myTable SELECT DBMS_RANDOM.VALUE(1, 500), DBMS_RANDOM.VALUE(1, 500), v_my_array(DBMS_RANDOM.VALUE(1, 5)) FROM DUAL CONNECT BY LEVEL <= 1000000;
I get the same value for column #3 on all the rows, when I want different results everytime("random" results).
Once again, thanks for taking the time to look over this!