create a temp table 1

[neskin]

Hi there,
i am using Oracle 8
I am new to Oracle
I need to create temp table to store all dates for the month.
The stored prog has 2 parameters p_month and p_year so if p_month = 10 then the temp table will fill with dates from 1 to 31
Regards
nat

 

[SantaMufasa]

...the temp table will fill with dates from 1 to 31...

Nat, "1 to 31" are not "dates"...they are just numbers. "01-OCT-2006" through "31-OCT-2006" are dates. I cannot tell which you need: 31 numbers or 31 dates. You do not need a "temp table"; an in-line view should do what you want. And since you haven't told us anything about your "stored prog", I'll illustrate proof of concept with bind variables, which should work just as well as arguments, but we don't need a "prog" for the proof of concept:


Option 1: An in-line view of numbers for a particular month:

SQL> var p_month char(2)
SQL> exec :p_month := '02'
SQL> var p_year number
SQL> exec :p_year := 2000;
select day
from (select rownum day from all_objects
       where rownum <= to_char(last_day(to_date(:p_month||:p_year,'mmyyyy')),'dd'))
/

DAY
---
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29

Option 2: An in-line view of dates for a particular month:

**************************************************************************************
SQL> var p_month char(2)
SQL> exec :p_month := '02'
SQL> var p_year number
SQL> exec :p_year := 2001;
select dates
  from (select to_date(:p_month||substr('0'||rownum,-2)||:p_year,'mmddyyyy')dates
          from all_objects
         where rownum <= to_char(last_day(to_date(:p_month||:p_year,'mmyyyy')),'dd'))
/

DATES
---------
01-FEB-01
02-FEB-01
03-FEB-01
04-FEB-01
05-FEB-01
06-FEB-01
07-FEB-01
08-FEB-01
09-FEB-01
10-FEB-01
11-FEB-01
12-FEB-01
13-FEB-01
14-FEB-01
15-FEB-01
16-FEB-01
17-FEB-01
18-FEB-01
19-FEB-01
20-FEB-01
21-FEB-01
22-FEB-01
23-FEB-01
24-FEB-01
25-FEB-01
26-FEB-01
27-FEB-01
28-FEB-01

I used February (for both a leap year and a non-leap year) to show that the code will generate the proper number of days for a given month.

Let us know if either of these solutions takes care of business for you.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

there were dates thank u