Styjun

How to get the speed of my spaceship?

Multi-user CRUD: Valid, Problem, or Error?

Alternative to Willpower in Fighting Cravings

How do resistors generate different heat if we make the current fixed and changed the voltage and resistance? Notice the flow of charge is constant

Why do Martians have to wear space helmets?

How do amateur satellites stay consistently in the amateur-sat bands acoss the globe?

Will Jimmy fall off his platform?

What are some bad ways to subvert tropes?

comparing two addresses

Examples of fluid (including air) being used to transmit digital data?

Does a Globe of Invulnerability spell block outsiders from teleporting inside with a spell?

Taking my Ph.D. advisor out for dinner after graduation

How complicated can a finite double complex over a field be?

Is this standard Japanese employment negotiations, or am I missing something?

How important is it for multiple POVs to run chronologically?

Is there a minimum amount of electricity that can be fed back into the grid?

How to play a D major chord lower than the open E major chord on guitar?

Initializing variables in an "if" statement

How did Captain Marvel do this without dying?

Attach a visible light telescope to the outside of the ISS

Is this car delivery via Ebay Motors on Craigslist a scam?

Who goes first? Person disembarking bus or the bicycle?

Can you create a free-floating MASYU puzzle?

Do intermediate subdomains need to exist?

Connect By clause works on 11g but not on Oracle 8i : “ORA-01436: CONNECT BY loop in user data”

Create View with 365 daysDifference between a user and a schema in Oracle?Retrieve Last days data from TIMESTAMP(6) ColumnWhat is the difference between connect by level and connect by prior in oracle?ORA-00904: ORDER BY with UNION ALLSQL: Create Trailer CountMigrating from Oracle to SQL server. Dual table select query -> SQL serverHow to determine week of a quarter in Oracle queryIssue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance appropriate for the client connectionsql pivot function using date as a columnOracle inequality comparison - invalid number

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I found the code for a row generator from this question Create View with 365 days

CREATE VIEW year_days (the_day) AS
SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
/
SELECT * FROM year_days

Since I don't have CREATE privileges, I modified it to an inline-query form :

SELECT
 year_days.* 
FROM
 (
 SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
 FROM DUAL
 CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
 ) year_days

The inline-query code above works perfectly fine on one of our Oracle 11g R2 (v11.2.0.3.0) instance, as well as on Oracle's own LiveSQL (19c, v19.2.0.0.0).

However, it does not work on the instance that it needs to run on, which is 8i (v8.1.7.4.0).
I get ORA-01436: CONNECT BY loop in user data.

At first glance it seems that 8i sees an infinite loop in that code, but not 11g and up. Why ?

Note: I know 8i is old. I have no control over that.

sql oracle connect-by oracle8i

share|improve this question

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

add a comment | 

1

I found the code for a row generator from this question Create View with 365 days

CREATE VIEW year_days (the_day) AS
SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
/
SELECT * FROM year_days

Since I don't have CREATE privileges, I modified it to an inline-query form :

SELECT
 year_days.* 
FROM
 (
 SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
 FROM DUAL
 CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
 ) year_days

The inline-query code above works perfectly fine on one of our Oracle 11g R2 (v11.2.0.3.0) instance, as well as on Oracle's own LiveSQL (19c, v19.2.0.0.0).

However, it does not work on the instance that it needs to run on, which is 8i (v8.1.7.4.0).
I get ORA-01436: CONNECT BY loop in user data.

At first glance it seems that 8i sees an infinite loop in that code, but not 11g and up. Why ?

Note: I know 8i is old. I have no control over that.

sql oracle connect-by oracle8i

share|improve this question

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

add a comment | 

I found the code for a row generator from this question Create View with 365 days

CREATE VIEW year_days (the_day) AS
SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
/
SELECT * FROM year_days

Since I don't have CREATE privileges, I modified it to an inline-query form :

SELECT
 year_days.* 
FROM
 (
 SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
 FROM DUAL
 CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
 ) year_days

The inline-query code above works perfectly fine on one of our Oracle 11g R2 (v11.2.0.3.0) instance, as well as on Oracle's own LiveSQL (19c, v19.2.0.0.0).

However, it does not work on the instance that it needs to run on, which is 8i (v8.1.7.4.0).
I get ORA-01436: CONNECT BY loop in user data.

At first glance it seems that 8i sees an infinite loop in that code, but not 11g and up. Why ?

Note: I know 8i is old. I have no control over that.

sql oracle connect-by oracle8i

share|improve this question

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

CREATE VIEW year_days (the_day) AS
SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
/
SELECT * FROM year_days

SELECT
 year_days.* 
FROM
 (
 SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day
 FROM DUAL
 CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)), 'DDD'))
 ) year_days

