Re: Inserting Records Into a table

From: B. Williams <willdrama_at_hotmail.com>
Date: Thu, 7 Sep 2006 20:05:36 -0400
Message-ID: <st2Mg.10122$JR5.9954@dukeread11>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1157673085.523104.66250_at_m73g2000cwd.googlegroups.com...
> B. Williams wrote:

>> "Ed Prochak" <edprochak_at_gmail.com> wrote in message
>> news:1157660418.704290.80130_at_h48g2000cwc.googlegroups.com...
>> > You are on the right path. Exactly what confuses you? All you need at
>> > this point is another loop to scan thru the days, inserting each pair
>> > (month,date). I'll refrain from further hints until your next post. So
>> > try the next step and see what you get. Come back if you need more
>> > help, or if you solve it. (It is nice when the discuaaion loop can be
>> > closed.)
>> >
>> > Ed
>> >
>> Ed,
>> I can get the loop to isert the months into the table using the following
>> code.
>>
>> FOR i IN 1..12 LOOP
>>      INSERT INTO yeardates
>>      (year_month)
>>      VALUES(varray_name(i));
>> END LOOP;
>>
>> I can also get the loop to insert the date using a similiar loop
>>
>> FOR j IN 1..31 LOOP
>>      INSERT INTO yeardates
>>      (year_day)
>>      VALUES(varray_date(j));
>> My problem is that I can't figure out how to get my output to display the
>> (month    day) format and even more confusing is that I can't figure out 
>> how
>> to have January display 31 dates while having February displays only 28 
>> and
>> so on.
>>
>> I need to make a loop for each month that will display that month along 
>> with
>> the days of that month.
>>
>> Can you assist me with that.
>

> Are you maybe making it more difficult than necessary for the learning
> experience? Take a look at this statement, no looping required:
> INSERT INTO MY_TABLE
> SELECT
> TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'MONTH')
> YEAR_MONTH,

>

> TO_NUMBER(TO_CHAR(TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1),'DD'))
> YEAR_DAY
> FROM
> ALL_OBJECTS
> WHERE
> ROWNUM<=366
> AND TO_DATE('01/01/2006','MM/DD/YYYY')+(ROWNUM-1)<'01-JAN-2007';

>

> In the above, I am using the ALL_OBJECTS view to seed a counter, in
> this case from 1 to 366 (to account for years with leap year days) -
> you can use any table with at least 366 rows. By using January 1, 2006
> as the starting date, and adding to that (ROWNUM - 1) days, we obtain
> all of the dates for 2006. All that you need to do is to format the
> dates as required. Note the check at the end to make certain that we
> do not include dates in the next year.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

>
Charles, I can do this in different ways as seen below.

set serveroutput on size 1000000

Declare
Type name_varray is varray(12) of varchar2(20); varray_name name_varray :=
name_varray(null,null,null,null,null,null,null,null,null,null,null,null);

vLastDay number;
vFirstDay varchar2(20);

v_counter BINARY_INTEGER := 0;

begin

varray_name(1) := 'January';
varray_name(2) := 'February';
varray_name(3) := 'March';
varray_name(4) := 'April';
varray_name(5) := 'May';
varray_name(6) := 'June';
varray_name(7) := 'July';
varray_name(8) := 'August';
varray_name(9) := 'September';
varray_name(10) := 'October';
varray_name(11) := 'November';
varray_name(12) := 'December';

dbms_output.put_line('Year_Month     Year_Day');

for i in 1..12 loop

vFirstDay := varray_name(i)||' 1 '||to_char(sysdate, 'YYYY');

vLastDay := to_number(to_char(last_day(to_date(vFirstDay, 'Month DD YYYY')), 'DD')); -- compute last day of 'current' month

for cdate in 1..vLastDay loop

dbms_output.put_line(rpad(varray_name(i),10)||' '||cdate); v_counter := v_counter + 1;
end loop;

end loop;
dbms_output.put_line('Total number of ' || v_counter ||' records were inserted.');
end;
/

but, I really want to accomplish this task while meeting all of the requirements. Received on Thu Sep 07 2006 - 19:05:36 CDT