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

Question

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

The original query is this:

INSERT INTO HR.fastData (ID) 
SELECT 1 + (Level -1) * 1 
FROM dual connect by Level < 100000000;

First error received:

ORA-30009: Not enough memory for CONNECT BY operation

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);

Next error I received:

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

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!

The syntax is `connect by [boolean expression]` - that is, after `connect by` there should be an expression that evaluates to true or false, and rows will keep being added until it evaluates to true. The `connect by` clause is intended for hierarchical queries (`connect by prior key = parent_key`) but it was discovered some time ago that it could be used as a row generator. Other row generator constructions are available, e.g. `select rownum from xmltable('1 to 10000')`. — William Robertson, Jan 10 '20 at 23:54

score 3 · Answer 1 · answered Jan 10 '20 at 04:00

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.

score 1 · Answer 2 · answered Jan 10 '20 at 04:18

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;

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

2 Answers2