CODEWITHSUNDEEP
sqloracle-databasefor-looprandomsql-insert
Phaki
Phaki

Reputation: 315

Insert statement inserts wrong random numbers into table Oracle

I try to generate sample data, therefore I would like to insert multiple rows into my Oracle table. Column values should be in between 1-5 and should be totally random. I need 20 random whole number.

My insert statement:

BEGIN
FOR i IN 163 .. 400 LOOP
INSERT ALL INTO results
  (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
  VALUES
  (i,
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)))
  SELECT * FROM DUAL;
END LOOP;
COMMIT;
END;

It works but in one row the numbers are all the same. It is not generating a new random number to every column, just one and insert it to all fields of my table. Why is it happening? You can see it after 102 id row. First inserted row (163 id) is already wrong. enter image description here

Upvotes: 0

Views: 173

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

INSERT ALL has some quirks, but you don't need it here, you can just remove ALL and the dummy select to make it a simple insert-values statement:

BEGIN
FOR i IN 163 .. 400 LOOP
INSERT INTO results
  (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
  VALUES
  (i,
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)));
END LOOP;
COMMIT;
END;
/

You don't need PL/SQL either though, you could use a hierarchical query (or recursive CTE) instead of a loop:

INSERT INTO results
  (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
SELECT
  level + 162,
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5)),
  round(dbms_random.value(1,5))
FROM dual
CONNECT BY level <= 1 + 400 - 163;

or as @MTO suggested, using floor instead of round:

INSERT INTO results
  (student_id,OPN1,OPN2,OPN3,OPN4,AGG1,AGG2,AGG3,AGG4,NEU1,NEU2,NEU3,NEU4,EXT1,EXT2,EXT3,EXT4,CSN1,CSN2,CSN3,CSN4)
SELECT
  level + 162,
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6)),
  floor(dbms_random.value(1,6))
FROM dual
CONNECT BY level <= 1 + 400 - 163;

db<>fiddle (with fewer columns for simplicity).

Upvotes: 2

Related Questions