SQL statement for generating random data

Question

I have this Oracle table which I want to populate with random data:

CREATE TABLE AGENT_HISTORY(
  EVENTID INTEGER NOT NULL,
  AGENTID INTEGER NOT NULL,
  EVENT_DATE DATE,
  CPU INTEGER,
  RAM NUMBER,
  HDD NUMBER,
  NETWORK_UP NUMBER,
  NETWORK_DOWN NUMBER
)
/

-- CREATE INDEXES FOR TABLE AGENT_HISTORY

CREATE INDEX IX_RELATIONSHIP1 ON AGENT_HISTORY (AGENTID)
/

-- ADD KEYS FOR TABLE AGENT_HISTORY

ALTER TABLE AGENT_HISTORY ADD CONSTRAINT KEY8 PRIMARY KEY (EVENTID)
/

How I can generate random data for 60 days back(column EVENT_DATE)? Every day I want to insert for example some random network traffic data?

You have to use dbms_random package to generate random numbers. and 60 days back means? and how many rows you have to generate?? — Tharunkumar Reddy
– Tharunkumar Reddy, Commented Aug 26, 2015 at 11:28
provide table structer with constraints like unique key, primary key — Tharunkumar Reddy
– Tharunkumar Reddy, Commented Aug 26, 2015 at 12:00

Tharunkumar Reddy · Accepted Answer · 2015-08-26 12:36:54Z

3

Use below pl/sql block for generating random data and also it will insert into your table.

    declare 
      v_EVENTID INTEGER;
      v_AGENTID INTEGER:=1;
      v_EVENT_DATE  DATE:=sysdate-60; 
      v_CPU INTEGER;
      v_RAM NUMBER;
      v_HDD NUMBER;
      v_NETWORK_UP NUMBER;
      v_NETWORK_DOWN NUMBER;
    begin 
    for i in 1..100
    loop
    v_EVENT_DATE:=v_EVENT_DATE-1;
    select i,ceil(dbms_random.value(1,1000)),floor(dbms_random.value(1,1000)),ceil(dbms_random.value(1,100))*2,floor(dbms_random.value(1,100))*2,100+i
     into v_EVENTID,v_CPU,v_ram,v_HDD,v_NETWORK_UP,v_NETWORK_DOWN    from dual;
    --insert into AGENT_HISTORY values(v_EVENTID,v_AGENTID,v_EVENT_DATE,v_CPU,v_ram,v_HDD,v_NETWORK_UP,v_NETWORK_DOWN);
dbms_output.put_line(v_EVENTID||' '||v_AGENTID||' '||v_EVENT_DATE||' '||v_CPU||' '||v_ram||' '||v_HDD||' '||v_NETWORK_UP||' '||v_NETWORK_DOWN) ;
    end loop;
    end;

edited Aug 26, 2015 at 12:36

answered Aug 26, 2015 at 11:36

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

Peter Penzov Over a year ago

I updated the post. Column AGENTID is always with value 1

Peter Penzov Over a year ago

I get Error report - ORA-06550: line 18, column 13: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 18, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Tharunkumar Reddy Over a year ago

Please check the latest pl/sql block i have updated it. I have missed ram column thats why we got error:)

Peter Penzov Over a year ago

I get Error report - ORA-00001: unique constraint (ADMIN.KEY8) violated ORA-06512: at line 16 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

Tharunkumar Reddy Over a year ago

Again i have edited the pl/sql block please check once.

|

Husqvik · Accepted Answer · 2015-08-26 11:43:11Z

1

INSERT INTO AGENT_HISTORY (EVENTID, AGENTID, EVENT_DATE, CPU, RAM, HDD, NETWORK_UP, NETWORK_DOWN)
SELECT LEVEL, LEVEL, TRUNC(SYSDATE - LEVEL + 1), DBMS_RANDOM.VALUE * 100, DBMS_RANDOM.VALUE, DBMS_RANDOM.VALUE, DBMS_RANDOM.VALUE, DBMS_RANDOM.VALUE
FROM DUAL CONNECT BY LEVEL <= 60;

edited Aug 26, 2015 at 11:43

answered Aug 26, 2015 at 11:34

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Comments

Bryant · Accepted Answer · 2015-08-26 13:22:45Z

If you want a procedural-generation feel to your code, without actually context-switching over to PL/SQL, you could generate data using the MODEL clause...

INSERT INTO agent_history (EVENTID, AGENTID, EVENT_DATE, CPU, RAM, HDD, NETWORK_UP, NETWORK_DOWN)
SELECT EVENTID, AGENTID, EVENT_DATE, CPU, RAM, HDD, NETWORK_UP, NETWORK_DOWN
FROM agent_history
MODEL DIMENSION BY (0 ITERATION)
      MEASURES (EVENTID, AGENTID, EVENT_DATE, CPU, RAM, HDD, NETWORK_UP, NETWORK_DOWN)
      RULES ITERATE (60) ( --Or however many days you want to iterate over
        EVENTID[ITERATION_NUMBER]      = 100-ITERATION_NUMBER,              
        AGENTID[ITERATION_NUMBER]      = 1,                                 
        EVENT_DATE[ITERATION_NUMBER]   = TRUNC(SYSDATE-ITERATION_NUMBER+1), 
        CPU[ITERATION_NUMBER]          = ceil(dbms_random.value(1,100)),
        RAM[ITERATION_NUMBER]          = floor(dbms_random.value(1,100)),
        HDD[ITERATION_NUMBER]          = ceil(DBMS_RANDOM.VALUE * 100),
        NETWORK_UP[ITERATION_NUMBER]   = DBMS_RANDOM.VALUE * 100+1,
        NETWORK_DOWN[ITERATION_NUMBER] = DBMS_RANDOM.VALUE * ITERATION_NUMBER
      )
ORDER BY EVENT_DATE;

Collectives™ on Stack Overflow

SQL statement for generating random data

3 Answers 3

19 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

19 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related