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
  • 60. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi All,

    Thank you very much for quick responses and am getting food idea in Oracle now.

    As you suggested Have downloaded the data into a table. so now the table 'A' is having data and the structure as follows.
    create table A
    (
    eno number,
    ename varchar2(20),
    sal number(4),
    join_year number,
    join_month number,
    finance_year number);
    by using SQL *loader have downloaded the data for
    eno number,
    ename varchar2(20),
    sal number(4)

    so here 100 records downloaded.

    and by using the below pl/sql block i am trying to insert
    join_year number,
    join_month number,
    finance_year number
    BEGIN
      DBMS_OUTPUT.ENABLE;
      
    FOR i in (SELECT join_YEAR,join_month,FInance_YEAR from A) 
    LOOP
         INSERT INTO A ( join_YEAR,join_month,FInance_YEAR)
      SELECT to_number(to_char(sysdate,'YYYY'))
           , CASE when to_number(to_char(sysdate,'MM')) = 1 then 12 
              ELSE to_number(to_char(sysdate,'MM'))-1 end
              ,to_number(to_char(sysdate,'YYYY'))
       FROM dual;
    END LOOP;
    END;
    COMMIT;
    if I execute this block it's completing successfully and inserting the data for these columns after 100 records and showing NULL from101 for remaining columns.

    Am i doing any mistake in the above process? and let me know is it possible to write the above whole part in a single PL/SQL procedure/Fucntion/...

    Thanks.
  • 61. Re: Load data from excel to Oracle Tables
    SomeoneElse Guru
    Currently Being Moderated
    You just want to insert the same row over and over (for as many rows are in table A)?

    Why are you selecting the columns from table A in your cursor? You're not referencing the i cursor columns anywhere in your loop.

    For that matter, why a loop?

    Wait...what?
  • 62. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    because in Table A, mentioned 3 columns having many records so I want to update those records.because if I write Update <table_name> set <column_name>=2012 it will reflect for all the columns. we need to enter the values for every year and every time. if we are trying to write a query it will show us single row query can't update multiples. correct me if am wrong.

    so I am trying to keep the code in LOOP so it will search for each and every record and it will update the values which we defined in the code ( hopefully).

    here am facing one more problem is data already loaded for some records ,now I want to update the values for these columns which are having nulls. but it's gain loading the data and giving nulls to the remaining columns except these 3 columns. if already 100 records are existed , once I execute this pl/sql block and again it is adding 100 records so total 200. but i don't want to see 200 records and want to see 100 records with updated joined_year,joined_month and finance_year.

    Thanks.

    Edited by: 966949 on Nov 16, 2012 3:19 PM
  • 63. Re: Load data from excel to Oracle Tables
    SomeoneElse Guru
    Currently Being Moderated
    now I want to update the values...
    You said you want to update so why are you doing an insert?
  • 64. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    By mistake I entered Insert instead of UPDATE.
  • 65. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    966949 wrote:
    By mistake I entered Insert instead of UPDATE.
    post correct & valid UPDATE statement
  • 66. Re: Load data from excel to Oracle Tables
    SomeoneElse Guru
    Currently Being Moderated
    It's time for you to close this thread (and possibly lock it, admins?)

    Your original post and subject line was about loading tables from excel. It looks like you've done that. Looking over the 60 posts (!), you've drifted aimlessly from topic to topic ignoring the questions asked by those trying to help you.

    If you still need help with something, open a new thread with clearly stated requirements. But keep in mind, this is not the place for you to learn the ultra basics of SQL or ask about simple syntax errors.

    (watch removed)
  • 67. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi SomeonElse,
    How can he open a new thread with clearly stated requirements?

    Look at this:
    966949 wrote:
    because in Table A, mentioned 3 columns having many records so I want to update those records.because if I write Update <table_name> set <column_name>=2012 it will reflect for all the columns. we need to enter the values for every year and every time. if we are trying to write a query it will show us single row query can't update multiples. correct me if am wrong.

    so I am trying to keep the code in LOOP so it will search for each and every record and it will update the values which we defined in the code ( hopefully).
    I may have my limits because English is not my mother tongue but are you able to understand what he mean? I have tried to help this guy several times and not only in this thread but he doesn't seem to be able to explain himself in a way that everybody can understand, beside ignoring suggestions and confusing people by jumping from one topic to another.

    Personally I don't have problems in answering even basic questions but this guy is able to create endless threads without being able to say what he wants clearly. He is one of the toughest I have met in this forum.

    Regards.
    Al
  • 68. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Here am posting what exactly I required.Please have a look.

    I have Temp table structure as follows
    EMPNO   ENAME    SAL     JOINED_YEAR   JOINED_MONTH    FINANCIAL_YEAR     JID
    --------------------------------------------------------------------------------------------------------------------------------
    1              A         1000      (NULL)           (NULL)                   (NULL)                (NULL)
    2              B          2000     (NULL)           (NULL)                   (NULL)                 (NULL)
    3              c          3000      (NULL)           (NULL)                   (NULL)                (NULL)
    ....
    ..
    ..
    {CODE}
    
    Staging Table :
    SEMPNO SENAME SSAL SJOINED_YEAR SJOINED_MONTH SFINANCIAL_YEAR SID
    -----------------------------------------------------------------------------------------------------------------------------------------
    1 A 1000 2011 10 2011 123
    2 B 2000 2011 10 2011 245
    3 C 3000 2011 10 2011 768
    ....
    ..
    ..
    Regional Table:
    ID USerName CON
    -------------------------------------------
    908 HYD UDF
    089 KKKG RDT
    123 CHN BHY
    245 GYF IJY
    ....
    ...
    i have created TEMP table as follows
    CREATE table TEMP
    (
    EMPNO NUMBER,
    ENAME VARCHAR2(20),
    SAL NUMBER,
    JOINED_year NUMBER,
    JOINED_MONTH NUMBER,
    Financial_year NUMBER,
    JID NUMBER
    );
    by using sql*loader for ENO,ENAME,SAL fields retrieved data from some excel( converted into .CSV format) spread sheet.
    
    Now the TEMP table is having 100 records.
    
    Requirement 1:
    ---------------------
    Now i want to update the TEMP table as
                           Joined_year = Current Year  ii.e. (to_char(sysdate,'YYYY'))
                           Joined_month = current month - 1 ( 11-1=10)   (to_number_to_char(sysdate,'MM')-1)
                           financial year = current year  (i.e.  to_char(sysdate,'YYYY') )
    for above 3 columns where these 3 columns are NULL in TEMP table.
    
    2, Need to populate JID by using Staging Regional tables where Staging.SID= Regional.ID. and load it into TEMP table.
    
    Please share your ideas.
    
    Edited by: 966949 on Nov 17, 2012 1:29 PM
    
    Edited by: 966949 on Nov 17, 2012 1:32 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 69. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    Please stop this threadl!!!
    This topic is not anymore the initial topic and after more than 60 replies you still are not able to close it. The initial topic was loading the data from Excel to Oracle tables. Now this is a different thread and anytime you are adding new requests (JID now).

    Open a new thread posting the following information:

    a) table structure of all tables: you need to post working create table statements for all tables.
    b) insert statements: you don't have to post the output of a select but the insert statements to have data into your tables.
    c) expected output for the data you have posted: expected output is not what you have posted. You need to post data.
    d) enclose your code between two lines starting with {noformat}
    {noformat} (note that is lowercase) and check your message before posting by clicking on the preview tab. You have not been able to format decently your message.
    
    I will show here below ONCE FOR ALL an example about how a correct message should be posted:
    
    Table structure:
    CREATE TABLE temp
    (
    empno NUMBER
    , ename VARCHAR2(20)
    , sal NUMBER
    , joined_year NUMBER
    , joined_month NUMBER
    , financial_year NUMBER
    , jid NUMBER
    );

    CREATE TABLE staging_table
    (
    sempno NUMBER
    , sename VARCHAR2(20)
    , ssal NUMBER
    , sjoined_year NUMBER
    , sjoined_month NUMBER
    , sfinancial_year NUMBER
    , sid NUMBER
    );

    CREATE TABLE regional_table
    (
    id NUMBER
    , username VARCHAR2(20)
    , con VARCHAR2(20)
    );
    Sample data:
    INSERT INTO temp ({columns}) VALUES({values});
    INSERT INTO temp ({columns}) VALUES({values});
    ...

    INSERT INTO staging_table({columns}) VALUES({values});
    ...

    INSERT INTO regional_table({columns}) VALUES({values});
    ....
    Logic:
    1) Now i want to update the TEMP table as
    - joined_year = Current Year (from SYSDATE)
    - joined_month = current month - 1 ( 11-1=10)   (from SYSDATE)
    - financial_year = current year  (from SYSDATE)
    Note that I want to update these 3 columns when they are NULL
    
    2)Need to populate JID by using Staging Regional tables where Staging.SID= Regional.ID. and load it into TEMP table.
    
    Expected output (this is important so you show what you want to have in the output):
    EMPNO ENAME SAL JOINED_YEAR JOINED_MONTH FINANCIAL_YEAR JID
    ----- --------- ------- ------------- --------------- ------------------ -----
    1 A 1000 2012 10 2012 123
    2 B 2000 2012 10 2012 245
    3 C 3000 2012 10 2012 768
    I hope you have understood how to post correctly a question after my example.
    
    Please mark this thread as answered and open a new one posting information as I suggested for the new topic.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 70. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi All. thanks a lot for your help and patience.

    Thanks.
1 2 3 4 5 Previous Next

Legend

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