share|improve this question

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

share|improve this question

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

edited Mar 25 at 21:02

Littlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

asked Mar 25 at 20:46

mach128xmach128x

3388 bronze badges

1 Answer
1

active

oldest

votes

1

If it doesn't have to be a connect by query, how about something simpler, such as

select rownum
from all_objects
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

or perhaps

select rownum 
from (select null from dual
 group by cube (1, 2, 3, 4, 5, 6, 7, 8, 9, 20)
 )
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

which both should work on Oracle 8i.

More nice row generator techniques on OraFAQ Forum, here: http://www.orafaq.com/forum/t/95011/102589/

share|improve this answer

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I am aware that other row generation techniques exist. However, this does not answer the question.
  
  – mach128x
  Mar 26 at 21:15
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OK, no problem. I hope at least one of them will satisfy your (and 8i's) needs.
  
  – Littlefoot
  Mar 26 at 21:17
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For future reference and to help others, this is the best article / script collection I found on the topic of row generators : "ROW GENERATOR - Methods to Generate Series", livesql.oracle.com/apex/livesql/file/…
  
  – mach128x
  Mar 26 at 22:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My preference went to the 'hierarchical query method' based on the above reference from Oracle. It should work on 8i. There must be some intricacies to that specific version that I need to discover and understand to make it work.
  
  – mach128x
  Mar 26 at 22:33
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55346167%2fconnect-by-clause-works-on-11g-but-not-on-oracle-8i-ora-01436-connect-by-loo%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

If it doesn't have to be a connect by query, how about something simpler, such as

select rownum
from all_objects
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

or perhaps

select rownum 
from (select null from dual
 group by cube (1, 2, 3, 4, 5, 6, 7, 8, 9, 20)
 )
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

which both should work on Oracle 8i.

More nice row generator techniques on OraFAQ Forum, here: http://www.orafaq.com/forum/t/95011/102589/

share|improve this answer

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I am aware that other row generation techniques exist. However, this does not answer the question.
  
  – mach128x
  Mar 26 at 21:15
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OK, no problem. I hope at least one of them will satisfy your (and 8i's) needs.
  
  – Littlefoot
  Mar 26 at 21:17
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For future reference and to help others, this is the best article / script collection I found on the topic of row generators : "ROW GENERATOR - Methods to Generate Series", livesql.oracle.com/apex/livesql/file/…
  
  – mach128x
  Mar 26 at 22:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My preference went to the 'hierarchical query method' based on the above reference from Oracle. It should work on 8i. There must be some intricacies to that specific version that I need to discover and understand to make it work.
  
  – mach128x
  Mar 26 at 22:33
  
  

  
  
  
  

add a comment | 

1

If it doesn't have to be a connect by query, how about something simpler, such as

select rownum
from all_objects
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

or perhaps

select rownum 
from (select null from dual
 group by cube (1, 2, 3, 4, 5, 6, 7, 8, 9, 20)
 )
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

which both should work on Oracle 8i.

More nice row generator techniques on OraFAQ Forum, here: http://www.orafaq.com/forum/t/95011/102589/

share|improve this answer

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I am aware that other row generation techniques exist. However, this does not answer the question.
  
  – mach128x
  Mar 26 at 21:15
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  OK, no problem. I hope at least one of them will satisfy your (and 8i's) needs.
  
  – Littlefoot
  Mar 26 at 21:17
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  For future reference and to help others, this is the best article / script collection I found on the topic of row generators : "ROW GENERATOR - Methods to Generate Series", livesql.oracle.com/apex/livesql/file/…
  
  – mach128x
  Mar 26 at 22:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My preference went to the 'hierarchical query method' based on the above reference from Oracle. It should work on 8i. There must be some intricacies to that specific version that I need to discover and understand to make it work.
  
  – mach128x
  Mar 26 at 22:33
  
  

  
  
  
  

add a comment | 

If it doesn't have to be a connect by query, how about something simpler, such as

select rownum
from all_objects
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

or perhaps

select rownum 
from (select null from dual
 group by cube (1, 2, 3, 4, 5, 6, 7, 8, 9, 20)
 )
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

which both should work on Oracle 8i.

More nice row generator techniques on OraFAQ Forum, here: http://www.orafaq.com/forum/t/95011/102589/

share|improve this answer

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

select rownum
from all_objects
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

select rownum 
from (select null from dual
 group by cube (1, 2, 3, 4, 5, 6, 7, 8, 9, 20)
 )
where rownum <= to_number(to_char(last_day(add_months(trunc(sysdate, 'YYYY'),11)), 'DDD'));

share|improve this answer

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges

answered Mar 25 at 20:53

LittlefootLittlefoot

30.4k77 gold badges1717 silver badges3434 bronze badges