Insert random data in Oracle table

Question

I wan to create random data in Oracle table:

CREATE TABLE EVENTS(
  EVENTID INTEGER NOT NULL,
  SOURCE VARCHAR2(50 ),
  TYPE VARCHAR2(50 ),
  EVENT_DATE DATE,
  DESCRIPTION VARCHAR2(100 )
)
/

I tried this:

BEGIN  
FOR loop_counter IN 1..1000 
LOOP 
INSERT INTO EVENTS (EVENTID, SOURCE, TYPE, EVENT_DATE, DESCRIPTION) VALUES (loop_counter, loop_counter, 'warning', 
DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J') ,TO_CHAR(DATE '9999-12-31','J')), dbms_random.value(1,100)); 
END LOOP; 
COMMIT; 
END;

I get this error exception

Error report - ORA-06550: line 5, column 13: PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER ORA-06550: line 4, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Can you give me advice how I can fix this issue?

That dbms_random() call will also not return a date, so it will fail if you try to insert a number like 3684482.0997 into a DATE column — user330315
– user330315, Commented Nov 22, 2015 at 21:39

Lukasz Szozda · Accepted Answer · 2015-11-22 21:41:40Z

16

Use:

BEGIN  
FOR loop_counter IN 1..1000 LOOP 
INSERT INTO "EVENTS" (EVENTID, "SOURCE", TYPE, EVENT_DATE, DESCRIPTION) 
VALUES (loop_counter, loop_counter, 'warning', 
        TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J') ,TO_CHAR(DATE '9999-12-31','J'))),'J')
        ,dbms_random.value(1,100)
       ); 
END LOOP; 
COMMIT; 
END;

SqlFiddleDemo

Changes:

Add mising ; after final END
Quote keywords
Rewrite random date generation

answered Nov 22, 2015 at 21:41

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

Bartek · Accepted Answer · 2017-11-30 14:39:56Z

4

INSERT INTO EVENTS (EVENTID, "SOURCE", TYPE, EVENT_DATE, DESCRIPTION)
SELECT level, level, 'warning', 
        TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J') ,TO_CHAR(DATE '9999-12-31','J'))),'J')
        ,dbms_random.value(1,100)
  FROM DUAL
CONNECT BY LEVEL <= 1000;

answered Nov 30, 2017 at 14:39

Bartek

1463 bronze badges

Comments

Leo · Accepted Answer · 2017-07-26 10:01:48Z

3

Also, if you use PL/SQL Developer by Allroundautomations, you can find out good tool for this job: Data Generator. It can be very useful, because it can help to generate some data in any types and place it to a tables.

(see screenshot attached)

edited Jul 26, 2017 at 10:01

answered Jan 19, 2016 at 8:29

Leo

1,0315 gold badges22 silver badges44 bronze badges

Collectives™ on Stack Overflow

Insert random data in Oracle table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related