Oracle INSERT and CONNECT by-CodePudding

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;

CodePudding user response：

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>