I'm trying to load some test data into table t1 by calling 2 functions but I can't seem to get the INSERT and CONNECT by to work.
Any help would be greatly appreciated.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to IN DATE
) RETURN DATE
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_date;
/
CREATE OR REPLACE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE,
ts TIMESTAMP
);
INSERT into t1 (dt, ts) VALUES
random_date (DATE'2022-04-01',DATE '2022-04-30'),
random_timestamp (DATE'2022-04-01',DATE '2022-04-30')
CONNECT BY LEVEL<=1000;
Not values, but select from dual (didn't insert 1000 rows; 10 will suffice; also, SEQ_NUM values don't start from 1 as I ran that code several times, but that's irrelevant):
SQL> INSERT INTO t1 (
2 dt,
3 ts
4 )
5 SELECT
6 random_date(DATE '2022-04-01', DATE '2022-04-30'),
7 random_timestamp(DATE '2022-04-01', DATE '2022-04-30')
8 FROM
9 dual
10 CONNECT BY
11 level <= 10;
10 rows created.
Result:
SQL> select * From t1;
SEQ_NUM DT TS
---------- -------------------- ------------------------------
41 03-APR-2022 00:36:33 05-APR-2022 03:38:39.215073
42 22-APR-2022 15:29:50 26-APR-2022 23:44:20.687417
43 27-APR-2022 23:42:49 29-APR-2022 23:54:17.692053
44 21-APR-2022 19:24:10 22-APR-2022 23:07:20.602254
45 13-APR-2022 20:45:39 04-APR-2022 04:05:08.815214
46 07-APR-2022 09:35:37 07-APR-2022 21:32:28.443624
47 23-APR-2022 11:48:18 18-APR-2022 06:40:39.608578
48 22-APR-2022 23:53:04 02-APR-2022 13:13:54.285010
49 14-APR-2022 21:35:57 10-APR-2022 12:26:08.419025
50 11-APR-2022 21:49:32 10-APR-2022 17:20:45.033907
SQL>