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
  • 15. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Yes. Thanks a lot.i came to know my mistake and resolved it.... but now getting another issue

    have written below pl/sql block but while executing getting isssue.
    BEGIN
        FOR i in (SELECT * from EMP  WHERE SAL = 'N') 
         LOOP
           UPDATE EMP 
             set dept_id= i.dept_ID
              WHERE trim(upper(E_num)) = trim(upper(i.dep_num));
        END LOOP;
    COMMIT;
    end; 
    Error report:
    ORA-30657: operation not supported on external organized table
    ORA-06512: at line 4
    30657.0000 - "operation not supported on external organized table"
    *Cause:    User attempted on operation on an external table which is
    not supported.
    *Action:   Don't do that!
    what I have to to resolve this.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 16. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    966949 wrote:
    Yes. Thanks a lot.i came to know my mistake and resolved it.... but now getting another issue

    have written below pl/sql block but while executing getting isssue.
    BEGIN
    FOR i in (SELECT * from EMP  WHERE SAL = 'N') 
    LOOP
    UPDATE EMP 
    set dept_id= i.dept_ID
    WHERE trim(upper(E_num)) = trim(upper(i.dep_num));
    END LOOP;
    COMMIT;
    end; 
    Error report:
    ORA-30657: operation not supported on external organized table
    ORA-06512: at line 4
    30657.0000 - "operation not supported on external organized table"
    *Cause:    User attempted on operation on an external table which is
    not supported.
    *Action:   Don't do that!
    what I have to to resolve this.
    EXTERNAL TABLE is READ ONLY
  • 17. Re: Load data from excel to Oracle Tables
    rp0428 Guru
    Currently Being Moderated
    >
    Yes. Thanks a lot.i came to know my mistake and resolved it.... but now getting another issue
    >
    Open a new thread for your new issue. This question has nothing to do with the question in this thread.

    It even uses a table called 'emp' while this thread had a problem with a different table: FMS_ALL.TEMP_REAL_CNTR_ROW_MTH_INFO
  • 18. Re: Load data from excel to Oracle Tables
    sharavs Newbie
    Currently Being Moderated
    I m not using any querey but there is one simple method


    step1: get the input excel

    step2:create the table with the columns(same data type) what is present in the excel file.

    step3:in the sql developer tool click on the table name

    step4:u ill find the Action icon click on it

    step5:click the importdata and browse the excel sheet and open then proceed with the steps .

    step6: u ill get the table  with the content of excel file

    Edited by: sharavs on Nov 15, 2012 8:12 PM
  • 19. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi ,

    Thanks for your suggestions. I have done the following things

    1. Loaded the data using SQL* Loader
    2. Transfer the data using SPOOL

    I got stuck over here in the below query.

    I want to insert PAY_Year and pay_month as follows.

    pay_year=current year
    pay_month=current_month -1

    for retrieving this data have written the following query as suggested.
    select  to_char(SYSDATE,'mm')-1,TO_CHAR(SYSDATE,'YYYY') from dual
    have created one temporary table and now want to insert these two columns data

    like
    insert into temp( col1,col2) -- here col1 and col2 are defined as  DATE format and both are having NULL values.
     select select  to_char(SYSDATE,'mm')-1,TO_CHAR(SYSDATE,'YYYY') from dual;
    I am getting below error
    Error report:
    SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
    *Cause:    
    *Action: 
    Please have a look and give me suggestion.

    Thanks.

    Edited by: 966949 on Nov 15, 2012 5:59 AM

    Edited by: 966949 on Nov 15, 2012 6:07 AM
  • 20. Re: Load data from excel to Oracle Tables
    890823 Newbie
    Currently Being Moderated
    Errm... You defined the columns as DATEs, but try to insert some other rubbish. If you really need (for whatever reason) a 'month' column having the value '11', define it as a NUMBER. And then insert the data as that datatype, don't let Oracle do any implicit datatype conversions.
  • 21. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Hi,

    Can you please try the following query.

    The column COL1 is defined as Date data type. But I want to see the last month only.
    SELECT to_number(to_char(sysdate,'MM')-1)) from <table_name>;
    {code}
    
    it is giving the output as '10' for all the columns.
    
    so I want to insert this particular Month data into other table having the column values are NULL.
    
    Thanks.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 22. Re: Load data from excel to Oracle Tables
    890823 Newbie
    Currently Being Moderated
    Sorry, but at least to me it is not clear what you're asking.
  • 23. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 24. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:
    Hi,

    Can you please try the following query.
    Here you come. You manage to create a thread with 23 replies, in a similar way you have done for post {message:id=10684787} where after 27 replies you were not able to explain what you wanted.

    I suggest:

    a) As you have all your questions as unresolved, please close those that they are answered:
    Handle:      966949  
    Status Level:      Newbie
    Registered:      Oct 22, 2012
    Total Posts:      32
    Total Questions:      7 (7 unresolved) 
    b) I tell you once more but I'm sure you will not do it trying to drive crazy people reading your post: read SQL and PL/SQL FAQ and post sample data, table structure and expected output.

    This will make easier to understand your requirement and help you.

    Regards.
    Al
  • 25. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Al

    Thanks you all for your suggestions. I am facing the problem is,

    Can you please let me know how to replace a NULL with current YEAR?

    I have written a QUERY
    UPDATE TABLE1
      SET SYSYEAR =  --- ( SYSYEAR is having all NULLS)
                  CASE SYSYEAR
                     WHEN NULL THEN 'to_date(sysdate,'YYYY')'
                  ELSE
                    SYSYEAR
    am unable to update the data. Please help me out.

    Thanks.
  • 26. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    to_date(sysdate,'YYYY')
    SYSDATE is already a DATE datatype; you want as below

    to_char(sysdate,'YYYY')
  • 27. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    yeah. Here I want to replace NULL values with the SYSTEM year.

    as mentioned earlier the column which is having NULL values is defined as DATE data type and I want to update that column with system year.
  • 28. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    UPDATE table1
    SET sysyear = To_char(SYSDATE, 'YYYY')
    WHERE sysyear IS NULL;
  • 29. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Tried the same but got the below error message.
    SQL Error: ORA-01861: literal does not match format string
    Please have a look

Legend

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