1 2 3 4 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 11:33 AM by 969952 Go to original post RSS
      • 15. Re: Load data from excel to Oracle Tables
        969952
        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
          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
            >
            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
              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
                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
                  Guido S
                  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
                    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
                      Guido S
                      Sorry, but at least to me it is not clear what you're asking.
                      • 23. Re: Load data from excel to Oracle Tables
                        sb92075
                        How do I ask a question on the forums?
                        SQL and PL/SQL FAQ
                        • 24. Re: Load data from excel to Oracle Tables
                          AlbertoFaenza
                          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
                            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
                              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
                                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
                                  UPDATE table1
                                  SET sysyear = To_char(SYSDATE, 'YYYY')
                                  WHERE sysyear IS NULL;
                                  • 29. Re: Load data from excel to Oracle Tables
                                    969952
                                    Tried the same but got the below error message.
                                    SQL Error: ORA-01861: literal does not match format string
                                    Please have a look