Insert statement inserts wrong random numbers into table Oracle

Phaki Published at Dev

Phaki

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.

Alex Poole

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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-09-20

Comments

0 comments

transaction to delete table content and reload using insert statement and check if inserts executed then commit and print message else rollback

Multiple inserts to temp table followed by single insert to material table OR multiple inserts straight to material table

TOP Ranking

Article

Insert statement inserts wrong random numbers into table Oracle

Insert statement inserts wrong random numbers into table Oracle

pump.io port in URL

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Using Response.Redirect with Friendly URLS in ASP.NET

Can a 32-bit antivirus program protect you from 64-bit threats

Double spacing in rmarkdown pdf

How to fix "pickle_module.load(f, **pickle_load_args) _pickle.UnpicklingError: invalid load key, '<'" using YOLOv3?

3D Touch Peek Swipe Like Mail

Bootstrap 5 Static Modal Still Closes when I Click Outside

Assembly definition can't resolve namespaces from external packages

Vector input in shiny R and then use it

Emulator wrong screen resolution in Android Studio 1.3

Svchost high CPU from Microsoft.BingWeather app errors

Graphics Context misaligned on first paint

Python connect to firebird docker database

Is this docker-for-mac password dialog legit?

How to save models trained locally in Amazon SageMaker?