14 Replies Latest reply: Nov 21, 2013 8:15 AM by Frank Kulash RSS

    PL/SQL help

    5bd2282d-55cf-45ae-b582-c665bc983116

      Write a PL/SQL program to compute the number of days in each month for year 2020. Your program will first create a table named Month_Days with two columns (see the table structure below). Use a Loop in your program to insert month and the number of days in that month into the table. INSERT INTO command can only be used once in your program. Your program should work correctly for any year.


      i get a error ..any ideas


      CREATE TABLE MONTH_DAYS( Month_ Varchar(9),Days_ Number(2));

       

       

      declare

      mons Varchar2(10);

      dats Varchar2(10);

       

       

      begin

       

       

      for i in 0..11 loop

      insert into MONTH_DAYS (month_, days_)

      values

      (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

      to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

       

       

      select month_ , days_ into mons, dats from month_days ;

      DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

      end loop;

      end;

        • 1. Re: PL/SQL help
          Sudhakar_B

          Dear OP,

          When I tried your example... I got

          PL/SQL: ORA-00913: too many values

          The INSERT statement is inserting 3 values into a table that has 2 columns!!

          Please remove the "i" from the INSERT statement and Insert works.

          However, you cannot select INTO in the loop. You have a to use a CURSOR or do the insert in loop (remove select from loop).

          Also,

          If you want to store Dates, Months, Time, Hours (or any time related values) in DB, please use the date / timestamp data type for column definition.

           

          vr,

          Sudhakar

          • 2. Re: PL/SQL help
            5bd2282d-55cf-45ae-b582-c665bc983116

            i got this code ....

             

            DROP TABLE MONTH_DAYS;

             

            CREATE TABLE MONTH_DAYS(cnt number(2), Month_ Varchar(9),Days_ Number(2));

             

            declare

            mons Varchar2(10);

            dats Varchar2(10);

             

            begin

             

            for i in 0..11 loop

            insert into month_days(cnt, month_, days_)

            values

            (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

            to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

             

            select month_ , days_ into mons, dats from month_days where cnt = i;

            DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

            end loop;

            end;



            But this code has another colum called CNT which i don't want how do i remove that column....without doing a ALTER

            • 3. Re: PL/SQL help
              rp0428

              Drop the table and recreate it without the third column.


              • 4. Re: PL/SQL help
                5bd2282d-55cf-45ae-b582-c665bc983116

                i did ...

                 

                CREATE TABLE MONTH_DAYS( Month_ Varchar(9),Days_ Number(2));

                 

                 

                declare

                mons Varchar2(10);

                dats Varchar2(10);

                 

                 

                begin

                 

                 

                for i in 0..11 loop

                insert into MONTH_DAYS (month_, days_)

                values

                (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

                to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

                 

                 

                select month_ , days_ into mons, dats from month_days ;

                DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

                end loop;

                end;

                 

                 

                but cant figure out whats wrong ..am a novice for PLSQL

                • 5. Re: PL/SQL help
                  Sudhakar_B

                  @OP,

                  Try the following...

                  Note that

                  I have commented out the SELECT inside the "anonymous block"

                  Removed the "i" from the INSERT statement

                  Selecting from table "after" the inserts are completed.

                  > drop table month_days

                  table MONTH_DAYS dropped.

                  > CREATE TABLE MONTH_DAYS( Month_ Varchar(9),Days_ Number(2))

                  table MONTH_DAYS created.

                  > declare

                  mons Varchar2(10);

                  dats Varchar2(10);

                  begin

                  for i in 0..11 loop

                  insert into MONTH_DAYS (month_, days_)

                  values

                  (to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

                  to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

                  --select month_ , days_ into mons, dats from month_days ;

                  --DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

                  end loop;

                  end;

                  anonymous block completed

                  > select month_ , days_  from month_days

                  MONTH_         DAYS_

                  --------- ----------

                  January           31

                  February          29

                  March             31

                  April             30

                  May               31

                  June              30

                  July              31

                  August            31

                  September         30

                  October           31

                  November          30

                  December          31

                   

                  12 rows selected

                  • 6. Re: PL/SQL help
                    5bd2282d-55cf-45ae-b582-c665bc983116

                    This is what i get

                     

                    ERROR at line 2:
                    ORA-00922: missing or invalid option
                    SP2-0851: Command beginning "dats Varch..." is not available in iSQL*Plus

                    insert into MONTH_DAYS (month_, days_) * 

                    ERROR at line 3:

                    ORA-06550: line 3, column 13:

                    PL/SQL: ORA-00942: table or view does not exist

                    ORA-06550: line 3, column 1:

                    PL/SQL: SQL Statement ignored

                     

                    http://199.17.15.102:5560/isqlplus/cabo/images/t.gif
                    • 7. Re: PL/SQL help
                      Frank Kulash

                      Hi,

                      5bd2282d-55cf-45ae-b582-c665bc983116 wrote:

                       

                      i did ...

                       

                      CREATE TABLE MONTH_DAYS( Month_ Varchar(9),Days_ Number(2));

                       

                       

                      declare

                      mons Varchar2(10);

                      dats Varchar2(10);

                       

                       

                      begin

                       

                       

                      for i in 0..11 loop

                      insert into MONTH_DAYS (month_, days_)

                      values

                      (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

                      to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

                       

                       

                      select month_ , days_ into mons, dats from month_days ;

                      DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

                      end loop;

                      end;

                       

                       

                      but cant figure out whats wrong ..am a novice for PLSQL

                      Your assignment says to create the table in PL/SQL, but you're doing it in SQL, before you start the PL/SQL.  Actually that's the correct way to do it; creating tables in PL/SQL is usually a really bad idea.  If you really want to comply with the instructions, then you need to use EXECUTE IMMEDIATE to do the CREATE TABLE inside PL/SQL, but remember, they're teaching something that you'll want to forget as soon as the class is over.  In real life, create tables in pure SQL, like you did above.

                       

                      If you only have 2 columns in the table, then you should only have 2 values in the INSERT statement.  Lose the first one, that is, the NUMBER i.

                       

                      There's no need to have a SELECT statement in this block.  You're telling the table what values it has, so you don't need a SELECT to make the table tell you what they are.   Set both of the variables, mons and dats, at the beginning of the loop, then use those variables in both the INSERT statement and the call to dbms_output.put_line.

                      I would make mons a DATE variable.  You can use

                      TO_CHAR (mons, 'fmMonth')

                      to get the name of the month when you need to.

                      You almost never need to nest TO_DATE within TO_CHAR in Oracle; 999 times out of a thousand there will be a simpler, more efficient way to get whatever you need without switching data types like that.  For example, if mons is a DATE, and you need to set a NUMBER variable to the number of days in the same month, then you can use EXTRACT and LAST_DAY, like this:

                       

                      dats := EXTRACT (DAY FROM LAST_DAY (mons));

                      • 8. Re: PL/SQL help
                        5bd2282d-55cf-45ae-b582-c665bc983116

                        i am a novice on this..can you show one ...am little bit confused..thank you so much for your help

                        • 9. Re: PL/SQL help
                          Frank Kulash

                          Hi,

                           

                          Here's an anonymous block that finds (and displays) the last day of 6 consecutive months, starting with October, 2013:

                          DECLARE
                              d     DATE  := TO_DATE ('31-Oct-2013', 'DD-Mon-YYYY');
                          BEGIN
                              FOR  i  IN  1 .. 6
                              LOOP
                                  dbms_output.put_line ( TO_CHAR (d, 'DD-Mon-YYYY') );

                                  d := ADD_MONTHS (d, 1);
                              END LOOP;
                          END;
                          /

                          Output:

                          31-Oct-2013

                          30-Nov-2013

                          31-Dec-2013

                          31-Jan-2014

                          28-Feb-2014

                          31-Mar-2014

                          This isn't exactly what you want to do, but it should give you some ideas.

                          • 11. Re: PL/SQL help
                            5bd2282d-55cf-45ae-b582-c665bc983116

                            CREATE TABLE MONTH_DAYS(cnt number(2), Month_ Varchar(9),Days_ Number(2));

                             

                            declare

                            mons Varchar2(10);

                            dats Varchar2(10);

                             

                            begin

                             

                            for i in 0..11 loop

                            insert into month_days(cnt, month_, days_)

                            values

                            (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

                            to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

                             

                            select month_ , days_ into mons, dats from month_days where cnt = i;

                            DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

                            end loop;

                            end;

                            /

                             

                            if you try this code it has a colum CNT which i dont want....i tried many ways ..how do u remove it without a ALTER

                            CNTMONTH_DAYS_
                            0January31
                            1February29
                            2March31
                            3April30
                            4May31
                            5June30
                            6July31
                            7August31
                            8September30
                            9October31
                            10November30
                            11December31
                            • 12. Re: PL/SQL help
                              Frank Kulash

                              Hi,

                               

                              If you want the table to have 2 columns (which is what the instructions require), the best way is to create the table with only 2 columns.  You don't need a 3rd column for this assignment.

                              The only way I know of to remove a column from a table is ALTER TABLE.  What's wrong with ALTER TABLE, if you really need to do it?

                               

                              You can use a table without displaying every column in it.  In real applications, it's quite common to only use 2 columns from a table, even though the table may have 3 columns (or 30).

                               

                              There is no need to use a SELECT in your PL/SQL code.  If you really, really want to do a SELECT anyway, then you can find the row you just INSERTed by using the month_ column.  No 2 months in the same year have the same name, so it will be unique, just as cnt is unique in your 3-column table.

                               

                              To check that the code was working correctly, I would say

                              SELECT  *

                              FROM    month_days;

                              in SQL, after the PL/SQL had finished  Your instructions don't say anything about checking it in PL/SQL, and PL/SQL is the wrong tool for that job.

                              • 13. Re: PL/SQL help
                                5bd2282d-55cf-45ae-b582-c665bc983116

                                well you cant have alter because ..the project requires..to write a code that generates 2 colums table on one shot. Only thing i dont get is i cant remove the CNT column when i remove it form the code it give an error...heres the code after removing the CNT column....but it gives sum error.

                                 

                                 

                                CREATE TABLE MONTH_DAYS( Month_ Varchar(9),Days_ Number(2));

                                 

                                 

                                declare

                                mons Varchar2(10);

                                dats Varchar2(10);

                                 

                                 

                                begin

                                 

                                 

                                for i in 0..11 loop

                                insert into MONTH_DAYS (month_, days_)

                                values

                                (i,to_char(add_months(to_date('20200101','YYYYDDMM'),i),'Month'),

                                to_char(last_day(add_months(to_date('20200101','YYYYDDMM'),i)),'DD'));

                                 

                                 

                                select month_ , days_ into mons, dats from month_days ;

                                DBMS_OUTPUT.PUT_LINE('Month: '||Mons||'Days: '|| Dats);

                                end loop;

                                end;

                                • 14. Re: PL/SQL help
                                  Frank Kulash

                                  Hi,

                                   

                                  The error occurs in the 2nd lap through the loop, after you've INSERTed the 2nd row into month_days.  The SELECT statement doesn't have a WHERE clause, so it will SELECT both rows from the table.  However, the variables that you're SELECTing INTO (mons and dats) can only hold 1 value each, so the TOO_MANY_ROWS error (ORA-01422) is raised.

                                   

                                  For solutions, see my last message.

                                   

                                  There are ways to SELECT multiple rows at the same time.  No doubt you'll be learning about those techniques in a future lesson.  Don't confuse yourself by trying to learn them before you've learned the material in this assignment.