Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Inserting Records Into a table

Re: Inserting Records Into a table

From: B. Williams <willdrama_at_hotmail.com>
Date: Thu, 7 Sep 2006 18:19:02 -0400
Message-ID: <yV0Mg.10111$JR5.8533@dukeread11>

"Ed Prochak" <edprochak_at_gmail.com> wrote in message news:1157660418.704290.80130_at_h48g2000cwc.googlegroups.com...
>
> B. Williams wrote:
>> "DA Morgan" <damorgan_at_psoug.org> wrote in message
>> news:1157647831.249454_at_bubbleator.drizzle.com...
>> > B. Williams wrote:
>> >> I have created a table with two columns and one of the columns is the
>> >> month and the other is the date. I want to use a pl/sql program to
>> >> insert
>> >> the months and dates into the table using a loop instead of using a
>> >> bunch
>> >> of insert statements.Will someone assist me with this? If I can get
>> >> some
>> >> help with January, I can figure the rest of the months out.
>> >>
>> >> Thanks
>> >
>> > Why two columns to do one column's work?
>> >
>> > SELECT SYSDATE, TO_CHAR(SYSDATE, 'MON'), TO_CHAR(SYSDATE, 'MM'),
>> > TO_CHAR(SYSDATE, 'MONTH')
>> > FROM dual;
>> >
>> > And using PL/SQL to do this is even more backwards.
>> >
>> > But assuming this is just a self-education exercise:
>> >
>> > CREATE TABLE t (
>> > datecol DATE);
>> >
>> > BEGIN
>> > FOR i IN 1..10 LOOP
>> > INSERT INTO t
>> > (datecol)
>> > VALUES(SYSDATE+i);
>> > END LOOP;
>> > COMMIT;
>> > END;
>> > /
>> >
>> > SELECT * FROM t;
>> > --
>> > Daniel Morgan
>> > University of Washington
>> > Puget Sound Oracle Users Group
>>
>> I have this book on using PL/SQL and it is one of the exercises on
>> working
>> with records. It ask to create the two columns with the month being a
>> varhcar2 and the date being an integer. The end result is supposed to
>> list
>> the two columns with the month under the month column and the date under
>> the
>> day column. It also requires using a seperate loop for every month and a
>> counter to keep track of the total records inserted. I have a good
>> understanding of counter so I don't need any assistance with that. I
>> created
>> a loop for the months, but it is rather long and it doesn't meet the
>> requirement for the day. I'll post that below. What I need is something
>> like
>> this
>>
>> YEAR_MONTH YEAR_DAY
>> January 1
>> January 2
>> ...
>> January 30
>> January 31
>>
>>
>> This is the code I wrote to just list the months. I haven't added the
>> insert
>> portion yet because I am a little confused.
>>
>> 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);
>>
>> 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');
>>
>> for i in 1..12 loop
>>
>> dbms_output.put_line(varray_name(i));
>>
>> end loop;
>> end;
>> /
>
> 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. Received on Thu Sep 07 2006 - 17:19:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US