Re: Yearly Automatic Table Insertion

Brian,

Thanks for pointing out that you don't really even need a table. I was just thinking along the lines of how to avoid having a scheduled job. (I think that's what the author of this question was looking for.)

I like your idea even better than mine.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

Bricklen <bricklen-rem_at_yahoo.comz> wrote in message news:<oqY7c.926$wg1.459_at_edtnps84>...
> 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;
Received on Tue Mar 23 2004 - 14:16:17 CST