Re: Yearly Automatic Table Insertion

"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 Received on Tue Mar 23 2004 - 08:28:18 CST