6 Replies Latest reply: Jan 5, 2012 7:06 AM by 908634 RSS

    oracle export sql inserts how to commit every N rows

    908634
      I have a table with 5,000,000 records and I want select several fields out and insert to a MYSQL database table . So I concat a SQL to insert . But there are too many records MYSQL can't import one time (The file is more than 700M). So I should commit every N rows . I want to find a good method to solve this problem .
      I use PL/SQL to execute my SQL statement . I also find when I export a table using PL/SQL I can select commit every N rows . I think if I know how PL/SQL implement it I will know how to do .
      Any answer is appreciative .
        • 1. Re: oracle export sql inserts how to commit every N rows
          Mkirtley-Oracle
          Hi,
          What are you using for the connection between Oracle and MySQL ? Is the Database Gateway for ODBC (DG4ODBC) ? If not, then what are you using ?
          If you are using PL/SQL for the insert statements then you could add a counter for every insert statement then issue a commit when the counter reaches a certain value.
          If this doesn't make sense then post the PL/SQL you are using and we maybe able to help further.

          Regards,
          Mike
          • 2. Re: oracle export sql inserts how to commit every N rows
            908634
            Hi,
            There is no connection between Oracle and MySQL . I want to use PL/SQL to connect to Oracle and select & concat out lots of insert statements to a SQL file and then import the SQL file to MySQL by mysql command .
            Yes , I am using PL/SQL and it has a Export Tables tool . But I find it can only export all the table fields and the insert SQL can not be defined .
            Of cause , I can modify the SQL file by Unix command , but I wonder if I can get a better method .
            Thanks.
            Regards.

            WangHao
            • 3. Re: oracle export sql inserts how to commit every N rows
              Mkirtley-Oracle
              WangHao,
              Thanks for the clarification.
              As this is a problem using PL/SQL itself it would be better to ask the question in the PL/SQL forum here -

              PL/SQL

              They will have more experience of using PL/SQL itself to do what you want.

              Regards,
              Mike
              • 4. Re: oracle export sql inserts how to commit every N rows
                908634
                Hi Mike,

                Thanks for your patient reply .
                Do you have a good method to export data with commit every N rows in SQLPLUS ? I think it's better to implement in SQLPLUS .
                Thanks .

                Regards,
                WangHao
                • 5. Re: oracle export sql inserts how to commit every N rows
                  Mkirtley-Oracle
                  WangHao,
                  If I understand correctly this is what you want to do -

                  - create insert statements from data in Oracle tables
                  - run these insert statements directly in MySQL using the MySQL equivalent of SQLPLUS
                  - you want the insert statements to have a commit after 'x' number of rows

                  If this is the case I still think the best option is to raise it in the SQL-PL/SQL forum.
                  The issue is creating the insert statements and adding an 'commit' statement after a number of rows.
                  I would have thought depending how you write the insert statements it should be possible to have some of counter and add a 'commit' statement after a number of rows.
                  Try the SQL-PL/SQL forum for further advice on the best way to do this.

                  Regards,
                  Mike
                  • 6. Re: oracle export sql inserts how to commit every N rows
                    908634
                    Hi Mike,

                    Thanks for your generous answers . Maybe Oracle or SQL is not good at this work .
                    I think it's also a good method to finish the work using Unix command .
                    I will also try to find out how the PL/SQL implements it .
                    Thanks.

                    Best Regards,
                    WangHao