1 2 3 4 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 11:33 AM by 969952 Go to original post RSS
      • 30. Re: Load data from excel to Oracle Tables
        sb92075
        UPDATE table1
        SET sysyear = SYSDATE
        WHERE sysyear IS NULL;
        • 31. Re: Load data from excel to Oracle Tables
          969952
          still unable to update the data.


          is it possible through pl/sql block by using cursor?
          • 32. Re: Load data from excel to Oracle Tables
            AlbertoFaenza
            966949 wrote:
            Al

            Thanks you all for your suggestions. I am facing the problem is,
            It looks that you are making fun of us. Just thanking for suggestions but totally ignoring them. You still have all your question unresolved and you are not able to post proper information when you ask something.

            How could you expect people to help in you if they have to guess what you are doing?
            And again, this thread was related to loading data from Excel and it looks that now you just post anything that comes to your mind.

            Regarding your update in table1, post table structure if it is not asking too much.
            Which data type is sysyear in table1?
            I tried to use my crystal ball but it looks that today it is not working.
            I have tried:
            ALTER SYSTEM SET MIND READING MODE;
            also not working.

            The only one that can help in that is you. :-)

            Regards.
            Al
            • 33. Re: Load data from excel to Oracle Tables
              969952
              I am not joking am doing hardly.. but i am not able to resolve it. Please find the below data with structure as well.
              create table t1
                 (
                   no  number,
                   name varchar2(20),
                   joindt  date,
                   joined_month date
                   sal number(8,2)
              )
              
              so here got created.
               the columns Joindt and joined_month having nulls.
              
              Joindt    joined_month
              ------------------------------
                   more than 100000 records having nulls.
              
              so now I want to update 
              
              JOINDT = CURRENT_YEAR
              JOINED_MONTHS = current_month -1 ( i.e. LastMONTH number  it'll give like joined_month=11-1 i.e 10)
              like that I need to update the data.

              Please let me know still need more info.

              Thanks.
              • 34. Re: Load data from excel to Oracle Tables
                odie_63
                JOINDT = CURRENT_YEAR
                JOINED_MONTHS = current_month -1 ( i.e. LastMONTH number  it'll give like joined_month=11-1 i.e 10)
                Aren't you beginning to see the contradiction?

                Both target columns are of DATE datatypes, but you want to store NUMBERs in them.
                If you don't see the difference then what can we do?

                Now you have two options :

                1) use the correct datatype to store your data, i.e. redefine columns as NUMBER.

                2) if you absolutely want to use DATEs, then the best you can do is to store TRUNC(sysdate, 'YYYY') in JOINDT and TRUNC(ADD_MONTHS(sysdate,-1), 'MM') in JOINED_MONTHS :
                SQL> select TRUNC(sysdate, 'YYYY')
                  2       , TRUNC(ADD_MONTHS(sysdate,-1), 'MM')
                  3  from dual;
                 
                TRUNC(SYSDATE,'YYYY') TRUNC(ADD_MONTHS(SYSDATE,-1),'
                --------------------- ------------------------------
                01/01/2012            01/10/2012
                 
                • 35. Re: Load data from excel to Oracle Tables
                  969952
                  Hi am able to retirve the data by using the aboe query but the problem which I am facing is am not able to insert or update the data into the table which are having null values.

                  and one more is please have a look into the below
                  UPDATE TEMP
                    SET JOINED_YEAR= 
                                   CASE JOINED_YEAR
                                        WHEN JOINED_MONTH  > '9 ' then   JOINED_YEAR+1
                                        WHEN  JOINED_MONTH   = '10' then   JOINED_YEAR+1
                                        WHEN JOINED_MONTH   = '11' then   JOINED_YEAR+1
                                      Else
                                        JOINED_YEAR
                                    END;
                  but am getting the below error please have a look .

                  ORA-00905: missing keyword

                  Thanks.
                  • 36. Re: Load data from excel to Oracle Tables
                    AlbertoFaenza
                    966949 wrote:
                    I am not joking am doing hardly.. but i am not able to resolve it. Please find the below data with structure as well.
                    I'm not saying that you are not doing hardly. You were simply doing wrong. When you posted the correct information (table structure) you've got the information very quickly. Was it difficult to post the same information from the beginning?

                    If you follow the FAQ to post your questions you can be sure to get quicker answer. If you think the people here should know what you do you threads will become longer and longer.

                    Regards.
                    Al
                    • 37. Re: Load data from excel to Oracle Tables
                      969952
                      am sorry I didn't mean it..

                      But looking for your suggestions and will follow the same.

                      Thanks.
                      • 38. Re: Load data from excel to Oracle Tables
                        rp0428
                        >
                        Hi am able to retirve the data by using the aboe query but the problem which I am facing is am not able to insert or update the data into the table which are having null values.
                        >
                        Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!

                        You can use TRUNC and TO_CHAR to extract the part of the date (day, month, year) that you want to display but the parts you don't display are still stored in the DATE column whether you display them or not.

                        So if you are only interested in storing a particular year value you must still store a day and a month to make it a proper date.
                        • 39. Re: Load data from excel to Oracle Tables
                          969952
                          Hi,

                          I have written a PL/BLOCK as follows but getting the below error
                          ORA-06550: line 11, column 3:
                          PL/SQL: ORA-00933: SQL command not properly ended
                          ORA-06550: line 9, column 1:
                          PL/SQL: SQL Statement ignored
                          06550. 00000 -  "line %s, column %s:\n%s"
                          *Cause:    Usually a PL/SQL compilation error.
                          *Action:
                          DECLARE
                          CURSOR C1 is
                            SELECT to_char(sysdate,'YYYY') Joined_YEAR, to_number(to_char(sysdate,'MM')-1) joined_MONTH 
                               FROM TEMP1
                          
                          BEGIN
                          FOR  i in C1
                          LOOP
                          UPDATE TEMP1  a
                            SET  a.JOINED_YEAR = i.JOINED_YEAR
                            AND  a.JOINED_MTH  = i.JOINED_MONTH
                            WHERE  a.JOINED_YEAR is NULL and a.JOINED_MONTH is NULL ;
                          --COMMIT;
                          END LOOP;
                          END;
                          so here my intention is to update the table TEMP1 as JOINED_YEAR=CURRENT_YEAR and JOINED_MONTH=CURRENT_MONTH - 1( i.e. 11 -1 = 10)



                          Please have a look.

                          Thanks.
                          • 40. Re: Load data from excel to Oracle Tables
                            sb92075
                            966949 wrote:
                            Hi,

                            I have written a PL/BLOCK as follows but getting the below error
                            ORA-06550: line 11, column 3:
                            PL/SQL: ORA-00933: SQL command not properly ended
                            ORA-06550: line 9, column 1:
                            PL/SQL: SQL Statement ignored
                            06550. 00000 -  "line %s, column %s:\n%s"
                            *Cause:    Usually a PL/SQL compilation error.
                            *Action:
                            DECLARE
                            CURSOR C1 is
                            SELECT to_char(sysdate,'YYYY') Joined_YEAR, to_number(to_char(sysdate,'MM')-1) joined_MONTH 
                            FROM TEMP1
                            
                            BEGIN
                            FOR  i in C1
                            LOOP
                            UPDATE TEMP1  a
                            SET  a.JOINED_YEAR = i.JOINED_YEAR
                            AND  a.JOINED_MTH  = i.JOINED_MONTH
                            WHERE  a.JOINED_YEAR is NULL and a.JOINED_MONTH is NULL ;
                            --COMMIT;
                            END LOOP;
                            END;
                            so here my intention is to update the table TEMP1 as JOINED_YEAR=CURRENT_YEAR and JOINED_MONTH=CURRENT_MONTH - 1( i.e. 11 -1 = 10)



                            Please have a look.

                            Thanks.
                            FROM TEMP1
                            not as above but as below

                            FROM TEMP1;
                            • 41. Re: Load data from excel to Oracle Tables
                              969952
                              Typo mistake here. But I mentioned FROM TEMP1;

                              Edited by: 966949 on Nov 15, 2012 2:53 PM
                              • 42. Re: Load data from excel to Oracle Tables
                                sb92075
                                966949 wrote:
                                Typo mistake here. But I mentioned FROM TEMP1;

                                Edited by: 966949 on Nov 15, 2012 2:53 PM
                                submit Bug Report that COPY & PASTE are broken.

                                http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_10007.htm#i2112182
                                • 43. Re: Load data from excel to Oracle Tables
                                  rp0428
                                  Well now you should understand why everyone is complaining about what you are doing. You posted this table DDL
                                  create table t1
                                     (
                                       no  number,
                                       name varchar2(20),
                                       joindt  date,
                                       joined_month date
                                       sal number(8,2)
                                  )
                                  But now you post code that tries to update a different table with different column names
                                  UPDATE TEMP1  a
                                    SET  a.JOINED_YEAR = i.JOINED_YEAR
                                    AND  a.JOINED_MTH  = i.JOINED_MONTH
                                    WHERE  a.JOINED_YEAR is NULL and a.JOINED_MONTH is NULL ;
                                  The table DDL has no JOINED_YEAR column; it ia JOINDT and is a DATE. So you can't put a year into it; you have to put a DATE into it. The date can have the year you want but it has to be a DATE and a DATE also includes a day and a month.

                                  This code produces a string NOT a date
                                  to_char(sysdate,'YYYY') Joined_YEAR
                                  • 44. Re: Load data from excel to Oracle Tables
                                    969952
                                    Here am posting the actual structure now.
                                    create table temp1
                                    (
                                    no nuumber(8),
                                    name varchar2(20),
                                    joined_year date,
                                    joined month date
                                    sal number(8,2)
                                    )
                                    this is the actual structure and in pl/sql block copied MTH instead of Month.

                                    as you told to_char(sysdate,'YYYY') is a string. can you please let me know how can I load only YEAR in JOINED_YEAR table which is having all NULL values.

                                    Thanks.