This discussion is archived
1 2 3 4 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 9:33 AM by 969952 Go to original post RSS
  • 30. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    UPDATE table1
    SET sysyear = SYSDATE
    WHERE sysyear IS NULL;
  • 31. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points