Re: Yearly Automatic Table Insertion

Mark C. Stock wrote:

> "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
> news:40604062.5085BD27_at_remove_spam.peasland.com...
> | > You could insert all of the years for the next 20 years all at once.
> | > That way you don't have to schedule a job. Change your query so that
> | > it selects only the appropriate values...
> | >
> | > select year
> | > from dates
> | > where year<=to_number(to_char(sysdate, 'YYYY'))
> | > /
> |
> | Why use a special table at all? In 20 years, you'll just have to add
> | more rows. But you don't need a special table for this. Just extend your
> | query further:
> |
> | SQL> select rownum+1990 from all_objects
> | 2 where rownum+1990 <= to_char(sysdate,'YYYY');
> |
> | ROWNUM+1990
> | -----------
> | 1991
> | 1992
> | 1993
> | 1994
> | 1995
> | 1996
> | 1997
> | 1998
> | 1999
> | 2000
> | 2001
> | 2002
> | 2003
> | 2004
> |
> | 14 rows selected.
> |
> |
> | All you need is *any* table or view that has the number of rows you need
> | to display. It doesn't have to be a table that you specifically populate
> | with data. In my example above, I display all of the years from 1990 to
> | the current year. This can be modified accordingly.
>
> yabut, don't use a data dictionary view! use a simple table that you know
> will always be in memory -- or a pipelined function, or (if HTML) a function
> that generates the <SELECT> tag, or just handle it on the client side
>
> ;-{ mcs
>
>

Alternatively:

select to_char(sysdate,'YYYY')+rownum dt from (select 1

     from
         (select 0 a,0 b,0 c,0 d,0 e
         from dual
         where rownum = 1)
     group by cube(a,b,c,d,e)) b
     where rownum <= 20;