Home » SQL & PL/SQL » SQL & PL/SQL » INSERT, SELECT CONNECT by (19.8)
INSERT, SELECT CONNECT by [message #685920] Tue, 26 April 2022 12:22 Go to next message
Unclefool
Messages: 52
Registered: August 2021
Member
I'm trying to load some test data into table t1 by calling 2 functions but I can't seem to get the INSERT, SELECT CONNECT by to work.

Any help would be greatly appreciated. Thanks to all who respond.



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 
SELECT random_date (DATE'2022-04-01',DATE '2022-04-30'),
random_timestamp (DATE'2022-04-01',DATE '2022-04-30')
CONNECT BY LEVEL<=1000;

Re: INSERT, SELECT CONNECT by [message #685921 is a reply to message #685920] Tue, 26 April 2022 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68226
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What does mean "I can't seem to get the INSERT, SELECT CONNECT by to work" precisely?

Note that INSERT VALUES and INSERT SELECT are exclusive, you choose one or the other one.

Re: INSERT, SELECT CONNECT by [message #685922 is a reply to message #685921] Tue, 26 April 2022 14:05 Go to previous messageGo to next message
Unclefool
Messages: 52
Registered: August 2021
Member
I've tried that too and it didn't work. I'm missing something but don't know what.
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;

Re: INSERT, SELECT CONNECT by [message #685923 is a reply to message #685922] Tue, 26 April 2022 14:45 Go to previous message
Michel Cadot
Messages: 68226
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You choose the wrong way, you have to use INSERT SELECT.

Maybe you should read your previous topic with the same question and have a look at the documentation.

Previous Topic: Remove unwanted characters
Next Topic: partition non partioned table
Goto Forum:
  


Current Time: Mon Jul 04 15:38:39 CDT 2022