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?
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:
;
after final END
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With