Skip to Main Content
  • Questions
  • No result from the query when counter exceeds 110000000


Question and Answer

Chris Saxon

Thanks for the question, Michel.

Asked: September 07, 2023 - 10:50 am UTC

Last updated: September 07, 2023 - 12:21 pm UTC

Version: Oracle Database 19c Enterprise Edition Release - Production

Viewed 1000+ times

You Asked


The below query does not return any value when the value of the counter is equal or greater than 110000000.

NOTE: It works well for 6100 or 19 000 000 as example.

(select level counter from dual connect by level <= 120000000)
counter =(SELECT SUM(peevf.screen_entry_value)
  FROM pay_element_entries_f peef,
       pay_element_entry_values_f peevf,
       pay_input_values_f pivf,
       pay_sub_classn_rules_f pscrf,
       pay_ele_classifications_tl pect
WHERE peef.element_entry_id = peevf.element_entry_id
AND peef.effective_start_date BETWEEN TO_DATE('01/01/2021','DD/MM/YYYY') AND TO_DATE('31/12/2021','DD/MM/YYYY')
   AND peef.person_id = <REPLACE with PersonID>
   AND peevf.input_value_id = pivf.input_value_id
   AND pivf.base_name = 'Amount'
   AND peef.element_type_id = pscrf.element_type_id
   AND pscrf.classification_id = pect.classification_id
   AND pect.LANGUAGE = 'US'
   AND pect.classification_name = 'TOTAL_VARIABLE_COMPENSATION')

Is this a limitation from the "select level counter from dual connect by level" ?

NOTE: to replace with personID in the query: <REPLACE with PersonID>

and Chris said...

It's unclear exactly what the problem is here - providing any error messages or output you get helps a lot!

That said, I'm guessing you're hitting this problem:

select count(*) from dual connect by level <= 100000000;

ORA-30009: Not enough memory for CONNECT BY operation

i.e. you've hit a memory limit.

You can overcome this by:

Cross joining a smaller data set with itself and limiting the rows:

with rws as (
  select level n from dual connect by level <= 1000000
  select count(*) from rws cross join rws
  where  rownum <= 100000000;


You could also use recursive WITH instead (this can be much slower than connect by):

with rws (n) as (
  select 1 n from dual
  union all
  select n+1 from rws 
  where n <= 100000000
select count(*) from rws;

Is this answer out of date? If it is, please let us know via a Comment

More to Explore


The Oracle documentation contains a complete SQL reference.