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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
mach128x
  • 320
  • 3
  • 13

1 Answers1

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/

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I am aware that other row generation techniques exist. However, this does not answer the question. – mach128x Mar 26 '19 at 21:15
  • OK, no problem. I hope at least one of them will satisfy your (and 8i's) needs. – Littlefoot Mar 26 '19 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", https://livesql.oracle.com/apex/livesql/file/content_CA9YZ0WXN7POXY4JEST3PU87G.html – mach128x Mar 26 '19 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 '19 at 22:33