if you factor that predicate so that ROWNUM is on the LHS by itself -- it'll be OK

rownum <= TO_DATE('01/31/2002','MM/DD/YYYY')-TO_DATE('01/01/2001','MM/DD/YYYY')+1

otherwise, it'll evaluate that for every row in all_objects -- not just the 31 you want.

SELECT TO_DATE('01/01/2001','MM/DD/YYYY') + (ROWNUM - 1) DATEGEN
FROM ALL_OBJECTS
WHERE TO_DATE('01/01/2001','MM/DD/YYYY') + (ROWNUM - 1) <=
TO_DATE('01/31/2002','MM/DD/YYYY')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 28 0.74 0.72 0 14080 4 396
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 0.76 0.75 0 14080 4 396


SELECT TO_DATE('01/01/2001','MM/DD/YYYY') + (ROWNUM - 1) DATEGEN
FROM ALL_OBJECTS
WHERE
rownum <=
TO_DATE('01/31/2002','MM/DD/YYYY') - TO_DATE('01/01/2001','MM/DD/YYYY') + 1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 28 0.10 0.08 0 1636 4 396
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 0.12 0.10 0 1636 4 396

if you need more then 2 to 3 years of dates generated -- (given that all objects in 73 is around 1000 rows and 20-30,000 for a java install in 8i), I question what you are doing. What would you do with 1,000 days in a list?

The solution of course would be for YOUR application to install and populate a table that is big enough for you. Part of your install would be:

create table rows ( x int );
begin
for i in 1 .. 1000000 -- or whatever
loop
insert into rows values ( i );
end loop;
commit;
end;
/

In 9i, we can use a pipelined PLSQL function to generate as many rows as you like on the fly. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2270447621346 <code>
for an example of that.

A DBA in production doesn't have to give the rights -- select on all_objects is always granted to PUBLIC upon install. Many Many products will not work without it. It is called "the data dictionary".

This is in catalog.sql:

grant select on ALL_OBJECTS to PUBLIC with grant option
/

all of the ALL_* and USER_* views are selectable by PUBLIC (anyone with CREATE SESSION can see them)

Hopefully your friend is not a DBA for a production environment!

Read the above comments about all_objects and security.

If you every have a database where all_objects access has been revoked, you are in a database that is

o not supported by Oracle support in all likelyhood.
o won't support most any 3rd party application

ALL_OBJECTS is *always* granted select on to PUBLIC. If it isn't, the database is broken and needs to be fixed.

The very idea that accessing ALL_OBJECTS is a security "risk" is utter nonsense. I would be very interested in hearing from others who think otherwise AND WHY -- so I can correct their thinking.

ok, I'd use BETWEEN then?

... .where column between :a and :b

oh, you mean like the original answer above??

select to_date(your_start)+rownum-1
from all_objects
where rownum <= to_date(your_end)-to_date(your_start)+1

in 9ir2 and up, use

with as_many_rows_as_you_need
as
(select level l
from dual
connect by level <= :N )
select .....
from as_many_rows_as_you_need, .....

isn't that just:

where due_date between pdue_date-55 and pdue_date+55;

sorry, clear as mud -- no example to work with.

do you want start_date of null to be like "negative infinity"?? not sure why you put null in there if so.

order by column NULLS FIRST;

order by column NULLS LAST;

umm, I don't even know what I'm looking at here.

This is a little vague, don't you think?

the dual trick to generate rows only works in software written this century, eg: 9ir2 and above.