CODEWITHSUNDEEP
sqloracle-databasesqlplusconnect-by
slsu
slsu

Reputation: 163

ORA-30009: Not enough memory for CONNECT BY operation / ORA-01788: CONNECT BY clause required in this query block

Oracle newbie here - I am trying to run an insert statement to generate a very large amount of data.

  1. The original query is this:
INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM dual connect by Level < 100000000;
  1. First error received:

ORA-30009: Not enough memory for CONNECT BY operation

  1. I followed the guidance provided here

    Modified Query:

INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM
    (select level from dual connect by Level < 10000),
    (select level from dual connect by Level < 10000);
  1. Next error I received:

ORA-01788: CONNECT BY clause required in this query block

  1. Modified query now looks like this:
INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM DUAL CONNECT BY 
     (select Level from dual connect by Level < 10000),
     (select Level from dual connect by level < 10000);

I am not able to get this to execute correctly, after many tries of different variations of the query. Am I using/placing the CONNECT BY statement properly? Would appreciate any guidance.

Thanks!

Upvotes: 2

Views: 2397

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

var x number;
exec :x := 10;
SELECT          level FROM dual connect by level <= :x
union all
select 1 * :x + level from dual connect by level <= :x
union all
select 2 * :x + level from dual connect by level <= :x
union all
select 3 * :x + level from dual connect by level <= :x;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

It appears that the query you want is

SELECT ((lvl1-1)*10) + (lvl2-1) + 1 as ID
  FROM (select level as lvl1 from dual connect by Level <= 10000)
  CROSS JOIN (select level as lvl2 from dual connect by Level <= 10000);

I can't guarantee that your system can generate all these numbers at one go, but in principle this will work. Here's a db<>fiddle which shows this query works when each subquery is limited to 10 levels, generating a total of 100 rows.

Upvotes: 3

Related Questions